PDO SQLite cannot bind NOT IN expression
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 exception is determined by a built-in SQL condition NOT IN (2)
.
The second operator cannot exclude 2
from the result. The exception is determined by binding the PDO parameter 2
in the condition NOT IN (?)
.
In PDO / MySQL, two statements produce the same results.
What's happening? Is this a bug or is this result documented?
PS I tested this on all PHP versions using https://3v4l.org/otfDj and got identical results from 5.6.0 - 5.6.30, 7.0.0 - 7.2.0alpha2.
source to share
This is because it execute
treats the passed parameters as strings.
So your request would be
$badSql = 'SELECT number FROM (SELECT 1 number UNION SELECT 2) numbers WHERE number not in ("2")';
There are definitely no results equal to "2".
The solution is to bind the 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));
source to share