带有 LIKE 和 IN 条件的参数化查询 [英] Parameterized Queries with LIKE and IN conditions

问题描述

.Net 中的参数化查询在示例中始终如下所示:

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;

哪里

• CategoryIDs 是逗号分隔的数字列表123,456,789"(不带引号)
• 名称是一个字符串，可能带有单引号和其他坏字符

什么是正确的语法?

推荐答案

假设您的类别 ID 在一个整数数组中，而 Name 是一个字符串.诀窍是创建命令文本以允许您输入所有类别 ID 作为单独的参数并构建名称的模糊匹配.为了实现前者，我们使用循环来构造参数名称 @p0 到 @pN-1 的序列，其中 N 是数组中类别 id 的数量.然后我们构造一个参数并将其添加到命令中，并使用关联的类别 id 作为每个命名参数的值.然后我们在查询本身的名称上使用串联，以允许对名称进行模糊搜索.

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

这是一个完全参数化的查询，应该会让您的 DBA 满意.我怀疑由于这些是整数，尽管直接用这些值构造命令文本并同时仍然参数化名称不会有太大的安全风险.如果您的类别 ID 在字符串数组中，只需用逗号分割数组，将每个数组转换为整数，然后将其存储在整数数组中.

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.

最初的想法来自 http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

这篇关于带有 LIKE 和 IN 条件的参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！