When starting the migration of a legacy PHP codebase away from the old MySQL extension, a lot of people initially decide to replace it with the MySQLi extension’s procedural interface. On the surface, this seems like the quickest and most straightforward approach. Unfortunately, this is based on a misconception. Although they are very similar, the interfaces of the two extensions are not simply interchangeable. These differences can be a bit subtle and are not always obvious at the outset but they can introduce a considerable unexpected work to your migration process.
In this post, we are going to look at some of the more common pitfalls so you can be aware of them and keep them in mind when you plan for your migration effort. Understanding the amount of effort required might even make you re-evaluate whether MySQLi is the right choice.
To start, let’s look at the origin of the misconception itself.
After a quick glance over the documentation, it’s apparent that MySQLi has an equivalent function for each MySQL function, with the only major difference being an “i” added in the function name:
This could lead you to believe that completing the conversion to MySqli would be as simple as firing up your IDE and doing something like the following:
Migration done! That was easy!
Almost too easy? While this is a necessary first step, there is almost certainly going to be a few things more things you will need to take carre of before you have working code. Let’s look at a few of the most common of these rabbit holes.
Re-ordered Function Parameters
For a number of mysql functions, just changing the function name to the mysqli equivalent will be enough. Look at mysqli_fetch_array for example:
Notice how for each of mysql_fetch_array‘s parameters, mysqli_fetch_array has an equivalent parameter, in exactly the same position of the function’s signature. This is true for a number of the mysqli_* functions, but not for all of them. Consider mysqli_query:
So mysqli_querydoes have equivalents for both of mysql_query‘s parameters, but their position in the function signature is swapped around.
Changing around the order arguments in each of your function calls is obviously going to take more than a quick Find/Replace.
Mandatory Link Id Parameters
:To understand this issue, let’s again compare a call to mysql_query with a call and its replacement function mysqli_query:
On the surface, everything looks good. We’ve changed the function name and switched around the parameter. So that should do the job, right?
If you are lucky, yes it would. It all depends on how the mysql_* calls in your codebase was written. If you are not so lucky, you will find calls in your code that completely leaves out the $link_identifier argument, like so:
How this even possible? Well, it worked because the $link_identifier parameter was optional in the old mysql_* functions. In the mysqli_* functions, however, the $link parameter is very much required.
See how this complicates things? We now need to introduce the missing arguments from… where exactly?
With both MySQL and MySQLi, you obtain a link identifier by calling mysql[i]_connect or one of the other connect functions.
When using MySQL, you had the option of leaving out the link identifier function argument, and, if you did, the function would just use the link identifier of the last opened connection or try to make a new one.
Using MySQLi however, you no longer have this option. You have to explicitly pass a valid link identifier every time you make the function call.
So to fully convert function calls like these, you have to assign the result of the mysqli_connect call, pass it along to where it is needed and then pass as the first argument in all the calls that require it.
Taking care of these could be a relatively quick job, or a massive one, depending on how many of these function calls there is in your codebase and how scattered or centralised they are.
Prepared Statement Complications
Chances are you are migrating away from the old MySQL extensions in the first place because you know about all its various security issues and you know that there are solutions to these in MySQLi. What is important to realize though, is that only converting to the mysqli_* functions as we’ve done above is not going to make our code any more secure than it was before.
To take full advantage of the SQL Injection protection in MySQLi, we have to use a feature called Prepared Statements. The thing is, the series of function calls we need to make to prepare and execute Prepared Statements in MySQLi are very, very different from the old model. How different? Here’s another side-by-side comparison: (There are no arrows this time, I initially tried to put some in but the whole thing just became a mess).
It should be pretty clear that the two interaction models differ so much that our last hope for a simple statement-by-statement conversion is now entirely out of the window.
“Thanks for Ruining Everything! Now What?”
If your only reason for choosing MySQLi procedural interface was that it appeared to be quicker and easier to convert to, the examples above have shown that this is not necessarily the case. There are many very good reasons why you may want to steer clear of MySQLi. There are also more than enough alternative approaches to get rid of the depreciated MySQL extension in your code. I’ll look at all of those in upcoming blog posts.
What if you’ve considered your options and you still decide to stick with MySQLi Procedural as your target technology? Are you doomed to spend endless hours doing nothing but refactoring mysql_* calls by hand? Hopefully not. There are a few tools out there that promise to automate all or at least some of the migration process for you. I’ll review some of these in an upcoming blog post.
Did this help you?
Sign up for my newsletter and I will send you regular articles and other usefull stuff.
So you have a medium to large PHP codebase that is… well let’s just say it’s not a spring chicken anymore. Not that there’s anything wrong with that. It does what it needs to do and it does that well.
So, everything is awesome! Except…
Well except that your code still uses the old deprecated MySQL Extension.
But that’s not really a big deal, is it? People have been going on about this for years. It’s not that you aren’t aware of the reasons you should upgrade. It’s just that you never got around to actually doing it. Anyway, your system is doing just fine as it is, right? Right?
Well, that may have been true up till recently, but the situation is becoming a bit more urgent. This is not something you can afford to put off much longer. Let me explain why…
FACT: The MySQL Extension was still available for PHP 5 but has been removed from PHP 7 onward.
This means that if your codebase still uses the MySQL extension, your system will not work on PHP 7. Having any code that uses the old extension rather than one of the alternatives is effectively keeping you stuck on PHP 5 by preventing you from upgrading to PHP 7.
This version lock-in situation has probably not been too big a deal for you up till now. You have things running just fine in PHP 5. Sure PHP 7 looks awesome, but your system doesn’t really need the new features and you couldn’t justify the upgrade effort. Sure, you were probably even able to maintain a decent level of security with the old MySQL extension if you did things like escaping your queries properly. Unfortunately, this is not a viable position anymore, because…
This means, that starting 2019 there will be no more security updates and patches for PHP 5. So any system or site running on PHP 5 will become inherently insecure.
In addition, PHP 5 has already started to disappear from OS package managers, hosting services and other sources. As a result, it will become increasingly difficult to set up or find a place to host your PHP 5 system when you need to move it.
All of which means that if your codebase is still locked into PHP 5 by its dependence on the MySQL extension, come 2019, you are in going to be in for a rough ride.
All of this means that you should seriously start working a migration strategy for codebase now.
It’s important to know that the process of converting your code could take a significant amount of time. How long exactly depends on a number of factors such as the size and complexity of your codebase, which technology you choose to replace the old API and the resources available to you.
Time is ticking. Start looking at your code and researching your options. Then come up with a plan and start converting. The longer you wait the harder this is going to get.
I’ll be posting a series of practical articles and resources to help you with the choices, planning and the actual migration process. Subscribe below if you don’t want to miss them.
Did this help you?
Sign up for my newsletter and I will send you regular articles and other goodies.