Dynamic WHERE Clause & SQL Injection

Question

I need to create functionality for users to determine the WHERE criteria of a select - the criteria will be dynamic.

Is there a way I can achieve this without opening up my code to SQL injection?

I'm using C# / .NET Windows Application.

Answer 1

Using parameterized queries would go long way toward protecting you from SQL injection attacks, because most bad things happen in the value portion of your where conditions.

For exampleg given a condition a=="hello" && b=="WORLD", do this:

select a,b,c,d
from table
where a=@pa and b=@pb -- this is generated dynamically

Then, bind @pa="hello" and @pb="WORLD", and run your query.

In C#, you would start with an in-memory representation of your where clause in hand, go through it element-by-element, and produce two output objects:

• A string with the where clause, where constants are replaced by automatically generated parameter references pa, pb, and so on (use your favorite naming scheme for these blind parameters: the actual names do not matter)
• A dictionary of name-value pairs, where names correspond to the parameters that you've inserted in your where clause, and values that correspond to the constants that you pulled from the expression representation.

With these outputs in hand, you prepare your dynamic query using the string, add parameter values using the dictionary, and then execute the query against your RDBMS source.

DO NOT DO THIS

select a,b,c,d
from table
where a='hello' and b='WORLD' -- This dynamic query is ripe for an interjection attack