c#: what is the proper way to perform sql LIKE searches using any characters

Question

I'm building (c#) sql select commands strings on the fly using LIKE %somestring% conditions. In my search strings I want to be able to handle any character found on a standard PC (US) keyboard (including ~ ! @ # % etc., alt-special chars not required but would be nice to have). I know that single quotes need to be doubled up and perhaps double quotes as well. What other string fixes might be required to ensure correct syntax?

Answer 1

No fixes required:

SqlCommand cmd = new SqlCommand("select * from Foo where Bar like @p", connection);

SqlParameter p  = new SqlParameter();
param.ParameterName = "@p";
param.Value = pattern;

cmd.Parameters.Add(param);

Answer 2

Use the quoting function of your SQL access library to defuse strings before passing them onto the SQL parser. This might happen automatically if you use parametrised statements.

Answer 3

See Anton Gogolev's answer for a nice code sample.

You only need to escape single quotes in SQL syntax anyhting inbetween 'single quotes' will be considered the string.

Have you considered using parameters instead though? It's a lot better and you don't have to escape single quotes.

SELECT * FROM MyTable WHERE Name LIKE @p0

If the parameter @p0 starts with %SomeText it will be the same as looking for a string which starts with SomeText, %SomeText% will be the same as contains SomeText and finally SomeText% means ends with.

No further escaping is necessary.

Answer 4

Apart from doubling up single quotes (or using a parametrised query), will the user know that "_" and "%" are wildcards (any-character and zero-or-more-any-characters respectively), and that "[...]" creates a closure?

To escape those characters there are two routes

WHERE Foo LIKE '%xxx\%yyy%' ESCAPE '\'

or WHERE Foo LIKE '%xxx[%]yyy%'

the second uses the side effect of creating a closure, and avoids having to use the ESCAPE (which in itself needs some thought to choose a character that does not conflict with the rest of the string, or is itself escaped where it occurs)

Note that using 'LIKE %somestring%' will usually require a table scan, and may therefore lead to performance problems - e.g. if you have millions of rows to be checked.

In SQL Server you can use sp_ExecuteSQL if you are generating WHERE clauses by string-concatenation - so that only fields that the user specifies criteria for are included. sp_ExecuteSQL will cache the query and, most times, improve performance. (please ask if that it relevant and you need help)