How to Properly Parameterize SQL Queries with LIKE and IN Clauses?

Parameterized Queries with LIKE and IN Conditions

One common obstacle in parameterized queries is handling complex conditions involving IN and LIKE clauses. In such scenarios, it's crucial to ensure that the correct syntax is used to achieve both parameterization and effectiveness.

Consider the following scenario:

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;

Here, the goal is to construct a query that retrieves products based on a comma-separated list of category IDs (CategoryIDs) and a string (Name) that may contain special characters. However, the code above will not function correctly because:

• CategoryIDs is incorrectly assigned to both SqlDbType.Int and SqlDbType.String.
• Name is assigned to SqlDbType.Int, which is incorrect for a string.
• Special characters in Name will not be handled properly.

The correct syntax requires a multi-step approach:

1. Create Individual Parameter Names: Construct a list of parameter names for each category ID, such as @p0, @p1, @pN-1.
2. Add Parameters to Command: Iterate through the category IDs, creating and adding a parameter for each one using the corresponding parameter name.
3. Construct Fuzzy Match for Name: Concatenate % around the Name parameter in the query string to enable fuzzy search.

The following code demonstrates the revised approach:

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 code produces a fully parameterized query that satisfies the requirements of both the IN and LIKE conditions.

The above is the detailed content of How to Properly Parameterize SQL Queries with LIKE and IN Clauses?. For more information, please follow other related articles on the PHP Chinese website!