Use of SqlParameter in SQL LIKE clause not working

Question

I have the following code:

const string Sql = 
    @"select distinct [name] 
      from tblCustomers 
      left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
      where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";

using (var command = new SqlCommand(Sql, Connection))
{       
    command.Parameters.AddWithValue("@SEARCH", searchString);
    ...
}

This does not work, I tried this as well:

const string Sql = 
    @"select distinct [name] 
     from tblCustomers 
     left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
     where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";

using (var command = new SqlCommand(Sql, Connection))
{       
    command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");
    ...
}

but this does not work as well. What is going wrong? Any suggestions?

score 200 · Accepted Answer · answered Mar 20 '09 at 06:02

200

What you want is:

tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'

(or edit the parameter value to include the % in the first place).

Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).

In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");

Either approach should work.

answered Mar 20 '09 at 06:02

Marc Gravell

1,026,079
266
2,566
2,900

3

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%"); Worked, the extra single quotes were the issue as pointed out by you – coder_bro Mar 20 '09 at 06:55
1

Doing it the second way looks better to me because the final statement will indeed look neat. – Javid Mar 27 '14 at 09:41
14

This won't work correctly when the searchString contains the characters `%` `_` or `[`, assuming you want to actually search for one of those charater literals. For a 100% solution, you need to wrap those characters in the searchString in brackets `[]`. The C# code `Regex.Replace(searchString, @"([%_\[])", @"[$1]")` does the trick. – Matt Miller Jul 18 '14 at 18:03
@MattMiller will just wrapping searchString in square brackets `command.Parameters.AddWithValue("@SEARCH","%[" + searchString + "]%");` instead of the regex not work? – Thabiso Mofokeng Oct 15 '19 at 14:02
1

@ThabisoMofokeng No, the brackets are not like quoting (unless it contains a single character). What the brackets do in a SQL LIKE is tell it to match any character within it, so for instance 'a[123]z' matches 'a1z', 'a2z', and 'a3z' but not 'a12z' because the bracketed expression only matches one character. My suggestion to use a regex to wrap each of the special characters in brackets forces the LIKE to match those characters literally. – Matt Miller Oct 16 '19 at 15:25
How to pass multiple like as a SQL parameter for the below case? foreach (var item in username) { whereQuery = string.Format("s.userName LIKE '%|{0}|%' or ", item); } – Shajin Chandran May 27 '20 at 14:27
you'll need to add a parameter per clause, and build the SQL iteratively – Marc Gravell May 27 '20 at 16:53

score 10 · Answer 2 · answered Sep 10 '16 at 07:42

10

Instead of using:

const string Sql = 
@"select distinct [name] 
  from tblCustomers 
  left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
  where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";

Use this code:

const string Sql = 
@"select distinct [name] 
  from tblCustomers 
  left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
  where (tblCustomer.Name LIKE '%' + @SEARCH + '%' OR tblCustomerInfo.Info LIKE '%' + @SEARCH + '%');";

answered Sep 10 '16 at 07:42

Ali Almasian

129
1
5

4

This doesn't make any sense to me but it works. Could you explain why? – Timothy May 15 '17 at 11:33
1

(@ needs escape in comments) The @SEARCH parameter is turned into an SQL varchar and then is concatenated with the % chars, finally its compared on the LIKE statement. For ex \@SEARCH the val "Joan", the turns into: tblCustomer.Name LIKE '%' + 'Joan' + '%', note 'Joan' here is completly escaped, -> tblCustomer.Name LIKE '%Joan%'. If the search param would inclue any unexpected characters(% or ', they would get escaped, given \@SEARCH = "%Ji'm". tblCustomer.Name LIKE '%' + '[%]Ji''m' + '%'. => '%[%]Ji''m%' (% is escaped by square braquets and single quote is escaped by doubling it). – Noman_1 Jun 29 '20 at 12:54
When using string interpolation you don't even need to escape de single quote: `var query = $"SELECT * FROM Customer LIKE '%' + {parameterValue} + '%'";` I recommended first to test in T-SQL with a sql variable: `DECLARE @parameterValue VARCHAR(10) = 'Cust';` `SELECT * FROM Customer LIKE '%' + @parameterValue + '%'` – Misi May 04 '21 at 15:24
1

@Misi Using string interpolation here creates a SQL Injection vulnerability. Always use a Sql Parameter. – Pierre-Alain Vigeant Aug 06 '21 at 01:38

LeoFraietta · Answer 3 · 2019-01-31T21:06:57.240

Just a little careful with a slight difference between Add and AddWithValue methods. I had the problem below, when I used the Add method and put the wrong SqlType parameter.

nchar and nvarchar can store Unicode characters.
char and varchar cannot store Unicode characters.

For example:

string query = " ... WHERE stLogin LIKE @LOGIN ";

SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.Char, 255) 
{ 
    Value = "%" + login + "%" 
};

command.Parameters.AddWithValue(p.ParameterName, p.Value); //works fine!!!

command.Parameters.Add(p); // won't work

When I changed the SqlType to NVarChar, the two methods worked fine to me.

SqlParameter p = new SqlParameter("@LOGIN", SqlDbType.NVarChar, 255) 
{ 
    Value = "%" + login + "%" 
};

command.Parameters.AddWithValue(p.ParameterName, p.Value); //worked fine!!!

command.Parameters.Add(p); //worked fine!!!

i think the reason the first example didnt work as expected is because you specified the column type. if you use the constructor which takes the parameter name and value, it should work as expected. looking at the source code of AddWithValue, all they do is call the constructor that takes parameter name and value. so: p = new SqlParameter("@LOGIN", "%" + login + "%"); command.Parameters.Add(p); — Heriberto Lugo, Sep 05 '19 at 15:23

score -1 · Answer 4 · answered Nov 28 '22 at 17:41

You can actually directly add the value instead of using parameter. For example this is how I implemented it and it works fine. Cheers!

string name = "test";
string  nameCondition= " and FirstName like '%"+ name +"%' or MiddleName like '%" + name +"%' or LastName like '%"+name+"%'";

score -7 · Answer 5 · edited Feb 26 '16 at 22:10

-7

You could do LIKE @SEARCH and in your C# code, do

searchString = "%" + searchString + "%"

edited Feb 26 '16 at 22:10

T.S.

18,195
11
58
78

answered Mar 20 '09 at 06:18

Charles Graham

24,293
14
43
56

6

wouldn't this expose an opportunity for sql injection? – Ben Pretorius Jul 09 '14 at 10:27
2

Yes it would. Or, possibly more likely, someone is going to search for a string including a ' character and it will blow up. Do not do this. – Taran Sep 20 '17 at 15:29

Use of SqlParameter in SQL LIKE clause not working

5 Answers5

Linked

Related