in reply to Preventing MySQL Injection

As stated above, either use place-holders - which is preferred because it's cleaner, faster and in general easier to read, or use dbh->quote and don't insert quotes yourself.

When you ask the database driver to quote your stuff (via either mechanism) you never add your own quotes. It won't work, it's not needed and even if it was, it would only add a new point of failure.

"What should it profit a man, if he should win a flame war, yet lose his cool?"

Replies are listed 'Best First'.
Re^2: Preventing MySQL Injection
by gamache (Friar) on Jan 03, 2008 at 15:42 UTC
    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.

[reply]
      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();
}

      [download]
      vs 
      for (@list_of_stuff) {
  my $sth = $dbh->prepare("SELECT something WHERE field=".$dbh->quote(
+$_));
  $sth->execute();
  push @results,$sth->fetchrow_arrayref();
}

      [download]
      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).
      "What should it profit a man, if he should win a flame war, yet lose his cool?"
[reply]
[d/l]
[select]
      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.
[reply]

In Section Seekers of Perl Wisdom