PDO binding values for MySQL IN statement

Question

I have an issue with PDO that I'd really like to get an answer for after being plagued by it for quite some time.

Take this example:

I am binding an array of ID's to a PDO statement for use in a MySQL IN statement.

The array would be say: $values = array(1,2,3,4,5,6,7,8);

The database-safe variable would be $products = implode(',' $values);

So, $products would then be a STRING with a value of: '1,2,3,4,5,6,7,8'

The statement would look like:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (:products)

Of course, $products would be bound to the statement as :products.

However, when the statement is compiled and values bound, it would actually look like this:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN ('1,2,3,4,5,6,7,8')

The problem is it is executing everything inside of the IN statement as a single string, given that I've prepared it as comma-separated values to bind to the statement.

What I actually need is:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (1,2,3,4,5,6,7,8)

The only way I can actually do this is by placing the values within the string itself without binding them, however I know for certain there has to be an easier way to do this.

Answer 1

Here is an example of binding an unknown number of record columns to values for an insert.

public function insert($table, array $data)
{
    $sql = "INSERT INTO $table (" . join(',', array_keys($data)) . ') VALUES ('
        . str_repeat('?,', count($data) - 1). '?)';

    if($sth = $this->db->prepare($sql))
    {
        $sth->execute(array_values($data));
        return $this->db->lastInsertId();
    }
}

$id = $db->insert('user', array('name' => 'Bob', 'email' => '[e-mail]'));

Answer 2

A good way to handle this situation is to use str_pad to place a ? for every value in the SQL query. Then you can pass the array of values (in your case $values) as the argument to execute:

$sql = '
SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products.id IN ('.str_pad('',count($values)*2-1,'?,').')
';

$sth = $dbh->prepare($sql);
$sth->execute($values);
$user_ids = $sth->fetchAll();

This way you get more benefit from using prepared statements rather than inserting the values directly into the SQL.

PS - The results will return duplicate user ids if the products with the given ids share user ids. If you only want unique user ids I suggest changing the first line of the query to SELECT DISTINCT users.id

Answer 3

you need to provide same number of ?s in IN as the number of values in your $values array

this can be done easily by creating an array of ?s as

 $in = join(',', array_fill(0, count($values), '?'));

and use this $in array in your IN clause

THis will dynamically provide you with tailor made array of ?s as per your changiing $values array

Answer 4

If the expression is based on user input without binding the values with bindValue(), experimental SQL might not be a great choice. But, you can make it safe by checking the syntax of the input with MySQL's REGEXP.

For example:

SELECT *
FROM table
WHERE id IN (3,156)
AND '3,156' REGEXP '^([[:digit:]]+,?)+$'

Answer 5

Please try like this:

WHERE products IN(REPLACE(:products, '\'', ''))

Regards

Answer 6

The best prepared statement you could probably come up with in a situation like this is something resembling the following:

SELECT users.id
FROM users
JOIN products
ON products.user_id = users.id
WHERE products IN (?,?,?,?,?,?,?,?)

You would then loop through your values and bind them to the prepared statement making sure that there are the same number of question marks as values you are binding.