A script is written to search and clean the database based on signature (characteristic substring) in the javascript code. SQL Query log is added to record all the executed queries. Nothing was found. The problem was finally identified by checking the web server visit log. There are visit requests where SQL commands such as UPDATE used in query string parameter value. It takes advantage of the fact that two consecutive SQL statements can be executed one after the other. This SQL injection attack achieved its goal. Checking the request parameter before executing the SQL can catch such problems. Use of stored procedure and avoid this vulnerability.
A perl script is written to analyze the visit log files and extract these attack requests. The attacked page and attacker's IP is found. Whois service is used to locate where the attacker's IP is from. Patches were made to the attacked page and the site.
Below is the perl script to analyze web server visit log. It extract queries using the 'update' command. Actually it's found that command 'select' is also used in other queries. Can change the script to extract those as well.
#!/usr/bin/perl
#
# @Author: Tom
# @Created on: 9/30/2010
# @Last modified: 9/30/2010
# @Usage example: perl Analyzer.pl < ex100920.log > 100920.txt
# All lines containing the substring "1091+update" are extracted.
#
use strict;
my @fields;
my $fields_ct;
my $line_ct = 0;
my $attack_line_ct = 0;
my @attacked_Pages;
my @attack_IPs;
my $v;
my @vs;
my $i;
my $prefix;
my @ascii_table = (
"NUL",
"SOH",
"STX",
"ETX",
"EOT",
"ENQ",
"ACK",
"BEL",
"BS",
"HT",
"LF",
"VT",
"FF",
"CR",
"SO",
"SI",
"DLE",
"DC1",
"DC2",
"DC3",
"DC4",
"NAK",
"SYN",
"ETB",
"CAN",
"EM",
"SUB",
"ESC",
"FS",
"GS",
"RS",
"US",
" ",
"!",
"\"",
"#",
"\$",
"%",
"&",
"'",
"(",
")",
"*",
"+",
",",
"-",
".",
"/",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
":",
";",
"<",
"=",
">",
"?",
"@",
"A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z",
"[",
"\\",
"]",
"^",
"_",
"`",
"a",
"b",
"c",
"d",
"e",
"f",
"g",
"h",
"i",
"j",
"k",
"l",
"m",
"n",
"o",
"p",
"q",
"r",
"s",
"t",
"u",
"v",
"w",
"x",
"y",
"z",
"{",
"|",
"}",
"~",
"DEL",
);
while(<>) {
$line_ct ++;
if (/^\s+$/) { next; } # ignore empty line.
if (/id=1091\+update/) {} else { next; } # ignore non-attack lines.
$attack_line_ct ++;
print $line_ct . ": \n" ;
print $_ . "\n";
print "Fields dump: \n";
@fields = split(' ', $_);
$fields_ct = @fields;
for ($i = 0; $i < $fields_ct; $i ++) {
print "$i: ";
$v = $fields[$i];
if ($i == 6) {
$v =~ s/\%2B/+/g;
#print "$v\n";
@vs = split('\+', $v);
foreach my $u (@vs) {
if ($u =~ /^varchar\(8000\)/) {
print "$u";
} elsif ($u =~ /(cast\()(char\()((\d)+)\)/) {
print "cast(" . $ascii_table[$3];
} elsif ($u =~ /(char\()((\d)+)\)/) {
print $ascii_table[$2];
} else {
print "$u";
}
#print "+";
print " ";
}
print "\n";
} elsif ($i == 5) {
if (Page_Exists($v) == 0) {
push(@attacked_Pages, $v);
}
print "$v\n";
} elsif ($i == 9) {
if (IP_Exists($v) == 0) {
push(@attack_IPs, $v);
}
print "$v\n";
} else {
print "$v\n";
}
}
print "\n";
}
print "$attack_line_ct attack requests found.\n";
my $Page_ct = @attacked_Pages;
print "$Page_ct attacked Page(s) found: \n";
foreach my $p (@attacked_Pages) {
print "$p\n";
}
my $IP_ct = @attack_IPs;
print "$IP_ct attacking IP(s) found: \n";
foreach my $p (@attack_IPs) {
print "$p\n";
}
sub Page_Exists() {
my ($ip) = @_;
foreach my $p (@attacked_Pages) {
if ($ip eq $p) { return 1; }
}
return 0;
}
sub IP_Exists() {
my ($ip) = @_;
foreach my $p (@attack_IPs) {
if ($ip eq $p) { return 1; }
}
return 0;
}
For cleaning the database, an ASP script is written.
'
' Remove injected code from database tables.
' Use signature and full_signature to specify the injected code.
' Use request string "doClean=y" to do cleaning.
' If not use doClean, will only show infected rows.
' Tom 9-24-2010
'
Dim signature, full_signature
signature = "</script>"
full_signature = "<script src=http://.../...js></script>"
Dim doClean
doClean = false
if request("doClean") <> "" then doClean = true
Response.Write("<p>Use doClean request parameter to do cleaning. ")
Response.Write("doClean = " & doClean & "</p>")
call getTables()
function getTables()
'Response.Write("test()<br>")
Dim db, rs, sql, count, tbl, infectedTblCount
infectedTblCount = 0
sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE'"
set db = Connect()
set rs = ExecuteRS(db, sql)
count = 1
do while not rs.eof
tbl = rs("Name")
Response.Write("Table " & count & ". " & tbl & "<br>")
if getTableColumns(tbl) > 0 then infectedTblCount = infectedTblCount + 1
count = count + 1
rs.MoveNext()
loop
Response.Write("<p>" & infectedTblCount & " tables are infected.</p>")
call rs.close()
set rs = nothing
call db.close()
set db = nothing
end function
function getTableColumns(tbl)
Dim db, rs, sql, col, str, chk
sql = "select column_name as Name from INFORMATION_SCHEMA.COLUMNS where TABLE_name ='" & tbl & "'"
set db = Connect()
set rs = ExecuteRS(db, sql)
count = 0 ' count of infected columns.
do while not rs.eof
col = rs("Name")
chk = checkTblColumn(tbl, col)
str = str & "<li>" & col & chk & "</li>"
if len(chk) > 0 then
count = count + 1
if doClean then str = str & cleanTblColumn(tbl, col)
end if
rs.MoveNext()
loop
str = "<ol>" & str & "</ol>"
Response.Write(str)
getTableColumns = count
call rs.close()
set rs = nothing
call db.close()
set db = nothing
end function
function checkTblColumn(tbl, col)
Dim db, rs, sql, val, str
sql = "select " & col & " as Name from " & tbl
set db = Connect()
set rs = ExecuteRS(db, sql)
count = 0
str = ""
do while not rs.eof
val = rs("Name")
if InStr(1, val, signature) > 0 then
str = str & ("<li>Infected row: " & encodeStr(val) & "</li>")
count = count + 1
end if
rs.MoveNext()
loop
if count > 0 then
str = "<font color='red'>" & count & " rows infected</font>" & str
str = "<ol>" & str & "</ol>"
'Response.Write(str)
checkTblColumn = str
else
checkTblColumn = ""
end if
call rs.close()
set rs = nothing
call db.close()
set db = nothing
end function
function cleanTblColumn(tbl, col)
Dim db, rs, sql, val, str
set db = Connect()
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open tbl, db, 1, 2, adCmdTableDirect
count = 0
str = ""
do while not rs.eof
val = rs(col)
if InStr(1, val, signature) > 0 then
rs(col) = replace(val, full_signature, "")
rs.Update()
count = count + 1
end if
rs.MoveNext()
loop
if count > 0 then
str = "<font color='red'>CLEANED " & count & " rows infected</font>" & str
cleanTblColumn = str
else
cleanTblColumn = ""
end if
call rs.close()
set rs = nothing
call db.close()
set db = nothing
end function
function encodeStr(s)
s = replace(s, "<", "<")
s = replace(s, ">", ">")
encodeStr = s
end function
BTW, this is a wiki article on SQL injection. Search on google for SQL injection would bring up much more articles. Good and important to know for web developers, for security concern.
No comments:
Post a Comment