CODEWITHSUNDEEP
phppreg-replace
Dmitriy Chetverikov
Dmitriy Chetverikov

Reputation: 13

preg_replace - don't replace in already replaced parts

Given SQL-query with placeholders:

SELECT * FROM table WHERE `a`=? AND `b`=?

and query parameters ['aaa', 'bbb'], i would like to replace ?-placeholders with corresponding params. So, I do it like this:

$sql = preg_replace(array_fill(0, count($params), '#\?#'), $params, $sql, 1);

(we do not concentrate on mysql-escaping, quoting etc. in this question).

Everything works fine and I get

SELECT * FROM table WHERE `a`=aaa AND `b`=bbb

But if our first parameter looks like this: "?aa", everything fails:

SELECT * FROM table WHERE `a`=bbba AND `b`=?

obviously, first replacement pass changes "a=?" into "a=?aa", and second pass changes this (just inserted) question mark into "bbb".

The question is: how can I bypass this confusing preg_replace behaviour?

Upvotes: 0

Views: 78

Answers (2)

Scaramouche
Scaramouche

Reputation: 3257

You can use preg_replace_callback to use one item from $params at a time for each replacement.

$sql = 'SELECT * FROM table WHERE `a`=? AND `b`=?';
var_dump('Original: ' . $sql);
$params=['aaa','bbb'];

$sql = preg_replace_callback("/\\?/",function($m) use (&$params) {
    return array_shift($params);
}, $sql);

var_dump('Result: ' . $sql);

Let me know

Upvotes: 2

user3783243
user3783243

Reputation: 5224

I would not do this with preg_replace or str_replace. I would use preg_split so empty returns can be removed (If explode had empty removal option I'd use that). For there iterate over the return and add in values. You also can quote the values with this. I presume the purpose of this is for debugging parameterized queries.

$sql = 'SELECT * FROM table WHERE `a`=? AND `b`=?';
$v = array('1?1', "222");
$e = preg_split('/\?/', $sql, NULL, PREG_SPLIT_NO_EMPTY);
$c = '';
foreach($e as $k => $v1){
    $c .= $v1 . "'" . $v[$k] ."'"; 
}
error_log($c);

Then your error log will have:

SELECT * FROM table WHERE `a`='1?1' AND `b`='222'

Upvotes: 0

Related Questions