How do I escape special characters in user input for a SQL LIKE?

Take the following example (SQL Server 2008 - might work with more). You'll have to imagine @query being some parameter whose source is user input:

DECLARE @query varchar(100)
SET @query = 'less than 1% fat'

CREATE TABLE X ([A] VARCHAR(100))
INSERT X VALUES ('less than 1% fat')
INSERT X VALUES ('less than 1% of doctors recommend this - it''s full of fat!')开发者_如何学JAVA
SELECT * FROM X WHERE A LIKE '%' + @query + '%'
DROP TABLE X

The query states 'less than 1% fat', but we actually get more than we wanted:

less than 1% fat
less than 1% of doctors recommend this - it's full of fat!

To get the required behaviour, I change @query to 'less than 1[%] fat' - then only the first result is returned.

Is there a standard way to prepare strings for clauses which use LIKEs, or do I have to roll my own?

Instead of using LIKE you can use free-text search and the CONTAINS or FREETEXT predicates. LIKE with a leading wildcard ignores indexes and results in a full table scan while the full-text searches use existing free-text indexes to speed up the search. You will have to configure free text indexing before you can use it.

If you want to stick with LIKE the best solution would be to escape the string in your client code. T-SQL provides very limited string manipulation functionality and the REPLACE function doesn't even accept wildcards. You would have to nest multiple REPLACE statements to account for all wildcards used by LIKE.

You can combine that with the ESCAPE clause and use a rare character like §, ¶ or ¤ as an escape character if your search string may contain the [ or ] characters.

SQL Server allows you to specify an escape character, then you can just escape the query string before using it.

Sample from the MSDN page I linked:

SELECT c1 
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!';