July 8, 2014
Chris
, , , ,
Comments Off on Debug PDO with this one-line function. Yeah!

Debug PDO with this one-line function. Yeah!

pdo-debug

Update: This article is a little bit older, the tool has changed gently. Everthing this article still works exactly like described. But, to get the latest version (different syntax) and up-to-date install instructions please see the GitHub page.

You know the problem: Debugging PDO is a pain in the ass, as it’s “not really possible”. With the classic mysql_* and mysqli_* functions / methods you can easily create your SQL query and output it directly in PHP with echo, var_dump or – the correct way – see the content directly in your IDE’s debugger. A little reminder here: Learn real debugging, it will save your life! However, when using PDO you’ll get the advantages of automatic SQL injection protection, beautiful syntax and performance increases, but have to deal with the fact you’ll never really see the full SQL statement. This is a common problem and has been asked over and over again on the web. I was also frustrated with that, and therefore grabbed the best PDO query emulator function I could find, put it into a Composer project and released it on GitHub / packagist: pdo-debug.

Why does this problem exist ?

PDO sends the raw query (without real values) and the parameters independently to the database server. That’s it. So PHP will never know the full statement, as the final query is constructed by MySQL, not by PHP.

In theory, the possibilities to debug PDO are

1. Let MySQL log the finally created values. This needs a lot of configuration and is definitly the best way to go, as this shows the real, final statements.

2. Take the raw SQL with the placeholders, take the parameter values and simply combine them in the way PDO would do. This is not perfect, as PDO does / allows more than just replacing the placeholders with real values (like changing types etc.). It’s just an emulation, and a very basic one.

Prepare a PDO block for debuggin with pdo-debug

Your PDO block probably looks like this:

$sql = "INSERT INTO test (col1, col2, col3) VALUES (:col1, :col2, :col3)";

and this, right?

$query->execute(array(':col1' => $param_1, ':col2' => $param_2, ':col3' => $param_3));

To use this PDO logger, you’ll have to rebuild the param array a little bit: Create an array that has the identifier as the key and the parameter as the value, like below:
WARNING: write this WITHOUT the colon! The keys need to be ‘xxx’, not ‘:xxx’!

$parameters = array(
    'param1' => 'hello',
    'param2' => 123,
    'param3' => null
);

Your full PDO block would then look like:

$sql = "INSERT INTO test (col1, col2, col3) VALUES (:param1, :param2, :param3)";
$query = $database_connection->prepare($sql);
$query->execute($parameters);

Perfect!

Load pdo-debug into your project (with Composer) and use it

As Composer is the de facto standard, we’ll load this debug tools with Composer (note the require-dev as you don’t need a PDO debugger in production, just in development)

"require-dev": {
    "panique/pdo-debug": "0.1"
}

and perform an

composer update

to load this into your project. You can now use pdo-debug everywhere by using the global function debugPDO(). A simple real world example:

echo debugPDO($sql, $parameters);

This would echo out the full SQL statement including the parameter values, like

INSERT INTO test (col1, col2, col3) VALUES ('hello', 123, NULL)

Voila!

Big thanks

A big thanks to bigwebguy and Mike on this StackOverflow thread: Getting raw SQL query string from PDO prepared statements. These guys have created and improved the basic function that is used in this free and open-source project. Send them a Thanks-notice if pdo-debug saves you a lot of time and nerves!

Warning: This is highly experimental!

This little tool is super-basic. The underlaying function has a lot of upvotes on StackOverflow and already helped lots of people. However, it’s just a basic emulation, not looking at possible glitches when doing advanced PDO stuff. If you are advanced or an expert with PDO, it would be nice if you could add your part to the repo right on GitHub. Maybe it’s possible to make this function better by respecting PDO’s parameter options etc. – anyway, if you want to contribute, please do!