Can you prevent SQL injection attacks with stored procedures?

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?

    Requires Free Membership to View

    Login

    When you register, you'll receive targeted emails designed to keep you informed of the most relevant information on Agile development, application security, testing & QA, software requirements, and more.

    Hannah Smalltree, Editorial Director

    By submitting your registration information to SearchSoftwareQuality.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSoftwareQuality.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

SQL injection resources
Don't become a victim of SQL injection

Defense tactics for SQL injection attacks
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