On 01.06.2006 02:29, Jeff Zucker wrote:
Mason Loring Bliss wrote:
$sth->execute($dbh->quote($key));
Don't do that. Either use the quote() method or use placeholders,
never both.
Preferably use placeholders, especially for repeated statement executions.
Advantage 1 of using placeholders instead of quote:
You can not forget to quote your values. Forget to quote() only one
single parameter and your entire application may become vulnerable to
SQL injection. Imagine this:
$db->do("update counters set n=n+1 where x=$parameter"); # note the
missing quote()!
Assume $parameter="42; drop table counters" ...
When you use placeholders, this simply CAN NOT happen:
$db->do("update counters set n=n+1 where x=?",undef,$parameter);
With the "evil" $parameter value, the three things may happen: Update of
no row, update of the wrong row, database error due to the "strange"
value. But there is ABSOLUTELY no way to drop the counters table using
strange $parameter values. (And by the way, don't pass unchecked
parameters to DBI.)
Advantage 2 of using placeholders instead of quote: It makes things
easier and faster for all databases that can use prepared statements:
for $i (1..10_000) {
$sth=$dbh->prepare('insert into sometable values
('.$dbh->quote($i).')');
# ^-- DB parses the SQL string
$sth->excecute();
# ^-- DB executes the query
$sth->finish();
# ^-- cleanup
}
This code causes 10_000 runs of the DB's SQL parser, 10_000 inserts,
10_000 cleanups. ($dbh->do() would have done the same.)
Compare with this code:
$sth=$dbh->prepare('insert into sometable values (?)');
# ^-- DB parses the SQL string
for $i (1..10_000) {
$sth->execute($i);
# ^-- DB executes the query
}
$sth->finish();
# ^-- cleanup
This still causes 10_000 inserts, but only one run of the parser and one
cleanup. Guess what is faster ...
Some DBDs let the database parse the SQL string at the first execute,
but it really makes no difference. For execute number 2 to 10_000, the
SQL string is already parsed.
For "simple minded" database that can't use prepared statements, DBI
parses the query string and automatically inserts the value passed to
execute at the proper place. Not as efficient as prepared statements but
still the second variant of the code avoids some parser runs.
Alexander
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
