-1

I am using trying to use the escape character for " to construct an SQL string like follows:

selSQL += " WHERE [" + sFieldArr[i, 0] + "]" + op + "\"" + sFieldArr[i, 1] + "\"";

but it keeps producing the string incorrectly, for example

WHERE [State]=\"Illinois\"

How should I be escaping the double quotes here?

Update: As pointed out in the comments, the escape characters were actually working correctly, they just appeared to be working incorrectly in the debugger. The problem was that SQL Server was interpreting this as a column name - see this article: https://support.microsoft.com/en-us/kb/222664

Solution is to use two single quotes instead of one double quotes.

dashnick
  • 2,020
  • 19
  • 34
  • for SQL query, best is to use parameterized query. You can try using @ sign to do it without parameterized query, nevertheless. – Ian Feb 17 '16 at 02:19
  • like `@""""`? That actually does the same thing... – dashnick Feb 17 '16 at 02:20
  • I'd need to see more of your code example too. When I take your string and build it in C# studio 2015, it's fine, you've already properly escaped the double quotes. – Baronz Feb 17 '16 at 02:26
  • 1
    How do you actually check that it produces the string incorrectly? From the debugger in Visual Studio? Or do you actually execute the query? This is because debugger in VS will always show \" – Ian Feb 17 '16 at 02:27
  • Well, it's actually saying `invalid column name "Illinois"`, and then when I look at the string in the watch window this is what it looks like.... – dashnick Feb 17 '16 at 02:29
  • @dashnick the watch window in VS is always showing \" instead of ", more important question here may be: what is the database you use? microsoftSQL? Oracle? These may affect how Column name is written. Also, it is the *Column* name. Is it not supposed to be *State* rather than *Illinois*? – Ian Feb 17 '16 at 02:47
  • SQL Server 2014. Right, 'State' is the column name.. this doesn't happen if I use single quotes like "'". Problem with that is that if single quotes ever appear in the state name (or whatever the field is), it gives me problems... – dashnick Feb 17 '16 at 02:49
  • Please dont build query strings like this. Being in that habit is the number one cause of injection defects. – Eric Lippert Feb 17 '16 at 04:04
  • Don't worry - the selections are not based on user input. – dashnick Feb 17 '16 at 04:05
  • 1
    I would emend your statement: the selects are not based on user input -- yet. Write code to be secure by default and secure by design. – Eric Lippert Feb 17 '16 at 23:47
  • I don't even think it is possible to parameterize field names... – dashnick Feb 18 '16 at 00:12

2 Answers2

0

As stated here you can use the at sign with two double quotes:

selSQL += " WHERE [" + sFieldArr[i, 0] + "]" + op + @"""" + sFieldArr[i, 1] + @"""";

Output:

WHERE [State]="Illinois"
Community
  • 1
  • 1
McAngus
  • 1,826
  • 18
  • 34
0

You can escape it by using verbatim string literal.

@" WHERE [" + sFieldArr[i, 0] + "]" + op + @"""" + sFieldArr[i, 1] + @"""";

or if you want to do regular way,

" WHERE [" + sFieldArr[i, 0] + "]" + op + "\""" + sFieldArr[i, 1] + "\""";

As you stated for your sFieldArr value, they should return

WHERE [State]="Illinois"

But in your case, I suggest do the following

selSQL += String.Format(@" WHERE [{0}] {1} ""{2}""", sFieldArr[i, 0], op, sFieldArr[i, 1]);
choz
  • 17,242
  • 4
  • 53
  • 73