Build safe search conditions for SQL WHERE clause

Question

I need to build search conditions to be used with WHERE clause. This search condition is then passed to a different application to be executed as a part of SQL query. Because there search conditions can be quite complex (including sub-queries) I don't believe receiving application can intelligently parse them to prevent SQL injection attacks.

Best practices state that parametrized queries should be used. That works fine when you use command object to execute the query yourself. In my case I wish to obtain that query string with parameters merged into it, and parse out where search clause I am interested in. Is there a way to do this?

I work with MS SQL Server and currently simply replace all single quotes with two single quotes in string I receive from a caller. Is there a better way to achieve some level of protection from SQL injection attacks?

Answer 1

Have a look at these 2 links

Does this code prevent SQL injection?

and

Proving SQL Injection

Answer 2

some guidelines from OWASP

Answer 3

I think you are fine: According to the SQL Server Books Online, a solitary single quote seems to be the only way to exit a quoted string that was started with a single quote. Thus, replacing ' with '' should suffice to avoid SQL injection through string variables.

I cannot think of any way to inject SQL through other, non-string native C# data types, if they are properly (locale-invariant) converted to strings.

Nevertheless, parameterized queries are the "recommended" solution. At the moment, your application seems to be organized like this:

1. Part A creates a WHERE statement based on user input.
2. A string containing this WHERE statement is passed to Part B.
3. Part B adds SELECT etc. and sends it to SQL Server.

Would it be an option to rewrite your application like this?

1. Part A creates a parameterized WHERE statement plus a set of parameters based on user input.
2. A string containing the WHERE statement plus a Hashtable (or something similar) containing the parameters is passed to Part B.
3. Part B creates a command, adds SELECT etc., adds the parameters and sends it to SQL Server.

I was in a similar situation and solved it by creating a SubSQL class, which basically contains a parameterized string with the CommandText and a hash table with the parameters. You could then use this as mySubSQL.CommandText += ..., mySubSQL.Parameters("@myfield") = myValue and mySubSQL.MergeInto(myCommand) (the implementation should be obvious and straight-forward).