Parse IIS Log Logparser to filter request to prevent SQL injection.

Problem Scenario

We can reduce traffic by using URL rewrite at the same time making use of Request filtering will ensure unnecessary requests are stopped at IIS web server level itself thus not hitting any database server for further processing. To a greater extent increasingly different combination of useless querystring for given url also a cause of sql server performance degradation.

Parse IIS log using Logparser tool

Using the query below I could see the most frequently used querystring values and it was obvious the site experiencing a SQL Injection attack.

logparser.exe -i:iisw3c “select top 20 count(*),cs-uri-query from ex140702.log

group by cs-uri-query order by count(*) desc” -rtp:-1 >file.txt

 

By viewing the output we can add rules for request that is coming in for webserver in IIS request filtering.

We can keyword for querystring which are identified in the logs by doing this in a way we can restrict unnecessary request and provide access denied to such request. Status code 404.

 

SELECT STRCAT(TO_STRING(sc-status), STRCAT(‘.’, TO_STRING(sc-substatus))) AS Status, COUNT(*)

AS Total FROM w3svc.log to TopStatusCodes.txt GROUP BY Status ORDER BY Total DESC

 

 

Some important areas to be looked for performance stats

 

1. SQL Profiler
2. Database Tuning Advisor

For more details
http://www.peterviola.com/solving-sql-server-high-cpu-with-iis-request-filtering/