#!/usr/bin/perl -w

use DBI;

#  Test SQL injection attack. The test table 'jobs' was created with
#  the command 'create table jobs (j_id integer, j_value text);' in
#  all three databases.

my @databases = ( 'mysql:test', 'Pg:dbname=test', 'SQLite:test.sq3' );

my @data = (
  { id => 44, value => "Some benign text" },
  { id => 55, value => "Just regular data" },
  { id => 66, value => "Evil data');DELETE FROM jobs;" }
);

{
    foreach my $thisDbName ( @databases ) {

      my $dbh = DBI->connect("DBI:$thisDbName", undef, undef )
        or die "Unable to connect to $thisDbName: " . $DBI::errstr;
      print "Connected OK to $thisDbName.\n";

      #  Clean up test table before we start ..

      my $cmd = "DELETE FROM jobs";
      my $sth = $dbh->prepare($cmd);
      print "Clear out existing data from the test table ..\n";
      $sth->execute or die "Problem executing $cmd: " . $sth->errstr;

      $cmd = "INSERT INTO jobs (j_id, j_value) VALUES (?,?)";
      $sth = $dbh->prepare($cmd);

      #  Add test data into table ..

      foreach my $hashref ( @data ) {

        print "Add "
          ."($hashref->{'id'},$hashref->{'value'})"
          ." to the test table ..\n";
        $sth->execute($hashref->{'id'}, $hashref->{'value'})
          or die "Problem executing $cmd: " . $sth->errstr;
      }

      #  Dump out the resulting tables.

      $cmd = "SELECT * FROM jobs";
      $sth = $dbh->prepare($cmd);
      print "Dump out the result.\n";
      $sth->execute or die "Problem executing $cmd: " . $sth->errstr;

      DBI::dump_results($sth);
      print "\n";
    }
}

##</code><code>##

Connected OK to mysql:test.
Clear out existing data from the test table ..
Add (44,Some benign text) to the test table ..
Add (55,Just regular data) to the test table ..
Add (66,Evil data');DELETE FROM jobs;) to the test table ..
Dump out the result.
'44', 'Some benign text'
'55', 'Just regular data'
'66', 'Evil data');DELETE FROM jobs;'
3 rows

Connected OK to Pg:dbname=test.
Clear out existing data from the test table ..
Add (44,Some benign text) to the test table ..
Add (55,Just regular data) to the test table ..
Add (66,Evil data');DELETE FROM jobs;) to the test table ..
Dump out the result.
'44', 'Some benign text'
'55', 'Just regular data'
'66', 'Evil data');DELETE FROM jobs;'
3 rows

Connected OK to SQLite:test.sq3.
Clear out existing data from the test table ..
Add (44,Some benign text) to the test table ..
Add (55,Just regular data) to the test table ..
Add (66,Evil data');DELETE FROM jobs;) to the test table ..
Dump out the result.
44, 'Some benign text'
55, 'Just regular data'
66, 'Evil data');DELETE FROM jobs;'
3 rows