CODEWITHSUNDEEP
c#.netsqlt-sql
k1f1
k1f1

Reputation: 433

Escape single apostrophe when using SELECT from SQL, into an array in C# then UPDATE

In my C# code I read in all id's of a table into a string array ids[i], then update a different column in the table WHERE user_id = id[i].

The problem occurs when the id being read(also passed back into the UPDATE) contains an apostrophe - '

       while (rdr.Read()
       {
          ids[i] = rdr.GetValue(0).ToString().Trim();
          ids[i].Replace("'", "''");
          ....

I have also tried ids[i].Replace("'", "\'");
and ids[i].Replace("'", "-"); but I'm sure this will throw off my WHERE as it will look for user_id "O-Test" as opposed to "O'Test".

My UPDATE SQL is along the lines of: 

 UPDATE [User]
 SET first_name = '{1}'
 WHERE [user_id] = '{2}'

Any ideas?

Thanks.

Upvotes: 0

Views: 428

Answers (1)

Habib
Habib

Reputation: 223287

you need to assign it back to ids[i]

ids[i] = ids[i].Replace("'", "''");

ids[i].Replace("'", "''"); this will only create a string in memory and discard it, you need to assign it back to ids[i] in your while loop.

So your code should be: 

while (rdr.Read()
       {
          ids[i] = rdr.GetValue(0).ToString().Trim();
          ids[i] = ids[i].Replace("'", "''");
          ....

Upvotes: 3

Related Questions