Howto? Parameters and LIKE statement SQL

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:

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

EDIT: I am constructing this function with VB.NET, does that have impact on the syntax you guys have contributed?

Also, I ran this statement in SQL Server: SELECT * FROM compliance_corner WHERE (body LIKE '%max%') OR (title LIKE%max%')` and that returns results.

137625 次浏览

小开

You may have to concatenate the % signs with your parameter, e.g.:

LIKE '%' || @query || '%'

Edit: Actually, that may not make any sense at all. I think I may have misunderstood your problem.

小开

you have to do:

LIKE '%' + @param + '%'

小开

最佳答案

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)

小开

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

小开

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.

Hope this helps

小开

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 Concat instead of +