execute/quote issue...

Hi, all.

I'm experiencing something odd, and I was hoping someone might shed some
light on it for me.

If I do this:

$sth = $dbh->prepare("delete from attendees where name = ?");

This will fail:

$sth->execute($dbh->quote($key));

....but this will succeed:

$sth->execute($key);

I don't think there's anything wrong with the result of quote(), because in
the same place, this will work:

$dbh->do("delete from attendees where name = " . $dbh->quote($key) . ";");

If someone could tell me what's going on here, I'd be grateful. It seems
that adding quotes to the key, as quote() is doing, makes it the statement
fail to match. But, it seems like there should be quotes used here... (Also,
FWIW, the presence or absence of a semicolon at the end of the statement seems
to not matter for the prepare...)

aTdHvAaNnKcSe!

--
Mason Loring Bliss mason@blisses.org Cthulhu fhtagn!
http://blisses.org/ awake ? sleep : random() & 2 ? dream : sleep;

Re: execute/quote issue...

Mason Loring Bliss wrote:
> $sth->execute($dbh->quote($key));
>
Don't do that. Either use the quote() method or use placeholders, never
both.

--
Jeff

Re: execute/quote issue...

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:alexander@foken.de http://www.foken.de/alexander/