SQL Injection Mitigation: Using Parameterized Queries

http://blogs.technet.com/b/neilcar/archive/2008/05/21/sql-injection-mitigation-using-parameterized-queries.aspx

http://blogs.technet.com/b/neilcar/archive/2008/05/23/sql-injection-mitigation-using-parameterized-queries-part-2-types-and-recordsets.aspx  (not in this post)

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s