sql - 参数化 SQL IN 子句

Question

如何参数化包含带有可变数量参数的 IN 子句的查询(如本例)？

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

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

我不想为此使用专用存储过程(或 XML)，但如果有一些特定于 SQL Server 2008 的优雅方法的话，我对此持开放态度。

Answer 1

您可以参数化每个值，例如:

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 injection 开放。 CommandText 中唯一注入(inject)的文本并非基于用户输入。它完全基于硬编码的“@tag”前缀和数组的索引。索引始终是一个整数，不是用户生成的，并且是安全的。

用户输入的值仍然被填充到参数中，因此不存在漏洞。

编辑:

Injection concerns aside, take care to note that constructing the command text to accomodate a variable number of parameters (as above) impede's SQL server's ability to take advantage of cached queries. The net result is that you almost certainly lose the value of using parameters in the first place (as opposed to merely inserting the predicate strings into the SQL itself).

并不是说缓存的查询计划没有值(value)，但在我看来，这个查询还没有复杂到足以从中看到太多好处。虽然编译成本可能接近(甚至超过)执行成本，但你仍然在谈论毫秒。

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

此外，SQL Server 7 及更高版本将 auto-parameterize queries ，因此从性能的角度来看，使用参数并不是真正必要的 - 然而，从安全的角度来看，它是至关重要的 - 特别是对于像这样的用户输入数据。