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 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))*
