5

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
S. F.
  • 206
  • 4
  • 14
  • 4
    Yes, that is generally safe. Mysql and mysqli are perfectly safe when used right (specific bugs in very specific encodings notwithstanding). The advantage of prepared statements is that it's more difficult to do things the wrong way. – Pekka Dec 03 '15 at 08:54
  • Ok got it! Thanks a lot! – S. F. Dec 03 '15 at 09:45
  • 1
    Yeah, there are cases where escape-string doesn't work but they are obscure edge cases. But given that it's _less work_ to write code with query parameters, and results in code that's _easier to read_ and _easier to maintain_, the solution of query parameters should be preferred. – Bill Karwin Jan 14 '20 at 23:48

1 Answers1

5

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345