Dougal Campbell's geek ramblings

WordPress, web development, and world domination.

mysql vs mysqli in WordPress

I recently ran across an issue that I was previously unaware of, so other developers could run into it as well. I was having problems with a plugin, which started misbehaving. The plugin had previously worked fine (it generates a sidebar widget), and I wasn’t actively working on my site, so I wasn’t really sure when it had quit working.

In the course of debugging the problem, I discovered that the plugin was throwing warnings in my PHP error log regarding the mysql_real_escape_string() function. As a quick fix, I simply replaced all of those calls with WordPress’ esc_sql() function. Voila, problem fixed.

Curious, I took a peek into wp-db.php, and found this block of code:


/* Use ext/mysqli if it exists and:
 *  - USE_EXT_MYSQL is defined as false, or
 *  - We are a development version of WordPress, or
 *  - We are running PHP 5.5 or greater, or
 *  - ext/mysql is not loaded.
 */
if ( function_exists( 'mysqli_connect' ) ) {
        if ( defined( 'USE_EXT_MYSQL' ) ) {
                $this->use_mysqli = ! USE_EXT_MYSQL;
        } elseif ( version_compare( phpversion(), '5.5', '>=' ) || ! function_exists( 'mysql_connect' ) ) {
                $this->use_mysqli = true;
        } elseif ( false !== strpos( $GLOBALS['wp_version'], '-' ) ) {
                $this->use_mysqli = true;
        }
}

Note the bit about using a development version of WordPress. In my case, I’m running out of svn trunk, and my server has the mysqli extension installed, so wpdb chose to use it. That’s fine.

But this exposes the fact that some plugins use functions like mysql_real_escape_string() “in the raw”, so to speak, which depends on using PHP’s mysql extension, and not mysqli. WordPress provides convenience functions like esc_sql() and $wpdb->prepare() to help abstract details like this away and protect developers against environmental differences between servers.

Hopefully this will save somebody else out there some debugging time.

About Dougal Campbell

Dougal is a web developer, and a "Developer Emeritus" for the WordPress platform. When he's not coding PHP, Perl, CSS, JavaScript, or whatnot, he spends time with his wife, three children, a dog, and a cat in their Atlanta area home.
This entry was posted in WordPress and tagged , , , , , , , . Bookmark the permalink.

5 Responses to mysql vs mysqli in WordPress

  1. Pingback: Dougal Campbell: mysql vs mysqli in WordPress | A2Z Web Design Tutorial

  2. Bryan Petty says:

    This was exactly why MySQLi was turned on by default for development versions, so everyone had the chance to expose these issues, and fix their plugins. Notably, these plugins would have broken only on WP installations running PHP 5.5+, immediately as soon as WP was upgraded to 3.9+. Unless you were testing with PHP 5.5, you may have never noticed before the release.

  3. Pingback: Dougal Campbell: mysql vs mysqli in WordPress | htaccess

  4. Pingback: Dougal Campbell: mysql vs mysqli in WordPress | facebooklikes

  5. Pingback: PHPDeveloper.org: Dougal Campbell: mysql vs mysqli in WordPress

Leave a Reply

%d bloggers like this: