Monday, June 10, 2013

SQL Injection Prevention - Binding "IN" Clause Parameters

Most people know that using bound parameters is the best way to prevent SQL Injection vulnerabilities. However, the one place I still see problems are when developers need to utilize the IN clause:

However, it is possible to use bound parameters for use with an IN clause. If using SQL Server and ADO.NET one can use a user defined table type. Let's go through an example. First, we will create a sample table and populate some test data:

Next, I'll show how to create a user defined table type and use this in a stored procedure:

Finally, within your CSharp you can use the following code to bind the table type:

Using techniques like this one can bind correctly typed data when calling a stored procedure that uses an IN clause. Unfortunately, this functionality hasn't made it into Linq or the Entity Framework. If you are using Java and an Oracle DB there is similar functionality using arrays (one example can be found here).

Hopefully, people find this useful. Let me know if you have any questions.