Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


URL sanitizer for SQL injection attack prevention, ASP/IIS/MS SQL

Posted on 2011-04-18
Medium Priority
Last Modified: 2012-08-14
I have a large number of websites running classic ASP and MS SQL Server 2003. We had problems with SQL injection attacks several years ago. I mostly solved them by having queries such as:

qry="insert into table (column1) values ('" & replace(insertvalue, "'", "''") & "')"

qry="insert into table (column2) values (" & int(insertvalue) & )"

Now, I know there are ways around this if the querystring is encoded instead of plain text, which would block the single quotes and generate an error for numbers. Are there recommended products which will work with IIS and ASP to do a more comprehensive job of this? I would accept a solution that costs money or is licensed. I just want something easy to configure so I don't have to spend a ton of time setting up permission/denial lists.

Surely something like this is available by now? Would really appreciate any advice.

Thank you!
Question by:bbdesign

Accepted Solution

matija_ earned 500 total points
ID: 35419900
Function Injection(str)
      str = Replace(str,"select","")
      str = Replace(str,"drop","")
      str = Replace(str,"--","")
      str = Replace(str,"insert","")
      str = Replace(str,"delete","")
      str = Replace(str,"xp_","")
      str = Replace(str,"*","")
      str = Replace(str,"#","")
      str = Replace(str,"%","")
      str = Replace(str,"&","")
      str = Replace(str,"'","")
      str = Replace(str,"(","")
      str = Replace(str,")","")
      str = Replace(str,"/","")
      str = Replace(str,"\","")
      str = Replace(str,":","")
      str = Replace(str,";","")
      str = Replace(str,"<","")
      str = Replace(str,">","")
      str = Replace(str,"=","")
      str = Replace(str,"[","")
      str = Replace(str,"]","")
      str = Replace(str,"?","")
      str = Replace(str,"`","")
      str = Replace(str,"|","")
      Injection = str
End Function

qry="insert into table (column1) values ('" & Injection(insertvalue) & "')"

Depending on your needs, you can comment out some filters

Author Comment

ID: 35420146
Will this help if the person is using HEX, such as:

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 35421019
>>Are there recommended products which will work with IIS and ASP to do a more comprehensive job of this? <<
Yeah, they are called Stored Procedures and they avoid all the string gymnastics you are going through.  Check them out and they will not cost you a dime.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 75

Expert Comment

by:Anthony Perkins
ID: 35421022
P.S. There is no such animal as SQL Server 2003.  It is either 2000, 2005, 2008 or 2008-R2.

Assisted Solution

Kobe_Lenjou earned 500 total points
ID: 35422165
URLScan is a possible solution: http://www.iis.net/download/UrlScan

I never used it myself, but it seems to work.
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35423379
Please encode your querystring before send request to browser or save it to SQL Table.

Author Comment

ID: 35440532
Hello, can you briefly explain how putting these queries into a Stored Procedure would avoid these problems? Thank you.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 35451251
The easiest and safest method of avoiding SQL Injection is using Stored Procedures that do not use Dynamic SQL.  If you do not want/unable to use Stored Procedures then at the very least use parameterized queries.  Here are some articles on the subject:
SQL Injection
Are stored procedures safe against SQL injection?
The Curse and Blessings of Dynamic SQL

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question