Converting to MySQLi

Skip to end of metadata
Go to start of metadata

Converting scripts from the old PHP MySQL extension to MySQLi

Almost three years ago, a new, improved PHP extension has been released to connect PHP to the MySQL Server: the MySQLi extension. Back in spring 2004, Zak Greant and Georg Richter published an article outlining the major goals and the major features of the new MySQLi extension. Additionally, the authors answered the question why all PHP 5 users should switch from the old PHP MySQL extension to the new MySQLi extension. Almost everything what has been said in 2004 is still valid. It is still recommended to upgrade your scripts to use the MySQLi extensions.

We have good news for those of you who fear the work of converting thousands of lines of source code manually: MySQL has developed a tool which helps you with the conversion of the code.

Overview of ext/mysqli

Released together with PHP 5, the MySQLi extension aimed to replace the old, reliable MySQL extension which has been available in PHP since the mid-90s. After almost a decade of development activities some questionable features had crept into ext/mysql. The extension became difficult to maintain and the feature set of ext/mysql started to differ from that of the underlying MySQL client library.

At this point, Georg Richter made a clear cut and developed the MySQLi extension. The "i" in the name of the extension stands for: improved, interface, incomplete or whatever you want to call it.

The new extension ext/mysqli supports all new features of the MySQL Server Version 4.1 and higher, for example Prepared Statements and support for Character Sets. Prepared statements are a great step ahead, especially nowadays when everybody is concerned about security.

The MySQLi extension has a procedural interface which is very similar to the ext/mysql interface to make porting as simple as possible and it offers an additional object oriented interface for those who prefer the object oriented programming style.

The new features are the reason why you should switch to PHP 5 and ext/mysqli, if you have not done this step yet. Looking forward at PHP 6, we do not plan to add Unicode to the old PHP MySQL extension The feature set of the old extension is not state of the art any more, even with Unicode support.

Who should switch?

Switching from ext/mysql to ext/mysqli is easy in most cases, thanks to the wide usage of database abstractions. From a high-level point of view, PHP applications can be categorized in four groups, depending on how they access a database system. Typically one of the following coding styles gets used to connect an application to a databases:

  1. native connection: vendor-specific database API calls spread all over the application source code
  2. project specific layers: PHP-based database (API-) abstraction layers
  3. well known layers: PHP based, e.g. PEAR::DB, PEAR::MDB2
  4. PDO – a C-based, API-abstraction layer

In the early days of PHP, hardly anybody used a database abstraction layer in their applications. Applications have been slim, lightweight and small in general. Often PHP was not used for much more than simple templating tasks in the beginning, but grow dramatically over the time.

Many of applications of this generation spread the database API calls (e.g. the mysql_connect()) all over the application. Applications using this approach can be difficult to maintain. Some of them spread connection parameters all over the source. Whenever you have to change them, for example because of a new password of the database, you have to modify many files. Switching from one database to another or even only from ext/mysql to ext/mysqli is also very much work.

Those who had every to implement such a migration, started to use database modules. Thin API-abstraction layers which bundle all database API calls in one, central place became common. Upgrading an application which uses a project specific database layer or any other well known PHP based abstraction layer, is much easier, because all you have to do is to migrate the database module, given that no changes to the SQL statements are required.

The latest chapter in the book of PHP database connectivity is the introduction of a C-based database API abstraction layer: PDO.

If you are still in Chapter 1, you might be a bit behind the state of the art in PHP programming - unless you have strong reasons for it, for example the need to gain maximum performance. A similar note goes to readers of Chapter 2. No matter if you are in Chapter 1 or Chapter 2, consider stepping forward and read how the MySQL ext/mysql Converter Tool can help you with the switch from ext/mysql to ext/mysqli.

What does the Converter Tool do?

The MySQL ext/mysql Converter Tool does analyze you PHP source, searches for all ext/mysql expressions and replaces them with semantically identical ext/mysqli expressions. If all you have to do is to convert a few lines of code, it is a matter of minutes to upgrade your application from ext/mysql to ext/mysqli.

Of course, replacing the mysql_-function calls with their mysqli_-counterparts is only half of the of the story. For example, your script will not be modified to use prepared statements. This step is left to you. But the tool takes the job from your shoulders to identify the exact counterparts of ext/mysql functions in the ext/mysqli API. And the tool takes care of default connections which are an often used feature in ext/mysql.

In most cases, the result of the conversion can be run out of the box. For example you can successfully upgrade the popular blogging software WordPress 2.0.4 from ext/mysql to ext/mysqli within 45 seconds. We will demonstrate this in the following.

Installing the Converter Tool

Download the converter tool from a mirror and unpack the archive in the web directory of your web server. The latest release is 1.0, and the MD5 sum for it is 6c1720469070edda2bcbdc34c62b9d04; it is also signed with the MySQL key. An anonymous Subversion repository is also available. That's it, as long as you do not want to help developing it and you just want to use it. Open the URL http://<myserver>/<installation_path>/MySQLConverterTool/GUI/index.php in your web browser.

You will be given the opportunity to convert a single file, to convert all files from a directory or to convert a code snippet. Go for the directory option and point the converter tool to the installation path of WordPress 2.0.4. Instruct the tool to update the files in the directory and all its subdirectories which contain mysql_*-function calls and let the tool create backups of the original files before they get modified.

http://forge.mysql.com/wiki/Image:Mysqli_converter_tool_web_browser.jpg

Press "Start the conversion". Wait some 45 seconds - depending on the speed of your computer - and it is done. WorldPress now runs on ext/mysqli, try it out. Ignore the summary screen and the two warnings it reports for now. We will get back to it soon.

Calling the Converter Tool

The converter tool comes with a Web GUI and CLI user interface. All options available in the Web GUI are available in the CLI interface and vice versa. It is up to you to choose which interface you like best. Generally speaking the Web GUI is more convenient for most users, but GUI interfaces have the problems that is is difficult to integrate them into scripting environments. Scripts are a great tool to automate tasks. Automated tasks have the advantage that they use quality assured and standardized procedures which make their results less error prone in general.

In this article, the CLI interface will be used to demonstrate the usage of the converter tool. Using the GUI is self-explaining and deserves no detailed description.

Calling the converter tool on the command prompt is done by invoking the PHP binary with the script cli.php from the installation directory of the converter. Like a native command line binary, the script cli.php is controlled by command line options. The option "-h" prints the help screen:

C:\www\MySQLConverterTool>php cli.php -h

Usage of cli.php :
-f <file> Convert file
-d <directory> Convert directory
-p <pattern> File name pattern for -d, e.g. -p ".php,.php3". Default: *
-s <code> Convert code snippet
-u Update (modify) input file during the conversion
-b Backup files to [original_name].org before they get updated
-v verbose - print conversion details
-w warnings - print errors/warnings, if any
-q quiet - don't print the generated c

Converting a file, a directory, or a code snippet

The converter tool can convert a file (-f <file>), all files from a directory (-d <directory>) or a code snippet (-s <code>). To get familiar with the tool and to find out how it will convert certain ext/mysql functions, you can use -s to specify a code snippet on the command line. When you do this, make sure to enclose the PHP code in opening and closing PHP tags.

C:\www\MySQLConverterTool>php cli.php -s “mysql_connect('host', 'user', 'pass');”
mysql_connect('host', 'user', 'pass');

This is needed because the converter tool is based on the PHP tokenizer extension. The tokenizer does not consider any code which is not enclosed by PHP tags and so does the converter tool. This is the reason why you see no change in the example above, whereas the example below works as expected.

C:\www\MySQLConverterTool>php cli.php -s "<?php mysql_connect('host', 'user', 'pass'); ?>"
<?php ($GLOBALS["___mysqli_ston"] = mysqli_connect('host', 'user', 'pass')); ?>

By default the CLI interface prints nothing but the generated source code. If you invoke the tool with the option -v (verbose), additional information gets shown. Note that the code snippet is slightly modified to make the converter tool throw a warning. The first parameter to the mysql_connect() function is now $host instead of "'host'". Note that the pitfall when using single and double quotes to enclose the PHP snippet passed with -s to the converter tool: your command line shell might interpret $host as a shell variable on Unix if you use double quotes to enclose the PHP code as shown in the Windows examples. Try single quotes instead. On Windows, single quotes will give you a failure. Check the documentation of your command line shell.

C:\www\MySQLConverterTool>php cli.php -s "<?php mysql_connect($host, 'user', 'pass'); ?>" -v

  • Summary ----------------------------------------------------------------------
    Status: Warning
    Number of mysql_-functions found: 1
    Number of mysql_-functions converted: 1
    Warnings/Errors: 1
    Code length: 80 Bytes
  • Warnings/Errors --------------------------------------------------------------
    Warning/Error on line 1
    [Line 1] Cannot analyze server parameter to extract host, socket and port! Conversion cannot be performed automatically. You must manually check the result of the conversion.
  • Generated code ---------------------------------------------------------------
    <?php ($GLOBALS["___mysqli_ston"] = mysqli_connect($host, 'user', 'pass')); ?>
  • End of code ------------------------------------------------------------------

In addition to the generated code a summary and a list of all warnings/errors gets shown. This is the default behaviour in the web GUI. But on the CLI, we assume that most people want to be able to pipe the generated code into a file and therefore, the details are not shown by default.

Summary, Warnings, Errors and how to read them

The summary table shows a status row, the number of ext/mysql functions that have been found in the original source, the number of functions that have been replaced with their ext/mysqli counterparts, the number of warnings/errors which occurred the length of the generated source code. The status row is the most important information for you. The status can be "OK", "Warning" and "Error".

If the number of ext/mysql functions which have been found is equal to the number of functions which have been replace with their ext/mysqli counterparts and if no other warnings have been thrown, the conversion is considered as successful and "OK" gets printed.

If the number of functions found is equal to the number of functions converted but there are warnings, then the status is set to "Warning". The status usually means that runnable and 99% compatible code has been generated but the tool wants you to verify that under all circumstances the semantics of the new code is identical to that of the old code.

Warning is a common status, because the tool is very conservative and picky. One example: in the example above one warning gets thrown. The old MySQL extension allows you to specify a port or a path in the first parameter of mysql_connect(), in the server. mysqli_connect() does not support this, but expects the port and or path to be given as an extra parameter. In the example, the variable $host is used as the first argument to mysql_connect(). Using a very simple, static code analysis approach, the converter tool does not know of $host contains a port or socket specification. If it does, the generated code mysqli_connect($host, 'user', 'pass') is not semantically identical to the original code and it will behave differently when run. Therefore, the converter tool warns you that it was unable to analyze the host parameter. If you go back to the previous example code (-s “<?php mysql_connect('host', 'user', 'pass'); ?>”), the warning is gone, because the converter was able to parse the host parameter and knows that it does not contain a port and/or socket specification.

Normally, you'll never the the status "Error". Error means that not all ext/mysql functions which have been identified in the original code have been converted into ext/mysqli expressions. But there are only two mysql_*-functions which cannot be converted by the tool at all: mysql_result() and mysql_fetch_field2().

Converting individual files

To convert a file, use the option -f <file>. The tool will read the file you specify and print out the converted version. The file will not get modified by this. To convert multiple files, use multiple -f <file> options.

C:\www\MySQLConverterTool>php cli.php -f cli.php -f Converter.php -v

  1. File cli.php #################################################################
  • Summary ----------------------------------------------------------------------

Status: OK
Number of mysql_-functions found: 0
Number of mysql_-functions converted: 0
Warnings/Errors: 0
Code length: 13609 Bytes

If you want to update the files, use the option -u (update) to replace the original content of the files with the generated code. It is strongly recommended to use the option -u always together with the option -b (backup). The backup option makes the converter tool create a backup file named "<original_name>.org" before they get modified. In the unlikely event of a failure, you should have a backup to be on the safe side.

It is useful to combine -u -b with -q (quiet), if you convert multiple files or all files of a directory. -q will suppress printing the generated source code. Instead of the code, a status line for every file gets printed.

C:\www\MySQLConverterTool>php cli.php -f cli.php -f Converter.php -u -b -q

  1. [ OK ] cli.php ##########################################################
  2. [ OK ] Converter.php ####################################################

Converting all files from a directory

You should now be familiar with the command line interface and we can go ahead and validate the statement that it takes only 45 seconds to update WordPress 2.0.4 to ext/mysqli. Use the -d <directory> option to convert all files from the installation directory of WordPress. Add the options -u to update the files, -b to create backups and -q to get the compressed status output.

C:\www\MySQLConverterTool>php cli.php -d c:/www/wordpress/ -u -b -q

  • Directory "c:/www/wordpress/" ************************************************

[ OK ] c:/www/wordpress//index.php
[ OK ] c:/www/wordpress//license.txt
[]
[ Warning ] c:/www/wordpress//wp-includes/rss-functions.php

[]
[ Warning ] c:/www/wordpress//wp-includes/wp-db.php
[]
[ OK ] c:/www/wordpress//wp-rss2.php
[ OK ] c:/www/wordpress//wp-settings.php
[ OK ] c:/www/wordpress//wp-trackback.php
[ OK ] c:/www/wordpress//xmlrpc.php

As you can see, two files are given the status “Warning”. Recall, that “Warning” can be ignored in most cases and try out this patched version of WordPress. You will see that it runs out of the box.

It is possible to specify multiple -d <directory> options, just like you can specify multiple -f <file>. Furthermore you can combine -f <file> with -d <directory> and -s as you like best.

To exclude files like the “licence.txt” contained in the given directory or any of its subdirectories from the conversion, you can define a search pattern for files using the option -p <pattern>. If the option -p is omitted, the “default pattern” is “”, which means that all files are considered. To restrict the search to all files with the file suffix “.php” and “.phps” use '-p “.php, .phps”'. The only pattern identifier recognized by the converter tool is “”. Internally it gets translated into the perl regular expression “.*”.

= How to deal with warnings =It has been shown that the converter tool was able to upgrade WordPress without major problems, but what about the warnings. Can you really always ignore them. Again: you can not. The converter tool is not more but a helping hand. You have to fine-tune the results manually.

We know from the conversion of the directory c:/www/wordpress that there have been two files which have thrown warnings: wp-includes/wp-db.php and wp-includes/rss-functions.php.Run the converter tool again on the backups of the original files, but don't modify the backup files. Instead use the option -q to suppress all output just to enable the output of the warnings again with -w (warnings).

<code> C:\www\MySQLConverterTool>php cli.php -f c:/www/wordpress//wp-includes/wp-db.php.org -w -q

  1. [ Warning ] c:/www/wordpress//wp-includes/wp-db.php.org ######################
  • Warnings/Errors --------------------------------------------------------------

Warning/Error on line 43
[Line 43] Cannot analyze server parameter to extract host, socket and port! Conversion cannot be performed automatically. You must manually check the result of the conversion.
Warning/Error on line 82
[Line 82] WARNING: mysql_escape_string() is deprecated. If no global connection has been opened already the converter cannot use mysqli_real_escape_string() to emulate the function. We add a runtime test if a connection exists, if not trigger_error() gets used to throw an E_USER_ERROR. It would be a security bug to do anything else. You must fix our code manually!
Warning/Error on line 92
[Line 92] mysql_error()/mysql_errno() can be called without a connection handle, mysqli_error()/mysqli_errno() not. As we do not know if a default connection has been opened, we have wrapped the function call in ((<if_default_conn_is_object>) ? mysqli_<func>(<default_conn>) : NULL). This is not 100% the same as the original code in all cases. Check the generated code!
Warning/Error on line 155
[Line 155] mysql_error()/mysql_errno() can be called without a connection handle, mysqli_error()/mysqli_errno() not. As we do not know if a default connection has been opened, we have wrapped the function call in ((<if_default_conn_is_object>) ? mysqli_<func>(<default_conn>) : NULL). This is not 100% the same as the original code in all cases. Check the generated code!
Warning/Error on line 171
[Line 171] You should rewrite all your mysql_fetch_field() calls. The generated code should be compatible. But have a look at the generated code, it is unmaintainable! FIX your code!

Five problems have been identified in the file. On line 43, the converter tool failed to analyze the server parameter of mysql_connect(). This problem has been discussed in detail in the paragraph "Converting a file, a directory or a code snippet" already. If you do not specify a socket or port when you connect to the MySQL Server, this is nothing you need to worry about. If you do, you have to modify the generated mysqli_connect() function call manually.

$this->dbh = @($GLOBALS["___mysqli_ston"] = mysqli_connect($dbhost, $dbuser, $dbpassword));

On line 82, the converter has found the deprecated function mysql_escape_string(). The PHP manual explains why you should not use this function any more. The problem with mysql_escape_string() is that it does not take a connection argument and does not respect the current charset setting. Instead of mysql_escape_string() you should use mysql_real_escape_string() which has been introduced to PHP in version 4.3.0.

The converter takes this into account and tries to find a compatible expression using mysqli_real_escape_string(). But as there is no connection handle given, the best the converter can do is to add a conditional expression which checks if we have a default connection. It might be a good match to use mysqli_real_escape_string() and the last opened connection if the script is using only one connection, it might be wrong. Only the developer can decide on it. As this is a security problem in the original and potentially also in the generated good-guess code, the converter concludes “You must fix our code manually!”.

The addslashes() at the beginning of the function body that you can see has not been added by the converter. It has been there in the original code already. It is clearly a second-best, “FIX ME” solution. function escape($string)

Unknown macro: { return addslashes( $string ); // Disable rest for now, causing problems if( !$this->dbh || version_compare( phpversion(), '4.3.0' ) = '-1' ) return ((isset($GLOBALS["___mysqli_ston"]) && is_object($GLOBALS["___mysqli_ston"])) ? mysqli_real_escape_string($GLOBALS["___mysqli_ston"], $string ) }


The next two warnings from line 92 and line 155 are generic warnings. In line 92 the cause of the warning is an incompatibility between mysqli_error() and mysql_error(). mysqli_error() always expects a a connection handle to be given, mysql_error() automatically falls back to the last opened connection if no handle is given. The last opened connection handle is always contained in the variable $GLOBALS["___mysqli_ston"] in the converted code, but the converter is not sure if you really want to refer to the last opened connection or any other connections but you just forgot to specify the other connection. As said, the converter is very conservative and picky. Therefore it throws a warning, although in 99,999% of all cases, the generated code is exactly what you want. function print_error($str = ) {

global $EZSQL_ERROR;

if (!$str)

$str = ((is_object($GLOBALS["___mysqli_ston"])) ?

mysqli_error($GLOBALS["___mysqli_ston"]) : false);

[]


For line 155 it is the same, with the only difference that a developer can immediately recognize that in this case the use of mysql_error($this->dbh) instead of mysql_error() would have removed all uncertainties from the original source code. I suggest to fix the generated with mysqli_error($this->dbh). []

$this->result = @mysqli_query( $this->dbh, $query);

++$this->num_queries;

if (SAVEQUERIES)

$this->queries[] = array( $query, $this->timer_stop() );

// If there is an error then take note of it..

if ( ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : false) ) {

[]


The last remaining problem has been identified in line 171. You can ignore the warning, but don't ever show the generated ext/mysqli code to anybody. mysql_fetch_field() can be converted in a 100% identical statement, but well, decide yourself if you wan to see it in your source or if you prefer rewriting the generated code.

Conclusion

The MySQL ext/mysql Converter Tool is a helping hand when upgrading ext/mysql based PHP applications to use ext/mysqli. It is recommended to upgrade from ext/mysql to ext/mysqli to be able to use all features of the MySQL Server, for example prepared statements.

Prepared statements are often faster than other statements, because some parsing and communication overhead can be avoided. Additionally and even more important for most applications, they add security to your PHP applications. The tool does not convert existing code to use prepared statements, but it helps you with the first step: upgrading existing applications to use ext/mysqli.

More information how to convert mysql_-functions into mysqli_-counterparts will follow in a separate article. Stay tuned and happy hacking!

 
Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Dec 13, 2013

    The links to the Converter Tool are dead.

    Where can I find the tool for download?

    Thanks

    Bernard

  2. Apr 14, 2014

    Very nice article, but - where to get the convertor?  All links are incorrect !!

Sign up or Log in to add a comment or watch this page.


The individuals who post here are part of the extended Oracle community and they might not be employed or in any way formally affiliated with Oracle. The opinions expressed here are their own, are not necessarily reviewed in advance by anyone but the individual authors, and neither Oracle nor any other party necessarily agrees with them.