Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re^2: Preventing MySQL Injection

by gamache (Friar)
on Jan 03, 2008 at 15:42 UTC ( [id://660222] : note . print w/replies, xml ) Need Help??

in reply to Re: Preventing MySQL Injection
in thread Preventing MySQL Injection

Are placeholders truly faster than using DBI->quote()? I'm not trying to be contrary, but I thought that placeholder code was converted to a stored procedure before execution. Even with this overhead, it is faster?

Regardless of speed, I advocate the use of placeholders for the safety and readability benefits.

Replies are listed 'Best First'.
Re^3: Preventing MySQL Injection
by Joost (Canon) on Jan 03, 2008 at 19:57 UTC
    Technically, I think it depends on the specific DBD driver you're using what happens exactly when you're using placeholders, but one thing to consider that a statement using place-holders can be static and so only needs to be parsed once, which can mean considerable speedup.

    For example:

    my $sth = $dbh->prepare("SELECT something WHERE field=?"); for (@list_of_stuff) { $sth->execute($_); push @results,$sth->fetchrow_arrayref(); }
    for (@list_of_stuff) { my $sth = $dbh->prepare("SELECT something WHERE field=".$dbh->quote( +$_)); $sth->execute(); push @results,$sth->fetchrow_arrayref(); }
    Combine that with prepare_cached, and you can get probably see that there is a lot of potential for increased speed with placeholders. Especially if the database or its client library implements place holders natively (as I believe MySQL does).
Re^3: Preventing MySQL Injection
by dsheroh (Monsignor) on Jan 03, 2008 at 20:33 UTC
    Depending on the DBD you're dealing with, placeholders may or may not be faster, but at least they'll be not-slower. Splitting your queries up into explicit "prepare" and "execute" commands doesn't add any extra work for the database - if you just execute the literal query string, it still has to be implicitly prepared first.