参数化 SQL IN 子句

如何参数化包含带有可变数量参数的IN子句的查询，比如这个？

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在此查询中，参数的数量可以是 1 到 5 之间的任何值。

我不希望为此（或 XML）使用专用存储过程，但如果有一些特定于SQL Server 2008 的优雅方式，我对此持开放态度。

答案

您可以参数化每个值，如下所示：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

哪个会给你：

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

不，这不适用于SQL 注入。 CommandText 中唯一注入的文本不是基于用户输入。它完全基于硬编码的 “@tag” 前缀和数组的索引。索引将始终为整数，不是用户生成的，并且是安全的。

用户输入的值仍然填充到参数中，因此没有漏洞。

编辑：

除了注入问题之外，请注意构建命令文本以容纳可变数量的参数（如上所述）会妨碍 SQL 服务器利用缓存查询的能力。最终结果是你几乎肯定会失去首先使用参数的价值（而不是仅仅将谓词字符串插入 SQL 本身）。

并不是说缓存的查询计划没有价值，但 IMO 这个查询并不复杂，足以从中看到很多好处。虽然编译成本可能接近（甚至超过）执行成本，但您仍然在谈论毫秒。

如果你有足够的 RAM，我希望 SQL Server 可能会缓存一个常见的参数计数的计划。我想你总是可以添加五个参数，让未指定的标签为 NULL - 查询计划应该是相同的，但对我来说这似乎很难看，我不确定它是否值得进行微优化（尽管如此，在 Stack Overflow 上 - 它可能非常值得）。

此外，SQL Server 7 及更高版本将自动参数化查询，因此从性能角度来看，使用参数并不是必需的 - 然而，从安全角度来看，这是至关重要的 - 尤其是对于用户输入的数据。

这是我使用的一种快速而肮脏的技术：

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

所以这是 C＃代码：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

两个警告：

表现很可怕。 LIKE "%...%"查询未编入索引。
确保你没有任何| ，空白或空标签或这不起作用

还有其他方法可以实现这一点，有些人可能认为更干净，所以请继续阅读。

对于 SQL Server 2008，您可以使用表值参数。这有点工作，但它可以说比我的其他方法更清晰。

首先，您必须创建一个类型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后，您的 ADO.NET 代码如下所示：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}