Creating Dynamic SQL in a Stored Procedure with SQL Injection protection
Posted on 2006-04-18
I am currently dynamically creating my SQL statements in some of my stored procedures, as quite alot of information is coming through in my parameters. Currently I declare a nvarchar and build my statement in that, inserting my parameter variables where needed, then execute it. But this, i have only recently realised, does not give me the protection that non-dynamic stored procedures give me.
For example, i have several tables that have 2 fields, code and description. If i want to remove, update or insert into one of these tables, i pass the code, description and the table name as parameters. How can i create a statement that can allow me to use a variable in the statement to allow me to specify a specific table to use, and that will offer me protection for SQL Injection attacks.
I have more complex SP's so im hoping that when i find out how to do this i will be able to apply it on a more complex query.