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?

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[coder_bro]

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%"); Worked, the extra single quotes were the issue as pointed out by you

[Javid]

Doing it the second way looks better to me because the final statement will indeed look neat.

[Ben Pretorius]

wouldn't this expose an opportunity for sql injection?

[Matt Miller]

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.

[Timothy]

This doesn't make any sense to me but it works. Could you explain why?

[Taran]

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.

[Heriberto Lugo]

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);

[Thabiso Mofokeng]

@MattMiller will just wrapping searchString in square brackets command.Parameters.AddWithValue("@SEARCH","%[" + searchString + "]%"); instead of the regex not work?

[Matt Miller]

@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.

[Shajin Chandran]

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); }

[Marc Gravell]

you'll need to add a parameter per clause, and build the SQL iteratively

[Noman_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).

[Misi]

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 + '%'

[Pierre-Alain Vigeant]

@Misi Using string interpolation here creates a SQL Injection vulnerability. Always use a Sql Parameter.