Hidden Reasons Why Migrating from MySQL to MySQLi Procedural may not be as Simple as You Think

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:

image/svg+xml mysqli_connectmysqli_querymysqli_fetch_rowmysqli_insert_id mysql_connectmysql_querymysql_fetch_rowmysql_insert_id

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:

Screenshot of using an IDE's Find/Replace functionality to replace all occurrences of "mysql_" with "mysqli_ "

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:

image/svg+xml mysql_fetch_array( $result, $result_type); mysqli_fetch_array( $result, $resulttype);

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:

image/svg+xml mysql_query( $query, $link_identifier); mysqli_query( $link, $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:

image/svg+xml mysql_query( $query, $link_identifier); mysqli_query( $link, $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:

image/svg+xml mysql_query( $query);

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?

image/svg+xml mysql_query( $query); mysqli_query( $link, $query); ?

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.  

image/svg+xml mysql_connect(…);$result = mysql_query( $query ); $link = mysqli_connect(…);$result = mysqli_query( $link, $query );

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).

image/svg+xml $link_identifier = mysql_connect(…);$query = "SELECT column_1 FROM table WHERE column_2 = " . $column_2;$resource = mysql_query( $query, $link_identifier );$query_result = mysql_fetch_assoc( $resource );$column_1 = $query_result [‘column_1’];echo($column_1); $link = mysqli_connect(…);$query = "SELECT column_1 FROM table WHERE column_2 = ?";$stmt = mysqli_prepare( $link, $query);mysqli_stmt_bind_param( $stmt, ‘i’, $column_2);mysqli_stmt_execute( $stmt);mysqli_stmt_bind_result( $stmt, $column_1);mysqli_stmt_fetch( $stmt);echo($column_1);

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.

Sign Me Up!

You can unsubscribe at any time

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.