php PDO replace anonymous parameter ('?') in query with editable value

Question

I would like a way to show what the SQL query looks like when the anonymous parameters (?) are replaced with actual parameters.

This is only for readability purposes and debugging, and will not be used as an actual query.

I found this function works in most cases:

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

Replace? and actual value:

$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 end 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 so it only binds? has been replaced.

Answer 1

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

If named parameters are not feasible (due to the size of your current codebase or any other reason), you have two main approaches:

1. Use SQL query parser. It will produce the most reliable results.
2. Use some kind of self-written "replacer". Its results will never be ideal or completely reliable, but it should be fast both in terms of performance and development.

If you choose the latter approach, here is an example of how to do it quickly and dirty:

Replace in multiple steps:

1. Prepare the parameters by replacing ? with something else that is highly unlikely to appear in the parameter or query. For example\?.
2. Use a regular expression to replace the parameter, which will match ?, but will not match the substitution in the first step. If replaced with \?, it is (?
3. Replace all \? in the result with ?.

NOTE: The results of this substitution should never be used as a query in a program. This replacement has the potential to implement any or all of the following:

• SQL injection,
• If the initial query contains ? instead as a parameter (e.g. in a comment), the results are inaccurate,
• If the initial query or any parameters contain a replacement string (\? in our example), the results will be inaccurate.

<?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);
?>

Output:

-- 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 to reduce the impact of question marks within constant strings and quoted names, you can try using this replacement:

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

It only replaces ? outside the block quoted with "`'.

You can view the demo here.

Please keep in mind that this is not a fully fledged parser. For example, it doesn't know about comments. So the possibility of incorrect substitution remains high.