preg_replace()替换太多 [英] preg_replace() is replacing too much
问题描述
我正在开发一个简单的SQL调试器，该调试器将接受参数化的变量并尝试相应地替换它们，以便如果某条SQL出现问题，则可以将其直接复制+粘贴到我的RDBMS中以与查询一起使用，并希望更快地调试问题.
I am working on a simple SQL debugger which will accept parameterized variables and try to replace them accordingly so that if a piece of SQL has an issue then I can copy+paste it directly into my RDBMS to work with the query and hopefully debug an issue quicker.
到目前为止，我基本上已经有了这个，但是它替代了太多:
So far I essentially have this, but it is replacing too much:
<?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.
Notice that 'monkeyzeus@example.com' from position 1 is making it into the :b from position 0.
I've found this question, Can preg_replace make multiple search and replace operations in one shot?, but I cannot make heads or tails of it because I am certainly no regex expert.
更新.只是想分享最终产品:
Update. Just wanted to share the final product:
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
);
}
推荐答案
有一个专门针对这种情况的特殊功能:
strtr —翻译字符或替换子字符串
http://php.net/manual/zh/function.strtr.php
There is a special function exactly for this case:
strtr — Translate characters or replace substrings
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);
这篇关于preg_replace()替换太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
