在带有 PDO 的 PHP 中，如何检查最终的 SQL 参数化查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1786322/
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
In PHP with PDO, how to check the final SQL parametrized query?
提问by JB Hurteaux
In PHP, when accessing MySQL database with PDO with parametrized query, how can you check the final query (after having replaced all tokens)?
在 PHP 中，当通过参数化查询使用 PDO 访问 MySQL 数据库时，如何检查最终查询（替换所有令牌后）？
Is there a way to check what gets really executed by the database?
有没有办法检查数据库真正执行了什么？
采纳答案by JB Hurteaux
So I think I'll finally answer my own question in order to have a full solution for the record. But have to thank Ben James and Kailash Badu which provided the clues for this.
所以我想我最终会回答我自己的问题，以便为记录提供完整的解决方案。但必须感谢 Ben James 和 Kailash Badu，他们为此提供了线索。
Short Answer
As mentioned by Ben James: NO.
The full SQL query does not exist on the PHP side, because the query-with-tokens and the parameters are sent separately to the database.
Only on the database side the full query exists.
简短的回答
正如本·詹姆斯提到的：不。
PHP 端不存在完整的 SQL 查询，因为 query-with-tokens 和参数单独发送到数据库。只有在数据库端存在完整的查询。
Even trying to create a function to replace tokens on the PHP side would not guarantee the replacement process is the same as the SQL one (tricky stuff like token-type, bindValue vs bindParam, ...)
即使尝试创建一个函数来替换 PHP 端的令牌也不能保证替换过程与 SQL 相同（棘手的东西，如令牌类型、bindValue 与 bindParam，...）
Workaround
This is where I elaborate on Kailash Badu's answer.
By logging all SQL queries, we can see what is really run on the server.
With mySQL, this can be done by updating the my.cnf (or my.ini in my case with Wamp server), and adding a line like:
解决方法
这是我详细阐述 Kailash Badu 的答案的地方。通过记录所有 SQL 查询，我们可以看到服务器上真正运行的是什么。使用 mySQL，这可以通过更新 my.cnf（或在我使用 Wamp 服务器的情况下为 my.ini）并添加如下一行来完成：
log=[REPLACE_BY_PATH]/[REPLACE_BY_FILE_NAME]
Just do not run this in production!!!
只是不要在生产中运行它！！！
回答by Michael
You might be able to use PDOStatement->debugDumpParams. See the PHP documentation.
您也许可以使用PDOStatement->debugDumpParams. 请参阅PHP 文档。
回答by Ben James
Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL. You create a prepared statement at the database, and then send the parameter values alone.
使用带有参数化值的准备好的语句不仅仅是动态创建 SQL 字符串的另一种方式。您在数据库中创建一个准备好的语句，然后单独发送参数值。
So what is probably sent to the database will be a PREPARE ..., then SET ...and finally EXECUTE ....
所以可能发送到数据库的将是 a PREPARE ..., thenSET ...和 finally EXECUTE ...。
You won't be able to get some SQL string like SELECT * FROM ..., even if it would produce equivalent results, because no such query was ever actually sent to the database.
您将无法获得一些 SQL 字符串SELECT * FROM ...，即使它会产生等效的结果，因为实际上从未将此类查询发送到数据库。
回答by Kailash Badu
I check Query Log to see the exact query that was executed as prepared statement.
我检查查询日志以查看作为准备好的语句执行的确切查询。
回答by Chris Adams
I initially avoided turning on logging to monitor PDO because I thought that it would be a hassle but it is not hard at all. You don't need to reboot MySQL (after 5.1.9):
我最初避免打开日志记录来监视 PDO，因为我认为这会很麻烦，但这一点也不难。你不需要重启 MySQL（5.1.9 之后）：
Execute this SQL in phpMyAdmin or any other environment where you may have high db privileges:
在 phpMyAdmin 或任何其他您可能具有高 db 权限的环境中执行此 SQL：
SET GLOBAL general_log = 'ON';
In a terminal, tail your log file. Mine was here:
在终端中，拖尾您的日志文件。我的在这里：
>sudo tail -f /usr/local/mysql/data/myMacComputerName.log
You can search for your mysql files with this terminal command:
您可以使用此终端命令搜索您的 mysql 文件：
>ps auxww|grep [m]ysqld
I found that PDO escapes everything, so you can't write
我发现 PDO 逃避了一切，所以你不能写
$dynamicField = 'userName';
$sql = "SELECT * FROM `example` WHERE `:field` = :value";
$this->statement = $this->db->prepare($sql);
$this->statement->bindValue(':field', $dynamicField);
$this->statement->bindValue(':value', 'mick');
$this->statement->execute();
Because it creates:
因为它创造了：
SELECT * FROM `example` WHERE `'userName'` = 'mick' ;
Which did not create an error, just an empty result. Instead I needed to use
这并没有产生错误，只是一个空的结果。相反，我需要使用
$sql = "SELECT * FROM `example` WHERE `$dynamicField` = :value";
to get
要得到
SELECT * FROM `example` WHERE `userName` = 'mick' ;
When you are done execute:
完成后执行：
SET GLOBAL general_log = 'OFF';
or else your logs will get huge.
否则你的日志会变得很大。
回答by 6bytes
What I did to print that actual query is a bit complicated but it works :)
我为打印实际查询所做的工作有点复杂，但它有效:)
In method that assigns variables to my statement I have another variable that looks a bit like this:
在将变量分配给我的语句的方法中，我有另一个看起来像这样的变量：
$this->fullStmt = str_replace($column, '\'' . str_replace('\'', '\\'', $param) . '\'', $this->fullStmt);
Where:$columnis my token$paramis the actual value being assigned to token$this->fullStmtis my print only statement with replaced tokens
其中：$column是我的令牌$param是分配给令牌的实际值$this->fullStmt是我的仅打印语句并替换了令牌
What it does is a simply replace tokens with values when the real PDO assignment happens.
它所做的只是在真正的 PDO 分配发生时用值替换标记。
I hope I did not confuse you and at least pointed you in right direction.
我希望我没有混淆你，至少为你指明了正确的方向。
回答by Cristiano
The easiest way it can be done is by reading mysql execution log file and you can do that in runtime.
最简单的方法是读取 mysql 执行日志文件，您可以在运行时执行此操作。
There is a nice explanation here:
这里有一个很好的解释：
回答by Scott Saunders
I don't believe you can, though I hope that someone will prove me wrong.
我不相信你可以，尽管我希望有人能证明我是错的。
I know you can print the query and its toString method will show you the sql without the replacements. That can be handy if you're building complex query strings, but it doesn't give you the full query with values.
我知道您可以打印查询，它的 toString 方法将向您显示没有替换的 sql。如果您正在构建复杂的查询字符串，这会很方便，但它不会为您提供带有值的完整查询。
回答by shplintas
I think easiest way to see final query text when you use pdo is to make special error and look error message. I don't know how to do that, but when i make sql error in yii framework that use pdo i could see query text
我认为在使用 pdo 时查看最终查询文本的最简单方法是生成特殊错误并查看错误消息。我不知道该怎么做，但是当我在使用 pdo 的 yii 框架中出现 sql 错误时，我可以看到查询文本
