PHP正则表达式替换SQL查询条件 - PHP Regular expression to replace SQL query conditions

PHP Regular expression to replace SQL query conditions

Couldnt了解如何用PHP、替换sql查询中条件的值

示例：

SELECT * 
FROM table 
WHERE a=1 
and a = '2' 
and a= "3" 
and a LIKE '%a' 
and a LIKE "a%" 
and a < 1 
and a<1 
and a >1 
and a >1 
HAVING a <1

因此预期输出将是

SELECT * 
FROM table 
WHERE a=? 
and a = ? 
and a= ? 
and a LIKE ? 
and a LIKE ? 
and a < ? 
and a<? 
and a >? 
and a >? 
HAVING a <?

我失败的模式是：

#(like|<|>|=){1}['s]{0,1}['"'s"]{0,1}(.*?)['"'s"]{0,1}#si

您可以在没有模式的情况下做到这一点

像这样的东西：

$query = "SELECT * 
FROM table 
WHERE a=%s 
and a = %s 
and a= %s 
and a LIKE %s 
and a LIKE %s 
and a < %s 
and a<%s 
and a >%s 
and a >%s 
HAVING a <%s";
$query = sprintf($query,$arg1,$arg2,$arg3,$arg4,$arg5,$arg6);

或

$query = sprintf($query,$arrayArgs);

还有一个想法

$query = preg_replace("((.+)(like|<|>|<>|=)(.+)('s*,|'n|$))Ui","$1$2'?'$4",$query);

preg_replace("/(LIKE|<|>|<>|=|IS(?: NOT)?|(?:NOT )?IN)'s*((['"''(]).*?'3|[^'s]+)/si", "$1 ?", $query);

它严格要求在值周围匹配起始引号和结束引号(如果存在(，还匹配一些其他运算符和类似NULL值的东西。

但它并不完美，所以要小心

编辑：这里有一个更全面的，也处理IN ( ... )子句
但是，尽管如此：混淆疑问仍然是危险的。最糟糕的情况是您不小心创建了自己的sql注入

$pattern = '/(LIKE|<|>|<>|=|IS(?: NOT)?|(?:NOT )?IN)
  's*
  (
    (["''])     # capture opening quote
    .*?
    (?<![^''']''')'3  # closing quote
  |
    '(          # opening parenthesis
      [^')]*
    ')          # closing parenthesis
  |
    [^'s]+      # any other contiguous string
  )/six';
preg_replace($pattern, "$1 ?", $query);

或者您可以简单地使用replace函数，因为regex在php中的速度太慢了，而replace会给您带来巨大的速度提升！

像

$query = '...';
$query = str_replace('1', '?', $query);
$query = str_replace('2', '?', $query);
$query = str_replace('3', '?', $query);
$query = str_replace('4', '?', $query);
...

认为应该这样做，只需将每个匹配项替换为"？"(不带引号：(

((?<=like)|(?<=<)|(?<=>)|(?<==))'s*[^'s]+('s|$)(.(?!where))*