MySQL注入查询

Question

I'm familiar with prepared statements and I know that they are best practice when it comes to protecting against MySQL injection. But I'm wondering how this PHP/MySQL statement could be at risk of an injection attack:

$result = mysqli_query($db,"SELECT name FROM users WHERE id = '".$_POST['name']."';");

It seems to me like the input from the user would be contained inside the single quotes. Can you execute more than one query in one mysqli_query statement?

Also, is making the above safe just as easy as this...

$result = mysqli_query($db,"SELECT name FROM users WHERE id = '".mysqli_real_escape_string($_POST['name'])."';");

Answer 1

It seems to me like the input from the user would be contained inside the single quotes

It would unless you include single quotes in the posted name, which would allow you to break out of the quotes. Example, post the name as:

' or 1 or '

The WHERE clause becomes:

WHERE id = '' or 1 or '';

This would match and retrieve all rows in the table because of the or 1 part. As you can see, it breaks out of the quotes to inject some SQL, then it goes back into the quotes to make the query valid.

Can you execute more than one query in one mysqli_query statement?

No, but if it was executed with mysqli_multi_query then yes you could add multiple queries on to the end.

is making the above safe just as easy as mysqli_real_escape_string?

Generally yes but a Prepared Statement would be better. Using escaping, the WHERE clause would become (using my example above):

WHERE id = '\' or 1 or \'';

This is no longer vulnerable because the quotes can't be broken out of, and would only match rows if the name literally matches ' or 1 or ' which is obviously unlikely.