SQL Injection Attack in short, is to pass malicious code via program parameter to SQL Server and run as dynamic SQL statement. So the fundamental solution to protect your application from SQL Injection Attack is "not to run dynamic SQL statement" or "to check every parameter". The famous saying "all input is evil until proven otherwise" from Writing Secure Code is ture.
"But man, I’m the operation guy but not the coder and I maintain legacy system, what could I do?" You can use SQL login/user with least privilege for the application connection. And you can try URLScan 3.1 to secure IIS from SQL Injection.
URLScan is tool to secure IIS from old day. IIS 6.0 has built-in features that provide security functionality that is equal to or better than most of the features of UrlScan 2.x. You may not hear URLScan for years. MS release URL3.1 to provide feature like avoiding SQL Injection Attack. It’s rule-based tool to reject requst which hitting the rules.
Now let’s rock!
1. Download and install URLScan 3.1 from http://www.iis.net/download/urlscan. The basic setup guide is here.
2. After installing URLScan, modify the configuration file. The first default setting we should pay attendtion is "AllowHighBitCharacters=0", change it to 1 can enable the URL with Unicode. The second one is "RejectResponseUrl=", change it to "RejectResponseUrl=/~*" will enable the "Logging Only Mode". We should test it in production environment carefully first to avoild banning valid request.
3. Replace "RuleList=" to "RuleList=SQL Injection,SQL Injection Headers", and add the below rules to tail of the config file.
[SQL Injection]
AppliesTo=.asp,.aspx
DenyDataSection=SQL Injection Strings
ScanUrl=0
ScanAllRaw=0
ScanQueryString=1
ScanHeaders=
[SQL Injection Strings]
–
%3b ; a semicolon
/*
@ ; also catches @@
char ; also catches nchar and varchar
alter
begin
cast
convert
cursor
declare
delete
drop
end
exec ; also catches execute
fetch
kill
open
select
sys ; also catches sysobjects and syscolumns
table
[SQL Injection Headers]
AppliesTo=.asp,.aspx
DenyDataSection=SQL Injection Headers Strings
ScanUrl=0
ScanAllRaw=0
ScanQueryString=0
ScanHeaders=Cookie
[SQL Injection Headers Strings]
–
@ ; also catches @@
alter
cast
convert
declare
delete
drop
exec ; also catches execute
fetch
insert
kill
select
4. Next step is to create a "hello world" asp page and host it into IIS, name it like "test.asp" for example. Visit http://localhost/test.asp?q=select, one log should be written into the log file.
5. Run URLScan for days, and collect the log files. Now we need to analyze them to see if any valid URL includes the SQL Injection Strings or SQL Injection Headers Strings. Download Log Parser, your best friend to analyze any log. Run the query below to get the result.
logparser "SELECT COUNT(*) AS Hits,cs-uri,x-reason,x-control FROM urlscan.*.log TO url.csv GROUP BY cs-uri,x-reason,x-control ORDER BY Hits DESC" -i:W3C -o:CSV
logparser "SELECT COUNT(*) AS Hits,x-reason,x-control FROM urlscan.*.log TO keyword_type.csv GROUP BY x-reason,x-control ORDER BY Hits DESC" -i:W3C -o:CSV
6. Now you can modify the SQL-injection-taboo out of the valid URL or remove the word from SQL Injection Strings and SQL Injection Headers Strings.
7. The final step is change "RejectResponseUrl=/~*" back to "RejectResponseUrl=", URLScan will reject the request which hitting rules from now on. You can analyze the log weekly with the logparser query to monitor if any invalid request is reject.