Why everyone should be using mysqli
Posted: Fri Aug 20, 2010 5:04 am
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:
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:
...which would make the logic in your MySQL query:
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).
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;Code: Select all
administrator';Code: Select all
"SELECT * FROM accounts WHERE name='administator';' AND password=''";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).