设置包含放慢参数＆QUOT价值; '＆QUOT;在LIKE查询中使用（单引号） [英] Setting value of paramter containing " ' " (apostrophe) used in LIKE query

问题描述

我在ASP.NET/C# code这是他未能归还使用参数的任何值以下查询...

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 + "'%");

这些工作都没有。

None of those work.

我使用的搜索参数在其文字，我觉得更是尴尬的事情做单引号。我相信我逃避参数正确，因为如果我构建直接使用该值，而不是一个查询，通过像这样的参数...

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设置搜索值，因为我相信这是更好的做法。任何想法，将大大AP preciated。

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. Any thoughts would be greatly appreciated.

推荐答案

我认为这个问题是，你在逃避你的搜索参数报价，当SQL参数不适合您。

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

而code应该是这样的：

And the code should look like this:

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

注意搜索是原创的价值，而不是逃了出来。

Note that search is the original value, not escaped.

这篇关于设置包含放慢参数＆QUOT价值; '＆QUOT;在LIKE查询中使用（单引号）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！