SQLCommand with Parameter no longer updates my table

Question

I am modifying a previous developers code and found that he was not using parameters in his update statements. I went to modify it with parameters to make it safer against injection and now it won't update at all. It did update with the old code. The code steps through just fine. It gives no errors but does not update the table. If the values are shown as

csharpa="Hello"
csharpb="Text"
csharpc="1"

during debugging. Checking the table for

select * from table where sqlb="Text" and sqlc="1"

it still has the previous value in

sqla="Goodbye"

not updated to Hello as I would expect.

Code before:

string q = "update table set sqla='" + 
    csharpa + "' where sqlb='" + csharpb + 
    "' and sqlc=" + (string)HttpContext.Current.Session["csharpc"];
SqlConnection conn = new SqlConnection(connstr);
SqlCommand sda = new SqlCommand(q, conn);
conn.Open();
sda.ExecuteNonQuery();
conn.Close();

Code After:

string q = "update table set sqla='@para' where sqlb='@parb' and sqlc=@parc";
SqlConnection conn = new SqlConnection(connstr);
SqlCommand sda = new SqlCommand(q, conn);
sda.Parameters.AddWithValue("@para", csharpa);
sda.Parameters.AddWithValue("@parb", csharpb);
sda.Parameters.AddWithValue("@parc", (string)HttpContext.Current.Session["csharpc"]);

score 3 · Accepted Answer · answered Dec 17 '13 at 19:51

3

Remove the quotes:

string q = "update table set sqla=@para where sqlb=@parb and sqlc=@parc";

Your DB will automatically know if the field is a string, so you don't need to enclose anything in quotes.

answered Dec 17 '13 at 19:51

System Down

6,192
1
30
34

That worked however, I found one gotcha. If I type in one of those things that can cause injection attacks such as a single apostrophe ' it doesn't update the table. Are the parameters not smart enough to escape these? Do I still have to run it through some escape sequence first? – John Wesley Gordon Dec 17 '13 at 20:07
@JohnWesleyGordon - You shouldn't have to escape quotes. Perhaps something else is the problem? I would try to execute a SELECT statement with the same WHERE clause and see if it returns anything. – System Down Dec 17 '13 at 20:11
It was my own stupidity. I was on the old version when I tested the apostrophe. I verified it does work with the parameters. Thanks for all the help. – John Wesley Gordon Dec 18 '13 at 14:03
@JohnWesleyGordon - Glad everything worked out. Cheers! – System Down Dec 18 '13 at 17:21

score 2 · Answer 2 · answered Dec 17 '13 at 19:53

You don't need the single quotes in your parameterized statement.

Change this line:

string q = "update table set sqla='@para' where sqlb='@parb' and sqlc=@parc";

to this:

string q = "update table set sqla=@para where sqlb=@parb and sqlc=@parc";

Your sda.Parameters.AddWithValue calls will evaluate that the values are strings, and pass them correctly without you having to put single quotes around the parameters.

SQLCommand with Parameter no longer updates my table

2 Answers2