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 FunctionNow 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
NextThe 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 functionNote: 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 FunctionSo 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"-->
