Posted

Something that has always bugged me about using prepared statements, is that you can really only get the query sent to the database by catching it in the logs.

Today, a friend asking me if it was possible to get a prepared statement back from PDO with the values placeholders replaced, finally caught me in a moment where I could do something about it.

I wrote a thin PDO wrapper class that will [imperfectly, I'm sure] return the completed query.

It supports bound parameters, values and the array key->value methods of passing in values to prepared queries. You can see the code and examples below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134
<?php
class PDOTester extends PDO {
public function __construct($dsn, $username = null, $password = null, $driver_options = array())
{
parent::__construct($dsn, $username, $password, $driver_options);
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatementTester', array($this)));
}
}
 
class PDOStatementTester extends PDOStatement {
const NO_MAX_LENGTH = -1;
protected $connection;
protected $bound_params = array();
protected function __construct(PDO $connection)
{
$this->connection = $connection;
}
public function bindParam($paramno, &$param, $type = PDO::PARAM_STR, $maxlen = null, $driverdata = null)
{
$this->bound_params[$paramno] = array(
'value' => &$param,
'type' => $type,
'maxlen' => (is_null($maxlen)) ? self::NO_MAX_LENGTH : $maxlen,
// ignore driver data
);
$result = parent::bindParam($paramno, $param, $type, $maxlen, $driverdata);
}
public function bindValue($parameter, $value, $data_type = PDO::PARAM_STR)
{
$this->bound_params[$parameter] = array(
'value' => $value,
'type' => $data_type,
'maxlen' => self::NO_MAX_LENGTH
);
parent::bindValue($parameter, $value, $data_type);
}
public function getSQL($values = array())
{
$sql = $this->queryString;
if (sizeof($values) > 0) {
foreach ($values as $key => $value) {
$sql = str_replace($key, $this->connection->quote($value), $sql);
}
}
if (sizeof($this->bound_params)) {
foreach ($this->bound_params as $key => $param) {
$value = $param['value'];
if (!is_null($param['type'])) {
$value = self::cast($value, $param['type']);
}
if ($param['maxlen'] && $param['maxlen'] != self::NO_MAX_LENGTH) {
$value = self::truncate($value, $param['maxlen']);
}
if (!is_null($value)) {
$sql = str_replace($key, $this->connection->quote($value), $sql);
} else {
$sql = str_replace($key, 'NULL', $sql);
}
}
}
return $sql;
}
static protected function cast($value, $type)
{
switch ($type) {
case PDO::PARAM_BOOL:
return (bool) $value;
break;
case PDO::PARAM_NULL:
return null;
break;
case PDO::PARAM_INT:
return (int) $value;
case PDO::PARAM_STR:
default:
return $value;
}
}
static protected function truncate($value, $length)
{
return substr($value, 0, $length);
}
}
 
$pdo = new PDOTester('sqlite::memory:');
$pdo->query('CREATE TABLE foo (bar TEXT, baz TEXT, num NUMERIC, empty TEXT)');
$query = $pdo->prepare('SELECT * FROM foo WHERE bar = :bar AND baz = :baz');
 
// Test with passed in array
echo $query->getSQL(array(':bar' => 'foo', ':baz' => 'bat')) . PHP_EOL;
 
$query = $pdo->prepare('SELECT * FROM foo WHERE bar = :bar AND baz = :baz AND num = :num AND empty=:empty');
 
// Test with bound params and values
$bar = 'bar';
$baz = 'baz';
$num = '0.1';
$empty = 'empty!!';
 
// Bind Param
$query->bindParam(':bar', $bar);
 
// Bind Value
$query->bindValue(':baz', $baz);
 
// Bind With types
$query->bindParam(':num', $num, PDO::PARAM_INT);
$query->bindParam(':empty', $empty, PDO::PARAM_NULL);
 
echo $query->getSQL() . PHP_EOL;
 
// Change the vars
$bar = 'foo';
$baz = 'bat';
$num = '2.6';
$empty = 'blah!';
 
echo $query->getSQL() . PHP_EOL;
 
// Bind with length
$query->bindParam(':bar', $bar, PDO::PARAM_STR, 2);
 
echo $query->getSQL() . PHP_EOL;
?>

This results in the following output:

1 2 3 4
SELECT * FROM foo WHERE bar = 'foo' AND baz = 'bat'
SELECT * FROM foo WHERE bar = 'bar' AND baz = 'baz' AND num = '0' AND empty=NULL
SELECT * FROM foo WHERE bar = 'foo' AND baz = 'baz' AND num = '2' AND empty=NULL
SELECT * FROM foo WHERE bar = 'fo' AND baz = 'baz' AND num = '2' AND empty=NULL

Hopefully, this will help you get a somewhat better idea of what’s going on :)