An injection attack that succeeds with mysql_query, but fails with mysqli_query

Question

Update: There have been some illuminating responses to this, and the main points are that mysql functions are deprecated, and also that mysqli allows you to use prepared statements. That makes a lot of sense, and is helpful, while just "use mysqli" is neither constructive, nor helpful, in my opinion.

Anytime a PHP and MySQL question is asked on SO, and the OP has code which uses mysql_query, the instinctive community reaction is to comment that they should be using the mysqli family of functions instead.

Can you please provide a scenario where a malicious user could launch a successful injection attack if my code is using mysql_query, but it would be the thwarted if the same attack was attempted, but the code used the mysqli functions instead? Assuming multiple queries in one statement is disabled, as is the typical case.

That's actually misguided advise. `mysqli` isn't inherently safer. It all depends on using prepared statements → which happens to be quite cumbersome with mysqli (- but much easier to utilize with PDO). — mario, Jun 28 '15 at 19:42
Remember, too, that `mysql_query` has been deprecated. It's always sound advice to make sure people aren't writing with deprecated code. — christopher, Jun 28 '15 at 19:53
this almost seems to belong on Meta, the way you started off your question. — Funk Forty Niner, Jun 28 '15 at 19:59
Far as I can tell, the answer to your question's in http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php which is most likely a duplicate question. — Funk Forty Niner, Jun 28 '15 at 20:08
@Fred-ii- If phrased slightly different, this could be a meta question. I can see how the intro seems meta-y, but it is just to give context to the question, which is definitely stackoverflow-y. — chiliNUT, Jun 29 '15 at 02:03
@Fred-ii- for your other comment, the short answer of "how to prevent injection" being "use prepared statements", the answer to my question might be in there **if** the commenters I am referring to actually said "use prepared statements because..." but I am really referring to when a user simply says "use mysqli instead" without any more information, or contributing anything else to the initial question — chiliNUT, Jun 29 '15 at 02:04

score 6 · Accepted Answer · edited Jun 28 '15 at 22:51

I have been lead to believe that:

$selection = mysql_query($dblink, "SELECT * FROM table WHERE name='$idValue' ");

can be easily compromised with values for $idValue which close the ' and then add extra commands, such as

$idValue = "z'; DELETE * FROM table WHERE name IS NOT NULL";

While I realise you state that multiple statements are disabled, something that is not as horrific would be to return unauthorised data rather than editing data in the table directly, such as:

  $idValue = "z' OR name IS NOT NULL OR name = 'x";

Whereas with MySQLi there is the possibility that the approach can be used with prepared statements, which would prevent the variable acting outside of its status as just a variable. Such as:

mysqli->prepare("SELECT * FROM tables WHERE name = ? LIMIT 1");
mysqli->bind_param("s",$idValue);
mysqli->execute();

My understanding of bind_param is that the variable would have all MySQL keywords and key characters escaped thus preventing the security breach and the return of unauthorised rows.

This is a choice that MySQL does not have. Prepared statements do help with improving injection security but they will not prevent injection attacks alone, but more should be used as part of a wider strategy by the programmer.

Just as wearing body armour will not make you invincible, but it will greatly improve your chances of survival. MySQLi is not a magic bullet, and nor is PDO, but they will improve the security levels overall.

MySQL is also deprecated and as stated by Christopher, being no longer maintained means that the number of holes and problems with it will only increase as other technologies continues to develop.

Summary

If you write MySQLi statements in the same manner as you wrote MySQL statements, then you will have no additional protection from injections. However, MySQLi offers the Prepared Statements approach which does significantly increase the defence against SQL injection, but the change of underlying database interface in itself does not give you any inherent benefits or protections unless you choose to code these in yourself using prepared statements.

score 3 · Answer 2 · answered Jun 28 '15 at 19:45

3

I think, there is no such case. More important is MySQLi support for prepared statements, which is most reliable method to prevent SQL injection. If you use old MySQL API, these advanced feature is not supported. Both MySQL and MySQLi has function mysql*_real_escape_string, which is suspectible to attacks without using quotes, like 1 OR 1.

answered Jun 28 '15 at 19:45

david8

444
9
23

1

I think the availability of prepared statements is one of the main principles of wanting to move from MySQL to MySQLi, the way prepared statements works reduces injection opportunities. – Martin Jun 28 '15 at 20:07

An injection attack that succeeds with mysql_query, but fails with mysqli_query

2 Answers2

Summary

Linked