Preventing SQL Injection Attacks in Classic ASP

Database Management
SQL injection may be over a decade old, but even the best of us need a reminder once in a while. You should always validate input to your applications! There isn’t a ‘one size fits all’ solution to sanitizing input, so I will attempt to show what a general solution might look like for classic ASP (using VBScript). Remember, you need to keep in mind the specifics of your web application and add/remove things in the sample accordingly. So even though I am focusing on SQL injection here, input validation needs to be done to even prevent cross-site scripting attacks, among others. Check this article on how to prevent XSS to give you an idea of other sorts of validation that would need to be done on user input to secure a web application. If you are looking for something for ASP.NET check out this article.

Please note:

The purpose of this sample is to get folks off the ground and up and running. This is not intended to be a long-term solution to solving SQL injection attacks against your application. Using black lists like in the sample tend to give a lot of false positives that make many applications unusable. Increasing complexity in the list to avoid this leads to performance issues. Also, such simplistic signatures can be worked around by determined hackers. Consider UN/**/ION for example.

You want to use white lists and rules to sanitize input. You should restrict your web application to using stored procedures and calling them using parameterized SQL APIs.


The way this sample is constructed is that I have a script that checks certain inputs against a ‘black list’ of strings, and if I find a match I redirect to an error page. This script can then be ‘included’ into all public facing application scripts that process user input. There are 3 pieces to this solution: the script with the filtering logic, a sample application that will ‘include’ the filtering script and an error page we would forward to. I have added comments to the scripts themselves, so you have the reminders in front of you. Several folks asked about a send email script, so I have included a sample script for that as well. You will need to incorporate it into your application appropriately. Make sure you read the comments in the code as well for all the assumptions. The right way to do db access from web applications is to use parameterized SQL. Check out Neil  Carpenter's blog here on what this looks like.

SqlCheckInclude.asp

This is the code that does the main filtering. Copy the code below into an ASP file and modify according to your needs. The main things you need to add/modify for your needs are the BlackList array and the ErrorPage you want to forward to. Deploy this file in a location that will be accessible to all your web applications. Make sure that the path to your error page is correct. Use a full path here if possible, since this code will get ‘included’ into several applications that may all reside in different physical directories.

<%
'  SqlCheckInclude.asp
'
'  This is the include file to use with your asp pages to
'  validate input for SQL injection.

Dim BlackList, ErrorPage, s

'
'  Below is a black list that will block certain SQL commands and
'  sequences used in SQL injection will help with input sanitization
'
'  However this is may not suffice, because:
'  1) These might not cover all the cases (like encoded characters)
'  2) This may disallow legitimate input
'
'  Creating a raw sql query strings by concatenating user input is
'  unsafe programming practice. It is advised that you use parameterized
'  SQL instead. Check http://support.microsoft.com/kb/q164485/ for information
'  on how to do this using ADO from ASP.
'
'  Moreover, you need to also implement a white list for your parameters.
'  For example, if you are expecting input for a zipcode you should create
'  a validation rule that will only allow 5 characters in [0-9].
'

BlackList = Array("--", ";", "/*", "*/", "@@", "@",_
                  "char", "nchar", "varchar", "nvarchar",_
                  "alter", "begin", "cast", "create", "cursor",_
                  "declare", "delete", "drop", "end", "exec",_
                  "execute", "fetch", "insert", "kill", "open",_
                  "select", "sys", "sysobjects", "syscolumns",_
                  "table", "update")

'  Populate the error page you want to redirect to in case the
'  check fails.

ErrorPage = "/sqlbacklist.htm"
             
'''''''''''''''''''''''''''''''''''''''''''''''''''             
'  This function does not check for encoded characters
'  since we do not know the form of encoding your application
'  uses. Add the appropriate logic to deal with encoded characters
'  in here
'''''''''''''''''''''''''''''''''''''''''''''''''''
Function CheckStringForSQL(str)
  On Error Resume Next
 
  Dim lstr
 
  ' If the string is empty, return true
  If ( IsEmpty(str) ) Then
    CheckStringForSQL = false
    Exit Function
  ElseIf ( StrComp(str, "") = 0 ) Then
    CheckStringForSQL = false
    Exit Function
  End If
 
  lstr = LCase(str)
 
  ' Check if the string contains any patterns in our
  ' black list
  For Each s in BlackList
 
    If ( InStr (lstr, s) <> 0 ) Then
      CheckStringForSQL = true
      Exit Function
    End If
 
  Next
 
  CheckStringForSQL = false
 
End Function


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check forms data
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Form
  If ( CheckStringForSQL(Request.Form(s)) ) Then
 
    ' Redirect to an error page
    Response.Redirect(ErrorPage)
 
  End If
Next

'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check query string
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.QueryString
  If ( CheckStringForSQL(Request.QueryString(s)) ) Then
 
    ' Redirect to error page
    Response.Redirect(ErrorPage)

    End If
 
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check cookies
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Cookies
  If ( CheckStringForSQL(Request.Cookies(s)) ) Then
 
    ' Redirect to error page
    Response.Redirect(ErrorPage)

  End If
 
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Add additional checks for input that your application
'  uses. (for example various request headers your app
'  might use)
'''''''''''''''''''''''''''''''''''''''''''''''''''
%>


TestPage.asp

This is a sample that shows how to ‘include’ the script above in my application. Make sure the path to your include file is correct. The example below is for the application and the include file being in the same directory. Make sure you modify the path if these 2 are not in the same directory.

<%
'  TestPage.asp
'
'
'  This is a file to test the SQLCheckInclude file. The idea here is that you add
'  the include file to the beginning of every asp page to get SQL injection
'  input validation
%>

<!--#include virtual="/SqlCheckInclude.asp"-->

<%
Response.Write("Welcome to the Test Page.")
Response.Write("If you are seeing this page then SQL validation succeeded.")
%>


sqlblacklist.htm

If a ‘black list’ string is found in any input, this is the page you will be forwarded to. You can reuse any custom error page that you already have for this. I am including this only for the sake of completeness.

<%
<center><h1>SQL Commands Not Allowed</h1><br>
<br><h3>Your system submitted SQL commands as variables to our server.<br>
This is not allowed for security reasons (SQL Injection).




Add Feedback