Filtering SQL injection from Classic ASP
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 post from Stefan on the ASP.NET team.
Now that UrlScan v3.0 (x86, x64) is out I would highly recommend using that instead of this script. There is also a walk-through for it on implementing SQL injection blocking configuration.
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 ' ' Author: Nazim Lala ' ' 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 = "/ErrorPage.asp" ''''''''''''''''''''''''''''''''''''''''''''''''''' ' 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 ' ' Author: Nazim Lala ' ' 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 file="SqlCheckInclude.asp"--> <% Response.Write("Welcome to the Test Page.") Response.Write("If you are seeing this page then SQL validation succeeded.") %>
ErrorPage.asp
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.
<% ' ErrorPage.asp ' ' Author: Nazim Lala ' ' This is the error page that users will be redirected to if the input cannot ' be validated %> <%Response.Write("ERROR: Invalid Input")%>
SendEmail.asp
This script sends email via a remote SMTP server that uses credentials. You will need to integrate this into your application at the right place to get error reporting via email.
<% ' SendEmail.asp ' Author: Nazim Lala Function SendEmail(email, msg) On Error Resume Next ' If the string is empty, return false If ( IsEmpty(email) ) Then SendEmail = false Exit Function ElseIf ( StrComp(email, "") = 0 ) Then SendEmail = false Exit Function End If Set cdoConfig = CreateObject("CDO.Configuration") With cdoConfig.Fields .Item(cdoSendUsingMethod) = cdoSendUsingPort ' Fill in server name for remote SMTP server and ' credentials .Item(cdoSMTPServer) = "smtpserver.foo.com" .Item(cdoSMTPAuthenticate) = 1 .Item(cdoSendUsername) = "username" .Item(cdoSendPassword) = "password" .Update End With Set cdoMessage = CreateObject("CDO.Message") With cdoMessage 'Fill in sender information Set .Configuration = cdoConfig .From = "me@myself.com" .To = email .Subject = "Test Email" .TextBody = msg .Send End With Set cdoMessage = Nothing Set cdoConfig = Nothing SendEmail = true End Function %> <FORM VERB=POST METHOD="POST"> Test page for checking input with possible SQL injection.<br><br> Email: <INPUT NAME=Email></INPUT><BR> Message: <INPUT NAME=Message></INPUT><BR> Sent: <% = SendEmail(Request("Email"),Request("Message")) %><BR> <BUTTON TYPE=SUBMIT>Submit</BUTTON> </FORM>
Hope this helps. If folks are averse to VBScript I can cook up something for Jscript if there is demand.