PDO SQLite cannot bind NOT IN expression

Question

Here is the code:

<?php
$dbh = new \PDO('sqlite:');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$goodSql = 'SELECT number FROM (SELECT 1 number UNION SELECT 2) numbers WHERE number not in (2)';
echo json_encode($dbh->query($goodSql)->fetchAll(PDO::FETCH_NUM));

$badSql = 'SELECT number FROM (SELECT 1 number UNION SELECT 2) numbers WHERE number not in (?)';
$binds = ['2'];
$statement = $dbh->prepare($badSql);
$statement->execute($binds);
echo json_encode($statement->fetchAll(PDO::FETCH_NUM));
?>

The first statement successfully excludes 2 from the result. The exclusion is specified by an inline SQL condition NOT IN (2).

The second statement fails to exclude 2 from the result. The exclusion is specified by a PDO bind of the parameter 2 in a condition NOT IN (?).

In PDO/MySQL, the two statements produce identical results.

What is happening? Is this a bug or is this outcome documented?

P.S. I have tested this on all PHP versions using https://3v4l.org/otfDj and got identical results with 5.6.0 - 5.6.30, 7.0.0 - 7.2.0alpha2.

Answer 1

This is because execute treats passed parameters as strings.

So, your query becomes

$badSql = 'SELECT number FROM (SELECT 1 number UNION SELECT 2) numbers WHERE number not in ("2")';

Definitely, there no results that equals "2".

Solution is to bind params explicilty like int:

$badSql = 'SELECT number FROM (SELECT 1 number UNION SELECT 2) numbers WHERE number not in (?)';
$binds = ['2'];
$statement = $dbh->prepare($badSql);
$statement->bindValue(1, '2', PDO::PARAM_INT);   // this is it
$statement->execute();
echo json_encode($statement->fetchAll(PDO::FETCH_NUM));