Is MySQL_Real_Escape_String() Broken

Is mysql_real_escape_string() broken?

From the MySQL’s C API function mysql_real_escape_string description:

If you need to change the character set of the connection, you should use the mysql_set_character_set() function rather than executing a SET NAMES (or SET CHARACTER SET) statement. mysql_set_character_set() works like SET NAMES but also affects the character set used by mysql_real_escape_string(), which SET NAMES does not.

So don’t use SET NAMES/SET CHARACTER SET but PHP’s mysql_set_charset to change the encoding as that is the counterpart to MySQL’s mysql_set_character_set (see source code of /ext/mysql/php_mysql.c).

mysql_real_escape_string not escaping all types of ”

As I thought, it turns out to be an encounding problem.

After using utf8_encode (http://php.net/manual/en/function.utf8-encode.php) on my string everything worked fine.

Thank you all for you help (especially Polynomial).

mysql_real_escape_string not good enough?

Reasoning from the method name queryAsArray, it seems that you’re using this DbBase class from the comments of the MySQL functions manual page. If so, it’s the query method that removes the escape character from the escaped quotation marks:

function query($sql, &$records = null){
    $sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
    // …
}

Then it’s not a miracle that your example works (I simplified it):

$input = "', BAD SQL INJECTION --";

$sql = "SELECT '".mysql_real_escape_string($input)."'";
var_dump($sql);  // string(33) "SELECT '\', BAD SQL INJECTION --'"
//                      everything’s OK ↑

$sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
var_dump($sql);  // string(32) "SELECT '', BAD SQL INJECTION --'"
//                                Oops! ↑

what does mysql_real_escape_string() really do?

The function adds an escape character, the backslash, \, before certain potentially dangerous characters in a string passed in to the function. The characters escaped are

\x00, \n, \r, \, ', " and \x1a.

This can help prevent SQL injection attacks which are often performed by using the ' character to append malicious code to an SQL query.

Which SQL inject methods aren't destroyed by mysql_real_escape_string();?

If given a valid database connection, mysql_real_escape_string() is supposed to be safe for string data under all circumstances (with the rare exception described in this answer).

However, anything outside a string, it won't escape:

$id = mysql_real_escape_string($_GET["id"]);

mysql_query("SELECT * FROM table WHERE id = $id");

is still vulnerable, because you don't have to "break out" of a string to add an evil additional command.

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 does mysql_real_escape_string() do that addslashes() doesn't?

Addslashes is generally not good enough when dealing with multibyte encoded strings.

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";

broken links and styles on form post to mysql, is it because of mysql_real_escape_string?

stripslashes function of PHP

echo stripslashes($string);

takes out the slashes and displays it like it was originally

---

Related Topics