SQL Injection in Classic ASP and Possible Solutions
page 3 of 8
by Ehsanul Haque
Feedback
Average Rating: 
Views (Total / Last 10 Days): 114024/ 253

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)=Falsethen
        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=strthen
                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"-->

View Entire Article

User Comments

Title: dfa   
Name: asdfa
Date: 2012-11-13 6:20:17 AM
Comment:
asdfas
Title: asdf   
Name: asdf
Date: 2012-09-22 1:41:22 PM
Comment:
'dfads
Title: FDGHFH   
Name: NIKE NFL jerseys
Date: 2012-05-20 11:38:18 PM
Comment:
[/pre]Cheap NFL,NBA,MLB,NHL
[url=http://www.jersey2shop.com/]Jerseys From China[/url]
[url=http://www.jersey2shop.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jersey2shop.com/]cheap China Jerseys[/url]
[url=http://www.jersey2shop.com/]Sports Jerseys China[/url]
[url=http://www.jersey2shop.com/NFL-Jerseys-c68/]NFL Jerseys China[/url]
[url=http://www.jersey2shop.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
NHL Jerseys China
[url=http://www.jersey2shop.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
[/pre]
[pre]We Are Professional China jerseys Wholesaler
[url=http://www.cheapjersey2store.com/]Wholesale cheap jerseys[/url]Cheap mlb jerseys
[url= http://www.cheapjersey2store.com/]2012 mlb all atar jerseys[/url]
[url= http://www.cheapjersey2store.com/ [/url]Cheap China Wholesael[/url]
[url= http://www.cheapjersey2store.com/]Wholesale jerseys From China[/url]
[url=http://www.cheapjersey2store.com/]2012 nike nfl Jerseys[/url]Free Shipping,Cheap Price,7 Days Deliver
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.cheapjersey2store.com/]Jerseys From China[/url]
[url=http://www.cheapjersey2store.com/NFL-Jerseys-c68]NFL jerseys China[/url]
[url=http://www.cheapjersey2store.com/NHL-Jerseys-c96/]NHL Jerseys China[/url]
[url=http://www.cheapjersey2store.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
[url=http://www.cheapjersey2store.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]
[url= http://www.cheapjersey2store.com/]China Jerseys[/url],Free Shipping
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.jerseycaptain.com/]cheap jerseys sale online [/url]
[url= http://www.jerseycaptain.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jerseycaptain.com/NFL-Jerseys-c68]cheap NFL jerseys China[/url]
[url=http://www.jerseycaptain.com/NHL-Jerseys-c96/]NHL Jerseys C
Title: this is good!   
Name: joven
Date: 2012-05-19 11:14:33 AM
Comment:
this is good post of article.. thanks for this upload :)
Title: RE:sample code missing   
Name: Ehsanul Haque
Date: 2012-01-23 12:04:29 PM
Comment:
Hey Bob,
Thanks for notifying me about the missing URL for the sample code. I am in rush right now, but I will try to fix it as soon as possible.
Title: sample code missing   
Name: Bob
Date: 2012-01-23 9:14:23 AM
Comment:
Sadly, the sample code ZIP file is 404 not found. :( I believe I can follow along with the article, and extract the appropriate code to implement, but sample code is almost always more straight forward for fully understanding a concept, since it is usually a full solution vs a tutorial. Thanks for a great article, though!
Title: Possible solution for SQL Injection   
Name: Rey Calanta-ol
Date: 2011-04-01 10:06:12 AM
Comment:
1. Optimize your inputs, you may use replace function to replace all suspicious symbols in the inputs.
2. Use parameterized query.
Title: Great article :)   
Name: Benedict Basa
Date: 2010-11-07 11:51:15 PM
Comment:
Exactly what i needed :)
Title: ASP is here to remain for a long time.   
Name: pickatutorial
Date: 2010-10-05 11:27:44 AM
Comment:
ASP is here to remain for a long time.
Title: nice :)   
Name: MJ
Date: 2010-06-29 5:37:37 PM
Comment:
no comment :)
Title: Replace wrong words   
Name: Eric Coumans
Date: 2009-12-21 10:37:23 AM
Comment:
Hi there,

small question: is it also possible instead of going to the error page, replace the value in the scanned form input?

so when somebody fills in "copenhagen" the function (mentioned above) will change ("open") this into: "c*hagen"...

thanks for the help!
Title: RE: Function always return true   
Name: Ehsanul Haque
Date: 2009-12-09 5:01:13 AM
Comment:
Hello Mr. Javed,
I don't think the function is always returning true. After configuring the sample, open the test.asp and type username "cursor p1" and it will be blocked as the "cursor" is the blacklisted keyword. Similarly, if you write "abc" it will not block as it is not in the blacklist. Also see the relavant code below:

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

Where "lstr" is the string to check and "s" is the blacklisted keyword.

However, please check that the sample is configured correctly. Please let me know if I can help you anyway.

Thanks,
Ehsan
Title: Function always return true   
Name: Javed Iqbal
Date: 2009-12-09 2:01:23 AM
Comment:
\
Title: RE: 谢谢。   
Name: Ehsanul Haque
Date: 2009-12-05 6:40:15 AM
Comment:
Hi,
I don't know Chinese but I think you are looking for code sample. If it is true then please see the entire article here http://aspalliance.com/1703_SQL_Injection_in_Classic_ASP_and_Possible_Solutions.all

Also you can get the sample project by clicking on the Downloads link at the top or browse here http://aspalliance.com/1703_SQL_Injection_in_Classic_ASP_and_Possible_Solutions.all#Page6

Thanks,
Ehsan
Title: Thank you   
Name: Stefan
Date: 2009-11-24 11:22:30 AM
Comment:
Thank you very much. I had some SQL injection problems and I implemented this, tested and it seems to hold up thus far.

Now I have to teach myself stored procedures along with some .net stuff. Thanks again.
Title: :)   
Name: RJ
Date: 2009-01-13 2:04:18 AM
Comment:
good article. it really helps






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-20 9:21:40 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search