How to use "IN (..)" Parameter with dbcommand

[ydhainaut]

I have a (non blocking) problem using "IN" Parameter in Dynamic Query :

Sample :

'Declarations
Dim sqlCommand As String = ""
Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Me.GetDatabase()
sqlCommand = "SELECT * FROM " & Me.SchemaTableViewWithSeparator & Me.TableName & " WHERE 1=1 "
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

'For a simplified test here
Dim type_enr as String;
type_enr = "'AB','CD','EF'"

' Dynamycally add a parameter in the query if parameter value is not null (the probleme is located here !)
If Not String.IsNullOrEmpty(type_enr) then
sqlCommand += " AND CODE IN (@TYPE_ENR)"
db.AddInParameter(dbCommand, "TYPE_ENR", DbType.String, type_enr)
end if

' Query String Update before execution
dbCommand.CommandText = sqlCommand

'Execute Query (non interesting part but i join a full sample)
Try
If Me.LoadFromSql(dbCommand) Then
Me.Rewind()
l.Add(Me.MemberwiseClone)
While Me.MoveNext
Dim o As Object = Me.MemberwiseClone()
l.Add(o)
End While
End If
Catch ex As Exception
Throw New Exception("Error")
End Try

To avoid Sql Injection, i use parameters '@TYPE_ENR' but when using 'IN' clause, this part does'nt work (Only when using 'IN' ; i use it with '=' or Between etc ..) :

sqlCommand += " AND CODE IN (@TYPE_ENR)"
db.AddInParameter(dbCommand, "TYPE_ENR", DbType.String, type_enr)

and it works if i replace those 2 lines with :

sqlCommand += " AND CODE IN (" & type_enr & ")"

But it is not securised.
The first 2 lines works if the var contains a single string value like "AB". (Remarks this value is not enclosed with single quote in this case because i only use one value).
it appear that the first 2 lines doesnt works if the var contains a single string value like "'AB'". (<-- remarks the sigle quote)

It seems that when executing, EasyObject enclose the value of the parameter, defined as String, with quotes so the request
becomes :

.. AND CODE IN ("'AB','CD','EF'") .. "

witch is an incorrect syntax. So i think i have identified the problem but i have no solution .

Any idea would be appreciated

[mgnoonan]

You are correct, the IN and NOT IN clauses are not able to use parameters to help safeguard against SQL injection attacks. This is, unfortunately, a long standing issue with SQL Server, and there are no good solutions available. Various work-arounds include passing the comma-separated IDs in a parameter and then parsing the values inside a stored procedure. That approach might work if EasyObjects only used stored procedures, which of course, it doesn't.

In the end, I chose to keep the code flexible even though it is a security risk. At the heart of an injection attack is that the would-be hacker has to attempt the injection wherever there is a SQL query. If all of the queries use parameters, all attempts at SQL injection will fail.

EasyObjects uses parameters for every other type of query, except for the two IN queries. So any potential hacker would have to figure out if there is an IN query available for him to attack. It's not beyond the realm of possibility, but if you make sure to validate user input before doing your IN queries, that should be enough to keep the injection attacks at bay.