Placeholders not working
repli2dev opened this issue · 3 comments
Version: 2.4.7
Bug Description
After update from 2.4.6 to 2.4.7 our application crashes on first database query:
$connection = $container->getByType('Nette\\Database\\Connection');
$connection->onConnect[] = function (Connection $connection) {
$connection->query("SET TIME ZONE ?", date_default_timezone_get());
};
With an following error:
Nette\Database\DriverException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"
LINE 1: SET TIME ZONE $1
^ in /project/external/backend/nette/database/src/Database/DriverException.php:25
Stack trace:
#0 /project/external/backend/nette/database/src/Database/Drivers/PgSqlDriver.php(49): Nette\Database\DriverException::from(Object(PDOException))
#1 /project/external/backend/nette/database/src/Database/ResultSet.php(75): Nette\Database\Drivers\PgSqlDriver->convertException(Object(PDOException))
#2 /project/external/backend/nette/database/src/Database/Connection.php(183): Nette\Database\ResultSet->__construct(Object(Nette\Database\Connection), 'SET TIME ZONE ?', Array)
Steps To Reproduce
- Make mentioned query with placeholder on PostgreSQL database.
Expected Behavior
Timezone is set.
There is interesting discussion about the topic in Yii: yiisoft/yii2#6410
From that I would conclude that the PostgreSQL prepared statements (used by PDO) doesn't work when using SET TIME ZONE $1
or when using the placeholder in table/column name position (SELECT * FROM $1)
... and the previous version of nette/database just got around it somehow.
There seem to be a kind of workaround:
$connection->getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
There is some documentation http://php.net/manual/en/pdo.setattribute.php, however the claim It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query.
does seem to be in contrary with our findings.
Works... Thanks :-)