How to Log All SQL Queries in WordPress

To track a complete log of SQL queries during a request, using a plugin is one option, but often, adding a few lines of code directly can be more straightforward and efficient.

Once you achieve your goal, you can simply remove these lines.

For instance, in a scenario where I needed to trace the storage location of data originating from a custom options panel in a third-party plugin, direct code manipulation proved invaluable.

The plugin’s code was complex, making it challenging to locate the data storage.

By logging SQL queries, particularly focusing on UPDATE queries, I could pinpoint the affected table. This discovery enabled me to craft a custom function utilizing that data.

The process begins with defining a constant in WordPress to record all queries. This is done by inserting the following line in the wp-config.php file:

define('SAVEQUERIES', true);

After enabling query logging, the next step involves deciding where to save these logs. My approach is to store them in a file within the uploads folder, mainly because this directory typically has the necessary write permissions for WordPress.

To implement this, insert the following code into the functions.php file of your active theme, or within a custom plugin:

<?php
add_action('shutdown', 'explainwp_log_sql');
function explainwp_log_sql()
{
    global $wpdb;

    $file = fopen(trailingslashit(WP_CONTENT_DIR) . 'uploads/sql.txt', 'a');

    fwrite($file, "\n\n------ NEW REQUEST [" . date("F j, Y, g:i:s a") . "] ------\n\n");

    foreach ($wpdb->queries as $q) {
        fwrite($file, $q[0] . " - ($q[1] s)" . "\n\n");
    }

    fclose($file);
}

After you apply these changes, every time you reload a page, make an Ajax request, or save something, all SQL requests will be recorded in the uploads/sql.txt file.

It’s as straightforward as that!

However, a crucial note of caution: once you have obtained the necessary information, it’s essential to remove this code from your theme or plugin.

This functionality is strictly for development use and should never be operational in a live production environment. Keeping such code active can lead to performance issues and potential security vulnerabilities.