Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: