About a single quote character causing a problem? Wow, you must think that the DBD modules are almost completely untested?
No -- I'm confident that the DBD modules are tested against this specific challenge.
Anyway, just to be sure, I expanded my script ..
#!/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;" },
{ id => 77, value => 'Horrible data");DELETE FROM jobs;' },
{ id => 88, value => "More Evil data\\');DELETE FROM jobs;" },
{ id => 99, value => 'More Horrible 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";
}
}
And I still got good results ..
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 ..
Add (77,Horrible data");DELETE FROM jobs;) to the test table ..
Add (88,More Evil data\');DELETE FROM jobs;) to the test table ..
Add (99,More Horrible 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;'
'77', 'Horrible data");DELETE FROM jobs;'
'88', 'More Evil data\');DELETE FROM ...'
'99', 'More Horrible data\");DELETE F...'
6 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 ..
Add (77,Horrible data");DELETE FROM jobs;) to the test table ..
Add (88,More Evil data\');DELETE FROM jobs;) to the test table ..
Add (99,More Horrible 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;'
'77', 'Horrible data");DELETE FROM jobs;'
'88', 'More Evil data\');DELETE FROM ...'
'99', 'More Horrible data\");DELETE F...'
6 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 ..
Add (77,Horrible data");DELETE FROM jobs;) to the test table ..
Add (88,More Evil data\');DELETE FROM jobs;) to the test table ..
Add (99,More Horrible 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;'
77, 'Horrible data");DELETE FROM jobs;'
88, 'More Evil data\');DELETE FROM ...'
99, 'More Horrible data\");DELETE F...'
6 rows
So it's not guaranteed that these three DBDs are 100% protected, but a few obvious tests show that I'm fairly confident that the three DBDs that I care about are OK.
Alex / talexb / Toronto
"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds