Share via

SQL Injection Mitigation: Using Parameterized Queries

  • Article

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

Comments

  • Anonymous
    January 01, 2003
    While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm

  • Anonymous
    January 01, 2003
    While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm

  • Anonymous
    January 01, 2003
    Well, there was quite some chatter over the last few weeks with regards to the massive defacements we

  • Anonymous
    January 01, 2003
    Trodde egentlig dette emnet var dekket godt nok opp i gjennom, men den siste ukas begivenheter viser

  • Anonymous
    January 01, 2003
    SQL injection may be over a decade old, but even the best of us need a reminder once in a while. You

  • Anonymous
    January 01, 2003
    Microsoft has recently published a series of best practices to help developers build SQL code that is

  • Anonymous
    January 01, 2003
    本文翻译自微软博客上刊载的相关文章，英文原文版权归原作者所有，特此声明。（特别感谢NeilCarpenter对本文写作提供的帮助） 近期趋势 从去年下半年开始，很多网站被损害，他们在用于生成动...

  • Anonymous
    January 01, 2003
    There s a lot of noise arround currently ongoig SQL injection attacks and even if that is quite an "old"

  • Anonymous
    January 01, 2003
    I just sent a piece of e-mail to my team about input validation and SQL injection and it occurred to

  • Anonymous
    January 01, 2003
    攻击依然持续不断。Michael和Neil添加了关于这方面的blog，大家可以看看: Michael: How the SDL Addresses SQL injection Neil: SQL Injection

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    Microsoft has recently published a series of best practices to help developers build SQL code that is

  • Anonymous
    January 01, 2003

  1. Introduction SQL Injection is a potential threat to any web application that has a SQL based database
  • Anonymous
    January 01, 2003
    It is very common to us from CSS Security receive calls about SQL Injection and sometimes customers prefers