php PDO replace anonymous parameters ('?') in queries with redable values

Question

I want a way to show SQL query how it will look with anonymous parameters (?) substituted by actual parameters.
This is only for readability purposes and debugging, and wouldn't be used as an actual query.

I have found this function which works for the most part:

return array_reduce($this->bindValues, function ($sql, $binding) {
  return preg_replace('/\?/', is_numeric($binding) ? $binding : '"' . $binding . '"', $sql, 1);
}, $this->query);

replacing ? with actual values:

    $data = array(
        'item' => '1,
        'type' => 'Are you ok.'
    );

UPDATE `list` set `item`=?,`type`=? WHERE  (`id` = ?) ;
UPDATE `list` set `item`="1",`type`="Are you ok." WHERE (`id` = 1) ;

but if the value contains ? I'm ending up with:

    $data = array(
        'item' => '1,
        'type' => 'Are you ok?'
    );

UPDATE `list` set `item`="1",`type`="Are you ok2" WHERE (`id` = ?) ;

How can I make this work, so only binding ? are replaced.

Answer 1

First of all, consider using named parameters instead of ?. In this case you'll don't need to substitute anything: named parameters are clear and fairly easy to show in logs, and supported by most dbms clients for debugging purposes.

If named parameters are not feasible (due to large current code base or any other reason) you have two major ways:

1. Use SQL query parser. It will produce most reliable outcome.
2. Use some kind of self written "substitutor". It's outcome never will be ideal or fully reliable, but should be fast both in terms of performance and development.

If you'll choose latter way, here is example how it could be done quick and dirty:

Do your substitution in multiple steps:

1. Prepare your parameters by replacing ? with something other, highly unlikely to be present in parameters or query. For example \?.
2. Substitute parameters using regex, that will match ?, but would not match substitution from first step. For substitution with \? it will be (?<!\\)?
3. Substitute all \? with ? in result.

ATTENTION: result of this substitution should never be used as a query in your program. Such a substitution has a potential for any or all of the below:

• SQL injections,
• inaccurate result if initial query contains ? not as a parameter (for example in comments),
• inaccurate result if initial query or any of the parameters contain substitution string (\? in our example).

<?php
$query = 'UPDATE `list` set `item`=?,`type`=? WHERE  (`id` = ?);';
$params = array(
    'item' => '1',
    'type' => 'Are you o\'k?',
    'id'   => 2
);

function substitute_params($query, $params) {
    $prep_params =  str_replace(array("'","?"),array("''","\\?"),$params);
    $query = array_reduce($prep_params, function ($interm, $param) {
        return preg_replace('/(?<!\\\\)\\?/m', 
            is_numeric($param) ? $param : '\'' . $param . '\'', 
            $interm, 
            1);
    }, $query);
    return "-- Not to be used as a query to database. For demonstration purposes only!\n"
      .str_replace("\\?", "?", $query);
}
echo substitute_params($query, $params);
?>

Outputs:

-- Not to be used as a query to database. For demonstration purposes only!
UPDATE `list` set `item`=1,`type`='Are you o''k?' WHERE  (`id` = 2);

EDIT: to try and lower impact of question marks inside of constant strings and quoted names you could try to use this replacement:

        return preg_replace('/^([^"\'`]*?(?:(?:`[^`]*?`[^"\'`]*?)*?(?:"[^"]*?"[^"\'`]*?)*?(?:\'[^\']*?\'[^\'"`]*?)*?)*?)(?<!\\\\)\\?/m', 
            '${1}'.(is_numeric($param) ? $param : '\'' . $param . '\''), 
            $interm, 
            1);

It substitutes only ? outside of blocks quoted with either of "`'.

Demo can be seen here.

Please remember, this is not fully fledged parser. It is not aware of comments, for example. So erroneous substitutions are still very much possible.

Answer 2

You can always use plain string operations:

<?php

$str = 'UPDATE `list` set `item`=?,`type`=? WHERE  (`id` = ?);';

$data = array(
    'item' => '1',
    'type' => 'Are you ok?'
);

$pos = 0;
$index = 0;
do {
    $pos = strpos($str, '?', $pos);
    if ($pos === false) {
        break;
    }
    $binding = array_values($data)[$index%count($data)];
    $replacement = is_numeric($binding) ? $binding : '"' . $binding . '"';
    $str = substr_replace($str, $replacement, $pos, 1);
    $pos += strlen($replacement);
    $index++;
} while ($pos !== false);

echo $str; // Outputs UPDATE `list` set `item`=1,`type`="Are you ok?" WHERE  (`id` = 1);

3v4l code