I think you are on the wrong track. Never accept ANYTHING and just execute it. Don't blacklist. Let DBI do its job for you.
Let's say you have a search field and want to execute that in a query. Use prepare/execute with placeholders:
use strict;
use warnings;
use DBI;
my $searchstring = "DROP TABLE mydata;";
$dbh = DBI->connect("dbi:Pg:dbname=$dbname", 'myuser', 'verysecret', {
+AutoCommit => 0}) or die("Can't connect to database");
my $sth=$dbh->prepare("SELECT * FROM mydata WHERE mycolumn = ?") or di
+e($dbh->errstr);
if(!$sth->execute($searchstring)) {
$dbh->rollback;
exit 500; # or whatever you want to do in case of statement level
+error
}
while((my $line = $sth->fetchrow_hashref)) {
# do something with the data
}
$sth->finish;
$dbh->rollback; # Since we didn't change anything, finish the transact
+ion with a rollback instead of commit; just in case
Using the placeholder approach, DBI/DBD::Pg automatically quotes your arguments, meaning it turns potentially harmful characters into their quoted string equivalents.
In this case, it might even return rows if mycolumn holds entries for "DROP TABLE mydata;".
Edit: There is a reason i die() in connect and prepare, but not in execute: When the first two go wrong, this is probably an implementation error or the database just died - needing admin intervention anyway. On the other hand, execute() might just be blocked to some (temporary) circumstances like database locks. Of course, thats just my style of doing things and it's far from perfect. You know, old dogs and new tricks...
Edit 2: Your blacklist approach has also some very interesting side effects. Say, you implement a bug tracker for the company database admin. That bugtracker would be a bit pointless, since you couldn't actually report which SQL statements fail ;-)
BREW /very/strong/coffee HTTP/1.1
Host: goodmorning.example.com
418 I'm a teapot
|