How to use wildcards in SQL query with parameters

Question

Say I have a basic query, something like this:

 SELECT holiday_name
 FROM holiday
 WHERE holiday_name LIKE %Hallow%

This executes fine in my sql query pane and returns 'Halloween'. My problem occurs when I try to use parameters with with the wildcard '%' characters in my code.

SqlConnection Connection = null;
SqlCommand Command = null;

string ConnectionString = ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString;
string CommandText = "SELECT holiday_name "
                   + "FROM holiday "
                   + "WHERE holiday_name LIKE %@name%";
Connection = new SqlConnection(ConnectionString);

try
{
      Connection.Open();
      Command = new SqlCommand(CommandText, Connection);
      Command.Parameters.Add(new SqlParameter("name", HolidayTextBox.Text));
      var results = Command.ExecuteScalar();
}

catch (Exception ex)
{   
     //error stuff here       
}

finally
{
    Command.Dispose();
    Connection.Close();
}

This throws an incorrect syntax error. I've tried moving the '%' to my parameter like so

Command.Parameters.Add(new SqlParameter("%name%", HolidayTextBox.Text));

but then I receive an error saying I haven't declared the scalar variable '@name'. So, how do you properly format wildcard characters to be included with query parameters? Any help is appreciated!

score 28 · Accepted Answer · edited Nov 22 '17 at 16:30

First off, your SqlParameter name is @name not name.

Second, I would move your wildcards.

So it would look like this:

string CommandText = "SELECT holiday_name "
               + "FROM holiday "
               + "WHERE holiday_name LIKE @name;"
Connection = new SqlConnection(ConnectionString);

try
{
  var escapedForLike = HolidatyTextBox.Text; // see note below how to construct 
  string searchTerm = string.Format("%{0}%", escapedForLike);
  Connection.Open();
  Command = new SqlCommand(CommandText, Connection);
  Command.Parameters.Add(new SqlParameter("@name", searchTerm));
  var results = Command.ExecuteScalar();
}

Note that LIKE requires special care when passing parameters and you need to escape some characters Escaping special characters in a SQL LIKE statement using sql parameters.

gordy · Answer 2 · 2018-06-08T03:26:39.410

12

whatever you do don't do this:

string CommandText = "SELECT holiday_name "
                   + "FROM holiday "
                   + "WHERE holiday_name LIKE '%'" + HolidayTextBox.Text + "'%'";

as that will open you up to sql injection, instead do this:

Command.Parameters.Add(new SqlParameter("@name", "%" + HolidayTextBox.Text + "%"));

you may like to know about Command.Parameters.AddWithValue, e.g:

Command.Parameters.AddWithValue("@name", "%" + HolidayTextBox.Text + "%");

edited Jun 08 '18 at 03:26

answered Nov 01 '13 at 16:27

gordy

9,360
1
31
43

2

I do not recommend SQL injection. – Tim S. Nov 01 '13 at 16:28
8

Little Bobby Tables likes SQL injection! – Ryan Anderson May 16 '16 at 14:23
"as that will open you up to sql injection" --- could you please elaborate, how? – zerkms May 09 '17 at 20:28
@zerkms I don't actually know how but when I posted this answer it was downvoted a lot and there are comments calling it a sql injection. AFAIK `like '%' + @var + '%'` is perfectly safe ¯\_(ツ)_/¯ – gordy May 09 '17 at 20:45
1

It is perfectly safe indeed – zerkms May 09 '17 at 21:22
@zerkms it protects of some cases, but not enough to be useful. What if I type `__ck` in the `HolidayTextBox.Text`? (see https://stackoverflow.com/questions/25703637/escaping-special-characters-in-a-sql-like-statement-using-sql-parameters) – Alexei Levenkov Nov 22 '17 at 16:34
@AlexeiLevenkov it protects against sql injections, that's what was discussed in the comments. – zerkms Nov 22 '17 at 18:56
2

@zerkms letting user to provide input so `var = "%"` for something like `DELETE FROM users WHERE userName LIKE '%' + @var + '%'` is still plenty dangerous... (indeed one needs to trust everyone too much to have `LIKE` with user input, especially in update statements and may as well deserve it :)) – Alexei Levenkov Nov 26 '17 at 05:53
@AlexeiLevenkov - Anyone stupid enough to do that with a delete should have their computer taken away ... and possibly their fingers so they can never type again. – Paul Aug 08 '18 at 09:49
1

You may want to know this about AddWithValue: [AddWithValue is Evil](http://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](http://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Andrew Morton Nov 02 '19 at 16:00

score 2 · Answer 3 · answered Nov 01 '13 at 16:29

2

The %s should be part of the search string, not the query.

string CommandText = "SELECT holiday_name "
                + "FROM holiday "
                + "WHERE holiday_name LIKE @name";
Connection = new SqlConnection(ConnectionString);

try
{
    Connection.Open();
    Command = new SqlCommand(CommandText, Connection);
    string name = "%" + HolidayTextBox.Text + "%";
    Command.Parameters.Add(new SqlParameter("@name", name));

answered Nov 01 '13 at 16:29

Tim S.

55,448
7
96
122

it protects of some cases, but not enough to be useful. What if I type `__ck` in the `HolidayTextBox.Text`? (see https://stackoverflow.com/questions/25703637/escaping-special-characters-in-a-sql-like-statement-using-sql-parameters) – Alexei Levenkov Nov 22 '17 at 16:35

How to use wildcards in SQL query with parameters

3 Answers3

Linked