There is a specific kind of SQL injection that is related to handling multiple values in a query. Multiple values are often used in dynamically generated queries, for example, in combination with IN SQL clause or when you need to filter by different words. In this case you want to apply the same technique described in the article How to use parameterized SQL queries to protect ASP.NET web application from SQL injection in VB.NET to a query composed of multiple values. In other words you want to stay secure by continuing to use parameters, but you need to pass multiple values to the query.
For example if you have to get every movie in a given list of genre, you’ll probably opt for a piece of code similar to this snippet:
string sql = “SELECT * FROM Movies WHERE Genre IN ({0})”;
sql = string.Format(sql, Request[“Genres”]);
If you’re using a <select /> HTML tag with multiple selection, the browser will automatically send the values, separated by a comma, which is the exact syntax used in SQL. You will be in a trouble if someone passes an evil string, like 0);DROP TABLE Movies–, the result is the following query:
SELECT * FROM Movies WHERE Genre IN (0);DROP TABLE Movies—
Attacker uses the ; character to separate different queries, so he uses it to arbitrarily execute a query. A malicious user can employ special techniques that aim at retrieving the database schema using a normal page created to visualize data. For this reason, you need a mechanism to support these queries, but one that uses parameters. You can dynamically compose a query with multiple values by using the next code lines:
‘Compose the base query
Dim Sql As New StringBuilder(“SELECT * FROM Movies “WHERE Genre IN (“)
Dim Genres As String() = Request(“Genres “).Split(“,”)
Dim Parameters As SqlParameter() = New SqlParameter(Genres .Length – 1)
For i As Integer = 0 To Genres.Length – 1
Sql.AppendFormat(“@p{0}, “, i)
‘Set the parameter value
Parameters(i) = New SqlParameter(String.Format(“@p{0}”, i), Genres(i))
Next
Sql.Append(“0)”)
The listing above shows you the solution to your problem. You can dynamically generate the SQL string by safely adding the parameters, based on multiple values. It’s perfectly possible to name parameters sequentially. This solution does not rely on end user input, and you can pass values to the database engine, which will sanitize them for you. The resulted query will be similar to this one:
SELECT * FROM Movies WHERE Genre IN (@p0, @p1, @p2, 0)
The parameter is then added, using iteration, to the corresponding SqlCommand instance. Your query will be secured.