2

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'])."';");
unsunghero
  • 971
  • 1
  • 10
  • 22

5 Answers5

3

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

All the attacker has to do is put a single quote inside the name POST data, and it won't be any more.

name=' OR 1=1

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

That looks OK … but it hurts my eyes. Use prepared statements. They are much easier to read then SQL built by concatenating strings together.

Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
3

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.

MrCode
  • 63,975
  • 10
  • 90
  • 112
1

Basic explaination:

If you simply insert $_POST['name'] into the query as per your first example, the resulting SQL string will be invalid if the name variable contains a single quote character.

This will immediately annoy anyone named O'Brien, or similar.

But this can then be exploited by a hacker, who could modify his "name" to include valid SQL code after the single quote. This could be any valid SQL, allowing the hacker to do anything to your DB or query anything from it. Exactly what he can do would depend on other factors in your code, but suffice to say that even in the best case scenario, he could do some pretty devastating things.

To answer your second question: Yes. Escaping using mysqli_real_escape_string() will mitigate this problem.

However, to take things one step further, you might also want to investigate using Prepared Queries, which is a feature of the mysqli extension. This can make your code a lot neater as it avoids having to use that nasty long mysqli_real_escape_string() function name all over the place. It also has other benefits such as improved query caching.

Hope that helps answer the question.

SDC
  • 14,192
  • 2
  • 35
  • 48
0

What if I passed the following value for $_POST['name']?

'; DELETE FROM users WHERE name <> '

I would be closing the first single quote, then introducing the damaging query which just has a single open quote at the end, which would be closed by the single quote in your original query.

You second query is fine. Though you really ought to consider use of prepared statements (which are supported by mysqli)

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • 2
    That's a common misconception (probably due to that comic), you can only append a query if it is executed with `mysqli_multi_query()`. You can still exploit it but you can't add a new query. – MrCode Jan 14 '13 at 17:25
0

If you're using mysqli you should always be using the SQL placeholder method for doing this. The escaping functions are the hard way.

$stmt = $db->prepare("SELECT name FROM users WHERE id = ?");
$stmt->bind_param('i', $_POST['name']);
$stmt->execute();

If you don't understand the risk here, you really need to read up on SQL injection attacks in general, and read what automated hacking tools can do to those that aren't cautious enough.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • I am using mysqli and I'd never go for the prepared statements as they're inconvenient to use, insufficient for protection and [although only in theory] slower. – Your Common Sense Jan 14 '13 at 17:21
  • 1
    Can you clarify this, please? How are prepared statements "insufficient" and more inconvenient than having to wrap every single string inside `mysqli_real_escape_string`? You seem to recommend using `bind_param` in your other comment, so what are you saying? – tadman Jan 14 '13 at 19:00