Can you prevent SQL injection attacks with stored procedures?
If I use stored procedures, does that guarantee my application will be safe from SQL injection?
Unfortunately, no. SQL injection
flaws exist when inputs passed to an application are supposed to be treated as data, but due to a failure to escape or filter those values, certain inputs containing SQL control characters are instead treated as code that is executed by the database server. This often happens when database queries are built up in Web applications from a combination of static text and unfiltered inputs from cookies or HTTP parameters.
The benefit of using stored procedures
in most cases is that it performs the escaping required so that the application treats inputs as data to be operated on rather than SQL code to be executed. For example, T-SQL code in the stored procedure might look like:
SELECT * FROM User where username = @username
In this case the database handles escaping any SQL control characters that might have been passed in with the @username parameter. The problem is that T-SQL code will also allow for the creation of queries from a combination of static text and user inputs. For example:
EXEC('SELECT * FROM User where userid = ' + @userid)
In this case, if the @userid parameter was something like:
12345 OR 1=1
It would still be possible for an attacker to execute a SQL injection attack -- even though stored procedures were in use.
Therefore, stored procedures can help to provide protection against SQL Injection attacks, but ultimately developers must understand the underlying causes of these vulnerabilities and build applications with the appropriate threats in mind.
This was first published in March 2006