Q

Can you prevent SQL injection attacks with stored procedures?

Can SQL injection attacks be prevented by using stored procedures? Unfortunately, no, says application security expert Dan Cornell.

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.

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

Dig deeper on Building security into the SDLC (Software development life cycle)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchSOA

TheServerSide

SearchCloudApplications

SearchAWS

SearchBusinessAnalytics

SearchFinancialApplications

SearchHealthIT

Close