1

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
5
  • 2
    instead of trying to emulate PDO, why not use sprintf, or use easier to replace tokens such as :myvariable:. What is the advantage to your method over PDO? Programming is about solving problems, not creating them. Commented Jun 4, 2015 at 22:37
  • 3
    well this looks barking mad - carry on Commented Jun 4, 2015 at 22:37
  • 2
    You spent so much time wondering if you could that you didn't stop to think if you should. Commented Jun 4, 2015 at 22:38
  • 3
    I'm voting to close this question because Oh My, Don't Do This! Commented Jun 4, 2015 at 22:49
  • @Augwa I did first, but it doesn't taking care about placeholders between quotes, and this is my real issue. Commented Jun 4, 2015 at 22:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.