Hi,

I am using PHP 5.2.1 and I have to implement a layer of abstraction into a user supplied SQL query. The user does not know the actual column names (let's call them "SQL columns"), but can use "easy" names for them (let's call them "PHP columns"):

I get a SQL where clause in the form of:

WHERE phpcol1 = 'abc'  AND phpcol2 LIKE 'def' AND phpcol3 > phpcol3

Actually my input might be any valid SQL where clause. Now I have to replace "phpcol1" with "sqlcol1" etc. (not literally, that would be to easy, phpcolX and sqlcolX can be arbitrary strings).

I have to make sure though, that all quoted strings ('abc', 'def' in the example above) are kept intact, even if a column name should be part of them. Escaped single quotes as in 'Bill''s PC' should be possible as well.

WHERE abc = 'abc'

should become

WHERE xyz = 'abc'

and NOT

WHERE xyz = 'xyz'

 So my question finally boils down to this: How do I replace a word (like "\bphpcolX\b") that is NOT within a quoted part of a string? Any help would be much appreciated.

Thanks in advance!

this side might be interesting for you: php-regex

search for lookahead and lookbehind in the text. 

and this one should help:
\s((?<!')(\w|'')+(?!'))\s

but as you'll see the spaces before and after the column name is not optional. so

WHERE abc='abc' won't match anything

but it matches

WHERE abc = 'abc'
WHERE ab''c = 'ab''c'

$phpcolumns= "WHERE phpcol1 = 'abc' ";
$sqlcolumns = preg_replace("/\s((?<!')(\w|'')+(?!'))\s/", " xyz ", $phpcolumns); 

if  you got to replace more than this (also the AND phpcol) then this won't work because of the keywords that will match too. i don't know how you exlude them already

more examples are usefull. your example started whith

"WHERE..." so a

"   WHERE...." would fail already.

 

 

 

 

docdawson:

this side might be interesting for you: php-regex

Thank you for your reply, Doc! Whoa! Is it that obvious that I'm from Germany? Or are you? Actually, I know this site quite well and have been able to do quite a lot of "simple" regexps for a while. But this problem has me stumped.

Seems like I have not been precise enough describing my problem. I will try to be now with a more hands-on example:

I have a map of column names to replace in the user supplied SQL where clause like:

search	replace
user	username
password	pass
wronglogins	login_fail_counter

My input string might be something like (the "WHERE" has not necessarily to be part of it):

"user='foo user bar' AND password = 'sec''ret' AND wronglogins <= 3"

Note that spaces next to operators like = are not guaranteed, there might be spaces ('foo user bar') as well as SQL escaped quotes ('sec''ret') in quoted text, "AND" might be another operator, etc. Actually any valid SQL where clause might be possible.

The output of my mystery regexp function should be:

"username='foo user bar' AND pass = 'sec''ret' AND login_fail_counter <= 3"

Note that "user" in the quoted 'foo user bar' has not been changed. Every occurence of the search string NOT within quotes has been replaced.

My idea was to walk through the map like this (I might be able to do this in one step using preg_replace with patterns and replace-arrays):

$input = "user='foo user bar' AND password = 'sec''ret' AND wronglogins <= 3";
foreach ($map as $search => $replace) {
    $input = preg_replace("/mystery pattern with $search in it somewhere/", $replace, $input);
}
echo $input;

Output should be the above string:

"username='foo user bar' AND pass = 'sec''ret' and login_fail_counter <= 3" 

Ideally $search should be surrounded by some kind of word boundaries, so that "password" does not match "other_password='foobar'".

Right now I'm using a really really dirty hack that matches quoted strings or $search, replacing (using a callback) with $replace if the match is not a quoted string, otherwise with "\\0". I am evading the fixed length look behind problem this way. I might be totally on the wrong track with this though.

yea, i'm from germany and i posted the link in german without thinking about it. sorry.

now your example is much better to deal with but unfortenatly i can't help. or wait:

don't there is always an "=" behind the $search?

"user="

or

"user =" ???

then your regex wouldn't be that mystical

for others who don't know whats in $map:
$map = array ('user' => 'username', 'password' => 'pass', 'wronglogins' => 'login_fail_counter') 

foreach ($map as $search => $replace) {
    $input = preg_replace("/(".$search."\s*=)/", $replace." =", $input);
}

i hope this one could help 

docdawson:

yea, i'm from germany and i posted the link in german without thinking about it. sorry.

No problem at all. I thought you found out about me being from Germany by some German-English quirks in my original post.

docdawson:

don't there is always an "=" behind the $search?

In most cases there will be an SQL operator behind $search, it might be something like =, <, >, LIKE, ILIKE, ISNULL, perhaps a closing ")", but there are many more.

But as you can see from my example in the first post $search might also be found on the right hand side of an operation. Example:

Map

search	replace
price	withdrawal
money	credit

Input

"price <= money"

Output

"withdrawal <= credit"

Here is what I'm using (reluctantly) right now, maybe it will be a solution for others or a start for finding a better solution:

function parsePattern($text, $search, $replace) {

   $pattern = "/(?(?=[^']*\b$search\b)\b$search\b|.*?'.*?')/";

   return preg_replace_callback($pattern, create_function('$matches',
         'return ($matches[0] == "' . $search . '") ? "' . $replace . '" : $matches[0];') , $text);
}

I'll try to explain: The pattern does two different things, depending on a forward assertion. If it finds no single quote in front of the next $search, it matches $search, otherwise it matches/consumes up to the closing (second) quote. Matches will be either $search or something else. In the callback function I test if the match is $search. If so, I replace it with $replace, otherwise the "replacement" will be the match itself, effectively changing nothing.

If there was a way to construct a look behind assertion, assuring that there is an even number of quotes in front of $search, I wouldn't need the callback.

Now, if there's no better solution than to use a callback, I can live with that. But I'd be happy for your input regarding speed and effectiveness of the pattern.

Another thing that might be helpful: If I would allow double quotes as well my pattern would be

"/(?(?=[^'\"]*\b$search\b)\b$search\b|.*?(['\"]).*?\\1)/"

Note that it does not check for backslash escaped quotes.

sorry i can't help you with this problem. i think i would work with the callback too but maybe here is someone able to do this in one regex?!. good luck

am also having the same type of issue.

Did you get any solution?

if yes kindy post it