views:

658

answers:

6

Hello,

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?

A: 

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.

Scott Saunders
+5  A: 

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.

Ben James
Accepting this for now as it makes sense. It's not very convenient for debugging then...
JB Hurteaux
Is there a way maybe we can see the "stream" of sql statements that actually gets executed? Maybe it can still be of some help, better than nothing.
nute
A: 

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.

6bytes
Yes, thanks. I also though of a custom function to do a manual replace of tokens after having seen the answer from Ben James. Not very convenient when there are several tokens but there might be a way to automate this (if we can access all the bindings created) and create a generic function.On the other hand, what I want is really the Query as generated before execution. Creating a function of my own to do that does not guarantee I'll get the same result (ex: taking into account inserted quotes around String parameters...)
JB Hurteaux
+2  A: 

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

Kailash Badu
+2  A: 

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.

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.

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

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:

log=[REPLACE_BY_PATH]/[REPLACE_BY_FILE_NAME]

Just do not run this in production!!!

JB Hurteaux
A: 

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

Michael