mysqli_real_escape_string - example for 100% safety

Question

I know there have been a lot of questions asked already about this topic. And I also know that the way to go are prepared statements. However I have still not completely understood if or how the following could become a security problem:

$mysqli = new mysqli("localhost", "root", "", "myDatabase");
$mysqli->set_charset("utf8");
$pw = mysqli_real_escape_string($mysqli,$_POST['pw']);
$username = mysqli_real_escape_string($mysqli,$_POST['username']);

$str = "SELECT * FROM users WHERE id='".$id."' AND username='".$username."'";
$result = $this -> mysqli -> query($qstr);

if($result->num_rows > 0){
    //user logged in
}

I tried many different inputs from a injection cheat sheet but could not find anything that passed the query. E.g. if I entered anything with an ";" then $result became false because one query cannot contain two separate statements as far as I know. Any input having an ' or " was sanitized by mysqli_real_escape_string.

Could you please explain to me, how the code above could be exploited? If you have a link, which explains it I am more than happy to read it, too!

Cheers

EDIT: This was answered already in this answer:

SQL injection that gets around mysql_real_escape_string()

This question however was about the older version of mysql but not mysqli. Secondly the answer with the most up votes statet the following example that could get around it:

mysql_query('SET NAMES gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

However I do not fully understand this. The first line

mysql_query('SET NAMES gbk');

can not be set from the outside, correct? This is just an example if someone set 'gbk' in his program. So if I used

$mysqli->set_charset("utf8");

and also used

id='".$id."' (single quotes around $id) 

then I would be 100% safe, correct?

Answer 1

Your isolated and simplified example is technically safe.

However, there are still two problems with it:

• the assumption: the very statement of question is made out of the assumption that mysqli_real_escape_string() is related to any security issues. Which is but a grave delusion. This is a string formatting function, that protects you from SQL injections only as a side effect. But such a protection is neither the goal nor the purpose of this function. And therefore it should never be used for the purpose. Such a fallacy will inevitably lead you to this function's misuse (such as using it to "protect" numbers) and eventually allow an SQL injection.
• the inherent separability of the code you posed. The protection consists of three parts:
  • setting the correct encoding
  • escaping special characters
  • wrapping the escaped value in quotes

It is not only the fact that some of these obligatory measures could be forgotten but again, the statement of question stresses only on a single part - escaping. It is only escaping which is always accented on, while two other measures get hardly mentioned at all. Just look at your question - you meant the code but asked about a function. So any literal answer to the question you asked will make a fatally wrong impression that mysqli_real_escape_string() is all right.

In short, the statement of question helps to promote the most dangerous of PHP related delusions: that this function protects from SQL injection.

Unlike this complex three-part equation, prepared statements constitute an inseparable measure. You cannot forget one part. You cannot misuse it. Try mysqli_real_escape_string() to protect an identifier, and it will silently go unnoticed, until the actual injection happen. Try a prepared statement for an identifier - and get an error.