Getting raw SQL query string from PDO prepared statements

by

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.

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

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

Re comment from @afilina:

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

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.

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.

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.

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.

More Related Contents:

  1. How to apply bindValue method in LIMIT clause?
  2. How to properly set up a PDO connection
  3. PDO with INSERT INTO through prepared statements [closed]
  4. Use bound parameter multiple times
  5. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  6. Do SQL connections opened with PDO in PHP have to be closed
  7. PDO and MySQL ‘between’
  8. Im trying to create some temporary tables in mysql using php
  9. How can I prevent SQL injection in PHP?
  10. SQL injection that gets around mysql_real_escape_string()
  11. Row count with PDO
  12. Error when preparing a multiple insert query
  13. pdo prepared statements with wildcards
  14. I cannot get my login form to connect interact properly with mySQL database [closed]
  15. mysql_num_rows(): supplied argument is not a valid MySQL result resource [duplicate]
  16. Increment value in MySQL update query
  17. Insert multiple rows with PDO prepared statements
  18. What is the advantage of using try {} catch {} versus if {} else {}
  19. Alternative for mysql_num_rows using PDO
  20. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  21. How to convert ISO8601 to Date format in php
  22. Unknown database error in PHP but it exists in PHPMyAdmin
  23. How do detect that transaction has already been started?
  24. return one value from database with mysql php pdo
  25. Trying to access array offset on value of type bool
  26. return multiple response data in one response
  27. How to get a query error from prepare() in PDO PHP?
  28. How can I pass an array of PDO parameters yet still specify their types?
  29. Fatal error: Call to a member function query() on null
  30. PHP PDO how to run a multiple query request?

Leave a Comment