How to Parameterize SQL IN Clauses with a Variable Number of Arguments?

Safely parameterize SQL IN clauses, dealing with variadic parameters

To prevent SQL injection vulnerabilities, it is crucial to parameterize SQL queries containing IN clauses. However, this can get complicated when dealing with a variable number of arguments. Here's how to fix this issue in SQL Server 2008:

To parameterize each parameter using C#, you can do the following:

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 < tags.Length; i++)
    {
        cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
    // ... 执行查询 ...
}

This code creates a parameterized IN clause containing the specified number of parameters. The result is:

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"

It is important to note that this solution does not pose a SQL injection risk as the injected text is not based on user input and only contains fixed values. User input is safely stored in parameters.

While cached query plans can be beneficial, they don't always provide significant advantages for simple queries like this. However, starting with SQL Server 7 version, SQL Server will automatically parameterize queries, highlighting the importance of parameterization for security.

The above is the detailed content of How to Parameterize SQL IN Clauses with a Variable Number of Arguments?. For more information, please follow other related articles on the PHP Chinese website!