带有LIKE和IN条件的参数化查询

Question

带有LIKE和IN条件的参数化查询

.netsqlparameterssql-injectionparameterized

56

在.NET中，参数化查询在示例中始终如下所示：

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

但是我在尝试做以下操作时遇到了困难：

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" (不含引号)
Name是一个字符串，可能包含单引号和其他特殊字符

- AviewAnew

在“IN”子句中，您必须在SQL命令中指定每个值。并将每个值添加到“参数”集合中。如果通过参数值将字符串值传递到SQL，则不必担心SQL注入。 - TcKs

你可以像这样编写查询语句：WHERE name LIKE CONCAT('%', ?, '%') - Salman A

1

可能是将SQL IN子句参数化的重复问题。 - GSerg

3个回答

20

SQL参数的值中需要包含“%”字符。

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

- TcKs

6

这种方法行不通。就是这样。

IN子句本身需要一系列参数，因此当您将一个参数绑定到它时，您只有机会传递一个值。

动态构建语句字符串，使用您打算传递的单个IN子句占位符的确切数量，然后在循环中添加参数并绑定值。

- Tomalak

另一个技巧是在SQL中创建许多参数并重复绑定值。这很不优雅，但它能够工作，因为IN（1,2,3）和IN（1,2,3, 1,2,3, 1,2）是等效的谓词。 - Bill Karwin

是的，虽然不太好看但很有效。服务器会处理重复项。 - Tomalak

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- tvanfosson · Accepted Answer

假设您有一个整数数组用于存储分类ID，Name则为字符串。关键在于创建命令文本，使您能够将所有分类ID作为单独的参数输入并构建模糊匹配的名称。要实现前者，我们使用循环来构造一系列参数名称@p0到@pN-1，其中N是数组中分类ID的数量。然后我们构造一个参数，并将其添加到命令中，将关联的分类ID作为命名参数的值。然后我们在查询本身上对名称进行连接，以允许对名称进行模糊搜索。

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在字符串数组中，则只需在逗号上拆分数组，将每个转换为整数，并将其存储在整数数组中。

注意：我使用了“数组”并在示例中使用它，但它应该适用于任何集合，尽管您的迭代可能会有所不同。

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