[php] How to debug PDO database queries?

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.

Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catch block), I can't see the query that caused them.

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

An old post but perhaps someone will find this useful;

function pdo_sql_debug($sql,$placeholders){
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}

Searching internet I found this as an acceptable solution. A different class is used instead of PDO and PDO functions are called through magic function calls. I am not sure this creates serious performance problems. But it can be used until a sensible logging feature is added to PDO.

So as per this thread, you can write a wrapper for your PDO connection which can log and throws an exception when you get a error.

Here is simple example:

class LoggedPDOSTatement extends PDOStatement    {

function execute ($array)    {
    parent::execute ($array);
    $errors = parent::errorInfo();
    if ($errors[0] != '00000'):
        throw new Exception ($errors[2]);
    endif;
  }

}

so you can use that class instead of PDOStatement:

$this->db->setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('LoggedPDOStatement', array()));

Here a mentioned PDO decorator implementation:

class LoggedPDOStatement    {

function __construct ($stmt)    {
    $this->stmt = $stmt;
}

function execute ($params = null)    {
    $result = $this->stmt->execute ($params); 
    if ($this->stmt->errorCode() != PDO::ERR_NONE):
        $errors = $this->stmt->errorInfo();
        $this->paint ($errors[2]);
    endif;
    return $result;
}

function bindValue ($key, $value)    {
    $this->values[$key] = $value;    
    return $this->stmt->bindValue ($key, $value);
}

function paint ($message = false)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $this->stmt->queryString . '</td></tr>';
    if (count ($this->values) > 0):
    foreach ($this->values as $key => $value):
    echo '<tr><th align="left" style="background-color: #ccc;">' . $key . '</th><td>' . $value . '</td></tr>';
    endforeach;
    endif;
    echo '</table>';
    echo '</pre>';
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->stmt, $method), $params); 
}

}

In Debian NGINX environment i did the following.

Goto /etc/mysql/mysql.conf.d edit mysqld.cnf if you find log-error = /var/log/mysql/error.log add the following 2 lines bellow it.

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

To see the logs goto /var/log/mysql and tail -f mysql.log

Remember to comment these lines out once you are done with debugging if you are in production environment delete mysql.log as this log file will grow quickly and can be huge.

You say this :

I never see the final query as it's sent to the database

Well, actually, when using prepared statements, there is no such thing as a "final query" :

• First, a statement is sent to the DB, and prepared there
  • The database parses the query, and builds an internal representation of it
• And, when you bind variables and execute the statement, only the variables are sent to the database
  • And the database "injects" the values into its internal representation of the statement

So, to answer your question :

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

No : as there is no "complete SQL query" anywhere, there is no way to capture it.

The best thing you can do, for debugging purposes, is "re-construct" an "real" SQL query, by injecting the values into the SQL string of the statement.

What I usually do, in this kind of situations, is :

• echo the SQL code that corresponds to the statement, with placeholders
• and use var_dump (or an equivalent) just after, to display the values of the parameters
• This is generally enough to see a possible error, even if you don't have any "real" query that you can execute.

This is not great, when it comes to debugging -- but that's the price of prepared statements and the advantages they bring.

Probably what you want to do is use debugDumpParams() on the statement handle. You can run that any time after binding values to the prepared query (no need to execute() the statement).

It doesn't build the prepared statement for you, but it will show your parameters.

Here's a function to see what the effective SQL will be, adpated from a comment by "Mark" at php.net:

function sql_debug($sql_string, array $params = null) {
    if (!empty($params)) {
        $indexed = $params == array_values($params);
        foreach($params as $k=>$v) {
            if (is_object($v)) {
                if ($v instanceof \DateTime) $v = $v->format('Y-m-d H:i:s');
                else continue;
            }
            elseif (is_string($v)) $v="'$v'";
            elseif ($v === null) $v='NULL';
            elseif (is_array($v)) $v = implode(',', $v);

            if ($indexed) {
                $sql_string = preg_replace('/\?/', $v, $sql_string, 1);
            }
            else {
                if ($k[0] != ':') $k = ':'.$k; //add leading colon if it was left out
                $sql_string = str_replace($k,$v,$sql_string);
            }
        }
    }
    return $sql_string;
}

No. PDO queries are not prepared on the client side. PDO simply sends the SQL query and the parameters to the database server. The database is what does the substitution (of the ?'s). You have two options:

• Use your DB's logging function (but even then it's normally shown as two separate statements (ie, "not final") at least with Postgres)
• Output the SQL query and the paramaters and piece it together yourself

i use this class to debug PDO (with Log4PHP)

<?php

/**
 * Extends PDO and logs all queries that are executed and how long
 * they take, including queries issued via prepared statements
 */
class LoggedPDO extends PDO
{

    public static $log = array();

    public function __construct($dsn, $username = null, $password = null, $options = null)
    {
        parent::__construct($dsn, $username, $password, $options);
    }

    public function query($query)
    {
        $result = parent::query($query);
        return $result;
    }

    /**
     * @return LoggedPDOStatement
     */
    public function prepare($statement, $options = NULL)
    {
        if (!$options) {
            $options = array();
        }
        return new \LoggedPDOStatement(parent::prepare($statement, $options));
    }
}

/**
 * PDOStatement decorator that logs when a PDOStatement is
 * executed, and the time it took to run
 * @see LoggedPDO
 */
class LoggedPDOStatement
{

    /**
     * The PDOStatement we decorate
     */
    private $statement;
    protected $_debugValues = null;

    public function __construct(PDOStatement $statement)
    {
        $this->statement = $statement;
    }

    public function getLogger()
    {
        return \Logger::getLogger('PDO sql');
    }

    /**
     * When execute is called record the time it takes and
     * then log the query
     * @return PDO result set
     */
    public function execute(array $params = array())
    {
        $start = microtime(true);
        if (empty($params)) {
            $result = $this->statement->execute();
        } else {
            foreach ($params as $key => $value) {
                $this->_debugValues[$key] = $value;
            }
            $result = $this->statement->execute($params);
        }

        $this->getLogger()->debug($this->_debugQuery());

        $time = microtime(true) - $start;
        $ar = (int) $this->statement->rowCount();
        $this->getLogger()->debug('Affected rows: ' . $ar . ' Query took: ' . round($time * 1000, 3) . ' ms');
        return $result;
    }

    public function bindValue($parameter, $value, $data_type = false)
    {
        $this->_debugValues[$parameter] = $value;
        return $this->statement->bindValue($parameter, $value, $data_type);
    }

    public function _debugQuery($replaced = true)
    {
        $q = $this->statement->queryString;

        if (!$replaced) {
            return $q;
        }

        return preg_replace_callback('/:([0-9a-z_]+)/i', array($this, '_debugReplace'), $q);
    }

    protected function _debugReplace($m)
    {
        $v = $this->_debugValues[$m[0]];

        if ($v === null) {
            return "NULL";
        }
        if (!is_numeric($v)) {
            $v = str_replace("'", "''", $v);
        }

        return "'" . $v . "'";
    }

    /**
     * Other than execute pass all other calls to the PDOStatement object
     * @param string $function_name
     * @param array $parameters arguments
     */
    public function __call($function_name, $parameters)
    {
        return call_user_func_array(array($this->statement, $function_name), $parameters);
    }
}

I've created a modern Composer-loaded project / repository for exactly this here:

pdo-debug

Find the project's GitHub home here, see a blog post explaining it here. One line to add in your composer.json, and then you can use it like this:

echo debugPDO($sql, $parameters);

$sql is the raw SQL statement, $parameters is an array of your parameters: The key is the placeholder name (":user_id") or the number of the unnamed parameter ("?"), the value is .. well, the value.

The logic behind: This script will simply grad the parameters and replace them into the SQL string provided. Super-simple, but super-effective for 99% of your use-cases. Note: This is just a basic emulation, not a real PDO debugging (as this is not possible as PHP sends raw SQL and parameters to the MySQL server seperated).

A big thanks to bigwebguy and Mike from the StackOverflow thread Getting raw SQL query string from PDO for writing basically the entire main function behind this script. Big up!

Sure you can debug using this mode {{ PDO::ATTR_ERRMODE }} Just add new line before your query then you will show the debug lines.

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$db->query('SELECT *******');  

The problem I had with the solution to catch PDO exemptions for debuging purposes is that it only caught PDO exemptions (duh), but didn't catch syntax errors which were registered as php errors (I'm not sure why this is, but "why" is irrelevant to the solution). All my PDO calls come from a single table model class that I extended for all my interactions with all tables... this complicated things when I was trying to debug code, because the error would register the line of php code where my execute call was called, but didn't tell me where the call was, actually, being made from. I used the following code to solve this problem:

/**
 * Executes a line of sql with PDO.
 * 
 * @param string $sql
 * @param array $params
 */
class TableModel{
    var $_db; //PDO connection
    var $_query; //PDO query

    function execute($sql, $params) { 
        //we're saving this as a global, so it's available to the error handler
        global $_tm;
        //setting these so they're available to the error handler as well
        $this->_sql = $sql;
        $this->_paramArray = $params;            

        $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->_query = $this->_db->prepare($sql);

        try {
            //set a custom error handler for pdo to catch any php errors
            set_error_handler('pdoErrorHandler');

            //save the table model object to make it available to the pdoErrorHandler
            $_tm = $this;
            $this->_query->execute($params);

            //now we restore the normal error handler
            restore_error_handler();
        } catch (Exception $ex) {
            pdoErrorHandler();
            return false;
        }            
    }
}

So, the above code catches BOTH PDO exceptions AND php syntax errors and treats them the same way. My error handler looks something like this:

function pdoErrorHandler() {
    //get all the stuff that we set in the table model
    global $_tm;
    $sql = $_tm->_sql;
    $params = $_tm->_params;
    $query = $tm->_query;

    $message = 'PDO error: ' . $sql . ' (' . implode(', ', $params) . ") \n";

    //get trace info, so we can know where the sql call originated from
    ob_start();
    debug_backtrace(); //I have a custom method here that parses debug backtrace, but this will work as well
    $trace = ob_get_clean();

    //log the error in a civilized manner
    error_log($message);

    if(admin(){
        //print error to screen based on your environment, logged in credentials, etc.
        print_r($message);
    }
}

If anyone has any better ideas on how to get relevant info to my error handler than setting the table model as a global variable, I would be happy to hear it and edit my code.

this code works great for me :

echo str_replace(array_keys($data), array_values($data), $query->queryString);

Don't forget to replace $data and $query by your names

Here is a function I made to return a SQL query with "resolved" parameters.

function paramToString($query, $parameters) {
    if(!empty($parameters)) {
        foreach($parameters as $key => $value) {
            preg_match('/(\?(?!=))/i', $query, $match, PREG_OFFSET_CAPTURE);
            $query = substr_replace($query, $value, $match[0][1], 1);
        }
    }
    return $query;
    $query = "SELECT email FROM table WHERE id = ? AND username = ?";
    $values = [1, 'Super'];

    echo paramToString($query, $values);

Assuming you execute like this

$values = array(1, 'SomeUsername');
$smth->execute($values);

This function DOES NOT add quotes to queries but does the job for me.

almost nothing was said about error displaying except check error logs, but there's a rather helpful functionality:

<?php
/* Provoke an error -- bogus SQL syntax */
$stmt = $dbh->prepare('bogus sql');
if (!$stmt) {
    echo "\PDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}
?>

(source link)

it is clear that this code can be modified to be used as exception message or any other kind of error handling

How to debug PDO mysql database queries in Ubuntu

TL;DR Log all your queries and tail the mysql log.

These directions are for my install of Ubuntu 14.04. Issue command lsb_release -a to get your version. Your install might be different.

Turn on logging in mysql

1. Go to your dev server cmd line
2. Change directories cd /etc/mysql. You should see a file called my.cnf. That’s the file we’re gonna change.
3. Verify you’re in the right place by typing cat my.cnf | grep general_log. This filters the my.cnf file for you. You should see two entries: #general_log_file = /var/log/mysql/mysql.log && #general_log = 1.
4. Uncomment those two lines and save via your editor of choice.
5. Restart mysql: sudo service mysql restart.
6. You might need to restart your webserver too. (I can’t recall the sequence I used). For my install, that’s nginx: sudo service nginx restart.

Nice work! You’re all set. Now all you have to do is tail the log file so you can see the PDO queries your app makes in real time.

Tail the log to see your queries

Enter this cmd tail -f /var/log/mysql/mysql.log.

Your output will look something like this:

73 Connect  xyz@localhost on your_db
73 Query    SET NAMES utf8mb4
74 Connect  xyz@localhost on your_db
75 Connect  xyz@localhost on your_db
74 Quit 
75 Prepare  SELECT email FROM customer WHERE email=? LIMIT ?
75 Execute  SELECT email FROM customer WHERE email='[email protected]' LIMIT 5
75 Close stmt   
75 Quit 
73 Quit 

Any new queries your app makes will automatically pop into view, as long as you continue tailing the log. To exit the tail, hit cmd/ctrl c.

Notes

1. Careful: this log file can get huge. I’m only running this on my dev server.
2. Log file getting too big? Truncate it. That means the file stays, but the contents are deleted. truncate --size 0 mysql.log.
3. Cool that the log file lists the mysql connections. I know one of those is from my legacy mysqli code from which I'm transitioning. The third is from my new PDO connection. However, not sure where the second is coming from. If you know a quick way to find it, let me know.

Credit & thanks

Huge shout out to Nathan Long’s answer above for the inspo to figure this out on Ubuntu. Also to dikirill for his comment on Nathan’s post which lead me to this solution.

Love you stackoverflow!

To log MySQL in WAMP, you will need to edit the my.ini (e.g. under wamp\bin\mysql\mysql5.6.17\my.ini)

and add to [mysqld]:

general_log = 1
general_log_file="c:\\tmp\\mysql.log"

for example you have this pdo statement :

$query="insert into tblTest (field1, field2, field3)
values (:val1, :val2, :val3)";
$res=$db->prepare($query);
$res->execute(array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
));

now you can get the executed query by defining an array like this :

$assoc=array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
);
$exQuery=str_replace(array_keys($assoc), array_values($assoc), $query);
echo $exQuery;