Preg_replace solution for prepared statements

Question

Hi Guys,

I have a command class that abstracts almost all specific database functions (We have the exactly same application running on Mssql 2005 (using ODBC and the native mssql library), MySQL and Oracle. But sometimes we had some problems with our prepare method that, when executed, replaces all placeholders with their respective values. But the problem is that I am using the following:

if(is_array($Parameter['Value']))
{
    $Statement = str_ireplace(':'.$Name, implode(', ', $this->Adapter->QuoteValue($Parameter['Value'])), $Statement);
}
else
{
    $Statement = str_ireplace(':'.$Name, $this->Adapter->QuoteValue($Parameter['Value']), $Statement);
}

The problem arises when we have two or mer similar parameters names, for example, session_browser and session_browse_version... The first one will partially replace the last one.

Course we learned to go around specifying the parameters within a specific order, but now that I have some "free" time I want to make it better, so I am thinking on switching to preg_replace... and I am not good in regular expression, can anyone give any help with a regex to replace a string like ':parameter_name`?

Best Regards, Bruno B B Magalhaes

Answer 1

You should use the \b metacharacter to match the word boundary, so you don't accidentally match a short parameter name within a longer parameter name.

Also, you don't need to special-case arrays if you coerce a scalar Value to an array of one entry:

preg_replace("/:$Name\b/", 
    implode(",", $this->Adapter->QuoteValue( (array) $Parameter['Value'] )), 
    $Statement);

Note, however, that this can make false positive matches when an identifier or a string literal contains a pattern that looks like a parameter placeholder:

SELECT * FROM ":Name";

SELECT * FROM Table WHERE column = ':Name';

This gets even more complicated when quoted identifiers and string literals can contain escaped quotes.

SELECT * FROM Table WHERE column = 'word\':Name';

You might want to reconsider interpolating variables into SQL strings during prepare, because you're defeating any benefits of prepared statements with respect to security or performance.

I understand why you're doing what you're doing, because not all RDBMS back-end supports named parameters, and also SQL parameters can't be used for lists of values in an IN() predicate. But you're creating an awfully leaky abstraction.