Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^4: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite (boggle)

by talexb (Canon)
on Jan 09, 2008 at 21:28 UTC ( #661511=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite (boggle)
in thread Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite

    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


Comment on Re^4: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite (boggle)
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://661511]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (14)
As of 2015-07-07 15:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (90 votes), past polls