SQL Injection Attack

One of the servers is hacked. The symptom is that javascript code were inserted into some database tables. When users visit the site, the javascript code would invoke remote site scripts, display a faked virus scan and report (might be a dynamic gif image), and ask the user to download and install a virus removal software. Once the user installs the software, his computer will be infected.

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, "<", "&lt;")
    s = replace(s, ">", "&gt;")
    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.