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.

Answer 1

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.