Preventing MySQL Injection

[Anonymous Monk]

In PHP there is a simple function, mysql_real_escape_string(), that takes whatever string you throw at it and escapes it for mysql.

Is there a perl function that will do the same? I know that $db->quote() exists, but it adds quotes around the string as well, which is not desired.

[Corion]

In Perl (and also in PHP, btw), you don't escape the strings, you use placeholders in your query. I don't know PHP, but here's an example for Perl:

my $sth = $dbh->prepare(<<SQL);
    SELECT (foo,bar,baz) 
    FROM dta
    WHERE (user = ? AND position < ?)
SQL

my $user = $q->param('user');
my $position = $q->param('position');
$sth->execute($user, $position);

Using placeholders protects you from injection attacks because the values are never interpolated into the SQL query by you but only by the driver for your SQL database which knows how to do this safely.

Blindly quoting everything is a stupid approach, because, as you already noticed, the quoting mechanism needs to know whether an element is supposed to be a number or a string. If you want to do the quoting manually, DBI->quote() is the correct approach to use, but you need to take care to validate your data and make sure that numbers look like numbers, strings look like strings and dates look like dates.

[Anonymous Monk]

Guess i'll start using place holders instead.

should this technique be used exclusively, or only where user/untrusted input is provided?

[moritz]

You should use it where possible, since it greatly reduces the (error prone) task to keep track of which variables contain "trusted" data, and which doesn't.

[jhourcle]

You should use placeholders, but there are times when placeholders aren't a suitable replacement for escaping the string ... however, those tend to be risky security wise.

Placeholders only work with values for fields ... if you're trying to do something like allowing the user to select an arbitrary table or field, you'll have to escape the value. I don't know if it's true for all versions, but from what I recall, even setting a LIMIT couldn't be done with a placeholder in older version. In these sorts of cases, I typically abort entirely if values aren't composed of only known good characters ([a-zA-Z0-9_] for fields/tables, [0-9] for limits, etc) ... but it's best to avoid the issues as best you can.

[Joost]

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.

[gamache]

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.

[Joost]

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

vs

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).

[dsheroh]

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.