SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways around whatever protection you try to manually write.

Don't be lazy. Write it so that the user picks the possible columns and operations from drop-downs, and only enters the parameter values into free text. Then you construct the dynamic SQL with the columns you know exist and with strongly-typed parameters that can only be treated as such rather than just appending your query with whatever someone typed into a form field on a web page and blindly executing it.

Surely there aren't 8,000 columns in this table, and the number of permutations of clauses that a user can add are not exhaustive. You could always limit it logically to 5 or 10 clauses so that you don't have GB-long WHERE clauses.

Some constructive ideas for dealing with dynamic SQL and flexible WHERE clauses:

• Protecting Yourself from SQL Injection - Part 1
• Protecting Yourself from SQL Injection - Part 2

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Other answers where I talk about the kitchen sink procedure:

• Tsql Query speed slow due to Or within where clause, causing index scan instead of seek
• Plan cache memory: parameterized SQL vs stored procedures
• SQL Server procedure optimisation
• Strange looking where clause in sql server
• Stored procedure with recompile