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
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
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
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_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
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
$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
With both MySQL and MySQLi, you obtain a link identifier by
When using MySQL, you had the option of leaving out the link identifier function argument, and, if you did, the function would
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.
You can unsubscribe at any time