fix invalid utf8 data coming from WHMCS API

[Seb]

When importing to Upmind, you can receive an invalid utf8 data error which happens when data is encoded incorrectly in WHMCS. This throws error on the API so we can't export data. Here is a script you can run (at your own risk!) on your WHMCS database to fix it. Please read the instructions caerefully.

 

<?php

/**
 * Admin script to repair invalid utf8 data in the requested table.
 *
 * Installation:
 * 1. These instructions assume you have the default admin directory path of /admin
 *    so if you have a custom admin directory, please adjust the path as necessary.
 * 2. Copy this file into the /admin directory of your WHMCS installation.
 * 3. Run the script from the command line or via the web interface, as documented below.
 *
 * Usage tips:
 * - Although this script has been carefully written and tested on production
 *   databases, it's advised to take a backup of your database before running it,
 *   since changes are irreversible.
 * - This script operates on a single database table so you may need to run it on
 *   multiple tables depending on the places you're seeing invalid UTF8 data.
 * - The most common place to see invalid UTF8 data is in the tblhosting.password
 *   column. Because this column is encrypted at rest, you need to instruct the
 *   script to explicitly fix passwords, which involves the script decrypting,
 *   fixing, and re-encrypting them.
 * - It's always advised to run the script using the --dry/?dry=1 flag first to
 *   view changes which would be made before finally running the script again with
 *   the --fix/?fix=1 flag to actually update the database with the changes.
 * - When running the script via the web interface, you will need to provide the
 *   token which is displayed on the page when you run the script in dry mode.
 *   This is to prevent accidental executions and CSRF.
 *
 * CLI Usage:
 * php fix_invalid_utf8_data.php {table-name} [--dry] [--fix] [--fix-passwords]
 *
 * Web Usage:
 * GET admin/fix_invalid_utf8_data.php?table={table-name}&dry={0/1}&fix={0/1}&fix_passwords={0/1}&token={token}
 *
 * @author Harry Lewis <[email protected]>
 * @copyright Upmind 2023
 */

function is_cli(): bool
{
    return defined('STDIN');
}

/**
 * Write a message to stdout.
 */
function stdout($str = ''): void
{
    print($str . PHP_EOL);
}

/**
 * Write an error message and end the script.
 */
function error($str, int $httpCode = 500): void
{
    stdout($str);

    if (is_cli()) {
        exit(1);
    }

    http_response_code($httpCode);
    exit();
}

restore_error_handler();
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('log_errors', 0);

if (is_cli()) {
    require __DIR__ . '/../init.php';

    // CLI - no auth required
    if (!isset($argv[1])) {
        return error(sprintf('Usage: php %s {table-name} [--dry] [--fix] [--fix-passwords]', $argv[0]));
    }

    $table = $argv[1];
    $fix = in_array('--fix', $argv);
    $dry = in_array('--dry', $argv);
    $fixPasswords = in_array('--fix-passwords', $argv);
} else {
    define('ADMINAREA', true);
    require __DIR__ . '/../init.php';

    // Web - auth required
    $aInt = new WHMCS\Admin('Database Status'); // admin permissions tied to this string
    $aInt->title = 'Fix Invalid UTF8 Data';
    $aInt->sidebar = 'config';
    $aInt->icon = 'database';
    $aInt->helplink = 'database';
    $aInt->requireAuthConfirmation(); // requires admin pw confirmation

    echo '<pre>';

    if (!isset($_GET['table'])) {
        return error('Please specify a table to fix in ?table={table-name}', 422);
    }

    $table = $_GET['table'];
    $fix = boolval($_GET['fix'] ?? false);
    $dry = boolval($_GET['dry'] ?? false);
    $fixPasswords = boolval($_GET['fix_passwords'] ?? false);

    if ($fix) {
        check_token("WHMCS.admin.default");
    }
}

/** @var WHMCS\Database */
$db = DI::make('db');
/** @var PDO */
$pdo = $db->getPdo();

if (!in_array($table, $db->listTables())) {
    return error('Table not found: ' . $table, 422);
}

stdout('Selected table: ' . $table . '...');

$query = $pdo->query(sprintf('SHOW COLUMNS FROM `%s` WHERE `Type` LIKE "varchar%%" OR `Type` LIKE "char%%" OR `Type` LIKE "%%text%%" OR `Key` LIKE "PRI";', $table));
$columnsData = $query->fetchAll(PDO::FETCH_ASSOC);

$fixColumns = [];
foreach ($columnsData as $column) {
    if ($column['Key'] == 'PRI') {
        $idColumn = $column['Field'];
        continue;
    }

    $fixColumns[] = $column['Field'];
}

if (!isset($idColumn)) {
    return error(sprintf('No primary key found for table %s', $table));
}

if (empty($fixColumns)) {
    return error(sprintf('No string columns found to fix for table %s', $table));
}

stdout(sprintf('Identified %d columns to fix in table %s: %s', count($fixColumns), $table, implode(', ', $fixColumns)));

$query = $pdo->query(sprintf('SELECT count(*) FROM `%s`', $table));
$rowCount = $query->fetchColumn();
$repaired = 0;

if ($rowCount == 0) {
    return error(sprintf('No rows found in table %s', $table));
}

if (!$fix && !$dry) {
    is_cli()
        ? stdout(sprintf('Execute again with --dry to do a test run'))
        : stdout(sprintf('Found %d rows in table %s. Execute again with &dry=1 to do a test run', $rowCount, $table));
    return;
}

stdout();

if ($dry) {
    stdout(sprintf('--- DRY RUN ---'));
} elseif ($fix) {
    stdout(sprintf('--- FIXING DATA ---'));
}

stdout(sprintf('Found %d rows in table %s. Repairing data...', $rowCount, $table));

$limit = 200;
$offset = 0;

$selectColumns = array_map(function (string $column) {
    return sprintf('`%s`', $column);
}, array_merge([$idColumn], $fixColumns));

$updateColumns = array_map(function (string $column) {
    return sprintf('`%s` = :%s', $column, $column);
}, $fixColumns);

$selectQuery = $pdo->prepare(sprintf('SELECT %s FROM `%s` LIMIT :limit OFFSET :offset', implode(', ', $selectColumns), $table));
$selectQuery->bindParam(':limit', $limit, PDO::PARAM_INT);
$selectQuery->bindParam(':offset', $offset, PDO::PARAM_INT);
$selectQuery->execute();

$updateQuery = $pdo->prepare(sprintf('UPDATE `%s` SET %s WHERE `%s` = :primary_key', $table, implode(', ', $updateColumns), $idColumn));

while ($rows = $selectQuery->fetchAll(PDO::FETCH_ASSOC)) {
    $offset += $limit;
    stdout(sprintf('  ...processing %s/%s', min($offset, $rowCount), $rowCount));

    foreach ($rows as $rawRow) {
        $row = $rawRow;

        if ($fixPasswords && !empty($row['password'])) {
            try {
                $decryptedPassword = decrypt($row['password']);
                $passwordJson = json_encode($decryptedPassword, JSON_INVALID_UTF8_SUBSTITUTE);
                $repairedPassword = json_decode($passwordJson);

                if ($repairedPassword !== $decryptedPassword) {
                    $row['password'] = encrypt($repairedPassword);
                }
            } catch (\Throwable $e) {
                $row['password'] = '';
            }
        }

        $rowJson = json_encode($row, JSON_INVALID_UTF8_SUBSTITUTE);
        $repairedRow = json_decode($rowJson, true);

        if ($repairedRow !== $rawRow) {
            stdout(sprintf('    - Repairing %s %s %s: %s', $table, $idColumn, $row[$idColumn], $rowJson));

            $updateQuery->bindValue(':primary_key', $row[$idColumn]);

            foreach ($fixColumns as $column) {
                $updateQuery->bindValue(sprintf(':%s', $column), $repairedRow[$column]);
            }

            if (!$dry) {
                $updateQuery->execute();
            }

            $repaired++;
        }
    }

    $selectQuery->execute();
}

stdout('Done!');
stdout();

if ($dry) {
    is_cli()
        ? stdout(sprintf('Would have repaired %d rows in table %s. Execute again with --fix to repair the data', $repaired, $table))
        : stdout(sprintf('Would have repaired %d rows in table %s. Execute again with &fix=1&token=%s to repair the data', $repaired, $table, generate_token('plain')));
} else {
    stdout(sprintf('Repaired %d rows in table %s', $repaired, $table));
}

return 0;