如何？参数和 LIKE 语句 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/251276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Howto? Parameters and LIKE statement SQL
提问by Anders
I am writing a searching function, and have thought up of this query using parameters to prevent, or at least limit, SQL injection attacks. However, when I run it through my program it does not return anything:
我正在编写一个搜索函数，并考虑使用参数来防止或至少限制 SQL 注入攻击。但是，当我通过我的程序运行它时，它不会返回任何内容：
SELECT * FROM compliance_corner WHERE (body LIKE '%@query%') OR (title LIKE '%@query%')
SELECT * FROM compliance_corner WHERE (body LIKE '%@query%') OR (title LIKE '%@query%')
Can parameters be used like this? or are they only valid in an instance such as:
参数可以这样使用吗？或者它们仅在以下情况下有效，例如：
SELECT * FROM compliance_corner WHERE body LIKE '%<string>%'(where <string>is the search object).
SELECT * FROM compliance_corner WHERE body LIKE '%<string>%'（<string>搜索对象在哪里）。
EDIT: I am constructing this function with VB.NET, does that have impact on the syntax you guys have contributed?
编辑：我正在用 VB.NET 构建这个函数，这对你们贡献的语法有影响吗？
Also, I ran this statement in SQL Server: SELECT * FROM compliance_corner WHERE (body LIKE '%max%') OR (title LIKE%max%')` and that returns results.
另外，我在 SQL Server 中运行了这个语句：SELECT * FROM compliance_corner WHERE (body LIKE '%max%') OR (title LIKE%max%')` 并返回结果。
采纳答案by John
Your visual basic code would look something like this:
您的可视化基本代码如下所示：
Dim cmd as New SqlCommand("SELECT * FROM compliance_corner WHERE (body LIKE '%' + @query + '%') OR (title LIKE '%' + @query + '%')")
cmd.Parameters.Add("@query", searchString)
回答by James Curran
Well, I'd go with:
好吧，我会选择：
Dim cmd as New SqlCommand(
"SELECT * FROM compliance_corner"_
+ " WHERE (body LIKE @query )"_
+ " OR (title LIKE @query)")
cmd.Parameters.Add("@query", "%" +searchString +"%")
回答by Andrew Bullock
you have to do:
你必须要做：
LIKE '%' + @param + '%'
LIKE '%' + @param + '%'
回答by Will Wagner
You may have to concatenate the % signs with your parameter, e.g.:
您可能需要将 % 符号与您的参数连接起来，例如：
LIKE '%' || @query || '%'
LIKE '%' || @查询|| '%'
Edit: Actually, that may not make any sense at all. I think I may have misunderstood your problem.
编辑：实际上，这可能根本没有任何意义。我想我可能误解了你的问题。
回答by Lalie
Sometimes the symbol used as a placeholder %is not the same if you execute a query from VB as when you execute it from MS SQL / Access. Try changing your placeholder symbol from %to *. That might work.
However, if you debug and want to copy your SQL string directly in MS SQL or Access to test it, you may have to change the symbol back to %in MS SQL or Access in order to actually return values.
有时%，当您从 VB 执行查询时，用作占位符的符号与从 MS SQL/Access 执行查询时使用的符号不同。尝试将占位符符号从 更改%为*。那可能会奏效。
但是，如果您调试并希望直接在 MS SQL 或 Access 中复制您的 SQL 字符串以对其进行测试，则可能必须将符号更改回%MS SQL 或 Access 中才能实际返回值。
Hope this helps
希望这可以帮助
回答by Ramgy Borja
try also this way
也试试这种方式
Dim cmd as New SqlCommand("SELECT * FROM compliance_corner WHERE (body LIKE CONCAT('%',@query,'%') OR title LIKE CONCAT('%',@query,'%') )")
cmd.Parameters.Add("@query", searchString)
cmd.ExecuteNonQuery()
Used Concatinstead of +
使用Concat而不是+
