从PDO预处理语句中检索（或模拟）完整查询

I stumbled upon this question from two years ago.

我两年前偶然发现了这个问题。

Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.

有没有办法在预准备语句上调用PDOStatement :: execute()时执行原始SQL字符串?出于调试目的,这将非常有用。

The winning answer states that

获胜的答案说明了这一点

[...] You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute().

[...]如果设置PDO属性PDO :: ATTR_EMULATE_PREPARES,您也可以获得所需的内容。在此模式下,PDO将参数插入到SQL查询中,并在执行()时发送整个查询。

But it doesn't mention how to get the resulting query string. I know it's a bad idea performance wise but that doesn't bother me in debug mode. Does anybody know how to do this?

但它没有提到如何获得结果查询字符串。我知道这是一个糟糕的主意,但在调试模式下这并不会让我感到困扰。有人知道怎么做这个吗?

PS If there is some way I could have reopened / drawn attention to the original two year old topic instead of opening a new one, please let me know.

PS如果有某种方式我可以重新开放/引起对原来两年前主题的关注,而不是打开一个新主题,请告诉我。

5 个解决方案

#1

I believe this is mentioned in the original question that was reference in this one. However there is actually supposed to be a method for retrieving this data.

我相信在这个问题中引用的原始问题中提到了这一点。然而,实际上应该有一种检索这些数据的方法。

PDOStatement::debugDumpParams

However it isn't currently working as documented. There is a bug report and patch submitted for it here http://bugs.php.net/bug.php?id=52384 in case anyone is interested in voting on it. Until it's fixed it seems like you are left to use query logging or setting a custom statement class using the PDO::ATTR_STATEMENT_CLASS attribute.

但是,它目前没有记录在案。如果有人有兴趣投票,可以在http://bugs.php.net/bug.php?id=52384上提交错误报告和补丁。在它修复之前,您似乎只能使用PDO :: ATTR_STATEMENT_CLASS属性来使用查询日志记录或设置自定义语句类。

#2

Afaik, PDO doesn't really expose it to you. On development servers, you could enable the general query log for MySQL (if that's what you use), with possibly more control with sql_log_off, which does require the SUPER privilege.

Afaik,PDO并没有真正向您揭露它。在开发服务器上,您可以启用MySQL的常规查询日志(如果这是您使用的),可能更多地使用sql_log_off控制,这需要SUPER权限。

#3

If you can't get it from PDO itself, consider using a wrapper class just for PDOStatement::execute() which will log the SQL query and values, and then call execute() on the statement. You will have to refactor your code to use the new class.

如果您无法从PDO本身获取它,请考虑使用仅适用于PDOStatement :: execute()的包装类,它将记录SQL查询和值,然后在语句上调用execute()。您将不得不重构代码以使用新类。

As a sidenote, I see that PDOStatement has a class variable $queryString that holds the query being used. The values will have to be retrieved from whatever's passed into execute() or bindParam().

作为旁注,我看到PDOStatement有一个类变量$ queryString,它保存正在使用的查询。必须从传递给execute()或bindParam()的任何内容中检索这些值。

First some utility functions for logging:

首先是一些用于记录的实用程序

//removes newlines and extra spaces from print_r output
function str_squeeze($str) {

    if (is_array($str)) {
        $str = print_r($str, true);
    }

    $str = preg_replace('/[(\r)?\n|\t]/', ' ', $str);
    $str = trim(ereg_replace(' +', ' ', $str));
    return $str;
}

function logger($str) {
    //log it somewhere
}

Option 1: wrapper class around PDOStatement

class My_PDO_Utils {

    public static function execute(PDOStatement &$stm, $values = array()) {
        logger("QUERY: " . $stm->queryString . ", values = " . str_squeeze($values)) ;
        return $stm->execute($values) ;

    }

}

Then your code will have to be:

然后你的代码必须是:

$stm = $db->prepare("SELECT * FROM table2 WHERE id = ?") ;

$res = My_PDO_Utils::execute($stm, array(79)) ;

instead of

$res = $stm->execute(array(79)) ;

Thinking some more about it, you could take it one further:

想一想更多关于它,你可以进一步:

Option 2: Extend PDO and PDOStatement

If you want to be adventurous you can extend PDOStatement to do the logging for you, and PDO to return your extended PDOStatement class. This will require the least possible refactoring, ie just change new PDO() to new MY_PDO(), but could get tricky in its implementation as you would need to explicitely define any PDOStatement functionality you need in MY_PDOStatement so it gets called properly.

如果你想冒险,你可以扩展PDOStatement为你做日志记录,PDO返回扩展的PDOStatement类。这将需要尽可能少的重构,即只需将新的PDO()更改为新的MY_PDO(),但在实现中可能会变得棘手,因为您需要在MY_PDOStatement中明确定义所需的任何PDOStatement功能,以便正确调用它。

class My_PDO extends PDO {

    public function prepare($sql, $options = array()) {

        //do normal call
        $stm = parent::prepare($sql, $options) ;

        //encapsulate it in your pdostatement wrapper
        $myStm = new My_PDOStatement() ;
        $myStm->stm = $stm ;

        return $myStm ;

    }

}

class My_PDOStatement extends PDOStatement {

    /**
     *
     * @var PDOStatement
     */
    public $stm ;

    public function execute($values) {

        logger("QUERY: " . $this->stm->queryString . ", values = " . str_squeeze($values)) ;
        return $this->stm->execute($values) ;

    }

    public function fetchAll($fetch_style = PDO::FETCH_BOTH, $column_index = 0, $ctor_args = array()) {
        return $this->stm->fetchAll($fetch_style, $column_index, $ctor_args) ;
    }


}

But now your code can be:

但现在您的代码可以是:

$db = new My_PDO($dsn, $user, $pass) ;

$stm = $db->prepare("SELECT * FROM table2 WHERE id = ?") ;

$res = $stm->execute(array(79)) ;    
$row = $stm->fetchAll() ;

#4

The best approach in my opinion is using the mysql log to show the last queries ran as getting them directly in php is a drag.

在我看来,最好的方法是使用mysql日志来显示最后运行的查询,因为直接在php中获取它们是一种拖累。

From How to show the last queries executed on MySQL? first answer:

从如何显示在MySQL上执行的最后查询?第一个答案:

Additionally, for those blessed with MySQL >= 5.1.12:

另外,对于那些有MySQL> = 5.1.12的人:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

Take a look at the table mysql.general_log If you prefer to output to a file:

看一下表mysql.general_log如果你想输出到一个文件:

SET GLOBAL log_output = "FILE"; which is set by default.
SET GLOBAL general_log_file = "/path/to/your/logfile.log"
SET GLOBAL general_log = 'ON';

I prefer this method because:

我更喜欢这种方法,因为:

you're not editing the my.cnf file and potentially permanently turning on logging you're not fishing around the filesystem looking for the query log - or even worse, distracted by the need for the perfect destination. /var/log /var/data/log /opt /home/mysql_savior/var restarting the server leaves you where you started (log is off) For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log

你没有编辑my.cnf文件,并且可能会永久打开日志记录,你不会在寻找查询日志的文件系统周围钓鱼 - 或者更糟糕的是,由于需要完美的目的地而分心。 / var / log / var / data / log / opt / home / mysql_savior / var重新启动服务器会使您离开的位置(日志已关闭)有关详细信息,请参阅MySQL 5.1参考手册 - 服务器系统变量 - general_log

#5

The following static method takes a PDO query template (an SQL query with ? and/or :name placeholders) and interpolates the parameters:

以下静态方法采用PDO查询模板(带有?和/或:名称占位符的SQL查询)并插入参数:

static public function getDebugFullQuery($query, $params = array()){
    if(is_array($params) && count($params)){

        $search = [];
        $replace = [];

        foreach($params as $k => $p){
            $pos = strpos($query, ":{$k}");
            if($pos !== false){
                $query = substr($query, 0, $pos) . "%!-!{$k}!-!%" . substr($query, $pos + strlen($k) + 1);
            }
            else {
                $pos = strpos($query, "?");
                if($pos !== false){
                    $query = substr($query, 0, $pos) . "%!-!{$k}!-!%" . substr($query, $pos + 1);
                }
                else {
                    break;
                }
            }

            $search[] = "%!-!{$k}!-!%";
            $replace[] = "'" . str_replace(array("\r", "\n", "'"), array("\\\\r", "\\\\n", "\\'"), $p) . "'";
        }

        if(count($search)){
            $query = str_replace($search, $replace, $query);
        }
    }

    return $query;
}

As indicated by the method name, you should use this for debugging purposes only.

如方法名称所示,您应该仅将其用于调试目的。