PHP: mysql v mysqli v pdo

Question

I've been reading some questions regarding using mysqli versus pdo to use mysql in php.

I've seen questions such as mysqli or PDO - what are the pros and cons? or Moving from mysql to mysqli or pdo?, which both deal with mysqli v pdo exclusively. I'm not as much interested as to which of these two methods are better.

I was wondering why mysql_ functions should be avoided. Of course, they're in the process of being deprecated per PHP's documentation http://php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated, the thread PHP PDO and MySQLi suggests PDO and MySQLi are more poweful, and the thread What is the difference between MySQL, MySQLi and PDO? implies that these newer methods are more secure.

Overall, I'm wondering what are the big weaknesses in the mysql_ methods, and what reasons there are for avoiding it (I guess more specifically than just because it's deprecated). I'm planning to update my affected scripts, and became curious as to why this old method was deprecated.

Thanks!

*"Of course, they're in the process of being deprecated per PHP's documentation"* Do you really need more than that? PHP is killing them. They're dying. It's not whether or not you *should* stop using them, you *must* stop using them because they simply won't exist. There are a million other resources which will tell you *why* using PDO over the mysqli_* functions is important, but this is off-topic for Stack Overflow. — user229044, Aug 23 '12 at 17:41
I asked why PHP is killing them. Not whether if it's a good idea to use them. — Vlad, Aug 23 '12 at 17:45
`mysql_query` is such a bad function I'm surprised there hasn't been a class-action lawsuit against Zend for supporting it. — tadman, Aug 23 '12 at 17:49

score 23 · Accepted Answer · answered Aug 23 '12 at 17:47

The design of the mysql_query function is such that you've got to be careful to escape each and every bit of data you're injecting into it, and if you miss even one your entire application can be destroyed by an automatic SQL vulnerability exploit tool.

Both mysqli and PDO support placeholders which are required to ensure that your queries are safe from SQL injection bugs. Calling mysql_real_escape_string on everything is not only tedious, but error-prone, and that's where the problems arise.

The mysql functions are a product of the very early days of PHP and are significantly more limited than the new object-oriented features offered by both mysqli as an option, or PDO by design.

There's a number of very good reasons to use one of these two new interfaces, but the most important is that the mysql_query function is simply too hazardous to use in production code. With it you will always be one mistake away from some very serious problems.

There's a reason rips of databases full of passwords and credit card numbers keep showing up. Having an obvious SQL injection point makes it almost too easy to completely take over a site.

Nice answer. Please include more examples and explanations, so that one does not need to search further on each point. — anusree, Dec 02 '16 at 06:25
This answer is pretty moot since `mysql_query` was, at last, removed from PHP in version 7. Use PDO, or even better, an ORM like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/5.3/eloquent). — tadman, Dec 02 '16 at 06:28

Henrik Karlsson · Answer 2 · 2014-04-16T10:38:15.470

From Choosing MySSQL API @ PHP.net:

// mysqli
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("example.com", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);

Creating an object, like mysqli and PDO does, is the recommended way of writing any modern software. The mysql liblary was released for PHP version 2.0 and haven't had a major rewrite since then. PHP 2.0 was released 1997, that should be enough to explain why to not use it.

this example is a mere cheating as it doesn't use prepared statements — Your Common Sense, Apr 16 '14 at 10:42
To be honest I didn't criticize the code as it was taken directly from the PHP manual, but I don't see this as cheating; It just shows the basic parts and does not go into neither prepared statements or real escape string. — Henrik Karlsson, Apr 16 '14 at 22:30
real escape string IS NOT an equivalent of prepared statement — Your Common Sense, Apr 17 '14 at 00:24

score 2 · Answer 3 · answered Aug 23 '12 at 17:42

Well for one, mysql functions are extremely prone to SQL injection if you do not properly sanitize the input manually. mysqli and pdo have parametrized sql statement options avoiding this risk. They also generally have a better style of coding in my opinion.

PHP: mysql v mysqli v pdo

3 Answers3

Linked