How to Debug Pdo Database Queries

How to debug PDO database queries?

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn't send the complete query to the database all at once, ryeguy's suggestion to use the DB's logging function actually allowed me to see the complete query as assembled and executed by the database.

Here's how:
(These instructions are for MySQL on a Windows machine - your mileage may vary)

• In my.ini, under the [mysqld] section, add a log command, like log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• Restart MySQL.
• It will start logging every query in that file.

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

Debugging PDO queries, how?

Did you try PDOStatement::debugDumpParams? It gives you detailed information about parameters.

From the manual:

Dumps the informations contained by a prepared statement directly on the output. It will provide the SQL query in use, the number of parameters used (Params), the list of parameters, with their name, type (paramtype) as an integer, their key name or position, the value, and the position in the query (if this is supported by the PDO driver, otherwise, it will be -1).

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

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!!!

PDO Debugging - View Query AFTER Bind?

That's the single most common myth about SQL debugging. "I need to see the query after preparation to be able to tell if an error occurred". The fact is, you don't, and I'll tell you why.

Once a query has been prepared, the placeholder can be considered as a valid string/integer. You don't care what's in it.

Also, if you set up PDO correctly, you'll get a detailed PDOException detailing the error you've had along with a complete backtrace of where the error happened, plus you get the error string from MySQL, which makes syntax errors very easy to find.

To enable PDO Exceptions and disable emulated prepares:

$pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Retrieve (or simulate) full query from PDO prepared statement

I believe this is mentioned in the original question that was reference in this one. However
there is actually supposed to be a method for retrieving this data.

PDOStatement::debugDumpParams

However it isn't currently working as documented. There is a bug report and patch submitted for it here http://bugs.php.net/bug.php?id=52384 in case anyone is interested in voting on it. Until it's fixed it seems like you are left to use query logging or setting a custom statement class using the PDO::ATTR_STATEMENT_CLASS attribute.

View and debug prepared PDO query without looking at MySQL logs

There is no built-in way to do it. bigwebguy created a function to do it in one of his answers:

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public static function interpolateQuery($query, $params) {
    $keys = array();

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }
    }

    $query = preg_replace($keys, $params, $query, 1, $count);

    #trigger_error('replaced '.$count.' keys');

    return $query;
}

---

Related Topics