参数化SQL IN子句
我如何使用可变数量的参数来对包含IN子句的查询进行参数化，就像这样？
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中，参数的数量可以在1到5之间。
我不想为这个（或XML）使用一个专用的存储过程，但是如果有一些针对SQL Server 2008的优雅方式，我对此很开放。
这是我用过的一种快速而肮脏的技术：
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 "%...%"查询未编入索引。 | ，空白或空标签或这不起作用 还有其他方法可以实现这一点，有些人可能会认为这是更清洁的，所以请继续阅读。
您可以参数化每个值，如下所示：
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 Server利用缓存查询的能力。 最终的结果是，你几乎可以肯定会首先失去使用参数的价值（而不是仅仅将谓词字符串插入SQL本身）。
并不是说缓存的查询计划没有价值，但是IMO这个查询并不复杂到看不到它的好处。 虽然编译成本可能接近（甚至超过）执行成本，但你仍然在说毫秒。
如果你有足够的内存，我希望SQL Server可能会缓存一个计划，用于参数的常见计数。 我想你总是可以添加5个参数，并且让未指定的标签为NULL - 查询计划应该是相同的，但对我来说这看起来相当丑陋，我不确定它是否值得微优化（尽管，在堆栈溢出 - 这可能是非常值得的）。
此外，SQL Server 7及更高版本将自动参数化查询，因此，从性能的角度来看，使用参数并不是真的必要 - 但从安全角度来看，这非常重要 - 尤其是对于像这样的用户输入数据。
对于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;
});
}