CODEWITHSUNDEEP
asp.netsqlvb.net
Anders
Anders

Reputation: 12580

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.

Upvotes: 81

Views: 142004

Answers (6)

Ramgy Borja
Ramgy Borja

Reputation: 2458

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 +

Upvotes: 1

Lalie
Lalie

Reputation: 11

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

Upvotes: 1

Andrew Bullock
Andrew Bullock

Reputation: 37436

you have to do:

LIKE '%' + @param + '%'

Upvotes: 25

James Curran
James Curran

Reputation: 103605

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

Upvotes: 117

Will Wagner
Will Wagner

Reputation: 4236

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.

Upvotes: 1

John
John

Reputation: 17501

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)

Upvotes: 77

Related Questions