Introduction

We got an alert two days before New Year’s. It was shortly after midnight on December 30, 2021. It was a daily anomaly alert relating to the database backend of an old website, but it was clearly an attack.

As it later turned out, this was the first of several such attack attempts. There were three more in January of 2022, two more in February, and one more in August, October, and November. All in all, nine similar attacks over the course of a year.

Background

We use WordPress (an open-source content management system) on many of our websites and always with a MySQL backend (the most popular setup). The website in question was old, and due to compatibility problems, we haven’t updated some of the software components in a while. So when I first saw this alert, it seemed highly plausible that there was a vulnerability that led to a successful attack.

As it later turned out, this was a false positive. The reason for this false positive was that in addition to an old version of WordPress and plugins, this website also used an old version of Core Audit. But more on this later.

None of our websites contain sensitive information, but as this paper will show, protecting the database of any application with Core Audit is a highly effective means of detecting attacks and protecting the application.

The Anomaly

The anomaly alert had 168 lines, and the first line was this:

SELECT * FROM wp_users WHERE user_login = '') UNION ALL SELECT NULL-- HupP'

While each line was different, every line started with one of these:

SELECT * FROM wp_users WHERE user_login =''

SELECT * FROM wp_users INNER JOIN wp_usermeta ON user_id = ID WHERE meta_key = '' AND user_login = ''

What made it clear that this was an attack were the many end variations of the SQLs that looked like these:

;SELECT SLEEP(9)#' LIMIT 9

) AND SLEEP(9) AND (\\''=\\''

WAITFOR DELAY \\'' AND \\''=\\'' LIMIT 9

;SELECT SLEEP(9)#'

);SELECT PG_SLEEP(9)--'

;SELECT PG_SLEEP(9)--' LIMIT 9

);WAITFOR DELAY \\''--'

;WAITFOR DELAY \\''--' LIMIT 9

) AND 9=(SELECT 9 FROM PG_SLEEP(9))

UNION ALL SELECT NULL-- HupP'

) UNION ALL SELECT NULL-- HupP' LIMIT 9

UNION ALL SELECT NULL,NULL,NULL-- iIWs'

UNION ALL SELECT NULL,NULL,NULL,NULL-- ynbe'

Bear in mind that the empty strings ('') and the 9’s are not part of the original SQL. They relate to how the Core Audit security repository operates. This repository automatically collects all the SQLs in the database, so to reduce storage space and eliminate anomalies from embedded literals, it automatically strips all the numbers and strings.

The attempts listed above were scanning for a SQL injection vulnerability. They were trying to detect whether SQL injection was possible and discover details that would facilitate an attack:

• The various sleep/delay statements would only work on particular databases. So a delayed response tells the attacker both that the injection was successful and the type of database used.
• The various UNION and NULL permutations were trying to determine the number of fields in the query and whether they could append data from other tables.

In addition to many more variations of SLEEP and UNION, there were other colorful expressions like:

) AND (SELECT 9 FROM(SELECT COUNT(),CONCAT (0x7178707671,(SELECT (ELT(9=9,9))),0x71717 67171,FLOOR(RAND(9)9))x FROM INFORMATION_SCHEMA.CHARAC

) AND 9=CAST((CHR(9)||CHR(9)||CHR(9)||CHR(9)||CHR(9))||(SELECT (CASE WHEN (9=9) THEN 9 ELSE 9 END))::text||(CHR(9)||CHR(9)||CHR(9)||CHR(9)||C

) AND 9=CAST((CHR(9)||CHR(9)||CHR(9)||CHR(9)||CHR(9))||(SELECT (CASE WHEN (9=9) THEN 9 ELSE 9 END))::text||(CHR(9)||CHR(9)||CHR(9)||CHR(9)||CHR(9)) AS NUMERIC) AND (\\''=\\'')

;SELECT DBMS_PIPE.RECEIVE_MESSAGE(CHR(9)||CHR(9)||CHR(9)||CHR(9),9) FROM DUAL--'

AND 9=CONVERT(INT,(SELECT CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+(SELECT (CASE WHEN (9=9) THEN CHAR(9) ELSE CHAR(9) END))+CHAR(9)+CHAR(9)+C

) AND (SELECT 9 FROM(SELECT COUNT(),CONCA T(0x7178707671,(SELECT (ELT(9=9,9))),0x717 1767171,FLOOR(RAND(9)9))x FROM INFORMATION_SCHEMA.CHARACTER_SETS GROUP BY x)a) AND (\\''=\\'')

) AND 9=CONVERT(INT,(SELECT CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+(SELECT (CASE WHEN (9=9) THEN CHAR(9) ELSE CHAR(9) END))+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9))) AND (\\''=\\''

These SQLs are clearly unusual and seem to attempt to bypass a SQL injection protection system like a WAF.

Forensics

We experienced an attack. The alert left no doubt about that. The two remaining questions were:

• Was the attack successful?
• Which part of the application was targeted?

Database Forensics

The first question was easy to answer. I started by locating the queries in the reduced SQL forensic view. The reduced SQL repository has a 5-minute resolution, and all these SQLs executed within the 5-minute window of 8:20 am to 8:25 am.

Once I located the queries, I also saw the good news – they were all successful (no errors), and the number of rows retrieved was zero for all.

Why are successful executions good news? Because this attack was trying to test whether the application was vulnerable to SQL injection. In this scan, most of the queries were supposed to fail, with the few successful ones indicating the method to exploit a vulnerability. For example, the SQLs with PG_SLEEP() could never be successful on our MySQL database since this function only exists in PostgreSQL databases. Therefore, successful executions mean the injection attempt failed to modify the SQL construct.

Additionally, these SQLs didn’t retrieve data, so there was no leak. While most of these SQLs were not attempting to extract anything, it’s comforting to know nothing was retrieved.

In other words – this attack failed to break through the literal boundary. We’ll get back to this subject later and also answer the more interesting question of why we got the anomaly alert in the first place.

Application Forensics

Now that we have more information, it’s easy to search the Apache logs for more details on the attack and what it targeted.

The attack was between 8:20 am and 8:25 am and accessed the wp_users table. Looking at the Apache log, we can see this attack started at exactly 8:20 am:

[29/Dec/2021:08:20:00] "GET /wp-login.php?log=1&pwd=-9696%20UNION%20ALL%20SELECT%2024%2C24--%20ptzf"

This attack was a GET request to the wp-login.php script. That is the WordPress login page. The attack delivered its injection attempt through the password field that, in this first attempt, included:

-9696 UNION ALL SELECT 24,24-- ptzf

The last attack attempt was using this POST request at 8:21:51 am:

[29/Dec/2021:08:21:51] "POST /wp-login.php?Phws%3D5963%20AND%201%3D1%20UNION%20ALL%20SELECT%201%2CNULL%2C%27%3Cscript%3Ealert%28%22XSS%22%29%3C%2Fscript%3E%27%2Ctable_name%20FROM%20information_schema.tables%20WHERE%202%3E1--%2F%2A%2A%2F%3B%20EXEC%20xp_cmdshell%28%27cat%20..%2F..%2F..%2Fetc%2Fpasswd%27%29%23"

The Apache logs don’t record the POST parameters, but we can see this payload on the request:

Phws=5963 AND 1=1 UNION ALL SELECT 1,NULL,'<script>alert("XSS")',table_name FROM information_schema.tables WHERE 2>1--/**/; EXEC xp_cmdshell('cat ../../../etc/passwd')#

This payload is a little funny as it contains a SQL injection with a cross-site scripting scan (alert(“XSS”)) and an attempt to have SQL Server execute a shell command (EXEC xp_cmdshell) with a Unix/Linux command printing the content of a Unix/Linux password file (cat …/passwd).

That is a mix of attack fragments that could never work together. And there are several other things wrong with this last request and payload.

That indicates the person running the attack had little understanding of what they were doing. Combined with the fact that the whole scan lasted just under 2 minutes, it suggests this was a script or, more likely, several scripts they downloaded off the internet and executed against various websites.

False Positive

So why did the attack fail, and why did we get an anomaly alert anyway?

A SQL injection attack attempts to modify the SQL construct by breaking through the literal boundaries. In other words, when a SQL contains a literal like this:

SELECT * FROM wp_users WHERE user_login = 'JOHN'

A SQL injection attack attempts to send a string other than “JOHN” so the SQL construct will change. For example, the string “X' or 'Y'='Y” will result in this SQL:

SELECT * FROM wp_users WHERE user_login ='X' or 'Y'='Y'

By changing the SQL construct and adding or 'Y'='Y', the database will run something not intended by the developer who wrote the code. Putting a tag (') in the input broke through the literal boundary and allowed the attacker to alter the SQL construct. Escaping literals before embedding them into a SQL prevents this vulnerability.

In the SQL standard, you can escape tags (') by using double tags (''). When escaped, the above example will yield:

SELECT * FROM wp_users WHERE user_login = 'X'' or ''Y''=''Y'

In this case, the database will compare the user_login field to the entire string, and the word OR is just part of the user name, not part of the SQL construct.

WordPress must have escaped the input correctly in our attack, and the SQL construct was not modified. That’s why the SQLs executed without error, and the attack failed.

But why did we receive an anomaly alert if the attack didn’t break the literal boundary?

Unlike other databases, in MySQL, there are two ways to escape tags in strings. The first is with double tags ('') according to the SQL standard. But there’s a second method of preceding the tag with a backslash (\'). WordPress uses the second method.

That’s where the old version of Core Audit comes into play. That old version was released shortly after the release of MySQL support, and the literal stripping in the Reduced SQL repository did not support the backslash escape method for MySQL databases. As a result, the old version thought the tags were not escaped and didn’t strip the literals correctly.

Once we discovered the unintended consequence of falsely detecting these SQL injection attacks, we purposely kept that old Core Audit version on that website. We wanted to see how many more failed attacks we’ll experience. As stated earlier, this old website experienced nine attempts over the following year. Once we upgraded the Core Audit version, we stopped receiving these false positive anomaly alerts.

Application attack surface area

You might wonder why someone attempted a SQL injection attack that doesn’t work against WordPress. The attackers, like anyone else, had access to WordPress. So why didn’t they know this attack would fail?

That is an interesting question that highlights the complexities of supply chain attacks in some 3rd party applications, like WordPress.

The first thought that comes to mind is that maybe some versions of WordPress are susceptible to this attack. However, SQL injection is a well-known threat, and the WordPress development team is strict about escaping inputs before embedding literals in SQLs. While using bind variables would be safer, web developers have a penchant for embedding literals.

However, it’s worthwhile noting that multiple versions and patches significantly increase the attack surface area. The reason is that most organizations upgrade and patch applications from time to time and can never be sure which vulnerabilities they eliminated or introduced and at what point.

But since it’s unlikely that any WordPress version was susceptible to a SQL injection on the login screen, that brings up another interesting feature of WordPress – Plugins.

A big part of the power and flexibility of WordPress are the tens of thousands of plugins that can extend it. These plugins are code that can attach to various places in WordPress and modify its behavior in almost any way imaginable.

Plugins offer power and flexibility to the users, but they also pose a security risk. WordPress plugins introduce additional vendors, developers, coding standards, changes to the database data model, new execution paths in the application, and, of course, new vulnerabilities.

The risk in plugins is not only vulnerabilities in 3rd party software but also the risk of supply chain attacks. Such attacks could be initiated by the plugin authors or by a hacker who altered their source code.

It’s unlikely that WordPress was ever susceptible to this attack, but it’s highly plausible that some plugin was. The attacker was probably targeting a plugin that was not installed in our WordPress.

Final thoughts

SQL injections are notoriously hard to detect and, even more so, to prevent. However, Core Audit anomaly analysis was able to easily alert on those attacks.

Not only was anomaly analysis able to detect the attack, but it did it without any attack signatures or support for PHP or WordPress. And, actually, without even looking for a SQL injection attack.

And that is the reason anomaly analysis is so effective against SQL injection – it’s not looking specifically for that. It’s searching for SQLs that are new to the application and are, therefore, suspicious. SQL injection can masquerade in many ways but, by definition, is not part of the SQL vocabulary of the application. Anomaly analysis will, therefore, always flag it as suspicious.