Are MySQL_Real_Escape_String() and MySQL_Escape_String() Sufficient For App Security

Are mysql_real_escape_string() and mysql_escape_string() sufficient for app security?

@Charles is extremely correct!

You put yourself at risk for multiple types of known SQL attacks, including, as you mentioned

• SQL injection: Yes! Mysql_Escape_String probably STILL keeps you susceptible to SQL injections, depending on where you use PHP variables in your queries.

Consider this:

$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);  

Can that be securely and accurately escaped that way? NO! Why? because a hacker could very well still do this:

Repeat after me:

mysql_real_escape_string() is only meant to escape variable data, NOT table names, column names, and especially not LIMIT fields.

• LIKE exploits: LIKE "$data%" where $data could be "%" which would return ALL records ... which can very well be a security exploit... just imagine a Lookup by last four digits of a credit card... OOPs! Now the hackers can potentially receive every credit card number in your system! (BTW: Storing full credit cards is hardly ever recommended!)

• Charset Exploits: No matter what the haters say, Internet Explorer is still, in 2011, vulnerable to Character Set Exploits, and that's if you have designed your HTML page correctly, with the equivalent of <meta name="charset" value="UTF-8"/>! These attacks are VERY nasty as they give the hacker as much control as straight SQL injections: e.g. full.

Here's some example code to demonstrate all of this:

// Contains class DBConfig; database information.
require_once('../.dbcreds');                       

$dblink = mysql_connect(DBConfig::$host, DBConfig::$user, DBConfig::$pass);
mysql_select_db(DBConfig::$db);
//print_r($argv);

$sql = sprintf("SELECT url FROM GrabbedURLs WHERE %s LIKE '%s%%' LIMIT %s",
               mysql_real_escape_string($argv[1]),
               mysql_real_escape_string($argv[2]),
               mysql_real_escape_string($argv[3]));
echo "SQL: $sql\n";
$qq = mysql_query($sql);
while (($data = mysql_fetch_array($qq)))
{
        print_r($data);
}

Here's the results of this code when various inputs are passed:

$ php sql_exploits.php url http://www.reddit.com id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://www.reddit.com%'
               ORDER BY id;
Returns: Just URLs beginning w/ "http://www.reddit.com"

$ php sql_exploits.php url % id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE '%%' 
               ORDER BY id;
Results: Returns every result Not what you programmed, ergo an exploit --

$ php sql_exploits.php 1=1
'http://www.reddit.com' id Results:
Returns every column and every result.

Then there are the REALLLY nasty LIMIT exploits:

$ php sql_exploits.php url 
> 'http://www.reddit.com'
> "UNION SELECT name FROM CachedDomains"
Generated SQL: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://reddit.com%' 
               LIMIT 1 
               UNION
               SELECT name FROM CachedDomains;
Returns:  An entirely unexpected, potentially (probably) unauthorized query
          from another, completely different table. 

Whether you understand the SQL in the attacks or not is irrevelant. What this has demonstrated is that mysql_real_escape_string() is easily circumvented by even the most immature of hackers. That is because it is a REACTIVE defense mechism. It only fixes very limited and KNOWN exploits in the Database.

All escaping will NEVER be sufficient to secure databases. In fact, you can explicitly REACT to every KNOWN exploit and in the future, your code will most likely become vulnerable to attacks discovered in the future.

The proper, and only (really) , defense is a PROACTIVE one: Use Prepared Statements. Prepared statements are designed with special care so that ONLY valid and PROGRAMMED SQL is executed. This means that, when done correctly, the odds of unexpected SQL being able to be executed are drammatically reduced.

Theoretically, prepared statements that are implemented perfectly would be impervious to ALL attacks, known and unknown, as they are a SERVER SIDE technique, handled by the DATABASE SERVERS THEMSELVES and the libraries that interface with the programming language. Therefore, you're ALWAYS guaranteed to be protected against EVERY KNOWN HACK, at the bare minimum.

And it's less code:

$pdo = new PDO($dsn);

$column = 'url';
$value = 'http://www.stackoverflow.com/';
$limit = 1;

$validColumns = array('url', 'last_fetched');

// Make sure to validate whether $column is a valid search parameter.
// Default to 'id' if it's an invalid column.
if (!in_array($column, $validColumns) { $column = 'id'; }


$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));
while (($data = $statement->fetch())) { }

Now that wasn't so hard was it? And it's forty-seven percent less code (195 chars (PDO) vs 375 chars (mysql_). That's what I call, "full of win".

EDIT: To address all the controversy this answer stirred up, allow me to reiterate what I have already said:

Using prepared statements allows one to harness the protective measures of
the SQL server itself, and therefore
you are protected from things that the
SQL server people know about. Because
of this extra level of protection, you
are far safer than by just using
escaping, no matter how thorough.

What is the difference between real_escape_string() and mysql_real_escape_string()?

I'm guessing you're using mysql or mysqli, you should switch over to PDO and use prepare statements instead of escaping it.

As requested. You should have a look at this site.

<?php
$username = $_POST['username'];
$password = $_POST['password'];
$STH = $DBH->prepare("INSERT INTO users (username, password) values (:username, :password)");
$STH->bindParam(':username', $username);
$STH->bindParam(':password', $password);
$STH->execute();
?>

mysql_escape_string vulnerabilities

Updated answer

The question was edited (after my answer was posted) to specifically target mysqli_escape_string, which is an alias of mysql_real_escape_string and therefore takes the connection encoding into account. This makes the original answer non-applicable anymore, but I 've left it for completeness.

The new answer, in short: mysqli_escape_string is as good security-wise as parameterized queries, provided you don't shoot yourself in the foot.

Specifically, what you must not do is highlighted in the giant warning on the PHP doc page:

The character set must be set either at the server level, or with the
API function mysqli_set_charset() for it to affect
mysqli_real_escape_string().

If you don't heed this warning (i.e. if you change the character set with a direct SET NAMES query) and you change the character set from a single-byte encoding to a "convenient" (from the attacker's perspective) multibyte encoding, you will have in effect emulated what the dumb mysql_escape_string does: attempt to escape characters without knowing which encoding the input is in.

This situation leaves you potentially vulnerable to SQL injection as described by the original answer below.

Important note: I remember reading somewhere that recent MySql versions have plugged this hole on their end (in the client libraries?), which means that you might be perfectly safe even if using SET NAMES to switch to a vulnerable multibyte encoding. But please don't take my word for it.

Original answer

In contrast to mysql_real_escape_string, the bare mysql_escape_string does not take into account the connection encoding. This means that it assumes the input is in a single-byte encoding, when in fact it can legitimately be in a multibyte encoding.

Some multibyte encodings have byte sequences that correspond to a single character where one of the bytes is the ASCII value of the single quote (0x27); if fed such a string, mysql_escape_string will happily "escape the quote", which means substituting 0x27 with 0x5c 0x27. Depending on the encoding rules, this could result in mutating the multibyte character into another that includes the 0x5c and leaving the "remaining" 0x27 as a stand-alone single quote in the input. Voilà, you have injected an unescaped quote into the SQL.

For more details see this blog post.

Why doesn't mysql_real_escape_string() work correctly?

SET CHARACTER SET / SET NAMES is not enough to protect against the GBK exploit, depending on what version of MySQL you are using.

If you can, use mysql_set_charset / mysqli_set_charset or real prepared statements.

You'll also want to use MySQl 5.0.77 or better. See this earlier post of mine for more information. If you are using older versions of MySQL, you can be vulnerable without the _set_charset functions.

Using prepared statements bypasses this problem entirely.

SQL injection that gets around mysql_real_escape_string()

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1");    
$sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect you against this.
The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:

$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";

---

Related Topics