2

I have the following query in ASP.NET/C# code which is failing to return any values using a parameter...

select * from MyTable where MyTable.name LIKE @search

I have tried the following query alternatives to set this parameter in SQL commands...

select * from MyTable where MyTable.name LIKE  %@search%
select * from MyTable where MyTable.name LIKE '%' + @search + '%'
select * from MyTable where MyTable.name LIKE '%@search%'

And through the api...

myCmd.Parameters.AddWithValue("@search", search);
myCmd.Parameters.AddWithValue("@search", "%" + search + "%");
myCmd.Parameters.AddWithValue("@search", "%'" + search + "'%");

None of those work.

The search parameter I am using has single quotes in its text which I think is making things even more awkward. I believe I am escaping the parameter correctly because if I construct a query which uses the value directly as opposed to through parameters like so...

select * from MyTable where MyTable.name LIKE '%MyValue''ToSearchForWith''Quotes%'

That works. From what I have seen all you need to do to have single quotes in your query is to double them up. I have not seen any errors so I am assuming I've got this correct. So worst case I have a solution but I would like to be setting the search value through the api as I believe this is better practice.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Rob Segal
  • 7,515
  • 12
  • 43
  • 72
  • @Rob Segal: Can you explain "None of those work"? It looks like the ones in the middle, respectively, are fine. Can it be there actually *is* no record that matches your test criterion? Or does it throw an error of some sort? – Tomalak May 13 '09 at 17:10
  • 1
    What is the value in the search variable? Did you pre-delimit the ' char as ''? – Jack Bolding May 13 '09 at 17:18

3 Answers3

6

I think the issue is that you're escaping the quotes in your search parameter, when the SQL parameter does that for you.

The percent signs should be inside the SQL Parameter value; your query just references the parameter plainly. The SQL should look like this:

select * from MyTable where MyTable.name LIKE @search

And the code should look like this:

string search = "MyValue'ToSearchForWith'Quotes";
myCmd.Parameters.AddWithValue("@search", "%" + search + "%");

Note that search is the original value, not escaped.

bdukes
  • 152,002
  • 23
  • 148
  • 175
  • I think you get the code right, but the SQL should be LIKE '%' + @search + '%' – Andomar May 13 '09 at 17:03
  • This was exactly it. I was escaping the single quote parameter value before inserting that value through the API. Note that for the query I didn't need the %'s around the @search parameter as they are being included in the call to AddWithValue. Thanks very much for the reply though. Works great now. – Rob Segal May 13 '09 at 17:15
3

On the SQL side, this is correct:

select * from MyTable where MyTable.name LIKE '%' + @search + '%'

If the parameter was passed in from outside, it would not matter if it contained single quotes.

On the API side, this should be correct:

myCmd.Parameters.AddWithValue("@search", "%" + search + "%");

The AddWithValue() method does all the necessary escaping for you, no need to interfere.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
0

within t-sql you escape ' by using two: ''

@query=' this doesn''t cause a problem'

KM.
  • 101,727
  • 34
  • 178
  • 212