masoudk1990 (12/24/2013)
Much appreciated, Ive read from somewhere if I use QUOTENAME(@FieldName,'''') with 4 single quotations, it makes stored procedure bullet proof from sql injection, but Im still in doubt if its adequate.According to documentations QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
But documentation didnt described how QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
Is there any logic behind this? Or its just a rule?
There are several things you can do to prevent SQL Injection. First, don't use dynamic T-SQL at all. Only use straight T-SQL and parameters. That code can't be modified by values within the parameters. Second, if you must use dynamic T-SQL, only use parameters as parameters in the same way you would with standard T-SQL. There are examples here in the Books Online. Finally, as has been said, put in logic checks to completely sanitize your inputs if you have to dynamically build T-SQL, especially if you're going to go down the dangerous route of passing in table names as values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning