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

小开

PDOStatement 有一个公共属性 $queryString。

我刚刚注意到 PDOStatement 有一个未记录的方法 degugDumpParams () ，您可能也想查看它。

小开

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

小开

最佳答案

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

当您执行 ready ()时，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

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

回复@afilina:

不，文本 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.

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

要求 PDO 公开重写的查询将是一个合理的特性请求。但是即使这样也不会给出“完整”查询，除非使用 PDO: : ATTR _ EMULATE _ PREPARES。

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

小开

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

小开

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

更新: 刚刚添加了 NULL 值检查和重复 $params，所以实际 $param 值不会被修改。

干得好，大个子，谢谢！

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

小开

增加了一点更多的代码由迈克-步行的值添加单引号

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

小开

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

虽然访问插入的查询字符串在故障排除时有很大的好处，但是我们希望能够只维护某些查询的日志(因此，为此目的使用数据库日志并不理想)。我们还希望能够使用日志在任何给定的时间重新创建表的条件，因此，我们需要确保正确地转义了插入的字符串。最后，我们希望将这个功能扩展到我们的整个代码库，尽可能少地重写它(截止日期、市场营销等等; 你知道它是怎样的)。

我的解决方案是扩展默认 PDOStatement 对象的功能来缓存参数化的值(或引用) ，并且在执行语句时，使用 PDO 对象的功能来正确地转义注入到查询字符串中的参数。然后，我们可以连接到语句对象的执行方法，并记录当时执行的实际查询(或者至少尽可能忠实于复制品)。

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

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

这个扩展以及安装和配置说明可以在 github 上找到:

Https://github.com/noahheck/e_pdostatement

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

小开

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

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

你可以在正式的医学文献上找到更多信息

例如:

<?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();


?>

小开

Mike 的回答在您使用“ re-use”bind 值之前是有效的。
例如:

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)

迈克的回答只能代替第一个: 搜索，但不能代替第二个。
所以，我重写了他的回答，使用了多个可以正确重用的参数。

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

小开

Preg _ place 对我来说不起作用，当 bind _ > 9时，binding _ 1和 binding _ 10被 str _ place 替换(留下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;

}

希望有人觉得有用。

小开

我需要在绑定参数之后记录完整的查询字符串，因此这是我代码中的一部分。希望，它对每个人都有同样的问题。

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

小开

可以扩展 PDOStatement 类来捕获有界变量并存储它们以供以后使用。然后可以添加两个方法，一个用于变量消毒(debug BindedVariables) ，另一个用于使用这些变量打印查询(debug Query) :

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

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

$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());

导致

SELECT user FROM users WHERE user = ‘ user _ test’

数组( [ : test ] = > user _ test )

小开

解决方案是自动在查询中输入错误并打印错误消息:

//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();
}

标准输出:

SQLSTATE [42000] : 第1行从年龄 = 18岁的人身上选择 * 附近的语法错误或访问冲突: [ ... ]

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

小开

我知道这个问题有点老，但是，我很久以前就在使用这段代码了(我使用的是@chris-go 的 response) ，现在，这些代码在 PHP 7.2中已经过时了

我将发布这些代码的更新版本(主要代码来自 @ bigwebguy、 @ Mike和 @ Chris-go，它们都是这个问题的答案) :

/**
* 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, function(&$v, $k) { if (!is_numeric($v) && $v != "NULL") $v = "\'" . $v . "\'"; });


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


return $query;
}

注意，代码的更改位于 array _ walk ()函数上，用一个匿名函数替换 create _ function。这使得这些优秀的代码具有实用性，并且与 PHP 7.2兼容(也希望将来的版本也是如此)。

小开

你可以使用 sprintf(str_replace('?', '"%s"', $sql), ...$params);

这里有一个例子:

function mysqli_prepared_query($link, $sql, $types='', $params=array()) {
echo sprintf(str_replace('?', '"%s"', $sql), ...$params);
//prepare, bind, execute
}


$link = new mysqli($server, $dbusername, $dbpassword, $database);
$sql = "SELECT firstname, lastname FROM users WHERE userage >= ? AND favecolor = ?";
$types = "is"; //integer and string
$params = array(20, "Brown");


if(!$qry = mysqli_prepared_query($link, $sql, $types, $params)){
echo "Failed";
} else {
echo "Success";
}

注意，这只适用于 PHP > = 5.6

小开

现有的答案似乎都不完整或安全，所以我想出了这个函数，它有以下改进:

同时使用未命名(?)和命名(:foo)参数。
使用引用()正确转义不是 NULL、 int、 float或 bool的值。
正确处理包含 "?"和 ":foo"的字符串值，不要将它们误认为占位符。

    function interpolateSQL(PDO $pdo, string $query, array $params) : string {
$s = chr(2); // Escape sequence for start of placeholder
$e = chr(3); // Escape sequence for end of placeholder
$keys = [];
$values = [];


// Make sure we use escape sequences that are not present in any value
// to escape the placeholders.
foreach ($params as $key => $value) {
while( mb_stripos($value, $s) !== false ) $s .= $s;
while( mb_stripos($value, $e) !== false ) $e .= $e;
}
        

        

foreach ($params as $key => $value) {
// Build a regular expression for each parameter
$keys[] = is_string($key) ? "/$s:$key$e/" : "/$s\?$e/";


// Treat each value depending on what type it is.
// While PDO::quote() has a second parameter for type hinting,
// it doesn't seem reliable (at least for the SQLite driver).
if( is_null($value) ){
$values[$key] = 'NULL';
}
elseif( is_int($value) || is_float($value) ){
$values[$key] = $value;
}
elseif( is_bool($value) ){
$values[$key] = $value ? 'true' : 'false';
}
else{
$value = str_replace('\\', '\\\\', $value);
$values[$key] = $pdo->quote($value);
}
}


// Surround placehodlers with escape sequence, so we don't accidentally match
// "?" or ":foo" inside any of the values.
$query = preg_replace(['/\?/', '/(:[a-zA-Z0-9_]+)/'], ["$s?$e", "$s$1$e"], $query);


// Replace placeholders with actual values
$query = preg_replace($keys, $values, $query, 1, $count);


// Verify that we replaced exactly as many placeholders as there are keys and values
if( $count !== count($keys) || $count !== count($values) ){
throw new \Exception('Number of replacements not same as number of keys and/or values');
}


return $query;
}

我相信它还可以进一步改进。

在我的例子中，我最终只是记录了实际的“未准备查询”(即包含占位符的 SQL)以及 JSON 编码的参数。但是，这些代码可能会在某些用例中使用，在这些用例中，您确实需要插入最终的 SQL 查询。