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.
, its use in PHP is (historically) done via
the PHP MySQL extension (
functions), which is obsolete and deprecated!
(Don't mix up: MySQL itself is not
Brace yourself, this article is a tad long... If you don't want to read, just remember this: ban all
functions and use mysqli
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
function use will trigger a
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
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.
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
. Some random tutorial will tell you to use
, 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');
"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:
- Object-oriented interface only
- Work with multiple DBMSs: MySQL, MSSQL, sqlite, ...
- 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.