[php] In PHP with PDO, how to check the final SQL parametrized query?

In PHP, when accessing MySQL database with PDO with parametrized query, how can you check the final query (after having replaced all tokens)?

Is there a way to check what gets really executed by the database?

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.

So what is probably sent to the database will be a PREPARE ..., then SET ... and 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.

You might be able to use PDOStatement->debugDumpParams. See the PHP documentation .

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:
$column is my token
$param is the actual value being assigned to token
$this->fullStmt is my print only statement with replaced tokens

What it does is a simply replace tokens with values when the real PDO assignment happens.

I hope I did not confuse you and at least pointed you in right direction.

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):

Execute this SQL in phpMyAdmin or any other environment where you may have high db privileges:

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:

>ps auxww|grep [m]ysqld

I found that PDO escapes everything, so you can't write

$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.

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

I check Query Log to see the exact query that was executed as prepared statement.

The easiest way it can be done is by reading mysql execution log file and you can do that in runtime.

There is a nice explanation here:

How to show the last queries executed on MySQL?

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.