Filtering SQL injection from Classic ASP

SQLinjection may be over a decade old, but even the best of us need areminder once in a while. You should always validate input to yourapplications! There isn’t a ‘one size fits all’ solution to sanitizinginput, so I will attempt to show what a general solution might look likefor classic ASP (using VBScript). Remember, you need to keep in mindthe specifics of your web application and add/remove things in thesample accordingly. So even though Iam focusing on SQL injection here, input validation needs to be done toeven prevent cross-site scripting attacks, among others. Check this articleon how to prevent XSS to give you an idea of other sorts of validationthat would need to be done on user input to secure a web application. Ifyou 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:

Thepurpose of this sample is to get folks off the ground and up andrunning. This is not intended to be a long-term solution to solving SQLinjection attacks against your application. Usingblack lists like in the sample tend to give a lot of false positivesthat make many applications unusable. Increasing complexity in the listto avoid this leads to performance issues. Also, such simplisticsignatures can be worked around by determined hackers. ConsiderUN/**/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.

Theway this sample is constructed is that I have a script that checkscertain inputs against a ‘black list’ of strings, and if I find a match Iredirect to an error page. This script can then be ‘included’ into allpublic facing application scripts that process user input. There are 3pieces to this solution: the script with the filtering logic, a sampleapplication that will ‘include’ the filtering script and an error pagewe would forward to. I have added comments to the scripts themselves, soyou have the reminders in front of you. Several folks asked about asend 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 theassumptions. The right way to do db access from web applications is touse parameterized SQL. Check out Neil  Carpenter's blog here on what this looks like.

SqlCheckInclude.asp

 

Thisis the code that does the main filtering. Copy the code below into anASP file and modify according to your needs. The main things you need toadd/modify for your needs are the BlackList array and the ErrorPage youwant to forward to. Deploy this file in a location that will beaccessible to all your web applications. Make sure that the path to yourerror page is correct. Use a full path here if possible, since thiscode will get ‘included’ into several applications that may all residein 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 NextDim lstr' If the string is empty, return trueIf ( IsEmpty(str) ) ThenCheckStringForSQL = falseExit FunctionElseIf ( StrComp(str, "") = 0 ) ThenCheckStringForSQL = falseExit FunctionEnd Iflstr = LCase(str)' Check if the string contains any patterns in our' black listFor Each s in BlackListIf ( InStr (lstr, s) <> 0 ) ThenCheckStringForSQL = trueExit FunctionEnd IfNextCheckStringForSQL = falseEnd Function''''''''''''''''''''''''''''''''''''''''''''''''''''  Check forms data'''''''''''''''''''''''''''''''''''''''''''''''''''For Each s in Request.FormIf ( CheckStringForSQL(Request.Form(s)) ) Then' Redirect to an error pageResponse.Redirect(ErrorPage)End IfNext''''''''''''''''''''''''''''''''''''''''''''''''''''  Check query string'''''''''''''''''''''''''''''''''''''''''''''''''''For Each s in Request.QueryStringIf ( CheckStringForSQL(Request.QueryString(s)) ) Then' Redirect to error pageResponse.Redirect(ErrorPage)End IfNext''''''''''''''''''''''''''''''''''''''''''''''''''''  Check cookies'''''''''''''''''''''''''''''''''''''''''''''''''''For Each s in Request.CookiesIf ( CheckStringForSQL(Request.Cookies(s)) ) Then' Redirect to error pageResponse.Redirect(ErrorPage)End IfNext''''''''''''''''''''''''''''''''''''''''''''''''''''  Add additional checks for input that your application'  uses. (for example various request headers your app '  might use)'''''''''''''''''''''''''''''''''''''''''''''''''''%>

Thisis a sample that shows how to ‘include’ the script above in myapplication. Make sure the path to your include file is correct. Theexample below is for the application and the include file being in thesame directory. Make sure you modify the path if these 2 are not in thesame 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%><%Response.Write("Welcome to the Test Page.")Response.Write("If you are seeing this page then SQL validation succeeded.")%>

ErrorPage.asp

 

Ifa ‘black list’ string is found in any input, this is the page you willbe forwarded to. You can reuse any custom error page that you alreadyhave 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

Thisscript sends email via a remote SMTP server that uses credentials. Youwill need to integrate this into your application at the right place toget error reporting via email.

<%'  SendEmail.asp'  Author: Nazim LalaFunction SendEmail(email, msg)On Error Resume Next' If the string is empty, return falseIf ( IsEmpty(email) ) ThenSendEmail = falseExit FunctionElseIf ( StrComp(email, "") = 0 ) ThenSendEmail = falseExit FunctionEnd IfSet 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".UpdateEnd WithSet cdoMessage = CreateObject("CDO.Message")With cdoMessage'Fill in sender informationSet .Configuration = cdoConfig.From = "me@myself.com".To = email.Subject = "Test Email".TextBody = msg.SendEnd WithSet cdoMessage = NothingSet cdoConfig = NothingSendEmail = trueEnd Function%>
Test page for checking input with possible SQL injection.

Email: 
Message: 
Sent: <% = SendEmail(Request("Email"),Request("Message")) %>
Submit
 

Hope this helps. If folks are averse to VBScript I can cook up something for Jscript if there is demand.