Mitigating SQL Injection Attacks in IIS 7.5
It is surprising with all the advances in Internet technologies that there is still a lot of old school code being used and reused. Because of that, there are some topics that will be around for many years to come. SQL Injection is one of those topics. Rather than write an abstract blog post about mitigating SQL Injection attacks, I am presenting this topic from my point of view.
I came across a server this week that was performing poorly. I narrowed it down to excessive disk IO. Through Resource Monitor, I was able to narrow it down even further to the SQL Server process. That made me suspicious. A properly configured SQL Server under a reasonable load shouldn’t be generating millions of B/sec on disk activity.
Through the process of elimination I was able to narrow it down to a single website driving requests to the SQL Server. A quick query of the logs for the website made me more suspicious. Here’s a sample of the Log Parser query that I use to help me quickly determine if there may be SQL Injection attacks happening. I have a list of words that I search for, although from experience I find CAST is one of the most common terms so I try it first.
logparser.exe -i:iisw3c “SELECT Top 25 Count(*) as HitCount, c-ip FROM C:\wwwlogs\w3svcXX\u_ex130806.log where cs-uri-query like ‘%cast%’ GROUP BY c-ip ORDER BY HitCount Desc” -o:csv
The results from that query looked like this:
2574,XXX.XXX.XXX.XXX
2504,XXX.XXX.XXX.XXX
1514,XXX.XXX.XXX.XXX
For obvious reasons, I have blanked out the actual IP addresses involved. While there might be legitimate reasons for the word CAST to show up in the query, it is unlikely that there would be so many hits from the same IP. I used Log Parser again to get those suspicious queries.
logparser.exe -i:iisw3c -o:csv “SELECT * INTO suspicious.csv FROM C:\wwwlogs\w3svcXX\u_ex130806.log where cs-uri-query like ‘%cast%’”
Looking through suspicious.csv, I could tell it was in fact a SQL Server Injection attack. Here is a snippet of part of the string from cs-uri-query.
%27%20or%201%3Dconvert%28int%2C%28char%2882%29%2bchar%2833%29%2b%28select%20top%201%20cast%28isnull%28%5BEmail1%5D%2Cchar%2832%29%29%20as%20nvarchar%284000%29%29%2bchar%2894%29%2bcast%28isnull%28%5BUserPassword%5D%2Cchar%2832%29%29%
I returned to my original Log Parser query to get the IP addresses being used and I was able to add those to IP Address and Domain Restrictions. That is a good first step and it allows you to continue to monitor the site log files while research continues. Any new requests coming in from those IP addresses have a sc-win32-status code of 403. Disk activity dropped significantly after this.
Shortly after that, I created a new Windows Firewall rule to block those IP addresses. That allowed me to start getting a clean site log file where where requests from the blocked IP addresses won’t show up. By moving the restrictions to the Windows firewall, it is very easy to enable and disable the incoming rule for further testing.
That brings me to the most important piece of this blog post. The best approach to resolve SQL Injection attacks is to make sure the web application is not vulnerable. The best way to mitigate them is not by restricting IP addresses but to filter out potentially dangerous requests. I opened up Request Filtering feature on the server and created a Filtering Rule using some common strings that I have encountered over the years.
This rule scans the url and query string for .asp and .aspx pages denying anything that matches the deny strings specified. You will want to carefully implement a rule like this to ensure that you are not blocking legitimate traffic. With the way that this specific site functions, it was also necessary to scan the URL, but in most cases you are good by merely scanning the query string. After saving the rule, I disabled the firewall rule and the server continues to perform as expected.
You can monitor the site log files to see what requests are being filtered since they return a 404.19 status. For a list of the HTTP Substatus codes returned, refer to this link: http://www.iis.net/configreference/system.webserver/security/requestfiltering. Here’s the simple Log Parser query I use to quickly check the logs.
logparser.exe -rtp:-1 “SELECT time, cs-uri-stem, cs-uri-query, c-ip, sc-status, sc-substatus into results.txt FROM c:\wwwlogs\w3svcXX\u_ex130806.log WHERE (sc-status = 404 AND sc-substatus = 19) ORDER BY time
You should note that Request Filtering is a great new feature of IIS 7 and above. In IIS 7 you have to configure it through applicationHost.config. In IIS 7.5, there is an icon for the feature to allow you to configure it through the GUI. Here are a few links to helpful articles if you need to configure this in IIS 7:
http://www.iis.net/configreference/system.webserver/security/requestfiltering/filteringrules
http://technet.microsoft.com/en-us/library/cc754791%28v=ws.10%29.aspx
http://www.iis.net/learn/manage/configuring-security/use-request-filtering
For some common ways to fix your code for vulnerabilities, check out the link below. In his post, Wade also provides a list of common strings that you can use to build your own SQL Injection filtering rule.