PHP Postgres PDO 驱动不支持prepared statement?

2021-12-26 00:00:00 postgresql php pdo

我是不是疯了，还是 Postgres PDO 驱动程序不支持准备好的语句，而是在客户端模拟它们?

Am I losing my mind, or does the Postgres PDO driver just not support prepared statements, but instead emulates them client side?

以下代码为 prepare() 调用返回 NO ERROR，即使它应该.相反，它在调用 execute() 时返回适用的错误.

The following code returns NO ERROR for the prepare() call, even though it should. Instead, it returns the applicable error when execute() is called.

因为根据 Daniel Vérité 我错了，我添加了他建议的代码.我仍然收到错误.我的代码现在看起来像下面这样，添加了 Daniel 的行.

Since according to Daniel Vérité I'm wrong, I added his suggested code. I still get the error. My code now looks like the below, with Daniel's line added.

<?php
$pdo = new PDO("pgsql:host=myhost;dbname=mydatabase");

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // as suggested by Daniel

$sth = $pdo->prepare('COMPLETE GARBAGE');
echo "[prepare] errorInfo = " . print_r($sth->errorInfo(), true);

$sth->execute();
echo "[execute] errorInfo = " . print_r($sth->errorInfo(), true);

PHP 5.3.15 版，PHP Postgres 客户端 9.1.4 版，Postgres 服务器版 9.2.1.

PHP version 5.3.15, PHP Postgres client version 9.1.4, Postgres server version 9.2.1.

推荐答案

参见 http://www.php.net/manual/en/pdo.prepare.php

注意:

模拟的预处理语句不与数据库通信服务器，因此 PDO::prepare() 不检查语句.

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

(实际上，无论如何都不会立即发送真正的准备好的语句，请参阅下面对 Q2 的回答)

(in fact real prepared statements are not sent immediately anyway, see answer to Q2 below)

无论如何你可以发出:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

获得使用 SQL PREPARE 命令实现的真正准备好的语句.请参阅http://www.php.net/manual/en/pdo.setattribute.php了解更多.

to get real prepared statements implemented with the SQL PREPARE command. See http://www.php.net/manual/en/pdo.setattribute.php for more.

在进一步的讨论和测试中，出现了两个问题:

On further discussion and tests, two questions arise:

第一季度.为什么 pdo::getAttribute(PDO::ATTR_EMULATE_PREPARES) 会产生错误?
实际上 setAttribute 没有出错，但是 getAttribute(PDO::ATTR_EMULATE_PREPARES) 说:

Q1. Why does pdo::getAttribute(PDO::ATTR_EMULATE_PREPARES) yield an error?
Indeed setAttribute doesn't error out but getAttribute(PDO::ATTR_EMULATE_PREPARES) says:

'SQLSTATE[IM001]: 驱动程序不支持此功能:驱动程序支持不支持该属性'

'SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute'

查看 pdo::getAttribute 的文档，它说常量适用于数据库连接的如下，从PDO::ATTR_AUTOCOMMIT到PDO::ATTR_TIMEOUT的一些常量，值得注意的是PDO::ATTR_EMULATE_PREPARES 不在其中.所以严格来说，无论如何，我们不应该期望 getAttribute(PDO::ATTR_EMULATE_PREPARES) 起作用.

Looking at the documentation for pdo::getAttribute, it says The constants that apply to database connections are as follows, and a number of constants follow from PDO::ATTR_AUTOCOMMIT to PDO::ATTR_TIMEOUT, and it's remarkable that PDO::ATTR_EMULATE_PREPARES is not in them. So strictly speaking, we should not expect getAttribute(PDO::ATTR_EMULATE_PREPARES) to work, anyway.

现在查看源代码可以肯定的是，pdo_pgsql 驱动程序似乎提供了一个 pdo_pgsql_get_attribute 函数，该函数具有一个 switch 语句:

Now looking at the source code to be sure, it appears that the pdo_pgsql driver provides a pdo_pgsql_get_attribute function that has a switch statement on:

  • PDO_ATTR_CLIENT_VERSION
  • PDO_ATTR_SERVER_VERSION
  • PDO_ATTR_CONNECTION_STATUS
  • PDO_ATTR_SERVER_INFO

就是这样.没有 PDO_ATTR_EMULATE_PREPARES 的痕迹，这就是最终出现此错误的原因.

and that's it. No trace of PDO_ATTR_EMULATE_PREPARES which is why ultimately this error appears.

另一方面，函数pdo_pgsql_set_attr有一个switch语句:

On the other hand, the function pdo_pgsql_set_attr has a switch statement on:

  • PDO_ATTR_EMULATE_PREPARES
  • PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT

确认在设置时实际考虑了此属性.所以 PDO 只是与 getAttribute 不一致，不匹配 setAttribute.

which confirms that this attribute is actually taken into account when set. So PDO is just inconsistent with getAttribute that doesn't match setAttribute.

Q2 - 当准备模拟为假时，为什么虚假语句在准备时不会立即引发错误?

考虑pgsql_statement.c中的这段代码:

        if (!S->is_prepared) {
stmt_retry:
            /* we deferred the prepare until now, because we didn't
             * know anything about the parameter types; now we do */
            S->result = PQprepare(H->server, S->stmt_name, S->query, 
                        stmt->bound_params ? zend_hash_num_elements(stmt->bound_params) : 0,
                        S->param_types);

它表明使用了 PQprepare(所以这是一个真正的"准备好的语句)，而且该语句不会立即发送到服务器.这就是 dbo::prepare("bogus statement") 不会返回 false 的原因:它实际上不会因为缺少参数类型而发送到服务器.

It shows that PQprepare is used (so that's a "real" prepared statement), but also that the statement is not immediately sent to the server. That's why the dbo::prepare("bogus statement") won't return false: it's actually not sent to the server for lack of parameter types.

相关文章