SQL Injection Mitigation: Using Parameterized Queries
Michael Howard wrote an excellent article yesterday on how the SDL addresses SQL injection. He walks through three coding requirements/defenses:
- Use SQL Parameterized Queries
- Use Stored Procedures
- Use SQL Execute-only Permissions
As Michael points out, only the first, parameterized queries, remedies the problem. The other two provide additional defense.
The good news is that changing your ASP pages to use parameterized queries instead of just dynamically building the query is dead simple. The bad news is that MSDN doesn't have a lot of samples of how to do parameterized queries in ASP so I thought providing one would be helpful.
As an example, I'm sure that a lot of the websites that have been compromised recently via SQL injection have something like this:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _
& "Initial Catalog=website;User Id=user;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
strSQL = "SELECT name, info FROM [companies] WHERE name =" & strSearch & "';"
Set objSearchResults = objConnection.Execute(strSQL)
This code is going to be extremely vulnerable to SQL injection since it's just taking the user input (which was passed in via a query string from a web form) and pasting it into the SQL statement.
The good thing about parameterization is that it separates the 'executable' code ("SELECT name, info...") from the 'data' (strSearch) we're using. With a few changes, we can make this code use parameters for the query and, with this small change, defend against being exploited in this way.
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _
& "Initial Catalog=website;User Id=user;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
strSql = "SELECT name, info FROM [companies] WHERE name = ?;"
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSql
objCommand.Parameters(0).value = strSearch
Set objSearchResults = objCommand.Execute()
All that we needed to do was:
- Replace the query string in our SQL squery statement with a ? (which is the placeholder for a parameter).
- Create a new Command object for our command.
- Assign our active connection and command text to the Command object.
- Set the first parameter in the parameters collection to our dynamic string.
- Execute the command.
If we needed to use multiple parameters in our query, we'd add additional question marks to strSQL and additional parameters to the Parameters collection. For example:
...
strSql = "SELECT name, info FROM [companies] WHERE name = ?" _
& "AND info = ?;"
...
objCommand.Parameters(0).value = strName
objCommand.Parameters(1).value = strInfo
...
There is a BIG caveat on this -- the method I show above has a performance hit because I haven't specified the types of the parameters. This means that ADO has to make a roundtrip to the SQL server to figure out the type before actually using it. You can fix this by creating parameters objects with the appropriate type which would have the added bonus of doing simple input validation as well. If there's interest, I'll write a followup in the next few weeks with some samples of typed, parameterized queries. (EDIT: Written, it's here.)
Additional info is available on MSDN here. NomadPete has a fuller walkthrough here that covers parameterized queries and stored procedures.
As always, this is only part of the job in securing against SQL injection; however, it is probably the single most useful change you could make.
(Big thanks to Bala Neerumalla for tech reviewing this for me.)
(Edit: Fixed two minor issues with the code examples. Thanks, Steve!)
Continue on to Part 2
