Setting value of paramter containing " ' " (apostrophe) used in LIKE query

Question

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. Any thoughts would be greatly appreciated.

Answer 1

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

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

Answer 2

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.

Answer 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.