An injection attack that succeeds in mysql_query but does not work with mysqli_query

Question

Update . There were some illuminating answers to this, and most importantly, the mysql functions are deprecated, and also that mysqli allows you to use prepared statements. This makes a lot of sense and is useful, although, in my opinion, just “using mysqli” is neither constructive nor useful.

At any time, PHP and MySQL ask about SO, and the OP has code that uses mysql_query , the instinctive reaction is to comment that they should use the mysqli family of functions instead.

Can you imagine a scenario in which an attacker could launch a successful injection attack if my code uses mysql_query , but would be thwarted if the same attack were made, but the code used mysqli functions instead? Assuming that several queries in one of the operators are disabled, as is usually the case.

Answer 1

I was lucky that:

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

can easily be compromised with values ​​for $idValue that close ' , and then add additional commands such as

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

Although I understand that you are claiming that several statements are disabled, what is not so terrible would be to return unauthorized data, and not edit the data in the table directly, for example:

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

Whereas in MySQLi there is a possibility that this approach can be used with prepared statements , which will prevent a variable acting outside its status as a variable. For instance:

 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 the MySQL keywords and keywords, which would prevent a security breach and the return of unauthorized strings.

This is a choice that MySQL does not have . The prepared statements do help in improving the safety of injections, but they will not prevent injection attacks, but should be used more as part of a broader programmer strategy.

Just like wearing body armor will not make you invincible, but it will greatly improve your chances of survival. MySQLi is not a magic bullet, and PDO, but they will improve the level of security in general.

MySQL is also outdated and, according to Christopher, is no longer supported, which means that the number of holes and problems with it will only increase as other technologies develop.

Summary

If you write MySQLi statements in the same way you wrote MySQL statements, then you will not have extra protection against injection. However, MySQLi offers a Prepared Statements approach that significantly increases protection against SQL injection, but changing the underlying database interface alone does not give you any inherent advantages or protections if you do not want to code them yourself using prepared instructions.

Answer 2

I think there is no such case. More important is MySQLi support for prepared statements, which is the most reliable way to prevent SQL injection. If you are using the old MySQL API, this advanced feature is not supported. Both MySQL and MySQLi have the mysql * _real_escape_string function, which is suspected of being attacked without quotes, for example 1 OR 1 .