从PDO准备语句获取原始SQL查询字符串。

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

14 个解决方案

#1

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.

SQL语句在执行prepare()时发送到数据库服务器，在执行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_prepare，您也可以得到您想要的。在这种模式下，PDO将参数插入到SQL查询中，并在执行()时发送整个查询。这不是一个真正准备好的查询。通过在execute()之前将变量插入到SQL字符串中，可以避免预先准备的查询的好处。

---

Re comment from @afilina:

从@afilina再保险的评论:

No, the textual SQL query is not combined 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_prepare, 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_prepare。

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服务器的通用查询日志的解决方案，因为在这种情况下，即使是带参数占位符的已准备查询也会在服务器上重写，并将参数值返回到查询字符串中。但这只在日志记录期间完成，而不是在查询执行期间。

#2

/**
 * 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;
}

#3

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;
}

#4

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()，您可能也想查看它。

#5

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;
}

#6

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)、参数列表、参数名称、类型(参数类型)作为一个整数、它们的键名或位置，以及查询中的位置(如果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();

?>

#7

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.

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

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对象的功能在将参数返回到查询字符串时正确地转义参数。然后，我们可以绑定到执行语句对象的方法，并记录当时执行的实际查询(或者至少尽可能忠实于复制)。

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()方法，对绑定数据进行缓存，然后调用父类:bindParam()或父类::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.

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

#8

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类来捕获有界变量，并将它们存储起来供以后使用。然后可以添加两种方法，一种用于变量清理(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.

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

$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'

从用户= 'user_test'中选择user

Array ( [:test] => user_test )

数组([:test] => user_test)

#9

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中的参数，并将其赋值替换为参数。

#10

Somewhat related... if you are just trying to sanitize a particular variable you can use PDO::quote. For example, to search for multiple partial LIKE conditions if you're stuck with a limited framework like CakePHP:

有些相关的…如果您只是想对一个特定的变量进行清理，您可以使用PDO::quote。例如，如果您使用的是CakePHP这样的有限框架，那么可以搜索多个类似局部的条件:

$pdo = $this->getDataSource()->getConnection();
$results = $this->find('all', array(
    'conditions' => array(
        'Model.name LIKE ' . $pdo->quote("%{$keyword1}%"),
        'Model.name LIKE ' . $pdo->quote("%{$keyword2}%"),
    ),
);

#11

I need to log full query string after bind param so this is a piece in my code. Hope, it is useful for everyone hat has the same issue.

我需要在bind param之后记录完整的查询字符串，所以这是我代码中的一部分。希望，它对每个有同样问题的人都有用。

/**
 * 
 * @param string $str
 * @return string
 */
public function quote($str) {
    if (!is_array($str)) {
        return $this->pdo->quote($str);
    } else {
        $str = implode(',', array_map(function($v) {
                    return $this->quote($v);
                }, $str));

        if (empty($str)) {
            return 'NULL';
        }

        return $str;
    }
}

/**
 * 
 * @param string $query
 * @param array $params
 * @return string
 * @throws Exception
 */
public function interpolateQuery($query, $params) {
    $ps = preg_split("/'/is", $query);
    $pieces = [];
    $prev = null;
    foreach ($ps as $p) {
        $lastChar = substr($p, strlen($p) - 1);

        if ($lastChar != "\\") {
            if ($prev === null) {
                $pieces[] = $p;
            } else {
                $pieces[] = $prev . "'" . $p;
                $prev = null;
            }
        } else {
            $prev .= ($prev === null ? '' : "'") . $p;
        }
    }

    $arr = [];
    $indexQuestionMark = -1;
    $matches = [];

    for ($i = 0; $i < count($pieces); $i++) {
        if ($i % 2 !== 0) {
            $arr[] = "'" . $pieces[$i] . "'";
        } else {
            $st = '';
            $s = $pieces[$i];
            while (!empty($s)) {
                if (preg_match("/(\?|:[A-Z0-9_\-]+)/is", $s, $matches, PREG_OFFSET_CAPTURE)) {
                    $index = $matches[0][1];
                    $st .= substr($s, 0, $index);
                    $key = $matches[0][0];
                    $s = substr($s, $index + strlen($key));

                    if ($key == '?') {
                        $indexQuestionMark++;
                        if (array_key_exists($indexQuestionMark, $params)) {
                            $st .= $this->quote($params[$indexQuestionMark]);
                        } else {
                            throw new Exception('Wrong params in query at ' . $index);
                        }
                    } else {
                        if (array_key_exists($key, $params)) {
                            $st .= $this->quote($params[$key]);
                        } else {
                            throw new Exception('Wrong params in query with key ' . $key);
                        }
                    }
                } else {
                    $st .= $s;
                    $s = null;
                }
            }
            $arr[] = $st;
        }
    }

    return implode('', $arr);
}

#12

Mike's answer is working good until you are using the "re-use" bind value.
For example:

Mike的答案在您使用“重用”绑定值之前运行良好。例如:

SELECT * FROM `an_modules` AS `m` LEFT JOIN `an_module_sites` AS `ms` ON m.module_id = ms.module_id WHERE 1 AND `module_enable` = :module_enable AND `site_id` = :site_id AND (`module_system_name` LIKE :search OR `module_version` LIKE :search)

The Mike's answer can only replace first :search but not the second.
So, I rewrite his answer to work with multiple parameters that can re-used properly.

迈克的回答只能取代第一个:搜索而不是第二个。因此，我重写了他的答案，使之适用于可以正确使用的多个参数。

public function interpolateQuery($query, $params) {
    $keys = array();
    $values = $params;
    $values_limit = [];

    $words_repeated = array_count_values(str_word_count($query, 1, ':_'));

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

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

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

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

    if (is_array($values)) {
        foreach ($values as $key => $val) {
            if (isset($values_limit[$key])) {
                $query = preg_replace(['/:'.$key.'/'], [$val], $query, $values_limit[$key], $count);
            } else {
                $query = preg_replace(['/:'.$key.'/'], [$val], $query, 1, $count);
            }
        }
        unset($key, $val);
    } else {
        $query = preg_replace($keys, $values, $query, 1, $count);
    }
    unset($keys, $values, $values_limit, $words_repeated);

    return $query;
}

#13

preg_replace didn't work for me and when binding_ was over 9, binding_1 and binding_10 was replaced with str_replace (leaving the 0 behind), so I made the replacements backwards:

preg_replace对我不起作用，当binding_超过9时，binding_1和binding_10替换为str_replace(将0留在后面)，因此我向后替换:

public function interpolateQuery($query, $params) {
$keys = array();
    $length = count($params)-1;
    for ($i = $length; $i >=0; $i--) {
            $query  = str_replace(':binding_'.(string)$i, '\''.$params[$i]['val'].'\'', $query);
           }
        // $query  = str_replace('SQL_CALC_FOUND_ROWS', '', $query, $count);
        return $query;

}

}

Hope someone finds it useful.

希望有人觉得它有用。

#14

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行，从年龄=18岁的人旁边选出*

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

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