How Can I Securely Parameterize an SQL IN Clause with a Dynamic Number of Arguments?

Use the number of dynamic parameters to parameter SQL in clause

Parameter SQL query helps prevent SQL from injecting attacks. However, when processing the parameters of variables, the traditional method (parameterization of each separate parameter in IN clauses) may become very cumbersome.

Parameterization per value

A more elegant solution is every possible value in the parameterized IN clause. For example, consider the following inquiries:

<code class="language-sql">SELECT * FROM Tags WHERE Name IN ('ruby','rails','scruffy','rubyonrails') ORDER BY Count DESC</code>

To parameterize this query, you can allocate each value to an array and create a parameterized in clause:

<code class="language-csharp">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]);
    }
    // ... 执行查询 ...
}</code>

The command generated by this method contains the parameters and values ​​of dynamic generated names and values:

<code class="language-sql">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"</code>

Safety precautions

It should be noted that this method can safely prevent SQL from injecting attacks, because the parameterization value is not input by the user. The only text injected into the CommandText is the hard -coded "@TAG" prefix and the index of array. These are not generated by users, so they are safe.

The above is the detailed content of How Can I Securely Parameterize an SQL IN Clause with a Dynamic Number of Arguments?. For more information, please follow other related articles on the PHP Chinese website!