Preventing SQL Injections in ASP

Author: Bala Neerumalla, Microsoft Corporation

SQL Injection attacks have drawn a lot of attention in the last few months [1]. While there has been a lot of information on best practices to prevent SQL Injection vulnerabilities in ASP.NET and TSQL, there is comparatively very little information on preventing them in ASP code. This paper discusses the common coding mistakes in ASP code that can lead to SQL Injections, the best practices to prevent them and ways to detect these vulnerabilities.

1. SQL injection issues in ASP code

When you combine untrusted user input and code, you need to be careful about injection issues. If you embed user supplied data within SQL statements then you can run into SQL Injection issues and similarly if you embed user supplied data with HTML script, then you can run into HTML scripting issues, commonly referred to as XSS, Cross Site Scripting issues.

There are two common kinds of SQL injection issues:

  • First-order SQL Injection and
  • Second-order SQL Injection

If the user-supplied data comes from ASP’s Request.Form or Request.Querystring and that data is used to construct dynamic SQL statements without any data validation, then attackers can inject SQL commands into the SQL statement and misuse it. These are generally referred to as a First Order SQL Injection vulnerability.

If user input is stored in a database via one ASP page and then retrieved from the database and used to construct dynamic SQL statements in a different ASP page, this is referred to as a Second Order SQL Injection vulnerability.

Let’s look at some examples that demonstrate these two kinds of SQL injection vulnerabilities.

First Order SQL injection issues

As I mentioned in the previous section, first-order SQL injection vulnerabilities exist when the user supplied data is directly used in the construction of dynamic SQL statement. Let’s have a look at the following example

ASP 
strAuthor = Request.QueryString("authorname")
strConnectString = "Provider=SQLOLEDB; Data Source=sqlmac;Initial Catalog=test; Integrated Security=SSPI;"
Set objConn = Server.CreateObject("ADODB.CONNECTION")
Set objCommand = Server.CreateObject("ADODB.COMMAND")
' Connect to SQL Server
objConn.Open(strConnectString)
' Execute the command
strCmd = "select title, description from books where author_name = '" & strAuthor & "'"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute()
' Process the resultset
' Close the objects

If you observe the code, author name is read from a GET parameter (through Request.QueryString) and is then used directly in the construction of a SQL statement (sqlCmd) without any input validation. If the author name is some valid string such as Michael then the query returns the books authored by Michael. But what if the end user supplies the author name as Michael'; drop table books;--? In this case the final SQL command becomes select title, description from books where author_name='Michael';drop table books;-- ' This not only selects the books authored by Michael but also drops the table named books. The problem here is that the developer simply assumed that the input is going to be a string without any single quotes but never validated the input string for that. In fact this code doesn’t work if the author name is some thing like O'Neil.

The best way to fix this problem is to use parameterized queries. In the previous example, assuming that the maximum length of an author name at the database is 50. You can fix the code as below

ASP 
' Execute the command
strCmd = "select title, description from books where author_name = ?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("author", adWChar, adParamInput, 50)
param1.value = strAuthor
objCommand.Parameters.Append param1
Set objRS = objCommand.Execute()
' Process the resultset
' Close the objects

As you can see, author name value is now passed to SQL Server as a parameter. Since the value is not used in the construction of a SQL statement, there is no scope for injection issues.

Let’s look at another example that gets the user input from Request.Form instead of Request.QueryString.

ASP 
strUserName   = Request.Form("username")
strPassword = Request.Form("password")
strConnectString = "Provider=SQLOLEDB; Data Source=sqlmac;Initial Catalog=test; Integrated Security=SSPI;"
Set objConn = Server.CreateObject("ADODB.CONNECTION")
Set objCommand = Server.CreateObject("ADODB.COMMAND")
' Connect to SQL Server
objConn.Open(strConnectString)
' Execute the command
strCmd = "select user_id, user_name from users where user_name='" + strUserName + "' AND password='" + strPassword + "'"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute()
' Process the resultset
' Close the objects

Just like the previous example, the username and password values received from the end user are used in the construction of a dynamic SQL statement without any input validation. This can easily be exploited by entering username as admin'— and a dummy password and make the final sql statement as select user_id, user_name from users where user_name='admin';--' AND password='dummy'. The only difference is that the values are read from Request.Form. Just like the previous example, this can easily be fixed using parameterized queries as shown here.

ASP 
' Execute the command
strCmd = "select user_id, user_name from users where user_name=? AND password=?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("user", adWChar, adParamInput, 20)
param1.value = strUserName
objCommand.Parameters.Append param1
Set param2 = objCommand.CreateParameter ("password", adWChar, adParamInput, 50)
param1.value = strPassword
objCommand.Parameters.Append param2
Set objRS = objCommand.Execute()
' Process the resultset
' Close the objects

Second Order SQL injection issues

Now that we have looked at few first order SQL injection issues, let’s take a look at the following example where the data comes from the database.

ASP 
strBookID = Request.QueryString("bookid")
strConnectString = "Provider=SQLOLEDB; Data Source=sqlmac;Initial Catalog=test; Integrated Security=SSPI;"
Set objConn = Server.CreateObject("ADODB.CONNECTION")
Set objCommand = Server.CreateObject("ADODB.COMMAND")
Set objCommand1 = Server.CreateObject("ADODB.COMMAND")
Set rsBook = Server.CreateObject("ADODB.RECORDSET")
Set rsBooks = Server.CreateObject("ADODB.RECORDSET")
' Connect to SQL Server
objConn.Open(strConnectString)
' Execute the command
strCmd = "select title, description, author_name from books where book_id=?"
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set objCommand.ActiveConnection = objConn
Set param1 = objCommand.CreateParameter ("id", adInteger, adParamInput)
param1.value = strBookID
objCommand.Parameters.Append param1
' Open the recordset, another way of executing SQL statements
rsBook.Open objCommand
' Process the resultset
If rsBooks.EOF = False Then
    ' Get other books authored by the same person
    strAuthorName = rsBook.Fields("author_name")
    strCmd = "select * from books where author_name='" + strAuthorName + "'"
    ' Execute this query and process the results
    Set objCommand1.ActiveConnection = objConn
    objCommand1.CommandText = strCmd
    objCommand1.CommandType = adCmdText
    rsBooks.Open objCommand1
    ' Process the books
    rsBooks.MoveNext
End If
' Close the objects

You will notice that in the prior code example I used the Open method of the RecordSet object to execute dynamic SQL. This is another way of executing dynamic SQL in ASP. Let’s look at how the SQL statements are constructed. The first statement, select title, description, author_name from books where book_id=? is a parameterized query as the user input bookid is not used in the construction of the dynamic SQL statement. So there is no SQL injection issue there. But if you look at the second statement, the code uses author_name value which is read from the books table. This can result in a SQL injection issue if an untrusted user controls this data through some other ASP page. If the web site has another page which lets end users to enter book details, then a bad guy can provide some malicious author name like dummy’; <sql statement of his choice>-- and misuse this vulnerability.

The fix is similar to the first two examples. Since this is a simple SELECT statement, you can use parameterized SQL statement to mitigate the second order SQL injection issue.

ASP 
If rsBooks.EOF = False Then
    ' Get other books authored by the same person
    strAuthorName = rsBook.Fields("author_name")
    strCmd = "select * from books where author_name=?"
    ' Execute this query and process the results
    Set objCommand1.ActiveConnection = objConn
    objCommand1.CommandText = strCmd
    objCommand1.CommandType = adCmdText
    Set authParam = objCommand.CreateParameter ("author", adWChar, adParamInput, 50)
    authParam.value = strAuthorName
    objCommand1.Parameters.Append authParam
    rsBooks.Open objCommand1, objConn
    ' Process the books
    rsBooks.MoveNext
End If

Even if the author_name value is validated before inserting it into the database, you should use parameterized SQL queries as a defense in depth step.

Other SQL Injections

Now that we have seen the most common sql injection issues in ASP, let’s see another example where user supplied data is used in the derivation of a table name.

ASP 
strUserName   = Request.Form("username")
strPassword = Request.Form("password")
strConnectString = "Provider=SQLOLEDB; Data Source=sqlmac;Initial Catalog=test; Integrated Security=SSPI;"
Set objConn = Server.CreateObject("ADODB.CONNECTION")
Set objCommand = Server.CreateObject("ADODB.COMMAND")
' Connect to SQL Server
objConn.Open(strConnectString)
' Execute the command
strCmd = "select user_id, user_name from users where user_name=? AND password=?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("user", adWChar, adParamInput, 20)
param1.value = strUserName
objCommand.Parameters.Append param1
Set param2 = objCommand.CreateParameter ("password", adWChar, adParamInput, 50)
param1.value = strPassword
objCommand.Parameters.Append param2
Set objRS = objCommand.Execute()
If objRS.EOF = True Then
    ' Sorry user name doesn't exist with these credentials.
End If
' Get users mails from his table.
strCmd = "select * from " + strUserName + "_emails"
Set objEmails = objConn.Execute strCmd(strCmd)
' Process the emails

A quick look at the above code snippet shows that the web page verifies a user account and then displays emails related to that user. It looks like there is one table for each user that is derived from the user’s name. So the variable in the second SELECT statement is actually the table name. While you write parameterized SQL queries that use varying values as column data, you cannot use parameterized queries for object names, such as table and column names. You can fix potential SQL injection vulnerabilities of this nature by using proper delimiters and escaping characters. For Microsoft SQL Server object identifiers, you must enclose the object names in square brackets and replace all the occurrences of right square brackets with two right square brackets. This is explained in detail at  http://msdn.microsoft.com/en-us/magazine/cc163523.aspx [2].

Here is how you fix it.

ASP 
strCmd = "select * from [" + Replace(strUserName, "]", "]]") + "_emails]"
Set objEmails = objConn.Execute strCmd(strCmd)

So if the user name contains any spaces or other special characters they will all be treated as part of a table name and there won’t be any syntax errors or SQL injection opportunities. For example, if the username is some thing like admin_emails; drop table admin_emails; --, then the whole statement will become select * from [admin_emails; drop table admin_emails; --_emails]. SQL Server would treat the expression admin_emails; drop table admin_emails; --_emails as one table name because the whole string is enclosed inside square brackets. One note of caution, there should not be any data transformations done after the escaping. If you escape the string and then do some valid unencoding then that can lead to SQL Injections. So make sure you perform all valid unencoding or validation prior to calling the Replace method.

You may have noticed I used the Execute method of the Connection object in the previous example. This is the third way of executing dynamic SQL in ASP code.

2. Identifying SQL Injection issues in ASP code

As I mentioned in the previous section, there are three methods that let you execute SQL statements in ASP.

  • Using the Execute() method of the ADODB.Command object
  • Using the Open() method of the ADODB.RecordSet object
  • Using the Execute() method of the ADODB.Connection object

Use the following steps to identify SQL injection issues in your source code.

  • Identify all the instances of the above mentioned methods and any wrapper functions for these methods in your source code.
  • For each instance of these function calls, observe the way the SQL statement is constructed. Carefully review to find out if the user supplied data is used in the query construction either directly or indirectly.

3. Mitigating SQL Injection issues in ASP

Follow these guidelines to mitigate SQL injection issues in ASP code

  • Validate user input. Use RegExp.Test() method[3] to reject all invalid input.
  • Use parameterized queries for data manipulation language (DML) statements (SELECT/INSERT/UPDATE/DELETE).
  • For SQL object names, escape right square brackets and delimit them with square brackets.
  • For string literals inside data definition language (DDL) statements, escape single quotes and delimit them in single quotes.

References

[1] http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx - Mass SQL injection attacks

[2] http://msdn.microsoft.com/en-us/magazine/cc163523.aspx - New SQL Truncation Attacks And How To Avoid Them

[3] http://msdn.microsoft.com/en-us/library/y32x2hy1(VS.85).aspx – RegExp.Test() method

[4] http://www.microsoft.com/mspress/books/5957.aspx - Writing Secure Code by Michael Howard and David LeBlanc

Page view tracker