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?

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. — TcKs, Nov 19 '08 at 20:08
You could write your query like `WHERE name LIKE CONCAT('%', ?, '%')` — Salman A, Oct 12 '17 at 09:49
Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — GSerg, May 08 '19 at 09:26

tvanfosson · Accepted Answer · 2017-10-24T13:30:45.657

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

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. — Mr. Shiny and New 安宇, Dec 03 '08 at 19:02
Your solution is still open to sql injection. for instance if name contained: %'; drop table Orders;-- — ttomsen, Oct 23 '17 at 17:54

TcKs · Answer 2 · 2020-10-19T07:26:43.830

16

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

edited Oct 19 '20 at 07:26

answered Nov 19 '08 at 20:05

TcKs

23,945
9
61
96

why `"@name", SqlDbType.Int` data type is Int? – Reza Oct 17 '20 at 01:40
@Raza Good point. It was error. Probably mistake caused by copy/paste. – TcKs Oct 19 '20 at 07:27

score 6 · Answer 3 · answered Nov 19 '08 at 20:43

6

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.

answered Nov 19 '08 at 20:43

Tomalak

306,836
62
485
598

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. – Bill Karwin Nov 19 '08 at 21:26
Yes, not nice but effective. The Server will sort out the duplicates. – Tomalak Nov 20 '08 at 09:46

score -2 · Answer 4 · answered Nov 19 '08 at 20:37

-2

not sure if this is the right way but it is a way I have done it in the Before

list templist = new list

comm.Parameters.Add("@categoryids", SqlDbType.varchar); comm.Parameters["@categoryids"].value = string.join(",",templist.toarray())

answered Nov 19 '08 at 20:37

B.A.Hammer

13
2

1

No. You cannot parameterize a list of values. Well, you can, but it'll compare Category_ID to the single *string* value '1,2,3', instead of comparing to each integer value in the list. This will have unintended results. :-) – Bill Karwin Nov 19 '08 at 21:30

Parameterized Queries with LIKE and IN conditions

4 Answers4

Linked

Related