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

Question

Will mysql_real_rescape_string() be enough to protect me from hackers and SQL attacks? Asking because I heard that these don't help against all attack vectors? Looking for the advice of experts.

EDIT: Also, what about LIKE SQL attacks?

Answer 1

@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.

Answer 2

No!

---

Important update: After testing possible exploit code provided by Col. Shrapnel and reviewing MySQL versions 5.0.22, 5.0.45, 5.0.77, and 5.1.48, it seems that the GBK character set and possibly others combined with a MySQL version lower than 5.0.77 may leave your code vulnerable if you only use SET NAMES instead of using the specific mysql_set_charset/mysqli_set_charset functions. Because those were only added in PHP 5.2.x, the combination of old PHP and old MySQL can yield a potential SQL injection vulnerability, even if you thought you were safe and did everything correctly, by-the-book.

---

Without setting the character set in combination with mysql_real_escape_string, you may find yourself vulnerable to a specific character set exploit possible with older MySQL versions. More info on previous research.

If possible, use mysql_set_charset. SET NAMES ... is not enough to protect against this specific exploit if you are using an effected version of MySQL (prior to 5.0.22 5.0.77).

Answer 3

Yes. If you will not forget to:

1. Escape string data with mysql_real_rescape_string()
2. Cast numbers to numbers explicitly (ie: $id = (int)$_GET['id'];)

then you're protected.

Answer 4

I personally prefer prepared statements:

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

It would be pretty easy to overlook one or another specific variable that has been missed when using one of the *escape_string() functions, but if all your queries are prepared statements, then they are all fine, and use of interpolated variables will stand out like a sore thumb.

But this is far from sufficient to ensure you're not vulnerable to remote exploits: if you're passing around an &admin=1 with GET or POST requests to signify that someone is an admin, every one of your users could easily upgrade their privileges with two or three seconds of effort. Note that this problem isn't always this obvious :) but this is an easy way to explain the consequences of trusting user-supplied input too much.

Answer 5

You should look into using prepared statements/parameterized queries instead. The idea is that you give the database a query with placeholders. You then give the database your data, and tell it which placeholder to replace with said data, and the database makes sure that it's valid and doesn't allow it to overrun the placeholder (i.e. it can't end a current query and then add its own - a common attack).