wp query - Is it safe to access the underlying mysqli object from wpdb for custom queries?

admin管理员组

文章数量:1278790

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.

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.

• wp-query
• wpdb

Share Improve this question edited Oct 5, 2021 at 10:18 Steve asked Oct 5, 2021 at 10:12 SteveSteve 20111 silver badge44 bronze badges 5

• 1 Why can't you write "real prepared queries" with $wpdb->prepare()? If the plugin is only for a specific case, I think you can make a lot more assumptions. If it were for the public, you'd need to be aware that some people swap mysqli for other clients (e.g. postgres). – kero Commented Oct 5, 2021 at 10:17
• @kero because wpdb::prepare() is just a giant string manipulation routine, that does not use prepared statements at all, as far as i can tell from looking through the source and reading – Steve Commented Oct 5, 2021 at 10:23
• And what advantages are you expecting from real prepared statements? Afaik they are immensely useful when you have the same query with different parameters multiple times - which in your example is not the case. For anything else we can assume that $wpdb->prepare() is quite safe, otherwise we would have heard security people comlaining loudly about it - especially since it is so widely used within WP and its ecosystem. – kero Commented Oct 5, 2021 at 10:27
• As for your question (which might be too localized/opinion based for this platform): In a controlled environment such as the instance of a single customer, you could even drop $wpdb altogether and use PDO for your queries - as long as you know what you're doing. I have no interest in reading the full source of wpdb but know that some caching is taking place there, your custom class/extension might clash with this. If the whole project is not about optimizing the db class, I would focus on more important aspects and just use $wpdb. – kero Commented Oct 5, 2021 at 10:30
• @kero Yes, multiple queries was what got me started down this road, so thats one of the main benefits. The other is being able to write more readable SQL, without having to account for wpdb::prepare() edge cases like % etc – Steve Commented Oct 5, 2021 at 10:32

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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.

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

本文标签： wp queryIs it safe to access the underlying mysqli object from wpdb for custom queries