Why everyone should be using mysqli

[Jackolantern]

There are several good reasons why everyone should be using the "mysqli" MySQL database extension rather than the "mysql" extension, such as increased performance, more features, and the fact that the mysql extension is going to be deprecated. But I found out something I never knew about it today that makes it so attractive it is really a no-brainer to switch: SQL injection attacks are much harder, if not almost impossible, to perform on a site using mysqli. The reason for this is because mysqli::query() (aka mysqli_query()) only accepts 1 query at a time, unlike the mysql extension (mysql_query()), which can take as many as you give it.

To those not familiar with SQL injection attacks, say you are using mysql_query(), and you go to add a form field to the database. A nefarious user has entered this as their name:

Code: Select all

steven; DROP DATABASE;

If your script handling MySQL user has DROP privileges, your database has just been deleted. Or, what could arguably be even worse, they entered this as their name on a login script:

Code: Select all

administrator';

...which would make the logic in your MySQL query:

Code: Select all

"SELECT * FROM accounts WHERE name='administator';' AND password=''";

Now the user may have gained access to an account without a password! This works because the " '; " completed the query without the "AND password=''" criteria. The second, incorrect query does not work syntactically, but the first query is correct and may allow the user to login without a password (depending on the rest of your login logic of course).

With the mysqli extension, both of these queries would fail with an error because they are trying to run more than 1 query at a time. Real reasons to run more than 1 query at a time in one run are few and far between since you can just as easily run the subsequent queries one after another.

Of course using mysqli::query() does not excuse us from escaping special characters with mysqli:real_escape_string() (mysqli_real_escape_string()), since creative crackers could still find many ways to exploit our site even if we are using mysqli. However, it could be easy to forget to filter input in at least one place if we have tens or hundreds of scripts that need to enter database data, and you are much safer from SQL injection attacks if you forget to filter input using mysqli rather than mysql, since almost all SQL injection attacks depend on prematurely terminating a query string and starting another (such as the first example above), or splitting a query in half to only execute a desired query (the second example above).

[hallsofvallhalla]

thanks for the info!

[Noctrine]

Would have expected you to argue for PDO.

[Zyviel]

Jackolantern,

Thanks for the information. I have been using mysqli queries because the examples in the php book I bought use that function. I wasn't sure why my book was using mysqli queries when many examples on the web were using mysql queries. Now I can see why and its nice to know that it protects against sql injection attacks also.

[Jackolantern]

Would have expected you to argue for PDO.

Honestly I have not looked too much in to Data Objects, since the only two databases I use are MySQL for PHP and SQL Server for .NET. Both of which are natively supported so well there seems to me (without knowing much about PDO) little reason to go to another library.