Preventing SQL Injection

I was reading the Database Input chapter on Writing Secure Code 2nd edition.

SQL injection is no longer a new thing. It has been mentioned many many times.

Some well known prevention technique includes :
DO NOT dynamically constuct SQL statement in your code.
Use Named Parameter on SQL.
Use QUOTE function to nullify invalid character in object names.
Use Stored Procedure.

Using stored procedure is an interesting topic to talk about. While it solve some problems, it does not solve all. If you are using an unsafe SQL in your stored procedure, you are defeating everything else.

Creating dynamic SQL in code is also very common scenerio. I do this very often in situation where the actual SQL is depends on user input. For example, in the search feature of my app, user can choose to filter/search by different fields. The actual SQL that is constructed will then depend on the user fields that he choose.

If you have to construct dynamic SQL, always use Named Parameter in the SQL. And if you are constructing the SQL in stored procedure, use the sp_executesql system stored procedure.