Howto? Parameters and LIKE statement SQL

Question

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.

James Curran · Accepted Answer · 2008-10-30 19:15:33Z

117

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

answered Oct 30, 2008 at 19:15

James Curran

104k37 gold badges186 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adam Over a year ago

This is correct, the accepted answer provided by John has the wrong syntax!

Anders Over a year ago

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.

John M Over a year ago

Additional Note: Syntax for C# using the MySQLDataAdapter = da = new MySQLDataAdapter("SELECT * FROM tableA WHERE fieldA = @fieldA"); da.SelectCommand.Parameters.AddWithValue("@fieldA","%" + someValue + "%");

Steven Lemmens Over a year ago

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.

WTFZane Over a year ago

Shouldn't be the cmd.Parameters.Add becomes cmdLoad.Parameters.AddWithValue or is there any issue? I understand that James's answer is from 2008 :)

|

John · Accepted Answer · 2008-10-30 18:54:17Z

77

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)

answered Oct 30, 2008 at 18:54

John

17.6k17 gold badges69 silver badges87 bronze badges

6 Comments

DOK Over a year ago

As Adam has pointed out in his answer, this does not protect against SQL injection. The query should be parameterized.

John Over a year ago

Could you provide an example where this does not prevent against SQL injection? From my testing it works fine

Alex Humphrey Over a year ago

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!'.

Anthony Mason Over a year ago

I agree, the like is subject to "Like Injection" and that the harm is only the user not getting what they expect. Query sanitation should be performed at all times even when parameterized. Relying on that protection alone is not exactly secure.

8DX Over a year ago

I'd also recommend doing searchString.Replace("[","[[]").Replace("%","[%]").Replace("_","[_]"), which will avaoid LIKE injection (as long as you're not using the escape clause).

|

Siddharth Rout · Accepted Answer · 2012-06-10 01:42:59Z

25

you have to do:

LIKE '%' + @param + '%'

edited Jun 10, 2012 at 1:42

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Oct 30, 2008 at 18:46

Andrew Bullock

37.6k38 gold badges166 silver badges241 bronze badges

1 Comment

B.M. Over a year ago

excellent solution - would give it more than +1 if possible! Thanks

Will Wagner · Accepted Answer · 2008-10-30 18:58:57Z

1

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.

edited Oct 30, 2008 at 18:58

answered Oct 30, 2008 at 18:44

Will Wagner

4,2463 gold badges25 silver badges14 bronze badges

Comments

Fred Wuerges · Accepted Answer · 2012-12-04 22:33:12Z

1

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

edited Dec 4, 2012 at 22:33

Fred Wuerges

1,9652 gold badges21 silver badges43 bronze badges

answered Dec 13, 2010 at 20:27

Lalie

111 bronze badge

1 Comment

Marcelo Scofano Diniz Over a year ago

I don't know for sure at 2012, but this % vs * is specific to ADO vs DAO engines. If you are using DAO, or Current() in Access the Wildcard is *; if using ADO from any code, including VBA inside Access, the Wildcard is %.

Ramgy Borja · Accepted Answer · 2017-08-02 01:31:01Z

1

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 +

edited Aug 2, 2017 at 1:31

answered Aug 1, 2017 at 5:04

Ramgy Borja

2,4682 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

Howto? Parameters and LIKE statement SQL

6 Answers 6

6 Comments

6 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

6 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related