Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Placeholders not working #211

Closed
repli2dev opened this issue Nov 14, 2018 · 3 comments
Closed

Placeholders not working #211

repli2dev opened this issue Nov 14, 2018 · 3 comments

Comments

@repli2dev
Copy link

@repli2dev repli2dev commented Nov 14, 2018

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

  1. Make mentioned query with placeholder on PostgreSQL database.

Expected Behavior

Timezone is set.

@dg

This comment has been minimized.

Copy link
Member

@dg dg commented Nov 14, 2018

It is related to ef1a467 (fix for #202).

It seems that there is stupid limitation that you can bind parameters only to SELECT, INSERT, UPDATE, DELETE, or VALUES statements…

@repli2dev

This comment has been minimized.

Copy link
Author

@repli2dev repli2dev commented Nov 15, 2018

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.

dg added a commit that referenced this issue Nov 20, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
@dg dg closed this in 3a27498 Nov 20, 2018
dg added a commit that referenced this issue Nov 20, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
dg added a commit that referenced this issue Nov 20, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
dg added a commit that referenced this issue Nov 20, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
dg added a commit that referenced this issue Nov 20, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
@repli2dev

This comment has been minimized.

Copy link
Author

@repli2dev repli2dev commented Nov 21, 2018

Works... Thanks :-)

dg added a commit that referenced this issue Nov 23, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
dg added a commit that referenced this issue Nov 26, 2018
… UPDATE, DELETE, REPLACE and EXPLAIN [Closes #211]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.