what does mysql_real_escape_string() really do?

One thing that I hate about documentation at times (when you're a beginner) is how it doesn't really describe things in english. Would anyone mind translating this documentation for me? I'd like to know how exactly this makes things harder for a hacker to insert characters.

http://php.net/manual/en/function.mysql-real-escape-string.php

Also, if this is the case, how would a hacker try to insert characters?

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.

Say you want to save the string I'm a "foobar" in the database.
Your query will look something like INSERT INTO foos (text) VALUES ("$text").
With the $text variable replaced, this will look like this:

INSERT INTO foos (text) VALUES ("I'm a "foobar"")

Now, where exactly does the string end? You may know, an SQL parser doesn't. Not only will this simply break this query, it can also be abused to inject SQL commands you didn't intend.

mysql_real_escape_string makes sure such ambiguities do not occur by escaping characters which have special meaning to an SQL parser:

mysql_real_escape_string($text)  =>  I\'m a \"foobar\"

This becomes:

INSERT INTO foos (text) VALUES ("I\'m a \"foobar\"")

This makes the statement unambiguous and safe. The \ signals that the following character is not to be taken by its special meaning as string terminator. There are a few such characters that mysql_real_escape_string takes care of.

Escaping is a pretty universal thing in programming languages BTW, all along the same lines. If you want to type the above sentence literally in PHP, you need to escape it as well for the same reasons:

$text = 'I\'m a "foobar"';
// or
$text = "I'm a \"foobar\"";

PHP’s mysql_real_escape_string function is only a wrapper for MySQL’s mysql_real_escape_string function. It basically prepares the input string to be safely used in a MySQL string declaration by escaping certain characters so that they can’t be misinterpreted as a string delimiter or an escape sequence delimiter and thereby allow certain injection attacks.

The real in mysql_real_escape_string in opposite to mysql_escape_string is due to the fact that it also takes the current character encoding into account as the risky characters are not encoded equally in the different character encodings. But you need to specify the character encoding change properly in order to get mysql_real_escape_string work properly.

Best explained here.

> http://www.w3schools.com/php/func_mysql_real_escape_string.asp > > http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

It generally it helps to avoid SQL injection, for example consider the following code:

<?php
// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND password='{$_POST['password']}'";
mysql_query($query);

// We didn't check $_POST['password'], it could be anything the user wanted! For example:
$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

// This means the query sent to MySQL would be:
echo $query;
?>

and a hacker can send a query like:

> SELECT * FROM users WHERE user='aidan' AND password='' OR ''=''

This would allow anyone to log in without a valid password.

The mysql_real_escape_string() helps you escape special characters such as single quote etc that users may submit to your script. You need to escape such characters because that comes in handy when you want to avoid SQL Injection.

I would sugggest you to check out:

mysql_real_escape_string() versus Prepared Statements

To be on much safer side, you need to go for Prepared Statements instead as demonstrated through above article.

The mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement.