|
So you're programming a database with perl. You need to know about placeholders.
Placeholders will escape "special" characters for you automatically, protect you from SQL injection vulnerabilities, and potentially make your code faster and cleaner to read.
I can't think of a reason NOT to use them.
Bad code
#Create the CGI.pm object and the database connection
#somewhere up here.
#Make sure to use the RaiseError attribute of the connect()
#to get automatic status checking of statements. . .
my $firstname=param('firstname');
my $lastname=param('lastname');
my $insert=$dbh->prepare("insert into users(firstname,lastname) values('$firstname','$lastname')");
$insert->execute();
$insert->finish();
What's wrong with that? If the $lastname you insert is O'Reilly you'll execute the statement:
insert into users(firstname,lastname) values('Colin','O'Reilly');
which has an unbalanced single quote. You've got broken SQL and you're (ahem) SOL.
Don't think you can get around this by quoting your statements cleverly- folks will surprise you with what they'll type into any old place.
The first time you store abitrary input (which is pretty much anything coming from a browser) you'll need to fix all the odd characters anyway. Placeholders to rescue!
Better Code
That (?,?) in the prepared SQL statement below represents the placeholders. Use them in your statement and then pass the values to your execute(). This will escape all bizarre characters automatically. You don't need to unescape data on the way out either- it's magic! my $firstname=param('firstname');
my $lastname=param('lastname');
my $insert=$dbh->prepare("insert into users(firstname,lastname) values(?,?)");
$insert->execute($firstname,$lastname);
$insert->finish();
Briefer Code
prepare(), execute() and finish() the statement in one fell swoop! The undef part of do() is where you'd set statement attributes- it needs to be there. my $firstname=param('firstname');
my $lastname=param('lastname');
$dbh->do("insert into users(firstname,lastname) values(?,?)",undef,($firstname,$lastname));
Briefest Code
Same as above, except we can execute stuff straight from the user without worrying about it breaking our SQL statements. $dbh->do("insert into users(firstname,lastname) values(?,?)",undef,(param('firstname'),param('lastname')));
Most efficient method for multiple executes
prepare() your statement using placeholders and re-use it. prepare() is expensive, and reusing your statement can save you a good chunk of time depending on the number of iterations. my $delete=$dbh->prepare('delete from users where id=?');
my @deletes=param('deletes');
foreach(@deletes){
$delete->execute($_);
}
$delete->finish();
But I'm not changing data!
So? You can still get broken SQL from data a user passes to you. Even if your data is just page ID numbers or something equally mundane, you can't trust anything that comes from a browser.
A user can download your forms HTML and submit whatever they want to any form field. Just because you think "pageID" will always be a number doesn't mean they can't submit: 31337 hax0r!'; delete * from table_name
Barring attacks, you'll still have stabler web applications because you'll be safe from all the "O'Reillys" of the world. Use placeholders and you'll probably see a lot fewer errors barfed from your web applications. They're a database geeks best friend!
|
