Best way to secure an IN query with PDO

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

1 Like