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.

score 118 · Answer 1 · answered Oct 30 '08 at 19:15

118

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 '08 at 19:15

James Curran

101,701
37
181
258

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
1

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
3

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
and far better for the SQL Server Execution Plan memory – Rabskatran Jan 12 '17 at 08:45
1

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 :) – WTFZane Feb 15 '17 at 06:58

score 79 · Accepted Answer · answered Oct 30 '08 at 18:54

79

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 '08 at 18:54

John

17,163
16
65
83

3

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
6

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
27

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
1

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. – Anthony Mason Mar 09 '15 at 16:31
@AlexHumphrey I actually laughed at the example, thank you :) – le3th4x0rbot Sep 29 '15 at 19:51
1

I'd also recommend doing `searchString.Replace("[","[[]").Replace("%","[%]").Replace("_","[_]")`, which will avaoid LIKE injection (as long as you're not using the escape clause). – 8DX Mar 15 '17 at 14:25

score 26 · Answer 3 · edited Jun 10 '12 at 01:42

26

you have to do:

LIKE '%' + @param + '%'

edited Jun 10 '12 at 01:42

Siddharth Rout

147,039
17
206
250

answered Oct 30 '08 at 18:46

Andrew Bullock

36,616
34
155
231

excellent solution - would give it more than +1 if possible! Thanks – B.M. Jul 22 '15 at 10:53

Will Wagner · Answer 4 · 2008-10-30T18:58:57.917

2

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 '08 at 18:58

answered Oct 30 '08 at 18:44

Will Wagner

4,128
3
22
14

score 1 · Answer 5 · edited Dec 04 '12 at 22:33

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 04 '12 at 22:33

Fred Wuerges

1,965
2
21
42

answered Dec 13 '10 at 20:27

Lalie

11
1

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 %. – Marcelo Scofano Diniz Oct 03 '20 at 18:50

Ramgy Borja · Answer 6 · 2017-08-02T01:31:01.710

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 02 '17 at 01:31

answered Aug 01 '17 at 05:04

Ramgy Borja

2,330
2
19
40

Howto? Parameters and LIKE statement SQL

6 Answers6

Linked