Insert SQL Statement into SQL Server column

Question

I've inherited an application with a lot of ADO work in it, but the insert/update helper method that was written returns void. We've also been experiencing a lot of issues with data updates/inserts not actually happening. My goal is to update all of them to check rows affected and depending on the results, act accordingly, but for the time being of finding what may be causing the issue, I wanted to log SQL statements that are called against the server and the number of rows affected by the statement.

This is the statement I'm attempting:

 SqlCommand com = new SqlCommand(String.Format("'INSERT INTO
     SqlUpdateInsertHistory(Statement, AffectedRows) VALUES (''{0}'', {1});'",
     statement.Replace("'", "''"), rows), con);

but it seems to constantly break somewhere in the sql that is being passed in (some cases on single quotes, but I imagine there are other characters that could cause it as well.

Is there a safe way to prep a statement string to be inserted?

Mike Perrenoud · Accepted Answer · 2013-11-01 18:54:35Z

2

I just can't rightly propose a solution to this question without totally modifying what you're doing. You're currently wide open to SQL Injection. Even if this is a local application, practice how you want to play.

using (SqlCommand com = new SqlCommand("INSERT INTO SqlUpdateInsertHistory(Statement, AffectedRows) VALUES (@Statement, @AffectedRows)", con))
{
    com.Parameters.AddWithValue("@Statement", statement);
    com.Parameters.AddWithValue("@AffectedRows", rows);

    com.ExecuteNonQuery();
}

answered Nov 1, 2013 at 18:54

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Over a year ago

Good point. I was more in a rush to get the thing working that I kind of forgot to add it in that manner. I found that the real issue is that the dev db vs the prod db were mismatched on structure so I had to add a copy to dev db.

Community · Accepted Answer · 2017-05-23 11:50:06Z

0

Have you tried SQL Server Profiler? It's already been written and logs queries, etc.

Someone else tried this and got a lot of decent answers here.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Nov 1, 2013 at 18:49

djdanlib

22.8k1 gold badge23 silver badges29 bronze badges

Collectives™ on Stack Overflow

Insert SQL Statement into SQL Server column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related