Parameterized Queries with LIKE and IN conditions

Question

Parameterized Queries in .Net always look like this in the examples:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID = @categoryid
", 
   conn);
comm.Parameters.Add("@categoryid", SqlDbType.Int);
comm.Parameters["@categoryid"].Value = CategoryID;

But I'm running into a brick wall trying to do the following:

SqlCommand comm = new SqlCommand(@"
   SELECT * 
   FROM   Products 
   WHERE  Category_ID IN (@categoryids) 
      OR  name LIKE '%@name%'
", 
   conn);
comm.Parameters.Add("@categoryids", SqlDbType.Int);
comm.Parameters["@categoryids"].Value = CategoryIDs;
comm.Parameters.Add("@name", SqlDbType.Int);
comm.Parameters["@name"].Value = Name;

Where

• CategoryIDs is a comma separated list of numbers "123,456,789" (without quotes)
• Name is a string, possibly with single quotes and other bad characters

What's the right syntax for this?

Aesop

In "IN" clasule you must specify every value in SQL command. And evenry value add to "Parameters" collection. If you pass string value into SQL via parameter value, you must not be afraid for sql injection. – Middleton

You could write your query like WHERE name LIKE CONCAT('%', ?, '%') – Alyss

Possible duplicate of Parameterize an SQL IN clause – Lyre

Answer 1

Let's say that you have your category ids in an integer array and Name is a string. The trick is to create the command text to allow you to enter all of your category ids as individual parameters and construct the fuzzy match for name. To do the former, we use a loop to construct a sequence of parameter names @p0 through @pN-1 where N is the number of category ids in the array. Then we construct a parameter and add it to the command with the associated category id as the value for each named parameter. Then we use concatenation on the name in the query itself to allow the fuzzy search on name.

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,
                                1618, 1619, 1620, 1951, 1952,
                                1953, 1954, 1955, 1972, 2022 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

This is a fully parameterized query that should make your DBA happy. I suspect that since these are integers, though it would not be much of a security risk just to construct the command text directly with the values, while still parameterizing the name. If your category ids are in a string array, just split the array on commas, convert each to an integer, and store it in the integer array.

Note: I say array and use it in the example, but it should work for any collection, although your iteration will probably differ.

Original idea from http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

Unearthly

your DBA will be mad if you hard-code the parameters in the SQL even if they are ints, because that means the DB can't use a statement cache and has to re-parse the statement every time. – Traitorous

Your solution is still open to sql injection. for instance if name contained: %'; drop table Orders;-- – Denature

@Denature thanks, don't know how I missed that. fixed now – Unearthly

Answer 2

You need "%" in value of sql parameter.

SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID IN (@categoryid1, @categoryid2) OR name LIKE @name", conn);
comm.Parameters.Add("@categoryid1", SqlDbType.Int);
comm.Parameters["@categoryid1"].Value = CategoryID[0];
comm.Parameters.Add("@categoryid2", SqlDbType.Int);
comm.Parameters["@categoryid2"].Value = CategoryID[1];
comm.Parameters.Add("@name", SqlDbType.NVarChar);
comm.Parameters["@name"].Value = "%" + Name + "%";

Middleton

Answer 3

This approach will not work. Period.

The IN clause expects a list of parameters itself, so when you bind one parameter to it, you have the chance to pass in one value.

Build your statement string dynamically, with the exact amount of individual IN clause placeholders you intend to pass in, and then add parameters and bind values to them in a loop.

Ultun

Another trick is to create a lot of parameters in the SQL and bind values repeatedly. This is inelegant but it works because IN (1,2,3) and IN (1,2,3, 1,2,3, 1,2) are equivalent predicates. – Communication

Yes, not nice but effective. The Server will sort out the duplicates. – Ultun