从PDO准备好的语句获取原始SQL查询字符串 [英] Getting raw SQL query string from PDO prepared statements

问题描述

在准备好的语句上调用PDOStatement :: execute()时，是否有办法使原始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.

推荐答案

我假设您的意思是您想要最终的SQL查询，并在其中插入了参数值.我知道这对调试很有用，但是它不是准备好的语句的工作方式.参数不会在客户端与预准备语句结合使用，因此PDO绝对不能访问与其参数结合使用的查询字符串.

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.

当您执行prepare()时，SQL语句将发送到数据库服务器，而当您执行execute()时，将分别发送参数. MySQL的常规查询日志的确显示了最终SQL，其中包含在execute()之后插入的值.以下是我的一般查询日志的摘录.我从mysql CLI(不是从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.

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()时发送整个查询. 这不是真正的预准备查询.您可以通过在execute()之前将变量插值到SQL字符串中来规避预准备查询的好处.

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().

来自@afilina的评论:

Re comment from @afilina:

否，在执行期间，文本SQL查询是 not 与参数结合在一起的.因此，PDO没有任何东西可以显示给您.

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

在内部，如果使用PDO :: ATTR_EMULATE_PREPARES，则PDO会复制SQL查询并在准备和执行之前将参数值插值到其中.但是PDO不会公开此修改后的SQL查询.

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的构造函数中设置，并且在用参数重写查询时不会更新.

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.

PDO要求他们公开重写的查询是合理的功能请求.除非您使用PDO :: ATTR_EMULATE_PREPARES，否则即使这样做也不会给您完整"的查询.

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.

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

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.

这篇关于从PDO准备好的语句获取原始SQL查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！