Debugging PDO Prepared Statement Emulation

Even though they were originally intended merely as a means to speed up execution of SQL queries within the database engine, prepared statements today are an important and de-facto standard for secure database access.

As not all the database backends that are supported by PDO can natively handle prepared statements, PDO optionally provides an emulation. Since this emulation happens internally within PDO it is surprisingly hard to debug from within userland PHP.

Starting with PHP 7.2, a new method PDOStatement::activeQueryString() has been added to make debugging easier by allowing you to retrieve the current SQL query string. The content of the returned string depends on the point in time the debugging method is used. Prior the execution of the query, the raw string including placeholders will be returned – regardless if a value has already been bound to it. Once executed, these placeholders will have been replaced by their bound values:

$db = new PDO(...);

$stmt = $db->prepare('SELECT :name');
$stmt->bindValue(':name', 'php7explained');

$before = $stmt->activeQueryString());

$stmt->execute();

$after = $stmt->activeQueryString());

var_dump($before, $after);

When run, the following output is produced:

string(12) "SELECT :name"
string(22) "SELECT 'php7explained'"

You can, of course, also rebind values and re-execute the query. The string returned from activeQueryString() will reflect the updated values but no longer return the original query string with the placeholders still in place.