包含"0"的参数的设定值.'"LIKE查询中使用的(撇号) [英] Setting value of parameter containing " ' " (apostrophe) used in LIKE query

问题描述

我在ASP.NET/C#代码中有以下查询，该查询无法使用参数返回任何值...

I have the following query in ASP.NET/C# code which is failing to return any values using a parameter...

select * from MyTable where MyTable.name LIKE @search

我尝试了以下查询替代方法来在SQL命令中设置此参数...

I have tried the following query alternatives to set this parameter in SQL commands...

select * from MyTable where MyTable.name LIKE  %@search%
select * from MyTable where MyTable.name LIKE '%' + @search + '%'
select * from MyTable where MyTable.name LIKE '%@search%'

并通过api ...

And through the api...

myCmd.Parameters.AddWithValue("@search", search);
myCmd.Parameters.AddWithValue("@search", "%" + search + "%");
myCmd.Parameters.AddWithValue("@search", "%'" + search + "'%");

这些都不起作用.

我正在使用的搜索参数在其文字中带有单引号，我认为这使事情变得更加尴尬.我相信我正确地转义了参数，因为如果我构造一个直接使用该值而不是通过诸如此类的参数的查询，那么……

The search parameter I am using has single quotes in its text which I think is making things even more awkward. I believe I am escaping the parameter correctly because if I construct a query which uses the value directly as opposed to through parameters like so...

select * from MyTable where MyTable.name LIKE '%MyValue''ToSearchForWith''Quotes%'

那行得通.从我所看到的来看，要在查询中使用单引号，您需要做的就是将它们加倍.我没有看到任何错误，所以我假设我已经正确了.因此，最糟糕的情况是我有一个解决方案，但我想通过api设置搜索值，因为我认为这是更好的做法.

That works. From what I have seen all you need to do to have single quotes in your query is to double them up. I have not seen any errors so I am assuming I've got this correct. So worst case I have a solution but I would like to be setting the search value through the api as I believe this is better practice.

推荐答案

我认为问题在于，当SQL参数为您执行此操作时，您正在转义 search 参数中的引号.

I think the issue is that you're escaping the quotes in your search parameter, when the SQL parameter does that for you.

百分号应位于SQL参数值的内部.您的查询只是简单地引用该参数.SQL应该看起来像这样:

The percent signs should be inside the SQL Parameter value; your query just references the parameter plainly. The SQL should look like this:

select * from MyTable where MyTable.name LIKE @search

代码应如下所示:

string search = "MyValue'ToSearchForWith'Quotes";
myCmd.Parameters.AddWithValue("@search", "%" + search + "%");

请注意， search 是原始值，不会转义.

Note that search is the original value, not escaped.

这篇关于包含"0"的参数的设定值.'"LIKE查询中使用的(撇号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！