PHP Regular expression to replace SQL query conditions

Couldnt figure out how to replace values of a conditions in a sql query with PHP ,

Example:

SELECT * 
FROM table 
WHERE a=1 
and a = '2' 
and a= "3" 
and 开发者_Python百科a LIKE '%a' 
and a LIKE "a%" 
and a < 1 
and a<1 
and a >1 
and a >1 
HAVING a <1

So expected output will be

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 <?

my failed pattern is:

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

you can do that without pattern

something like this:

$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);

or

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

one more idea

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

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

It's strict about matching beginning and end quotes (if present) around values, and also matches a few other operators and stuff like the NULL value.

But it's not perfect, so be careful

Edit: Here's a more comprehensive one that also handles IN ( ... ) clauses
But nevertheless: Messing with queries is still dangerous. Worst case is that you accidentally create your own sql injection

$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);

Or you could simply use the replace function, since regex ist far to slow in php and replace would give you a huge speed boost!

Like

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

Think this should do, just replace every match with " ? " (without quotes :)

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