Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 21 down vote favorite
5

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.

share|improve this question

6 Answers

up vote 19 down vote accepted

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)
share|improve this answer
As Adam has pointed out in his answer, this does not protect against SQL injection. The query should be parameterized. – DOK Oct 30 '08 at 19:58
5  
Could you provide an example where this does not prevent against SQL injection? From my testing it works fine – John Oct 30 '08 at 20:32
6  
It's not open to SQL injection, just LIKE injection. This means the user can enter special characters such as % ^ and _ which LIKE will interpret specially. This means the user may not get what they expect for certain searches. As an example, a search for 'less than 1% fat' may return the result 'less than 1% of doctors recommend this - it's full of fat!'. – Alex Humphrey Apr 13 '11 at 11:18
up vote 27 down vote

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 +"%")
share|improve this answer
This is correct, the accepted answer provided by John has the wrong syntax! – Adam Oct 30 '08 at 19:49
3  
I am unsure how his syntax is incorrect, his solution worked just fine. I have a function that constructs and returns an SQL statement for use in a datatable or whatever else. – Anders Oct 30 '08 at 19:56
Additional Note: Syntax for C# using the MySQLDataAdapter = da = new MySQLDataAdapter("SELECT * FROM tableA WHERE fieldA = @fieldA"); da.SelectCommand.Parameters.AddWithValue("@fieldA","%" + someValue + "%"); – John M May 11 '10 at 19:21
this is the only answer which worked for me! – nawfal Jul 5 '12 at 12:03
1  
The other answer (from John) works too, but is indeed vulnerable to like injection, which MIGHT give strange results, depending on the purpose of your field. – Steven Lemmens Aug 28 '12 at 11:30
up vote 5 down vote

Try:

select * from compliance_corner
   where (body like '%' + @query + '%') or (title like '%' + @query + '%')
share|improve this answer
As Adam has pointed out in his answer, this does not protect against SQL injection. The query should be parameterized. – DOK Oct 30 '08 at 19:56
up vote 2 down vote

you have to do:

LIKE '%' + @param + '%'

share|improve this answer
+ 1 short and simple – Siddharth Rout Jun 10 '12 at 1:43
up vote 1 down vote

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

share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.