使用sql参数在SQL LIKE语句中转义特殊字符 [英] Escaping special characters in a SQL LIKE statement using sql parameters

问题描述

我有一个包含产品的表。我需要查询所有匹配结果的用户输入值。我正在使用 SqlParameter 来插入输入。

  SqlCommand findProcutsByPattern = new SqlCommand（
SELECT *+ 
FROM [Products]+ 
WHERE ProductName LIKE @pattern，connection）; 
 
 findProcutsByPattern.Parameters.AddWithValue（@ pattern，'％'+ pattern +'％'）; 
  

当用户输入字符串包含_或％时，问题出现在解释为特殊字符。另一方面，考虑到这一点：

命令对象使用参数将值传递到SQL语句或
存储过程，提供类型检查和验证。与
命令文本不同，参数输入被视为文字值，而不是
可执行代码。

I不应该有这样的问题。我需要更换/转义输入字符串中的所有_和％，还是有更优雅的解决方案。我希望输入被视为文字。

我在表中有几条记录，其中包括名称中的特殊字符（N_EW，N \EW，N ％EW，NEW，N'EW）指定 \ ，和 ，因为输入正常文字）。

解决方案

你有两个选择。更容易（在我看来）将它们包含在 [和] 中。所以：

 其中的模式像'[％]'
  

查找百分比字符。

第二个是使用不太可能在字符串中的转义字符，例如反驳：

 其中的模式像''％'escape'`'
  

（请参阅语法这里。）

在这两种情况下，我建议您在应用程序层进行替换，但您也可以在SQL中，如果你真的想要：

 其中的模式像替换（@pattern，'％'，'[％]'）
  

而且，根据用户界面，给最终用户访问通配符可能是一件好事。

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

解决方案

You have two options. The easier (in my opinion) is to enclose them in [ and ]. So:

where pattern like '[%]'

Looks for the percentage character.

The second is to use an escape character that is unlikely to be in the string, such as a backtick:

where pattern like '`%' escape '`'

(See the syntax here.)

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.

这篇关于使用sql参数在SQL LIKE语句中转义特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！