Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
667 views
in Technique[技术] by (71.8m points)

c# - Escaping special characters in a SQL LIKE statement using sql parameters

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, NEW, N%EW, N"EW, N'EW). Specifying , ", and ' as the input works fine(considers them as literals).

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...