php 从 PDO 准备好的语句中获取原始 SQL 查询字符串

Getting raw SQL query string from PDO prepared statements

提问by Wilco

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 字符串？出于调试目的，这将非常有用。

采纳答案by Bill Karwin

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

我假设您的意思是您想要最终的 SQL 查询，并将参数值插入其中。我知道这对调试很有用，但这不是准备好的语句的工作方式。参数不会与客户端的准备好的语句组合，因此 PDO 永远不应访问与其参数组合的查询字符串。

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

执行prepare()时SQL语句发送到数据库服务器，执行execute()时单独发送参数。MySQL 的一般查询日志确实显示了在执行() 之后插入值的最终 SQL。以下是我的一般查询日志的摘录。我从 mysql CLI 运行查询，而不是从 PDO，但原理是一样的。

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

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(). This is not a true prepared query.You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

如果您设置 PDO 属性 PDO::ATTR_EMULATE_PREPARES，您也可以获得您想要的。在这种模式下，PDO 将参数插入到 SQL 查询中，并在您执行 () 时发送整个查询。 这不是一个真正的准备好的查询。您将通过在 execute() 之前将变量插入 SQL 字符串来规避准备查询的好处。

---

---

Re comment from @afilina:

来自@afilina 的评论：

No, the textual SQL query is notcombined with the parameters during execution. So there's nothing for PDO to show you.

不，文本 SQL 查询在执行期间不与参数组合。因此，PDO 没有什么可向您展示的。

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

在内部，如果您使用 PDO::ATTR_EMULATE_PREPARES，PDO 会在执行准备和执行之前制作 SQL 查询的副本并将参数值插入其中。但是 PDO 不会公开这个修改后的 SQL 查询。

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

PDOStatement 对象有一个属性 $queryString，但它仅在 PDOStatement 的构造函数中设置，并且在使用参数重写查询时不会更新。

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

PDO 要求他们公开重写的查询是一个合理的功能请求。但即使这样也不会给你“完整”的查询，除非你使用 PDO::ATTR_EMULATE_PREPARES。

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

这就是为什么我在上面展示使用 MySQL 服务器的常规查询日志的解决方法的原因，因为在这种情况下，即使是带有参数占位符的准备好的查询也会在服务器上重写，参数值回填到查询字符串中。但这仅在日志记录期间完成，而不是在查询执行期间完成。

回答by bigwebguy

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public static function interpolateQuery($query, $params) {
    $keys = array();

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }
    }

    $query = preg_replace($keys, $params, $query, 1, $count);

    #trigger_error('replaced '.$count.' keys');

    return $query;
}

回答by Mike

I modified the method to include handling output of arrays for statements like WHERE IN (?).

我修改了该方法以包括处理诸如 WHERE IN (?) 之类的语句的数组输出。

UPDATE: Just added check for NULL value and duplicated $params so actual $param values are not modified.

更新：刚刚添加了对 NULL 值的检查和重复的 $params，因此不会修改实际的 $param 值。

Great work bigwebguy and thanks!

bigwebguy 干得好，谢谢！

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public function interpolateQuery($query, $params) {
    $keys = array();
    $values = $params;

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }

        if (is_string($value))
            $values[$key] = "'" . $value . "'";

        if (is_array($value))
            $values[$key] = "'" . implode("','", $value) . "'";

        if (is_null($value))
            $values[$key] = 'NULL';
    }

    $query = preg_replace($keys, $values, $query);

    return $query;
}

回答by Jimmy Kane

A bit late probably but now there is PDOStatement::debugDumpParams

可能有点晚了，但现在有 PDOStatement::debugDumpParams

Dumps the informations contained by a prepared statement directly on the output. It will provide the SQL query in use, the number of parameters used (Params), the list of parameters, with their name, type (paramtype) as an integer, their key name or position, and the position in the query (if this is supported by the PDO driver, otherwise, it will be -1).

将准备好的语句中包含的信息直接转储到输出上。它将提供正在使用的 SQL 查询、使用的参数数量 (Params)、参数列表、它们的名称、类型 (paramtype) 作为整数、它们的键名称或位置，以及查询中的位置（如果这PDO 驱动程序支持，否则为 -1）。

You can find more on the official php docs

您可以在官方 php 文档中找到更多信息

Example:

例子：

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

$sth->debugDumpParams();

?>

回答by JacopoStanchi

A solution is to voluntarily put an error in the query and to print the error's message:

一种解决方案是自愿在查询中输入错误并打印错误消息：

//Connection to the database
$co = new PDO('mysql:dbname=myDB;host=localhost','root','');
//We allow to print the errors whenever there is one
$co->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//We create our prepared statement
$stmt = $co->prepare("ELECT * FROM Person WHERE age=:age"); //I removed the 'S' of 'SELECT'
$stmt->bindValue(':age','18',PDO::PARAM_STR);
try {
    $stmt->execute();
} catch (PDOException $e) {
    echo $e->getMessage();
}

Standard output:

标准输出：

SQLSTATE[42000]: Syntax error or access violation: [...] near 'ELECT * FROM Person WHERE age=18'at line 1

SQLSTATE[42000]：语法错误或访问冲突：[...] 靠近第 1 行的“ELECT * FROM Person WHERE age=18”

It is important to note that it only prints the first 80 characters of the query.

需要注意的是，它只打印查询的前 80 个字符。

回答by Chris Go

Added a little bit more to the code by Mike - walk the values to add single quotes

Mike 为代码添加了更多内容 - 遍历值以添加单引号

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public function interpolateQuery($query, $params) {
    $keys = array();
    $values = $params;

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }

        if (is_array($value))
            $values[$key] = implode(',', $value);

        if (is_null($value))
            $values[$key] = 'NULL';
    }
    // Walk the array to see if we can add single-quotes to strings
    array_walk($values, create_function('&$v, $k', 'if (!is_numeric($v) && $v!="NULL") $v = "\'".$v."\'";'));

    $query = preg_replace($keys, $values, $query, 1, $count);

    return $query;
}

回答by Glass Robot

PDOStatement has a public property $queryString. It should be what you want.

PDOStatement 有一个公共属性 $queryString。它应该是你想要的。

I've just notice that PDOStatement has an undocumented method debugDumpParams() which you may also want to look at.

我刚刚注意到 PDOStatement 有一个未公开的方法 debugDumpParams()，您可能还想查看它。

回答by Otamay

You can extend PDOStatement class to capture the bounded variables and store them for later use. Then 2 methods may be added, one for variable sanitizing ( debugBindedVariables ) and another to print the query with those variables ( debugQuery ):

您可以扩展 PDOStatement 类来捕获有界变量并存储它们以备后用。然后可以添加 2 种方法，一种用于变量清理 ( debugBindedVariables )，另一种用于打印带有这些变量的查询 ( debugQuery )：

class DebugPDOStatement extends \PDOStatement{
  private $bound_variables=array();
  protected $pdo;

  protected function __construct($pdo) {
    $this->pdo = $pdo;
  }

  public function bindValue($parameter, $value, $data_type=\PDO::PARAM_STR){
    $this->bound_variables[$parameter] = (object) array('type'=>$data_type, 'value'=>$value);
    return parent::bindValue($parameter, $value, $data_type);
  }

  public function bindParam($parameter, &$variable, $data_type=\PDO::PARAM_STR, $length=NULL , $driver_options=NULL){
    $this->bound_variables[$parameter] = (object) array('type'=>$data_type, 'value'=>&$variable);
    return parent::bindParam($parameter, $variable, $data_type, $length, $driver_options);
  }

  public function debugBindedVariables(){
    $vars=array();

    foreach($this->bound_variables as $key=>$val){
      $vars[$key] = $val->value;

      if($vars[$key]===NULL)
        continue;

      switch($val->type){
        case \PDO::PARAM_STR: $type = 'string'; break;
        case \PDO::PARAM_BOOL: $type = 'boolean'; break;
        case \PDO::PARAM_INT: $type = 'integer'; break;
        case \PDO::PARAM_NULL: $type = 'null'; break;
        default: $type = FALSE;
      }

      if($type !== FALSE)
        settype($vars[$key], $type);
    }

    if(is_numeric(key($vars)))
      ksort($vars);

    return $vars;
  }

  public function debugQuery(){
    $queryString = $this->queryString;

    $vars=$this->debugBindedVariables();
    $params_are_numeric=is_numeric(key($vars));

    foreach($vars as $key=>&$var){
      switch(gettype($var)){
        case 'string': $var = "'{$var}'"; break;
        case 'integer': $var = "{$var}"; break;
        case 'boolean': $var = $var ? 'TRUE' : 'FALSE'; break;
        case 'NULL': $var = 'NULL';
        default:
      }
    }

    if($params_are_numeric){
      $queryString = preg_replace_callback( '/\?/', function($match) use( &$vars) { return array_shift($vars); }, $queryString);
    }else{
      $queryString = strtr($queryString, $vars);
    }

    echo $queryString.PHP_EOL;
  }
}


class DebugPDO extends \PDO{
  public function __construct($dsn, $username="", $password="", $driver_options=array()) {
    $driver_options[\PDO::ATTR_STATEMENT_CLASS] = array('DebugPDOStatement', array($this));
    $driver_options[\PDO::ATTR_PERSISTENT] = FALSE;
    parent::__construct($dsn,$username,$password, $driver_options);
  }
}

And then you can use this inherited class for debugging purpouses.

然后你可以使用这个继承的类来调试目的。

$dbh = new DebugPDO('mysql:host=localhost;dbname=test;','user','pass');

$var='user_test';
$sql=$dbh->prepare("SELECT user FROM users WHERE user = :test");
$sql->bindValue(':test', $var, PDO::PARAM_STR);
$sql->execute();

$sql->debugQuery();
print_r($sql->debugBindedVariables());

Resulting in

导致

SELECT user FROM users WHERE user = 'user_test'

Array ( [:test] => user_test )

SELECT user FROM users WHERE user = 'user_test'

数组 ( [:test] => user_test )

回答by myesain

I spent a good deal of time researching this situation for my own needs. This and several other SO threads helped me a great deal, so I wanted to share what I came up with.

我花了很多时间研究这种情况以满足我自己的需要。这个和其他几个 SO 线程对我帮助很大，所以我想分享我的想法。

While having access to the interpolated query string is a significant benefit while troubleshooting, we wanted to be able to maintain a log of only certain queries (therefore, using the database logs for this purpose was not ideal). We also wanted to be able to use the logs to recreate the condition of the tables at any given time, therefore, we needed to make certain the interpolated strings were escaped properly. Finally, we wanted to extend this functionality to our entire code base having to re-write as little of it as possible (deadlines, marketing, and such; you know how it is).

虽然在排除故障时访问插入的查询字符串是一个显着的好处，但我们希望能够仅维护某些查询的日志（因此，为此目的使用数据库日志并不理想）。我们还希望能够在任何给定时间使用日志重新创建表的条件，因此，我们需要确保正确转义内插字符串。最后，我们希望将此功能扩展到我们的整个代码库，而必须尽可能少地重写（截止日期、营销等；你知道它是怎么回事）。

My solution was to extend the functionality of the default PDOStatement object to cache the parameterized values (or references), and when the statement is executed, use the functionality of the PDO object to properly escape the parameters when they are injected back in to the query string. We could then tie in to execute method of the statement object and log the actual query that was executed at that time (or at least as faithful of a reproduction as possible).

我的解决方案是扩展默认 PDOStatement 对象的功能以缓存参数化值（或引用），并在执行语句时，使用 PDO 对象的功能在将参数注入回查询时正确转义参数细绳。然后我们可以绑定语句对象的 execute 方法并记录当时执行的实际查询（或至少尽可能忠实地再现）。

As I said, we didn't want to modify the entire code base to add this functionality, so we overwrite the default bindParam()and bindValue()methods of the PDOStatement object, do our caching of the bound data, then call parent::bindParam()or parent::bindValue(). This allowed our existing code base to continue to function as normal.

正如我所说，我们不想修改整个代码库来添加此功能，因此我们覆盖PDOStatement 对象的默认值bindParam()和bindValue()方法，缓存绑定数据，然后调用parent::bindParam()或 parent:: bindValue()。这允许我们现有的代码库继续正常运行。

Finally, when the execute()method is called, we perform our interpolation and provide the resultant string as a new property E_PDOStatement->fullQuery. This can be output to view the query or, for example, written to a log file.

最后，当execute()调用该方法时，我们执行插值并将结果字符串作为新属性提供E_PDOStatement->fullQuery。这可以输出以查看查询，或者例如写入日志文件。

The extension, along with installation and configuration instructions, are available on github:

该扩展以及安装和配置说明可在 github 上找到：

https://github.com/noahheck/E_PDOStatement

https://github.com/noahheck/E_PDOStatement

DISCLAIMER:
Obviously, as I mentioned, I wrote this extension. Because it was developed with help from many threads here, I wanted to post my solution here in case anyone else comes across these threads, just as I did.

免责声明：
显然，正如我所提到的，我编写了这个扩展。因为它是在许多线程的帮助下开发的，所以我想在这里发布我的解决方案，以防其他人遇到这些线程，就像我一样。

回答by Kibbee

The $queryString property mentioned will probably only return the query passed in, without the parameters replaced with their values. In .Net, I have the catch part of my query executer do a simple search replace on the parameters with their values which was supplied so that the error log can show actual values that were being used for the query. You should be able to enumerate the parameters in PHP, and replace the parameters with their assigned value.

提到的 $queryString 属性可能只会返回传入的查询，而不会将参数替换为其值。在 .Net 中，我让我的查询执行器的 catch 部分对参数进行简单的搜索替换，并使用提供的值替换这些参数，以便错误日志可以显示用于查询的实际值。您应该能够枚举 PHP 中的参数，并将参数替换为其指定的值。