PHP: the MySQL extension is deprecated

September 2016


PHP is a wonderful web server programming language, but it is not great for storing structured data alone. That's why it has extensions, which can connect to and use DBMSs (DataBase Management Systems), including the popular MySQL. It's a respectable DB engine, with active development and popular usage, especially in [W/L/M]AMP setups.
However, its use in PHP is (historically) done via the PHP MySQL extension (
mysql_*
functions), which is obsolete and deprecated! (Don't mix up: MySQL itself is not deprecated)

Brace yourself, this article is a tad long... If you don't want to read, just remember this: ban all
mysql_*
functions and use mysqli or PDO.

Deprecated since PHP 5.5... gone in PHP 7.0

PHP 5.5.0 was officially released on June 20, 2013 (see the 2013 PHP news archive, including release notes). In this version, the PHP developers decided to deprecate the MySQL extension.
Multiple reasons for that have been expressed on the decision's RFC page. This FAQ article sums them up.

Also, since PHP 7.0, the extension has completely been removed, as it was unmaintained and became incompatible with the new version of the language's runtime.

So, who says deprecated, says there are good reasons not to use it any more. Just so you don't forget, any
mysql_*
function use will trigger a
E_DEPRECATED
warning (not error) by default in PHP 5.5+.

A bad design

More than 15 years of existence

The MySQL extension was introduced in PHP 2.0, that's even before 1998, year PHP 3 was released. 15 years old programming techniques are not always efficient as of today, even more in IT, which evolves very fast. That might explain its (relative) slowness compared to other DBMS extensions... and also explains the lack of some features that are important for today's usage, described below.

DB connections: the last one is the default one

mysql_*
functions, if not told explicitly, consider the DB connection to use is the most recently opened one. This behaviour is problematic in two cases:
  • when you use multiple DBs at the same time: forget to pass the connection parameter, and your SQL request goes in the wrong DB.
  • to track bugs: no variable explicitly describes the connection, so it's impossible to use a PHP IDE/debugger: you must find the incriminated
    mysql_connect
    yourself and add debugging code if that's necessary.

Error handling? What error handling?

PHP 5 brings a paradigm brought straight from Object-Oriented programming: exceptions. The MySQL extension is too old and never got updated to use them, so the only way to catch errors is to use mysql_error(). Big downside to this technique: you must put your error handling code after every
mysql_*
function call!

Exceptions allows for a code block to be interrupted and go straight to the error handling, simplifying the code not only for the programmer, but also for PHP: exceptions are passive and triggered only when a function reports an error by itself. With the other approach, PHP must check every time if everything went as planned, and most of the time it did... that's useless work.

Designed for MySQL 3.23

More advanced (My)SQL users will be disappointed: some features developed after 3.23 are simply not available, because of the lack of appropriate extension updates.
This results in the lack of SQL procedures, which are very useful in some cases.
According to some people, the extension also has issues with some text encodings.

Encrypted connections

Remote DB connections can be secures using SSL (Secure Socket Layer), but not TLS (Transport Layer Security). As some recent events have proved (as of writing, OpenSSL's HeartBleed) SSL to be a broken protocol, and should be replaced by TLS 1.1+ for a plenty of reasons that won't be explained here. The extension does not support TLS, therefore forces everybody to use an encryption standard that should also be considered deprecated.

No prepared statements

Did you notice the bold and underline title? This is probably the most important point of this whole article:
The MySQL extension has no prepared statements.
Whazzat, and why are they so important? SQL requests are most of the time variable according to the user's choices; the simplest (and sadly most widespread) solution looks like this:
mysql_query('UPDATE members SET name="'.$_GET['name'].'" WHERE name="'.$name.'"');

Everything's fine, you put double quotes around the user's data. Too bad, that's far from sufficient: any user can become an admin by accessing
change-name.php?name=%22%20admin%3D1%20name%3D%22USERNAME
. Some random tutorial will tell you to use
mysql_real_escape_string
, which even if it's efficient, is (honestly) over-long and ugly, and leads to confusion and double use or no use at all on a given variable.
Prepared statements provide an elegant solution to this mess: you prepare your request's structure, then execute it with the parameters you want (PDO example):
$req = $pdo->prepare('UPDATE members SET name=:newname WHERE name=:name');
$req->execute(array(
    "newname" => $_GET['name'],
    "name" => $name
));
Here you go, without ever having to worry about SQL injections. All the dirty work is done for you.

A danger for the future

The extension dangerously nears its end of life, its deletion from PHP is imminent, and your site too if it uses it... Even today, there's a whole "culture" around the extension, which will soon disappear, without knowing it: a huge lot of tutorials and people still do recommend its use, not aware of its future removal. You got it, this culture must be wiped away.

That's why you absolutely must convert your scripts, websites or even CMSs (if it's possible) to another DB access API. The sooner it is done, the better: your project maybe isn't big (yet), and the extension's documentation is still easily available, as are tutorials on converting.

Above-mentioned design issues makes the extension uneasy to convert. But once that's done, if you must translate your code to another DB connection driver, it will likely be easier: modern APIs are all (more or less) alike.

Soon not maintained any more

Lack of maintenance brings a major risk: security breaches that will be found in its code will probably never be fixed. If a critical security issue was discovered, that would threaten millions of websites if they did not make the switch. Try not to be one of them.

So, what to use?

The answer is easy, but take your pick:
  • PDO
    • Object-oriented interface only
    • Work with multiple DBMSs: MySQL, MSSQL, sqlite, ...
  • mysqli
    • Cross-compatible object-oriented and functional model
    • Higly ressembles the MySQL extension

Spread the word

If you see somebody advising the extension's use or learning to use it, do what you can to tell him/her (s)he's doing something wrong:
tell and link him/her this article, which will show arguments why not to use it, instead of writing a long and fastidious reply.

This FAQ article is licensed under CC BY-SA and was initially wrote by gravgun.

Related :

This document entitled « PHP: the MySQL extension is deprecated » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.