How to Successfully Rewrite Old MySQL-PHP Code With Deprecated MySQL_* Functions

How to successfully rewrite old mysql-php code with deprecated mysql_* functions?

Actually, a simple, sweet and short: Yes, not necessary any longer.

Let's review the code not that we have lost something:

__construct - The constructor merely contained all the configuration. PDO has a much easier concept here, a connection string containing the most information:
```
 mysql:host=127.0.0.1;dbname=people;charset=UTF-8
```
Also PDO provides the constructor for use ready-made, so double not necessary.
connect - The connection function is not necessary any longer as well. This is done by instantiating PDO already. You can look for exceptions, the PHP manual has an example on it's constructor page.
selectDb - This complicated function is not needed any longer as well. Wow, the third function we can just drop because of the PDO connection string. Much power with so less characters. Cheers!
__destruct - The destructor. Let's be fair: MySQL did not need this as well. However with PDO we get it for free - without writing a single line of code.

Looks good! You managed to migrate from that obscure database class to PDO by removing outdated code! Congratulations:

$db = new PDO('mysql:host=127.0.0.1;dbname=people;charset=UTF-8', 'root', 'pass');

If you now think, what about if I want to have database class on my own? Well you can do that, because you can extend from PDO (yes that works!):

class DB extends PDO
{
   ... my super-new-shiny-code
}

Why you might want to do that? No idea, but maybe it's more fluent for your code. If you're looking for a better code-example, I have one at PHP/MySQL Table with Hyperlinks.

How to successfully rewrite old mysql-php code with deprecated mysql_* functions? Part II

OK so solution login.php 1) seems to be ok simple and no rush.
Also the login page seems to be working fine and therefore it should be according to every rules and ok :)

Why shouldn't I use mysql_* functions in PHP?

The MySQL extension:

Is not under active development
Is officially deprecated as of PHP 5.5 (released June 2013).
Has been removed entirely as of PHP 7.0 (released December 2015)
- This means that as of 31 Dec 2018 it does not exist in any supported version of PHP. If you are using a version of PHP which supports it, you are using a version which doesn't get security problems fixed.
Lacks an OO interface
Doesn't support:
- Non-blocking, asynchronous queries
- Prepared statements or parameterized queries
- Stored procedures
- Multiple Statements
- Transactions
- The "new" password authentication method (on by default in MySQL 5.6; required in 5.7)
- Any of the new functionality in MySQL 5.1 or later

Since it is deprecated, using it makes your code less future proof.

Lack of support for prepared statements is particularly important as they provide a clearer, less error-prone method of escaping and quoting external data than manually escaping it with a separate function call.

See the comparison of SQL extensions.

Can I blindly replace all mysql_ functions with mysqli_?

The short answer is no, the functions are not equivalent.

The good news is there is a converter tool that will help you if you've got a lot of calls/projects to change. This will allow your scripts to work right away.

https://github.com/philip/MySQLConverterTool

It's a forked version of the Oracle original version, and it's kosher.

That said, it's not too difficult to update your code, and you might want to migrate to an object orientated methodology anyway ...

1) The Connection

For all intents and purposes, you need a new connection function that saves the connection as a PHP variable, for example;

$mysqli = new mysqli($host, $username, $password, $database);

Notice I've saved the connection to $mysqli. You can save to $db or whatever you like, but you should use this throughout your code to reference the connection.

Remember to enable error reporting for mysqli before opening the connection;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

2) The Query

Note: You should protect against SQL injection with prepared statements, which are available in MySQLi. Take a look at How can I prevent SQL injection in PHP?, but I'm just going to cover the basics here.

You now have to include the connection as an argument in your query, and other mysqli_ functions. In procedural code it's the first argument, in OO you write it like a class method.

Procedural:

$result = mysqli_query($mysqli, $sql);

OO:

$result = $mysqli->query($sql);

3) Fetch Result

The fetching of the result is similar to the old mysql_ function in procedural;

while ($row = mysqli_fetch_assoc($result))

but as $result is now an object in mysqli, you can use the object function call;

while ($row = $result->fetch_assoc())

4) Close Connection

So as before, you need to include the connection in the close function; as an argument in procedural;

mysqli_close($mysqli);

and as the object that you run the function on in OO;

$mysqli->close();

I would be here forever if I went through them all, but you get the idea. Take a look at the documentation for more information. Don't forget to convert any connection close, result release, or error and row counting functions you have.

The basic rule of thumb is for functions that use the database connection, you need to include it in the function now (either as the first argument in procedural, or the object you use to call the function in OO), or for a result set you can just change the function to mysqli_ or use the result set as the object.

Why are PHP's mysql_ functions deprecated?

The mysql extension is ancient and has been around since PHP 2.0, released 15 years ago (!!); which is a decidedly different beast than the modern PHP which tries to shed the bad practices of its past. The mysql extension is a very raw, low-level connector to MySQL which lacks many convenience features and is thereby hard to apply correctly in a secure fashion; it's therefore bad for noobs. Many developers do not understand SQL injection and the mysql API is fragile enough to make it hard to prevent it, even if you're aware of it. It is full of global state (implicit connection passing for instance), which makes it easy to write code that is hard to maintain. Since it's old, it may be unreasonably hard to maintain at the PHP core level.

The mysqli extension is a lot newer and fixes all the above problems. PDO is also rather new and fixes all those problems too, plus more.

Due to these reasons* the mysql extension will be removed sometime in the future. It did its job in its heyday, rather badly, but it did it. Time has moved on, best practices have evolved, applications have gotten more complex and require a more modern API. mysql is being retired, live with it.

Given all this, there's no reason to keep using it except for inertia.

* These are my common sense summary reasons; for the whole official story, look here: https://wiki.php.net/rfc/mysql_deprecation

Choice quotes from that document follow:

The documentation team is discussing the database security situation,
and educating users to move away from the commonly used ext/mysql
extension is part of this.

Moving away from ext/mysql is not only about security but also about
having access to all features of the MySQL database.

ext/mysql is hard to maintain code. It is not not getting new
features. Keeping it up to date for working with new versions of
libmysql or mysqlnd versions is work, we probably could spend that
time better.

The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead

Why is this happening?
The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, was officially deprecated in PHP v5.5.0 and removed in PHP v7.
It was originally introduced in PHP v2.0 (November 1997) for MySQL v3.20, and no new features have been added since 2006. Coupled with the lack of new features are difficulties in maintaining such old code amidst complex security vulnerabilities.
The manual has contained warnings against its use in new code since June 2011.
How can I fix it?
As the error message suggests, there are two other MySQL extensions that you can consider: MySQLi and PDO_MySQL, either of which can be used instead of ext/mysql. Both have been in PHP core since v5.0, so if you're using a version that is throwing these deprecation errors then you can almost certainly just start using them right away—i.e. without any installation effort.
They differ slightly, but offer a number of advantages over the old extension including API support for transactions, stored procedures and prepared statements (thereby providing the best way to defeat SQL injection attacks). PHP developer Ulf Wendel has written a thorough comparison of the features.
Hashphp.org has an excellent tutorial on migrating from ext/mysql to PDO.
I understand that it's possible to suppress deprecation errors by setting error_reporting in php.ini to exclude E_DEPRECATED:
```
error_reporting = E_ALL ^ E_DEPRECATED
```
What will happen if I do that?
Yes, it is possible to suppress such error messages and continue using the old ext/mysql extension for the time being. But you really shouldn't do this—this is a final warning from the developers that the extension may not be bundled with future versions of PHP (indeed, as already mentioned, it has been removed from PHP v7). Instead, you should take this opportunity to migrate your application now, before it's too late.
Note also that this technique will suppress all E_DEPRECATED messages, not just those to do with the ext/mysql extension: therefore you may be unaware of other upcoming changes to PHP that would affect your application code. It is, of course, possible to only suppress errors that arise on the expression at issue by using PHP's error control operator—i.e. prepending the relevant line with @—however this will suppress all errors raised by that expression, not just E_DEPRECATED ones.

What should you do?

You are starting a new project.
There is absolutely no reason to use ext/mysql—choose one of the other, more modern, extensions instead and reap the rewards of the benefits they offer.
You have (your own) legacy codebase that currently depends upon ext/mysql.
It would be wise to perform regression testing: you really shouldn't be changing anything (especially upgrading PHP) until you have identified all of the potential areas of impact, planned around each of them and then thoroughly tested your solution in a staging environment.
- Following good coding practice, your application was developed in a loosely integrated/modular fashion and the database access methods are all self-contained in one place that can easily be swapped out for one of the new extensions.
  Spend half an hour rewriting this module to use one of the other, more modern, extensions; test thoroughly. You can later introduce further refinements to reap the rewards of the benefits they offer.
- The database access methods are scattered all over the place and cannot easily be swapped out for one of the new extensions.
  Consider whether you really need to upgrade to PHP v5.5 at this time.
  You should begin planning to replace ext/mysql with one of the other, more modern, extensions in order that you can reap the rewards of the benefits they offer; you might also use it as an opportunity to refactor your database access methods into a more modular structure.
  However, if you have an urgent need to upgrade PHP right away, you might consider suppressing deprecation errors for the time being: but first be sure to identify any other deprecation errors that are also being thrown.
You are using a third party project that depends upon ext/mysql.
Consider whether you really need to upgrade to PHP v5.5 at this time.
Check whether the developer has released any fixes, workarounds or guidance in relation to this specific issue; or, if not, pressure them to do so by bringing this matter to their attention. If you have an urgent need to upgrade PHP right away, you might consider suppressing deprecation errors for the time being: but first be sure to identify any other deprecation errors that are also being thrown.
It is absolutely essential to perform regression testing.

The PHP MySQL Extension is Deprecated - Any Open Source Solutions?

If your code is secure, I wouldn't worry about the libraries being deprecated. They haven't been removed from PHP yet and probably won't for a while. When they're removed, they'll be put into an external library by somebody, which every single host will install.

The reason to rewrite would be security, i.e. if you aren't sure that your code is injection-proof, not the deprecation of a library which has only been deprecated but some people can't use it.