sql - 参数化SQL IN子句(Parameterize an SQL IN clause)

Question

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?

(我该如何参数化包含一个带有可变数量参数的IN子句的查询，就像这样？)

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

In this query, the number of arguments could be anywhere from 1 to 5.

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

I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some elegant way specific to SQL Server 2008 , I am open to that.

(我不希望为此（或XML）使用专用的存储过程，但是如果有一些特定于SQL Server 2008的优雅方法，我可以接受。)

  ask by Jeff Atwood translate from so

Answer 1

You can parameterize each value, so something 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]);
    }
}

Which will give you:

(这会给你：)

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"

No, this is not open to SQL injection .

(不，这对SQL注入不开放。)

The only injected text into CommandText is not based on user input.

(唯一注入到CommandText中的文本不是基于用户输入的。)

It's solely based on the hardcoded "@tag" prefix, and the index of an array.

(它仅基于硬编码的“ @tag”前缀和数组的索引。)

The index will always be an integer, is not user generated, and is safe.

(索引将始终是整数，不是用户生成的，并且是安全的。)

The user inputted values are still stuffed into parameters, so there is no vulnerability there.

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

Edit:

(编辑：)

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.

(除了注入问题外，请注意构造命令文本以容纳可变数量的参数（如上所述）会阻碍SQL Server利用缓存查询的能力。)

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).

(最终结果是，您几乎肯定首先失去了使用参数的价值（与仅将谓词字符串插入SQL本身相反）。)

Not that cached query plans aren't valuable, but IMO this query isn't nearly complicated enough to see much benefit from it.

(并不是说缓存的查询计划不是很有价值，但是IMO这个查询还不够复杂，无法从中受益良多。)

While the compilation costs may approach (or even exceed) the execution costs, you're still talking milliseconds.

(尽管编译成本可能接近（甚至超过）执行成本，但您仍在谈论毫秒。)

If you have enough RAM, I'd expect SQL Server would probably cache a plan for the common counts of parameters as well.

(如果您有足够的RAM，我希望SQL Server可能也会为通用的参数计数缓存一个计划。)

I suppose you could always add five parameters, and let the unspecified tags be NULL - the query plan should be the same, but it seems pretty ugly to me and I'm not sure that it'd worth the micro-optimization (although, on Stack Overflow - it may very well be worth it).

(我想您总是可以添加五个参数，并且将未指定的标签设为NULL-查询计划应该相同，但是对我来说似乎很难看，而且我不确定是否值得进行微优化（不过，堆栈溢出-可能非常值得）。)

Also, SQL Server 7 and later will auto-parameterize queries , so using parameters isn't really necessary from a performance standpoint - it is, however, critical from a security standpoint - especially with user inputted data like this.

(而且，SQL Server 7和更高版本将自动对查询进行参数化 ，因此从性能的角度来看，使用参数并不是真正必要的，但是从安全的角度来看，使用参数至关重要 （尤其是对于用户输入的数据而言）。)