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.
2
Answers
You can always use plain string operations:
3v4l code
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:
If you’ll choose latter way, here is example how it could be done quick and dirty:
Do your substitution in multiple steps:
?
with something other, highly unlikely to be present in parameters or query. For example?
.?
, but would not match substitution from first step. For substitution with?
it will be(?<!\)?
?
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:
?
not as a parameter (for example in comments),?
in our example).Outputs:
EDIT: to try and lower impact of question marks inside of constant strings and quoted names you could try to use this replacement:
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.