5

The LIKE operator in a SQL server query can be very useful to match custom patterns. However sometimes the need raises to escape some characters or substrings from the pattern, such as ampersands '%', underscores '_', square brackets '[' and ']', etc.

Indeed I'm using parametrized queries but it does not solve LIKE case because for example searching for _ would mean "any character".

What is the set of characters that must be considered while escaping such patterns? Can a C# function be provided to perform a safe escape?

Alexei Levenkov
  • 98,904
  • 14
  • 127
  • 179
Starnuto di topo
  • 3,215
  • 5
  • 32
  • 66
  • See https://stackoverflow.com/questions/19730941/how-to-use-wildcards-in-sql-query-with-parameters – IRQ Conflict Nov 22 '17 at 14:28
  • 1
    That question seems not to address the same problem: I would like to have a complete set of chars to escape.... – Starnuto di topo Nov 22 '17 at 14:31
  • "A group of double quotes" isn't a character. A double quote needs no escaping, in any case. A single quote does, but whether it occurs one time or many times is of no influence in how it's escaped, which is the same every time. – Jeroen Mostert Nov 22 '17 at 14:44
  • @Starnutoditopo Why? You shouldn't be escaping strings in arguments to LIKE predicates - you should be passing them in as proper parameters. Then you avoid any need to escape *and* you also tend to avoid SQL injection vulnerabilities. – Aaron Bertrand Nov 22 '17 at 14:44
  • 1
    @AaronBertrand: `LIKE` escaping is distinctly different from value escaping and can't be solved just by parameter passing. `DECLARE @p VARCHAR(10) = '[[]]'; SELECT 1 WHERE ']' LIKE @p` will return nothing; at the very least you need something like `DECLARE @p VARCHAR(10) = '[\[\]]'; SELECT 1 WHERE ']' LIKE @p ESCAPE '\'`, even if the argument is parameterized. – Jeroen Mostert Nov 22 '17 at 14:48
  • @JeroenMostert I was merely trying to steer the user away from building SQL queries by concatenating arguments to the WHERE clause. – Aaron Bertrand Nov 22 '17 at 14:55

2 Answers2

4

%, _, [, ], and ^ need to be escaped, and you will need to choose a suitable escape character, i.e. one that you aren't using elsewhere in your LIKE pattern.

Full description here LIKE (Transact-SQL)

I'm sure you could write a function in C# to do that.

Community
  • 1
  • 1
spodger
  • 1,668
  • 1
  • 12
  • 16
  • @Starnutoditopo [Seems to work fine for me](https://i.stack.imgur.com/k62eJ.png). – Aaron Bertrand Nov 22 '17 at 14:37
  • Of course, if you are constructing the sql string in C# then you will have to escape the double-quotes within your code, but you wouldn't in a SQL query window because the string delimiter is '. Not sure that's what you problem is, though. – spodger Nov 22 '17 at 14:41
  • You missed `-`, which needs to be escaped in `[a-z]`, and the escape character itself, which is whatever you pick with `ESCAPE` (by default, there is no escape character). – Jeroen Mostert Nov 22 '17 at 15:08
  • @JeroenMostert, `-` doesn't need to be escaped. Its usage inside square brackets, i.e. [a-e] is obvious and outside them it is treated as itself. However, you're right in that I didn't mention that you have to choose an escape character but that information is in the link. – spodger Nov 22 '17 at 15:13
  • Right -- if you're already escaping brackets, `-` will never end up in a situation where it needs to be escaped. If you're not escaping brackets, presumably you're not doing so because you want to give the user the ability to pass patterns, in which case they will take care of escaping. (Yes, the information about the escape character is in the link, but that of course is no reason not to put it in the answer.) – Jeroen Mostert Nov 22 '17 at 15:19
  • Ok, I'll update the answer. :-) – spodger Nov 22 '17 at 15:20
-1

See the existing stackoverflow answer here...

How to use wildcards in SQL query with parameters

DO use SQLParameter

DONT use stringbuilder or add strings together

Unless you understand SQL Injection Attacks https://en.wikipedia.org/wiki/SQL_injection

IRQ Conflict
  • 55
  • 5
  • This is completely unrelated to the question - while using parameterized queries is good practice it should be at most comment. – Alexei Levenkov Nov 22 '17 at 15:58