Tuesday, November 30, 2010

Preventing Classic ASP Application from SQL Injection


"An attack technique used to exploit web sites by altering backend SQL statements through manipulating application input."

SQL Injection happens when a developer accepts user input that is directly placed into a SQL Statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from your database, but also modify and delete it.

SQL (structured query language) is a very powerful gun for hackers. 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 .

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

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.

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.


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.

SELECT a.name,b.name FROM sysobjects a,syscolumns
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 
 EXEC('UPDATE ['+@T+'] 
 SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor;

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. 

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.

Dim BlackList, ErrorPage
BlackList = Array("=","#","$","%","^","&","*","|",";",_
                  "--""/*", "*/""@@",_
                  "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 ( InStr (lstr, s) <> 0 ) Then
          CheckStringForSQL = true
          Exit Function
        End If
    End If
  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.

CookieExceptionList = Array("""","(",")")
Function IsExceptionList(str,varType)
        For Each item in CookieExceptionList
                Exit Function
            End If
    End If
End Function

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

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

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.

'  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
    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.

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