php 获取带有绑定参数的 PDO 查询字符串而不执行它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/530627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting a PDO query string with bound parameters without executing it
提问by VirtuosiMedia
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？
采纳答案by Crescent Fresh
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);
回答by LSerni
This is a generic variation of the regexp technique, for a numbered array of parameters.
这是正则表达式技术的通用变体，用于参数的编号数组。
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 elsewhere1, '123' is lessthan '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 PDOStatementin 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; print $a;？
