Parameterized Queries with LIKE and IN conditions

by

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

More Related Contents:

  1. Does this code prevent SQL injection?
  2. How can I avoid SQL injection attacks in my ASP.NET application?
  3. How should I pass a table name into a stored proc?
  4. store image in database or in a system file? [closed]
  5. SQL Identity (autonumber) is Incremented Even with a Transaction Rollback
  6. SELECT * FROM X WHERE id IN (…) with Dapper ORM
  7. Bulk Insert to Oracle using .NET
  8. Using “GO” within a transaction
  9. Does using parameterized SqlCommand make my program immune to SQL injection?
  10. Does End Using close an open SQL Connection
  11. Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
  12. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  13. How can you name the Dataset’s Tables you return in a stored proc?
  14. How can I create database tables from XSD files?
  15. Why is some sql query much slower when used with SqlCommand?
  16. Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?
  17. How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?
  18. How do I convert a string into safe SQL String?
  19. Regular expression to find all table names in a query
  20. How to return a page of results from SQL?
  21. Using a variable for table name in ‘From’ clause in SQL Server 2008
  22. Parameterise table name in .NET/SQL?
  23. How to pass a variable to the SelectCommand of a SqlDataSource?
  24. Most useful NLog configurations [closed]
  25. A definitive guide to API-breaking changes in .NET
  26. Calling a .net library method from vba
  27. Correct way to escape characters in a DataTable Filter Expression
  28. How to see JIT-Compiled code in .NET VM (CLR)
  29. How to call managed code from unmanaged code?
  30. Serializing a list of Key/Value pairs to XML

Leave a Comment