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

Posted on 2011-04-18
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
    LVL 6

    Accepted Solution

    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

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

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    >>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.
    LVL 75

    Expert Comment

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

    Assisted Solution

    URLScan is a possible solution:

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

    Expert Comment

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

    Author Comment

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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    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.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now