Is it safe to access the underlying mysqli object from \wpdb for custom queries?

Question

I am writing a custom (bespoke, for 1 client, not for publication and general consumption) plugin, that needs to access custom tables.

In general with WP,I follow the principle of using the higher level APIs where possible, so rarely have to work with \wpdb. Now that i do have a need, i am discovering how archaic it is!

Looking through the source code, it seems I can be sure that it is using mysqli (since the plugin requires php 7.1, and we also control the server) so I thought why not use that for my custom tables logic?

I wrote a tiny wrapper:

<?php

namespace PluginNamespaceHere\DB;

use mysqli;
use wpdb;

/**
 * A small wrapper class that contains, and provides direct access to, the $wpdb object,
 * as well as the underlying MYSQLI object, so we can do real prepared statements etc
 */
class DB
{

    /** @var wpdb */
    public $wpdb;

    /** @var mysqli */
    public $mysqli;

    /**
     * @param wpdb $wpdb
     */
    public function __construct(wpdb $wpdb)
    {
        $this->wpdb = $wpdb;
        //$wpdb is protected, but accessible via magic __get() wp-db.php line: 643
        $this->mysqli = $wpdb->dbh;
    }


    /**
     * Run an SQL query. If $params are provided, prepared statements are used. If $bind_types are provided, they will be
     * used in the prepared statement, if not, all params will be treated as strings
     *
     * @param string $sql The SQL string, unprefixed table names should be wrapped in curly braces eg SELECT * FROM {posts}
     * @param array $params Optional parameters for prepared statements
     * @param string $bind_types Optional bind types for prepared statements, defaults to string
     * @retun bool|mysqli_result
     */
    public function run($sql, $params=[], $bind_types='')
    {
        $sql = $this->prefixTableNamesInSqlString($sql);

        if(!is_array($params) || empty($params)){
            return $this->mysqli->query($sql);
        }
        if($bind_types == ''){
            $bind_types = str_repeat("s", count($params));
        }
        $stmt = $this->mysqli->prepare($sql);
        $stmt->bind_param($bind_types, ...$params);
        $stmt->execute();
        return $stmt->get_result();
    }

    /**
     * Replaces curly brace table names with their actual, prefixed name
     * Eg "SELECT * from {table_name}" => "SELECT * from wp_table_name"
     * @param string $sql
     * @return string
     */
    private function prefixTableNamesInSqlString($sql)
    {
        return str_replace(["{", "}"], [$this->wpdb->prefix, ""], $sql);
    }

}

This would allow me to use real prepared queries, and have nice readable code like:

<?php
$sql = "
            SELECT DISTINCT c.ID AS course_id, c.post_title AS course_title
            FROM {posts} c
            JOIN {tmsc_course_product} cp
            ON c.id = cp.course_id
            WHERE cp.product_id IN(?,?)
        ";
//Yes, i know DB::run() can return bool! Out of scope for this question
$courses = $db->run($sql, [57,4761])->fetch_all(MYSQLI_ASSOC);

My only concern is if this is going to have any knock on effects with wpdb and its dependents. The wpdb class is not exactly easy to read, and appears holds a lot of state.

Answer 1

It depends on your definition of "safe".

Is the client always going to be your client? If not, then you're writing code for them based on assumptions about their hosting environment and available technologies that may not always be under your control, and you may be unnecessarily complicating things for them in the future.

$wpdb is likely to work as-is regardless of any changes to the underlying technologies over time, as long as WordPress is up to date. If mysqli were deprecated, then future versions of WordPress may update $wpdb to work with whatever's new and supported, but the client will be stuck with your code that depends on outdated technology.

This has already happened before with the mysql_ functions, when they were deprecated. If you were using $wpdb at the time, there'd be no problem. But any sites with code using the deprecated functions directly would begin seeing notices if they moved to a newer version of PHP.

How likely is this to happen again any time soon? Honestly, I have no idea, and only you know your relationship with your client and how big a problem this would even be if it did happen. But there's a reason you're 'supposed' to use $wpdb, and not doing so is indeed riskier in some respects, if only marginally.

Frankly, reinventing the wheel seems like a poor use of everybody involved's time.

Answer 2

Yes it is safe, but it is a meh. The main things that may fail are plugins that look into the queries being executed and either report them (query monitor) or cache the requests and results.

It is unlikely that not having your queries available to those tools will have any detectable impact on the site, but the proper way to do such thing is to replace the WPDB "driver" with you own which adds the functionality by adding a wpdb drop-in https://wpreset.com/customize-wpdb-class/ which implements the "proper" prepared statements while integrating with the loging and caching for the prepared statements as well.

query monitor will still not work (it is using a dropin by itself) but other tools should, and you DB abstraction logic will be in one specific place.

Is it smart to do? unless it is a "must have" to gain performance or security, probably not.