I'm trying to bind manually vars into SQL queries using PHP regex:
$query = 'SELECT * FROM table WHERE a = '.'\''.mysql_real_escape_string(":bar \' :bar ' :bar ' :bar \' :bar").'\''.' OR foo = :foo AND fooo = :foo_o AND bar = :bar';
$binding = array(
':foo_o' => 'good',
':foo' => 'nice',
'bar' => 'owned'
);
echo preg_replace_callback("/:\\w+(?![^']*'(?:(?:[^']*'){2})*[^']*$)/u", function($match) use($binding) {
if(isset($binding[ltrim($match[0], ':')])) {
return $binding[ltrim($match[0], ':')];
} else if(isset($binding[$match[0]])) {
return $binding[$match[0]];
} else {
return $match[0];
}
}, $query);
Result:
SELECT * FROM table WHERE a = ':bar \\\' owned \' :bar \' owned \\\' :bar' OR foo = nice AND fooo = good AND bar = owned
As you can see, two placeholders inside quotes are escaped but shouldn't be.
I also tried using the regex from here (https://stackoverflow.com/a/5610067/4965547 ) that I already used to split multi queries and that worked very well for that, but not for binding:
echo preg_replace_callback('%:\\w+((?=\'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*\'|"[^"\\\\]*(?:\\\\.[^"\\\\]*)*"|/*[^*]*\*+([^*/][^*]*\*+)*/|\#.*|--.*|[^"\'#])+(?:\\w|$))%xu', function($match) use($binding) {
if(isset($binding[ltrim($match[0], ':')])) {
return $binding[ltrim($match[0], ':')];
} else if(isset($binding[$match[0]])) {
return $binding[$match[0]];
} else {
return $match[0];
}
}, $query);
In this case every placeholder is replaced, even between quotes:
SELECT * FROM table WHERE a = 'owned \\\' owned \' owned \' owned \\\' owned' OR foo = nice AND fooo = good AND bar = owned
Any regex pro that can help me fixing one of the two expressions ? Thanks in advance.
PS: Yes I know I should use PDO, but I want to create a non-PDO adapter to use within mysql PHP extension, and yeah, I also know that this extension will be removed. Also I want not to use PHP script as more as possible, I really want to do it using regex.
Edit 06/05/2015: I finally understand how the complex regex works, and find a solution. So I post it here in case someone may need it or be interested.
Binding with named placeholders:
$query = 'SELECT * FROM table WHERE a = \''.mysql_real_escape_string(":bar \' :bar ' :bar ' :bar \' :bar").'\' OR b = \''.mysql_real_escape_string(':bar \" :bar " :bar " :bar \" :bar').'\' OR foo = :foo AND fooo = :foo_o AND bar = :bar';
$binding = array(
':foo_o' => 'good',
':foo' => 'nice',
':bar' => 'owned'
);
echo preg_replace_callback('%(:\w+)\W*|(?:\'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*\'|"[^"\\\\]*(?:\\\\.[^"\\\\]*)*"|/*[^*]*\*+([^*/][^*]*\*+)*/|\#.*|--.*|[^"\':#])+%u', function($match) use($binding) {
if(isset($match[1]) && isset($binding[$match[1]])) {
return str_replace($match[1], $binding[$match[1]], $match[0]);
} else {
return $match[0];
}
}, $query);
Same with question mark placeholders:
$query = 'SELECT * FROM table WHERE a = '.'\''.mysql_real_escape_string("? \' ? ' ? ' ? \' ?").'\''.' OR '.'\''.mysql_real_escape_string('? \" ? " ? " ? \" ?').'\''.' OR foo = ? AND fooo = ? AND bar = ?';
$binding = array(
1 => '*1',
2 => '2*?',
3 => 'lol',
4 => 'none'
);
$count = 0;
echo preg_replace_callback('%\?|(?:\'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*\'|"[^"\\\\]*(?:\\\\.[^"\\\\]*)*"|/*[^*]*\*+([^*/][^*]*\*+)*/|\#.*|--.*|[^"\'?#])+%u', function($match) use($binding, &$count) {
if($match[0] == '?' && isset($binding[++$count])) {
return $binding[$count];
} else {
return $match[0];
}
}, $query, -1, $count);
Good results:
SELECT * FROM table WHERE a = ':bar \\\' :bar \' :bar \' :bar \\\' :bar' OR b = ':bar \\\" :bar \" :bar \" :bar \\\" :bar' OR foo = :foo AND fooo = :foo_o AND bar = :bar
SELECT * FROM table WHERE a = ':bar \\\' :bar \' :bar \' :bar \\\' :bar' OR b = ':bar \\\" :bar \" :bar \" :bar \\\" :bar' OR foo = nice AND fooo = good AND bar = owned
SELECT * FROM table WHERE a = '? \\\' ? \' ? \' ? \\\' ?' OR '? \\\" ? \" ? \" ? \\\" ?' OR foo = ? AND fooo = ? AND bar = ?
SELECT * FROM table WHERE a = '? \\\' ? \' ? \' ? \\\' ?' OR '? \\\" ? \" ? \" ? \\\" ?' OR foo = *1 AND fooo = 2*? AND bar = lol
