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

Cofield

Your query is concatenated to SELECT *FROM [Products]WHERE ProductName LIKE @pattern You are missing some spaces – Amber

Don't worry about the spaces, the query works just fine. Except the special characters thing.. : ) – Cofield

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 – Amber

Oh, yes, that's correct. I am aware of that, this was just a sample code explaining the problem. Thank you! – Cofield

Answer 1

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

Forewoman

replacing with brackets is not working for brackets. Can u help. rextester.com/MJST70317 – Ecclesiolatry

@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 '$'. – Forewoman

Hmm. It came with @alexei Levenkov's changes. as mentioned in "'', '%', '[', ']' with corresponding replacements '[]', '[%]', '[[]', '[]]'" in answer. – Ecclesiolatry

Answer 2

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://mcmap.net/q/219565/-escaping-the-escape-character-does-not-work-sql-like-operator for some code.

Bookrack

Answer 3

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.

Positronium

Answer 4

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.

Cofield

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. – Labannah