preg_replace()替换了太多

Question

我正在开发一个简单的SQL调试器，它将接受参数化变量，并尝试相应地替换它们，以便如果一段SQL出现问题，我可以将其直接copy+paste到我的关系数据库管理系统中，以处理查询，并希望能够更快地调试问题。

到目前为止，我基本上有这样的想法，但它取代了太多的内容：

<?php
$sql = "select *
  from table_name
 where comment like :a and
       email = :b and
       status = :c";

$patterns = array();
$patterns[0] = '/:a/';
$patterns[1] = '/:b/';
$patterns[2] = '/:c/';

$replacements = array();
$replacements[0] = "'%that is a nice :b but this one%'";
$replacements[1] = "'monkeyzeus@example.com'";
$replacements[2] = "'active'";

echo preg_replace($patterns, $replacements, $sql);

导致

select *
  from table_name
 where comment like '%that is a nice 'monkeyzeus@example.com' but this one%' and
       email = 'monkeyzeus@example.com' and
       status = 'active'

请注意，来自位置1的'monkeyzeus@example.com'正在从位置0将其转换为:b。

我已经找到了这个问题，replace make multiple search and replace operations in one shot?，但是我不能把它搞清楚，因为我肯定不是正则表达式专家。

更新。我只想分享最后的产品：

function debug_sql($sql = NULL, $params = NULL)
{
    return (
        $sql !== NULL && is_array($params) && $params ? // $sql and $params is required
        strtr( // Feed this function the sql and the params which need to be replaced
            $sql,
            array_map( // Replace single-quotes within the param items with two single-quotes and surround param in single-quotes
                function($p)
                {
                    return "'".str_replace("'", "''", $p)."'"; // Basic Oracle escaping
                },
                $params
            )
        ) :
        $sql
    );
}

Answer 1

对于这种情况，有一个特殊的函数：strtr -转换字符或替换子字符串http://php.net/manual/en/function.strtr.php。

<?php

$sql = "select * from table_name where comment like :a and email = :b and status = :c";

$map = [
    ':a' => "'%that is a nice :b but this one%'",
    ':b' => "'monkeyzeus@example.com'",
    ':c' => "'active'"
];

echo strtr($sql, $map);

Answer 2

在jeroen的一些有见地的建议之后

首先，用永远不会出现的散列/占位符替换占位符，然后用替换字符串替换它们。

我已经想出了这个方法，它似乎适用于我的所有测试用例：

<?php
$sql = "select *
  from table_name
 where comment like :a and
       email = :b and
       status = :c and
       something = :bb";

$patterns = array();
$replacements = array();

$patterns[0][0] = '/(:a)\\b/';
$patterns[0][1] = '/(:b)\\b/'; // Use word-boundary to prevent :b from being found in :bb
$patterns[0][2] = '/(:c)\\b/';
$patterns[0][3] = '/(:bb)\\b/';

$replacements[0][0] = str_replace('.', '', uniqid('', TRUE));
$replacements[0][1] = str_replace('.', '', uniqid('', TRUE));
$replacements[0][2] = str_replace('.', '', uniqid('', TRUE));
$replacements[0][3] = str_replace('.', '', uniqid('', TRUE));

$patterns[1][0] = '/('.$replacements[0][0].')\\b/';
$patterns[1][1] = '/('.$replacements[0][1].')\\b/';
$patterns[1][2] = '/('.$replacements[0][2].')\\b/';
$patterns[1][3] = '/('.$replacements[0][3].')\\b/';

$replacements[1][0] = "'%that is a nice :b but this one%'";
$replacements[1][1] = "'monkeyzeus@example.com'";
$replacements[1][2] = "'active'";
$replacements[1][3] = "'another thing'";

$sql = preg_replace($patterns[0], $replacements[0], $sql);
$sql = preg_replace($patterns[1], $replacements[1], $sql);

echo $sql;

唯一可能失败的方法是用户在处理时查询str_replace('.', '', uniqid('', TRUE))的确切输出。

Answer 3

没有regexp的另一种方法是递归地爆炸/内爆查询：

$sql = "select * from table_name where comment like :a and email = :b and status = :c ";

$patterns = array();
$patterns[0] = ' :a ';
$patterns[1] = ' :b ';
$patterns[2] = ' :c ';

$replacements = array();
$replacements[0] = " '%that is a nice :b but this one%' ";
$replacements[1] = " 'monkeyzeus@example.com' ";
$replacements[2] = " 'active' ";

function replace($substr, $replacement, $subj) {
    if (empty($substr)) {
        return $subj;
    }
    $s = array_shift($substr);
    $r = array_shift($replacement);
    foreach($subj as &$str) {
        $str = implode($r, replace($substr, $replacement, explode($s, $str)));
    }
    return $subj;
}

echo replace($patterns, $replacements, [$sql])[0];