获取带有绑定参数的PDO查询字符串而不执行它

Is it possible to get a query string from a PDO object with bound parameters without executing it first? I have code similar to the following (where $dbc is the PDO object):

是否可以从PDO对象获取带有绑定参数的查询字符串而不先执行它？我有类似于以下代码（其中$ dbc是PDO对象）：

$query = 'SELECT * FROM users WHERE username = ?';
$result = $dbc->prepare($query);
$username = 'bob';
$result->bindParam(1, $username);
echo $result->queryString;

Currently, this will echo out a SQL statement like: "SELECT * FROM users WHERE username = ?". However, I would like to have the bound parameter included so that it looks like: 'SELECT * FROM users WHERE username = 'bob'". Is there a way to do that without executing it or replacing the question marks with the parameters through something like preg_replace?

目前，这将回显出一个SQL语句，如：“SELECT * FROM users WHERE username =？”。但是，我想包含绑定参数，使其看起来像：'SELECT * FROM users WHERE username ='bob'“。有没有办法在不执行它的情况下执行此操作或通过某些内容用参数替换问号喜欢preg_replace？

2 个解决方案

#1

In short: no. See Getting raw SQL query string from PDO prepared statements

简而言之：没有。请参阅从PDO预准备语句中获取原始SQL查询字符串

If you want to just emulate it, try:

如果您想模仿它，请尝试：

echo preg_replace('?', $username, $result->queryString);

#2

This is a generic variation of the regexp technique, for a numbered array of parameters.

对于编号的参数数组，这是regexp技术的一般变体。

It was a bit more paranoid than the accepted answer because quoting everything, numbers included, has bitten me in the backside more than once; in MySQL as well as elsewhere¹, '123' is less than '13'. Same goes for 'NULL', which is not NULL, and 'false', which is obviously true.

它比被接受的答案更偏执了，因为引用一切，包括数字，不止一次地咬我的背面;在MySQL以及其他地方1，'123'小于'13'。同样适用于'NULL'，它不是NULL，而'false'，这显然是正确的。

It has now been pointed out to me that I was not paranoid enough :-), and my ? replacement technique ("#\\?#") was naïve, because the source query might contain question marks as text in the body for whatever reason:

现在我已经向我指出，我不够偏执:-)，而我？替换技术（“＃\\？＃”）是天真的，因为源查询可能包含问号作为正文中的文本，无论出于何种原因：

$query = "SELECT CONCAT('Is ', @value, ' ', ?, '? ', 
       IF(@check != ? AND 123 > '13', 'Yes!', 'Uh, no?')) 
       ;

$values = array('correct', false, 123);

// Expecting valid SQL, selecting 'correct' if check is not false for 123
// and answering Yes if @check is true.

Output:

输出：

SELECT CONCAT('Is ', @value, ' ', 'correct', '? ',
   IF(check != false AND 123 > '13', 'Yes!', 'Uh, no?')) 
   ;

Is THIS_TEST correct? Yes!

My simpler implementation would have thrown an exception seeing too many question marks. An even simpler implementation would have returned something like

我更简单的实现会抛出一个看到太多问号的异常。更简单的实现会返回类似的东西

Is THIS_TEST correcttrue Uh, no

So this is the amended function. NOTE: I know there are things regexes shouldn't do. I do not claim this function to be working in all instances and for all border cases. I claim it is a reasonable attempt. Feel free to comment or email with non-working test cases.

所以这是修正后的功能。注意：我知道正则表达式不应该做的事情。我并未声称此功能适用于所有情况和所有边境情况。我声称这是一次合理的尝试。随意评论或发送电子邮件与非工作测试用例。

function boundQuery($db, $query, $values) {
    $ret = preg_replace_callback(
        "#(\\?)(?=(?:[^']|['][^']*')*$)#ms",
        // Notice the &$values - here, we want to modify it.
        function($match) use ($db, &$values) {
            if (empty($values)) {
                throw new PDOException('not enough values for query');
            }
            $value  = array_shift($values);

            // Handle special cases: do not quote numbers, booleans, or NULL.
            if (is_null($value)) return 'NULL';
            if (true === $value) return 'true';
            if (false === $value) return 'false';
            if (is_numeric($value)) return $value;

            // Handle default case with $db charset
            return $db->quote($value);
        },
        $query
    );
    if (!empty($values)) {
        throw new PDOException('not enough placeholders for values');
    }
    return $ret;
}

One could also extend PDOStatement in order to supply a $stmt->boundString($values) method.

还可以扩展PDOStatement以提供$ stmt-> boundString（$ values）方法。

(1) since this is PHP, have you ever tried $a = 1...1; print $a;?

（1）因为这是PHP，你有没有试过$ a = 1 ... 1;打印$ a;？

#1