Restoring PDO functionality

A few years ago, while PHP 5 was still in a state of flux, a change was made to the way that PDO handles parameters bound to prepared statements. Somewhere between versions 5.2.0 and 5.2.1 a change was made that gave rise to much annoyance and debate in bug 40417. Long story short, it used to be acceptable to reuse a placeholder in statement several times and bind a single variable to all of the instances thusly:

[sourcecode language=’php’]< ?php // Connect to the database with defined constants $dbh = safePDO_Factory::getInstance(PDO_DSN, PDO_USER, PDO_PASSWORD); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {

// Construct SQL query
$query = ‘
SELECT
*
FROM
tblnews
WHERE
title ILIKE :search
OR content ILIKE :search
‘;

// Prepare the statement
$stmt = $dbh->prepare($query);

// Bind the search string variable to the statement
$smt->bindParam(‘:search’, $search, PDO::PARAM_STR);

// Execute the query
$stmt->execute();

// Check to see if we have any results
if ($stmt->rowCount() > 0) {
// Process the results here . . .
} else {
echo ‘No search results were returned.’;
}
} catch (Exception $e) {
echo $e->getMessage();
)

// Destroy the database connection
$dbh = null;

?>[/sourcecode]

But all of a sudden the above rudimentary news searching code would cease to work if you upgraded to PHP 5.2.1 and there was no notice in the PHP change log at the time, which obviously led to much confusion. The issue was that it was no longer acceptable to bind a single variable to multiple placeholders – each placeholder required a unique name and explicit variable binding.

In much the same way that I subclassed the PDO connection class, the individual PDO Statement class can be extended to restore this multiple placeholder / single variable behaviour.

[ad#individual-post]

First of all we need to make some changes to the derived PDO class in order to let the object know where to find the new statement class but also to pre-parse the SQL prepared statement for repeated placeholders. To make this multiple placeholder / single variable behaviour available as an option, I have simply hijacked the PDO driver options array as any option set that the driver doesn’t understand will simply be ignored.

[sourcecode language=’php’]< ?php Class safePDO_Flyweight extends PDO { public $options; public $tokens; public static function exception_handler($exception) { // Output the exception details die('

Database connection error

‘ . $exception->getMessage() . ‘

‘);
}

// PDO constructor is public so we can’t make this private and uninstantiable
public function __construct($dsn, $username=”, $password=”, $driver_options=array()) {

// Temporarily change the PHP exception handler while we . . .
set_exception_handler(array(__CLASS__, ‘exception_handler’));

// Find out who tried to call the constructor
$chain = debug_backtrace();
$caller = $chain[1][‘class’];

// Enforce SprocketPDO_Factory creation
if (‘SprocketPDO_Factory’ == $caller) {

// . . . create a PDO object
parent::__construct($dsn, $username, $password, $driver_options);

} else {
throw new Exception(‘Cannot directly instantiate. Please use SprocketPDO_Factory’);
}

// Make the options available
$this->options = $driver_options;

// Define the PDO Statement class
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array(‘extendedPDOStatement’, array($this)));

// Change the exception handler back to whatever it was before
restore_exception_handler();
}

public function prepare($sql) {
$tokens = array();

// Check if the option is enabled
if (true === $this->options[‘singleTokenMultiParams’]) {

// Parse the SQL query for placeholders
preg_match_all(“/(:w+)/”, $sql, $matches, PREG_PATTERN_ORDER);
foreach($matches[1] as $index=>$token) {
$tokens[$token]++;
}

// For each placeholder
foreach($tokens as $token=>$count) {

// Check if the placeholder is repeated
if ($count > 1) {

// Find where to start searching (after the first occurrence)
$start = strpos($sql, $token);
$length = strlen($token);

// Replace each additional occurrence with a unique placeholder

do {
$start = strpos($sql, $token, $start+1);
if (false !== $start) {
$loop++;
$sql = substr_replace($sql, $token . ‘_’ . $loop, $start, $length);
}
} while(false !== $start);
}
}
}

// Call the standard prepare method
$pdoStatement = parent::prepare($sql);

// Store the tokens used with this query
$this->tokens[$pdoStatement->queryString] = $tokens;

return $pdoStatement;
}

}

?>[/sourcecode]

Now for the extended statement class in which I’ve implemented a private generic binding function. This can be called by the standard bindParam and bindValue PDO Statement methods and will propagate any bound variable to multiple placeholders as necessary.

[sourcecode language=’php’]< ?php class extendedPDOStatement extends PDOStatement { private $dbh; // Create and associate with a database handle protected function __construct($dbh) { $this->dbh = $dbh;
}

private function _genericBind($function) {

// Function is called with varying number of arguments
$args = array_slice(func_get_args(), 1);

// Bind the first placeholder by the appropriate method
call_user_func_array(array(parent, $function), $args);

if (true === $this->dbh->options[‘singleTokenMultiParams’]) {

// First argument is the placeholder
$placeholder = array_shift($args);

// Recover placeholder information from the prepared statement
$tokens = $this->dbh->tokens[$this->queryString];
$count = $tokens[$placeholder];

// Only interested in multiple placeholders with the same name
if ($count > 1) {

// Bind the value to each of the extra uniquely-named placeholders
for($loop = 1; $loop < $count; $loop++) { $uniqname = $placeholder . '_' . $loop; call_user_func_array(array(parent, $function), array_merge(array($uniqname), $args)); } } } } // Wrapper to call the generic binding function public function bindValue($placeholder, $value, $type=null) { $this->_genericBind(__FUNCTION__, $placeholder, $value, $type);
}

// Wrapper to call the generic binding function
public function bindParam($placeholder, $value, $type=null, $length=null, $options=null) {
$this->_genericBind(__FUNCTION__, $placeholder, $value, $type, $length, $options);
}

}

// Use the driver options array to specify new behaviour
$dbh = SprocketPDO_Factory::getInstance(PDO_DSN, PDO_USER, PDO_PASSWORD, array(
‘singleTokenMultiParams’ => true,
));

?>[/sourcecode]

[ad#individual-post]

Related Posts: