In this tutorial we will be showing you how to use Request Filtering in IIS to Prevent SQL Injections. We previously did a tutorial called, “How to block bots and spiders with Request Filtering,” and we will touch on a lot of the same concepts here.

Please note that these instructions only apply to our Windows 2008 IIS 7 & Windows 2012 IIS 8 Servers.

First, you will need to make a connection to your site using IIS Manager. Please read our knowledge base article on How to connect to the server using the Microsoft IIS Manager.

Once connected:

Double click the Request Filtering module in IIS Manager.

Now click the URL icon in the Request Filtering module.

Next click Deny Sequence… in the “Actions” section.

In this example we’ll be blocking the common SQL Injection term “varchar” so enter this in the Deny Sequence box and click OK.

So now when someone tries to enter “varchar” into your site’s URL, they will receive the follow error message from the server:

There are a number of other terms that you can also use. Here are some terms you may wish to add to the Deny Sequence rules for your site account as well:

• @@version
• char
• exec
• execute
• declare
• cast

So now when anyone tries to enter any of the above terms into your URL Sequence, they will receive the HTTP Error 404.5 – Not Found error message from the server.

Be Warned! If your site currently uses any of the terms that you deny, you will receive the HTTP Error 404.5 message too. So choose your terms wisely to prevent any issues with your site.

<requestFiltering>
   <filteringRules>
      <filteringRule name="SQLInjection" scanUrl="false" scanQueryString="true">
         <appliesTo>
            <clear />
            <add fileExtension=".asp" />
            <add fileExtension=".aspx" />
            <add fileExtension=".php" />
         </appliesTo>
         <denyStrings>
            <clear />
            <add string="--" />
            <add string=";" />
            <add string="/*" />
            <add string="@" />
            <add string="char" />
            <add string="alter" />
            <add string="begin" />
            <add string="cast" />
            <add string="create" />
            <add string="cursor" />
            <add string="declare" />
            <add string="delete" />
            <add string="drop" />
            <add string="end" />
            <add string="exec" />
            <add string="fetch" />
            <add string="insert" />
            <add string="kill" />
            <add string="open" />
            <add string="select" />
            <add string="sys" />
            <add string="table" />
            <add string="update" />
         </denyStrings>
         <scanHeaders>
            <clear />
         </scanHeaders>
      </filteringRule>
   </filteringRules>
</requestFiltering>

In this tutorial I’m going to show how to check your HTTP logs for a site account in order to find those nasty SQL Injections.

Let’s get started!

In order to complete this task, please make sure you have enabled raw logs for your hosting account by reading our knowledge base article: How do I access the raw log files?

If the SQL Injection happens before enabling the raw log files, then you wont be able to find the SQL Injection since the HTTP logs won’t be provided until the next day, and the past HTTP logs for your site account won’t be available. You may need to Contact Support and ask them if they can provide you with the HTTP logs in order to investigate an SQL Injection.

Please be sure to provide them with the dates of the HTTP logs you wish to access. Also, remember that support won’t have any HTTP logs that are more than 30 days old. If the Injection happened more than 30 days previous, no record HTTP logs will be available for your hosting account.

We’re going to need a special tool to help investigate

To help you search out the SQL Injection from your HTTP Logs, you’re going to need to use a tool called BareGrep. This tool can be downloaded here. Make sure you click on the “Free Version” link (if you like it and think you will put it to good use, consider purchasing the software). It’s a cool little tool because it’s not required to be installed on the computer and just runs off the .exe file.

Time to get down and dirty!

The awesome part about BareGrep it allows you to drag and drop multiple text files into it. This means if you’re not exactly sure of the exact date the injection happened, you can search multiple text files all in one shot.

Open BareGrep and select the text files that you wish to search. Now drag and drop the files into BareGrep’s grey area.

Let’s find those nasty SQL Injections!

We’re going to use a keyword search to find is the line in the HTTP logs where the SQL Injection occurred.

These are the keywords I like to use in BareGrep (feel free to add some of your own):

• – –  (that’s two dashes)
• @@version
• varchar
• char
• exec
• execute
• declare
• cast

Now it’s time to enter each of the keywords one at a time into BareGrep’s text field.

Hey! Hey! Hey! We found something!

Now let’s select the lines in BareGrep and see what we can find. Once selected, copy and paste the lines into an empty Notepad document.

You should get a few lines like the following HTTP line below. I know it looks nasty but let me try to explain what certain things are.

ex121209.log    414    2012-12-09 13:17:34 W3SVC100000 WEB151 216.177.71.6 GET /search.aspx home=177&id=1%27%20or%201=@@version-- 80 - 8.8.8.8 HTTP/1.0 Mozilla/4.0+(compatible;+Synapse) - - www.yourhosteddomainname.com 500 0 0 7639 354 531

This part of the line is stating the date and time (PacificTime) the SQL Injection happened.

2012-12-09 13:17:34

The other part is the web server and IP address.

WEB151 216.177.71.6

The following is interesting because it tells you exactly what page it was that was vulnerable to the SQL Injection. This will also give you a clue on what you will need to patch up on your site to prevent it from happening again.

GET /search.aspx

The other part is what they entered in their web browser when trying to check if a SQL Injection vulnerability is possible. If there is vulnerability this code displays an error message along with the SQL database version. This means that the SQL database is answering to the hacker and it’s a dead giveaway that the web application is vulnerable to a SQL Injection.

home=177&id=1%27%20or%201=@@version--

Here comes the best part of the HTTP log, the hacker’s IP address! The example here belongs to Google’s DNS, but this is where the hacker’s IP address will be located in the HTTP logs. Please remember that most people will hide their real IP address and it doesn’t mean that the IP really belongs to the hacker. The evil person could have been using a network that doesn’t belong to them. Most likely a proxy service they like using to hide behind.

Cool thing about this is that if you’re on an IIS 8 or IIS 7 account, you have the ability to block IP addresses using IIS Manager. Please read our knowledge base article on How to connect to the server using the Microsoft IIS Manager.

If you’re on a IIS 6 server you will need to contact our support department. Ask them to block an IP address for you and provide them with the IP you wish to block.

Okay, so you know how they checked for the vulnerability in your application. Where can you find the injection that changed all of your table fields?

For this you will need to keep looking in the HTTP logs. What I like to do next is enter the hacker’s IP address into BareGrep and see all the Injections the hacker used. It will also show you the other parts of your site that the person visited.

What you will need to look for is the following in the HTTP log. This piece of code in the log will be followed by a bunch of numbers and charters. This friends, is where the tables got inserted with the malicious URLs/text to one of your tables on the SQL database. This also means that the page “/search.aspx” is vulnerable to the SQL Injection.

/search.aspx id=10+declare+@s+varchar(8000)+set+@s=cast

Okay, so now you’re ready to prevent the SQL injections from happening on your site.

I have referred people to the following articles in order to prevent a SQL injection from happening again. I really hope this helps you guys and we can see an end to these stinky SQL Injections.

Filtering SQL injection from Classic ASP

Filtering for SQL Injection on IIS 7 and later

Blocking SQL injection using IIS URL Rewrite