Thursday, 2 June 2011

SQL Injection Attacks - EXEC() vs sp_executesql

Are Stored Procedures safe against SQL Injection Attacks...not necessarily.

If dynamic SQL is involved, they might not be.

This article explains the difference clearly:

Are Stored Procedures safe against SQL Injection Attacks

This extract summarizes why you should use sp_executesql instead of EXEC() in order to prevent Injection Attacks:

"The difference between the EXEC() and sp_executesql is that the former takes SQL statement in a string as parameter. But in case of the latter, it is a system procedure whose first parameter is a parameterized SQL statement. The second parameter is a parameter-list declaration, similar to the parameter list present in the declaration of a stored procedure. And the remaining are simply the parameters in that parameter-list. So the query built using this method is always same whenever it gets called but the user input changes which will be supplied at runtime. If the malicious user enters a value like ' or '1'='1, it will be passed as a parameter to the SQL statement. It won’t be part of the SQL statement any more as we have seen in vulnerable stored procedure above. It will search for the value ' or '1'='1 as product name in the database. Thus preventing SQL Injection attacks. "

No comments:

Post a Comment