Best way to secure an IN query with PDO


#1

What is the best way to secure an SQL IN statement with PDO? I think PDO doesn’t support binding for IN queries (please correct me if I am wrong), so am I forced to use more manual ways to protect against injection. Or can PDO help me out here?

Assuming $arrayOfUsernames is input from a user via POST (and exploded as needed below):

An example query:

$qstring = 'SELECT username FROM userInfoTable WHERE username IN ($arrayOfUsernames)';

$pdo->prepare($qstring);
$pdo->execute();

#2

Something like this:-


#3

It's unfortunate that sql prepared statements do not handle arrays. It's not php or pdo's fault. It is just the way sql was designed.

So what you to do is to dynamically build the sql based on the number of elements in your IN array.

$usernames = [ 'saban','jimbo','dabo'];
// Desired sql
$sql = 'SELECT * FROM users WHERE username IN (?,?,?);

Of course if you had a different number of names then the in clause would have to adjusted accordingly.

It is possible to this and even handle multiple parameter values but as you might imagine the code can get messy and difficult to maintain.

Fortunately, Doctrine's Database Abstraction Layer (DBAL) has already done the hard work. It's just a thin layer over PDO but allows you to:

$regTeams = ['Team1','Team2','Team3'];
$sql = 'SELECT * FROM poolTeams WHERE regTeamId IN (?);
$stmt = $dbConn->executeQuery($sql,[$regTeams],[Connection::PARAM_STR_ARRAY]);
$poolTeams = $stmt->fetchAll();

Under the hood enough ? are added into the sql statement to match the number of items in the array. You could add additional parameters as well. Everything just works.

So understand the concept and then turn the implementation over to DBAL.

http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

Plenty of other good stuff in DBAL as well.


#4

The above methods are both very good and the one using DBAL is very convenient. Unfortunately, doing it only with PDO prepared statements you have to build the placeholder strings with quite a bit of code like in the PDO tutorial.

However, for simple systems where I use pure PDO I often go for the most concise way by building the SQL without prepared statements. For security I apply a function on the array elements that sanitizes or escapes the data (only one of the versions is necessary):

// a sample list of values to use in IN():
$values = [1,4,10,'12a','xy\'z'];

// VERSION 1. For integers: make sure all array elements are integers
$in = implode(',', array_map('intval', $values));

// VERSION 2. For strings: apply PDO::quote() function to all elements
$in = implode(',', array_map([$pdo, 'quote'], $values));

// VERSION 3. Custom sanitization: allow only letters and numbers in strings
$in = implode(',', array_map(function($v) {
    return "'" . preg_replace('/[^a-zA-Z0-9]/', '', $v) . "'";
}, $values));

// now I can safely inject the values into SQL:
$stmt = $pdo->query("SELECT username FROM userInfoTable WHERE username IN ($in)");

In most cases I only need a one-liner to build the string value for IN() and I use that because it's short.


#5

Thanks for the link @SamA74 - I picked up a few things there.

@ahundiak - I can't use Doctrine in this project unfortunately, but thanks for letting me know of its capabilities with an example.

@lemon_juice. Those are very elegant ways of solving the problem, thanks. I will probably need something like your version 2. Due to my own unfamiliarity with some deeper security issues that might be present with PDO, I am a little wary of PDO::quote. Does it do the same thing as bindValue() would behind the scenes? php.net recommends bound parameters over PDO::quote:

http://php.net/manual/en/pdo.quote.php

If you are using this function to build SQL statements, you are
strongly recommended to use
PDO::prepare() to prepare SQL statements with bound
parameters instead of using PDO::quote() to interpolate
user input into an SQL statement. Prepared statements with bound parameters
are not only more portable, more convenient, immune to SQL injection, but
are often much faster to execute than interpolated queries, as both the
server and client side can cache a compiled form of the query.

I am worried about the line "immune to SQL injection" because it is presented as a differentiating feature of bound parameters over PDO::quote, although it could just be bad wording.

I am currently using a modified version of the last example on the site Sam linked since it binds every value in the IN array (manually), but if that is equivalent in security to what you posted, I'd like to use your method.


#6

Yes, it is unclear wording because it is not a differentiating factor. Actually, this whole paragraph in PHP documentation beginning with "If you are using this function to build SQL statements..." has little factual information in it, to me it's just a representation of a recent fashion for using prepared statements rather than documenting any real stuff. Although, generally it's not bad advice in itself treat it as a guide only, not as the oracle :slight_smile:

PDO::quote() uses a C function provided by the database driver that has been made to escape the string so that it becomes safe to inject in SQL. The only requirement to make it fully safe is for you to specify the correct character set in the connection string. So if you use quote() in this way you use this function for its intended purpose.

If you want to be paranoid then, theoretically, there's some small likelihood that PDO::quote() will be less secure than prepared statements just because it is possible that the database driver used by PHP gets a bug in its mysql_real_escape_string function and will do incorrect escaping. Once there was such a bug in an old PHP version where mysqli_real_escape_string() function escaped string improperly when it contained some rarely used characters and was used in some obscure character set (some eastern one AFAIK, not utf-8) - but it was quickly fixed. So you can ask yourself how likely it may be that such a bug is introduced. I would never worry about it but if you are paranoid then prepared statements will be safer :slight_smile:

Anyway, did you know that PDO for MySQL by default uses emulated prepared statements? If you are in that mode then guess how the strings are escaped when you do prepare() and bindValue(), etc.? It uses the same function to escape values that PDO::quote() uses! And nowhere does the documentation warns you that using PDO by default is not secure because the prepared statements are not real prepared statements! That's because PDO::quote() can be trusted (noting the paranoid disclaimer above).


#7

Thank you - this really helped me decide what to do, and was very informative, too.


#8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.