Is mysql_real_escape_string() unsafe against hexadecimal encoded data?

Question

We know that all user input must be escape by mysql_real_escape_string() function before executing on mysql in php script. And know that this function insert a \ before any ' or " character in user input. suppose following code:

$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='".mysql_real_escape_string($_POST['username']."' AND password='".mysql_real_escape_string($_POST['password']."'";

mysql_query($query);

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

this code is safe.

But I find out if user enters her inputs with hexadecimal format then mysql_real_escape_string() can not do any thing and user can execute her sql injection easily. in bellow 27204f522027273d27 is same ' OR ''=' but in hex formated and sql execute without problem :

$_POST['username'] = 'aidan';
$_POST['password'] = "27204f522027273d27";

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='".mysql_real_escape_string($_POST['username']."' AND password='".mysql_real_escape_string($_POST['password']."'";

mysql_query($query);

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

But whether this is true and if answer is yes how we can prevent sql injection in this way?

When dealing with issues like vulnerabilities, it is best to provide a sample of your code (simplified, if necessary, to demonstrate the issue) and input which triggers the problem you are seeing. — Conspicuous Compiler, Dec 02 '11 at 08:09
Please demonstrate how specifically an attacker can bypass `mysql_real_escape_string` by using hexadecimal notation. — deceze, Dec 02 '11 at 08:11
@deceze: An example of how hexadecimal encoding can be used to circumvent escaping [can be found in this answer by Rook](http://stackoverflow.com/a/4262043/106769). — Conspicuous Compiler, Dec 02 '11 at 08:21
@Ahmad: Huge improvement on the question with that edit! Thank you! I'll update my answer. — Conspicuous Compiler, Dec 02 '11 at 08:30
@Ahmad The example you give does *not* demonstrate any problem, running that query works as expected. — deceze, Dec 02 '11 at 09:22
@Conspicuous The example you link to by Rook is significantly different and does not even use `mysql_real_escape_string` (though the problem demonstrated probably affects it as well). You still have not demonstrated a case where a *correctly applied* `mysql_real_escape_string` has problems with hexadecimal characters. — deceze, Dec 02 '11 at 09:26
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5521/discussion-between-conspicuous-compiler-and-deceze) — Conspicuous Compiler, Dec 02 '11 at 09:30
"this code is safe." is not true, simply escaping single and double quotes don't ensure the query cannot be hijacked. Check this out: https://www.youtube.com/watch?v=qBVThFwdYTc — The Onin, Jan 21 '16 at 12:51

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

If you are using mysql_real_escape_string(), odds are you would be better served using a prepared statement.

For your specific case, try this code:

/*
Somewhere earlier in your application, you will have to set $dbh
 by connecting to your database using code like:
$dbh = new PDO('mysql:host=localhost;dbname=test', $DBuser, $DBpass);
*/

$_POST['username'] = 'aidan';
$_POST['password'] = "' OR ''='";

$user = $_POST['username'];
$password = $_POST['password'];

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user=? AND password=?";

$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $user);
$stmt->bindParam(2, $password);
$stmt->execute();

This does require you to use PDO for your database interaction, but that's a good thing overall. Here's a question discussing the differences between PDO and mysqli statements.

Also see this StackOverflow question which is remarkably similar to yours and the accepted answer, from which I poached some of this answer.

edited May 23 '17 at 10:27

Community

1
1

answered Dec 02 '11 at 08:09

Conspicuous Compiler

6,403
1
40
52

1

This does not really answer the question. Yes, prepared statements *are* preferable, but the question was something different. Also, PDO will fall back onto `mysql_real_escape_string`-like behavior if the database does not support prepared statements, so it's still an issue. – deceze Dec 02 '11 at 10:42
@deceze: I'm afraid that's inaccurate. As I detailed in our chat, this resolves the likely vector of attack. Moreover, any version of MySQL released in the last decade supports PDOs. Falling back to `mysql_real_escape_string()` would never realistically happen. I don't think being combative in this fashion is helpful to the question asker. – Conspicuous Compiler Dec 02 '11 at 10:52
And I don't think avoiding the *core question* helps either. Details in the [chat](http://chat.stackoverflow.com/rooms/5521/discussion-between-conspicuous-compiler-and-deceze). :) – deceze Dec 02 '11 at 12:16
@deceze: If you have a question you wish to have answered, I would suggest posting it so others might assist you. I'm afraid I am uninterested in continuing engaging with you here. Cheers! – Conspicuous Compiler Dec 02 '11 at 16:52

Is mysql_real_escape_string() unsafe against hexadecimal encoded data?

1 Answers1