PHP: mysql v mysqli v pdo [closed]

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!

Answer 1

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.

Answer 2

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.