Escaping special characters in a SQL LIKE statement using sql parameters

Question

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

Your query is concatenated to `SELECT *FROM [Products]WHERE ProductName LIKE @pattern` You are missing some spaces — juergen d, Sep 06 '14 at 18:55
Don't worry about the spaces, the query works just fine. Except the special characters thing.. : ) — zhulien, Sep 06 '14 at 19:06
This specific query does work. But if you replace `*` with a column name then it won't. But that is just a hint for future queries — juergen d, Sep 06 '14 at 20:05
Oh, yes, that's correct. I am aware of that, this was just a sample code explaining the problem. Thank you! — zhulien, Sep 09 '14 at 08:34

score 14 · Accepted Answer · edited Nov 22 '17 at 16:10

14

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 ']'.

edited Nov 22 '17 at 16:10

Alexei Levenkov

98,904
14
127
179

answered Sep 06 '14 at 18:55

Gordon Linoff

1,242,037
58
646
786

replacing with brackets is not working for brackets. Can u help. http://rextester.com/MJST70317 – Anonymous Creator Jun 11 '18 at 14:17
@HardikViradiya . . . I didn't say that it worked for the square braces, but it is easy enough to handle them, such as `col1 LIKE '%$[$_$]%' ESCAPE '$'`. – Gordon Linoff Jun 12 '18 at 01:49
Hmm. It came with @alexei Levenkov's changes. as mentioned in "'_', '%', '[', ']' with corresponding replacements '[_]', '[%]', '[[]', '[]]'" in answer. – Anonymous Creator Jun 12 '18 at 03:45

score 2 · Answer 2 · answered Sep 06 '14 at 18:56

You can do it like this: specify an explicit escape character in your SQL string, and then place that escape in front of all % and _ characters inside the string the user enters:

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

When you set the parameter, replace all instances of _ and % with __ and _%:

var escapedPattern = Regex.Replace(pattern, "[%_]", "_$0");

Demo.

score 1 · Answer 3 · edited May 23 '17 at 11:46

Generally speaking, manually escaping values in SQL is considered bad practice as using parameters is the preferred (and more secure) solution.

In your example, however, you are already using parameters and you want to use the LIKE operator, so manually escaping the characters should be fine. Don't forget escaping the escape characters, too - see https://stackoverflow.com/a/13861567/232175 for some code.

score 1 · Answer 4 · edited Aug 20 '21 at 13:33

So, basically, I manually escaped(replaced) all the wildcard symbols, which seems to work fine now. Here is the final code.

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

        string patternEscaped = pattern.Replace("[", "[[]");
        patternEscaped = patternEscaped.Replace("_", "[_]");
        patternEscaped = patternEscaped.Replace("%", "[%]");

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

Thank you for the support!

UPDATE: I see...

escape_character Is a character that is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard. escape_character is a character expression that has no default and must evaluate to only one character.

So you still have to do the escaping yourself.

The above code won't work perfectly, since it will first replace "%" with "[%]", then it will replace [ with [[], resulting in the string "[[]%]". If you change the order of the .Replace calls, put the [ replacement first, it would work better. — Adam B, Aug 24 '16 at 13:59

Escaping special characters in a SQL LIKE statement using sql parameters

4 Answers4

Linked

Related