Perl - count number of occurrences

http://www.chengfu.net/2005/10/count-occurrences-perl/

The author wants to find out what's the fastest way of counting occurrences of a string in a text. e.g. '-' in “1-4-7-8-37-5-7-8-3-42″. He gives 5 ways of doing this:

1. my $size = (scalar(@{[$string =~ /-/g]}) + 1);
2. my $size = scalar(split /-/, $string);
3. my $size = (($string =~ s/-//g) + 1);
4. my $size = (($string =~ tr/-//) + 1);
5. my $size = 1; $size++ while $string =~ /-/g;

He found the speed to be 4, 3, 5, 1, 2 in the order of fastest to slowest on his machine.

Doodling in math class

Doodling in math class: http://vihart.com/doodling/

Interesting.

Perlpod

Perlpod: The Plain Old Document format.

Description about Perlpod from the above source:

Pod is a simple-to-use markup language used for writing documentation for Perl, Perl programs, and Perl modules.
Translators are available for converting Pod to various formats like plain text, HTML, man pages, and more.
Pod markup consists of three basic kinds of paragraphs: ordinary, verbatim, and command.

Setup email forwarding in linux/unix

Email forwarding can be setup in an email client such as Thunderbird.

Under linux/unix, it can done by admin or by user himself, as explained here.

To do it by the user himself, he only needs to create a file .forward in his account root, and enter the forward email addresses separated by comma or new line. The .forward file should have permission 644.

An example .forward file is:

"|/usr/local/bin/procmail"
example@gmail.com

Java becomes closed source

December 9, 2010: The ASF Resigns From the JCP Executive Committee.

It seems that now Java to Oracle is as C# to Microsoft. Java becomes proprietary language of Oracle. But, C/C++/PHP/Perl/Python/Ruby are still open source.

Since Oracle also owns MySQL, don't know what will happen to MySQL. PostgreSQL is still open source though.

Silicon valley

Here is an introduction to silicon valley.

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.