Escaping special characters in a SQL LIKE statement using sql parameters

I have a table containing products. I need to make a query finding all the matching results to an user-input value. I am using SqlParameter for the insertion of the inputs.

SqlCommand findProcutsByPattern = new SqlCommand(
    "SELECT *" +
    " FROM [Products]" +
    " WHERE ProductName LIKE @pattern", connection);

findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + pattern + '%');

The problem comes when the user input string contains '_' or '%' as they're being interpreted as special characters. In the other hand, considering this:

Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code.

I shouldn't have such problems. Do I need to replace/escape all the '_' and '%' in the input string or is there a more elegant solution. I want the input to be considered as a literal.

I have a few records in the table which include special characters in the name(N_EW, N\EW, N%EW, N"EW, N'EW). Specifying \, ", and ' as the input works fine(considers them as literals).

Solution

You have two options:

enclose them in [ and ]. So:
```
where pattern like '[%]'
```
Looks for the percentage character. Full list of characters to escape - '_', '%', '[', ']' with corresponding replacements '[_]', '[%]', '[[]', '[]]'. Sample code can be found in Escaping the escape character does not work – SQL LIKE Operator
use an escape character that is unlikely to be in the string, such as a backtick:
```
where pattern like '`%' escape '`'
```
(See the syntax on MSDN - LIKE (Transact-SQL).)

In both cases, I would suggest that you make the substitution in the application layer, but you can also do it in SQL if you really want:

where pattern like replace(@pattern, '%', '[%]')

And, giving the end-user access to wildcards may be a good thing in terms of the user interface.

Note: there are couple more special characters '-' and '^' in the LIKE query, but they don't need to be escaped if you are already escaping '[' and ']'.