Anomaly analysis uses behavioral analysis, helping you save time while expanding your control to vast activity volumes.

These capabilities are made possible by the unique security repository technology in Core Audit. The anomaly analysis engine dynamically creates behavioral profiles based on the activity captured by the security repository, allowing you to compare the present with the past. Contrasting current activity with historical behaviors quickly highlights differences indicative of security problems.

That lets you break the preconception that you can’t control large activity volumes like the database or application. Anomaly analysis empowers you to detect the needle in the haystack to find a single offensive SQL within billions.

How does it work?

Like all IT systems, database activity is repetitive. While values change, patterns persist. While the anomaly analysis engine is capable of more, most analyses center on five aspects:

1. New activity. Something seen today but not in the past. Like a new user, a new program, a new SQL, and more.
2. High activity volume. Activity that exists both now and in the past but happens now more.
3. Time of day – Activity that occurs at a different time now than in the past.
4. Combined dimensions. A change in a single dimension can be a new user. A change in multiple dimensions is a new user + IP combination. Even if the user and IP are known, that user may have never used that IP.
5. Filters. Focus different anomalies on different areas of interest. Like sensitive tables, the application, particular users, and more. Various subsets of the activity pose distinct risks, but they are also likely to exhibit different behaviors and, therefore, benefit from other types of anomalies.

There are many ways to select the type of anomalies to use. It could be behaviors you expect, patterns you observed in proactive forensics, the Core Audit wizards, the Core Audit Control Guide, or just trial and error. But one of the crucial features of the anomaly engine is that you can immediately test an anomaly and find the results. That makes choosing and tuning the anomalies a relatively simple process.

Benefits

We initially created the anomaly engine to help answer a common customer request. Looking at traditional declarative auditing, some customers said, “I don’t want to tell Core Audit what to report on. I want Core Audit to tell me what to look at.”

As the technology evolved, it helped secure subsets of the activity previously thought impossible to control. For example, to protect the application with its billions of SQLs.

Anomalies have also proven effective in detecting SQL injection and injection attempts. SQL injection, inevitably, causes the application to do something it isn’t supposed to do, thereby creating a new SQL construct that’s easy to identify.

Today, anomalies are a powerful tool to reduce reporting volume, securing huge activity volumes with a relatively low false positive ratio.

Final Thoughts

Anomaly analysis is vital to modern security, allowing you to do more with less. Control higher volumes from more sources with fewer people, less time, and a lower skill set.

Instead of pouring over endless reports, anomalies do much of the heavy lifting, quickly pointing you to potential problems.

While they’re not a magic solution that solves everything, anomalies can and should be a key element in any security strategy.

Talk to us to learn more and experience the Core Audit difference.

One of the popular myths about security is that you can get it out of the box. Just install something, and voila! You’re magically secured. But that never works.

Regardless of what you’re trying to secure, your first step should always be understanding the activity. You should know how the system is used, by whom, when, etc. Gaining this kind of visibility into a live production system is fundamental to figuring out how to secure it.

“You can’t secure what you can’t see.”

However, gaining visibility into IT systems is not simple. It also gets increasingly challenging when the systems process large activity volumes. Databases can process thousands of SQLs every second. All this activity is nearly impossible to understand without the right tools.

Core Audit is a comprehensive security solution that includes, among its many capabilities, the ability to perform proactive forensic investigations. These will give you insight into what’s happening in your database. That’s the first step we recommend when setting up your security.

Once you know what’s happening in your systems, you can design effective reports, configure alerts that make sense, define practical anomaly analysis, and more.

But proactive forensics is not just about setting up your security. It also allows you to identify gaps in your security measures. As activity evolves, we must adjust the controls to fit the new activity patterns, and it’s impossible to do that without visibility. Otherwise, your controls will gradually become outdated and eventually obsolete.

Proactive forensics also lets you identify poor security practices. People sharing accounts, connecting from insecure locations, dumping tables out of the database, and more. While not a breach, these popular bad practices increase your exposure and make a data breach more likely.

There are many motives for regular activity review, but they all share the same underlying reason. We should include people in the security process. No matter the reports, alerts, or automation we create, regular human review can easily flag behaviors and security needs that a machine never will.

Talk to us to learn more and try Core Audit to see the difference.

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.

On Sunday morning, we got an anomaly alert. It was March 19, 2023. This story is about what happened.

Background

The Blue Core Research website uses WordPress (a free and open-source content management system). WordPress usually uses MySQL as a backend database, and our installation is no different.

While our WordPress doesn’t contain sensitive data, we still protect its database with Core Audit. We do it both to protect our server and prevent a breach from internet attack, and to use our own software.

As you’ll see in this true story, protecting the database also protects the application. WordPress happens to be the application we protected, but this defense is effective for any application.

The Anomaly

Our Core Audit installation has a relatively simple configuration with daily anomaly detection alerts. We get a few false positives every few days, but on Sunday morning of March 19, 2023, we got an alert of a new error:

Access denied for user ''@'' (using password: YES)

It immediately raised a red flag in my mind. That’s because anomalies only alert of something different, like a change in the application activity profile. But a change that causes an access-denied error seems suspicious.

The Investigation

Core Audit anomalies rely on the Core Audit Security Repository. And this particular anomaly is based on the Reduced SQL portion of that repository. The Reduced SQL repository contains every SQL construct executed in the database at a 5-minute resolution.

Once I got the alert, I logged into Core Audit and looked at the relevant forensic view. I quickly found what I was alerted about:

Date:     Sat 2023/03/18
Time:     18:55 - 19:00
Count:    1 per 5 minutes
Rows:     0
Command:  ERROR TEXT
Activity: Access denied for user ''@'' (using password: YES)

That is an internal MySQL error resulting from a failed login. The user and host in this error are between single quotes (‘) and are, therefore, stripped from the Reduced SQL repository. Striping literals is part of how the Reduced SQL repository operates.

My next stop was to look for the failed session that caused it. Switching to the session forensic view, I found this session:

Start:    2023/03/18 18:55:59
End:      2023/03/18 18:55:59
Type:     No Login
Username: username_here
Machine:  localhost

That seems unusual since we don’t have a user in the database called username_here. That is, obviously, an invalid username. My next stop was to look at the web server logs to see who or what triggered this unusual login.

Here is an excerpt from the Apache log:

[18:55:36] GET /.wp-config.php_copy
[18:55:38] GET /.wp-config.php.rar
[18:55:39] GET /.wp-config.php.7z
[18:55:41] GET /.wp-config.php.tmp
[18:55:42] GET /.wp-config.php_tmp
[18:55:44] GET /.wp-config.php.old
[18:55:46] GET /.wp-config.php.0
[18:55:47] GET /.wp-config.php.1
[18:55:49] GET /.wp-config.php.2
[18:55:50] GET /.wp-config.php.zip
[18:55:52] GET /.wp-config.php.gz
[18:55:53] GET /.wp-config.php~
[18:55:55] GET /wp-config.php.templ
[18:55:56] GET /wp-config.php1
[18:55:58] GET /wp-config.php2
[18:55:59] GET /wp-config-sample.php
[18:56:00] GET /wp-config-backup.txt
[18:56:02] GET /wp-config.php.orig
[18:56:03] GET /wp-config.php.original
[18:56:05] GET /wp-license.php?file=..%2F..
[18:56:06] GET /wp-config.save
[18:56:07] GET /wp-config.txt
[18:56:09] GET /wp-config.dist
[18:56:10] GET /.wp-config.php.swo
[18:56:12] GET /wp-config%20copy.php
[18:56:12] GET /wp-config_good
[18:56:14] GET /wp-config-backup
[18:56:15] GET /wp-config-backup.php
[18:56:16] GET /wp-config-backup1.txt
[18:56:18] GET /wp-config-good
[18:56:19] GET /wp-config-sample.php.bak
[18:56:21] GET /wp-config-sample.php~
[18:56:22] GET /wp-config.backup

The culprit is clearly wp-config-sample.php, and looking inside this PHP file, we can find these lines:

define( 'DB_USER', 'username_here' );
define( 'DB_PASSWORD', 'password_here' );
…
require_once ABSPATH . 'wp-settings.php';

We now understand what happened: someone scanned the website for vulnerabilities and called wp-config-sample.php. That PHP script contains an invalid user and password that triggered a failed connection to the database. Core Audit detected this unusual behavior and raised an alert.

WordPress?

While many won’t consider WordPress a good example of application security, we think it’s valuable because of several important factors:

3rd Party & Supply Chain

First and foremost – WordPress is a 100% third-party application. We didn’t code it, have not reviewed the source code, and have almost no knowledge of the code, the data model, or any other aspect of this application.

Being open-source software, WordPress is exposed to both vulnerabilities hackers may discover in the source code and to supply chain attacks.

To make matters worse, WordPress websites almost always use plugins. Much of the power of the WordPress platform is in its extensibility by the tens of thousands of plugins that are out there. Plugin use significantly expand the surface area of 3rd party software and supply chain attacks. Plugins mean a lot of additional unknown code from multiple vendors or developers, different coding standards, enhancements to the data model with more tables, and more.

In other words – WordPress is what you might consider and extreme example of a 3rd party application and protecting it is more challenging than most other applications.

Dynamic SQL

WordPress takes the concept of dynamic SQL to an extreme. Not only do all the SQLs embed literals, but SQLs are often dynamically generated with where clauses created on the fly based on user input.

The challenge is not only dynamic SQL attacks but also the large SQL vocabulary where it’s impossible to predict all the SQL combinations WordPress may generate.

Again, plugins worsen the problem due to an even larger and unknown SQL vocabulary and more potential vulnerabilities in the code that generates those dynamic SQLs.

Popular & Internet facing

WordPress is very popular and is an internet-facing application. Hackers are, therefore, highly incentivized to find vulnerabilities. Exploiting such vulnerabilities can also be easy since the internet is full of WordPress websites with little or no additional security.

Bottom line – we provided effective defense to a large, dynamic, and extensible application like WordPress without relying on known vulnerabilities or the specifics of the application.

Resolution

Once we knew what the attack was, the next challenge was finding a way to protect the application from this and similar attacks.

In WordPress, the file the was attacked (wp-config-sample.php) is part of the WordPress installation package. The WordPress installation process copies that file into wp-config.php and defines some parameters like the database user and password in the copy.

Therefore, wp-config-sample.php is expected to exist in any WordPress installation. And if the WordPress development team has done its job right, there is probably no means of breaching WordPress by calling it.

The attacker was clearly targeting some vulnerability in the code. And even if that vulnerability doesn’t exist in our installation today, as we all know, bugs exist, and zero-day attacks can always occur. Having that file sitting around seems like asking for trouble. That file serves no purpose in WordPress other than as a template for wp-config.php, so shouldn’t we delete it?

That takes us to another WordPress behavior – the WordPress update. When WordPress updates, it overwrites all the files that are part of the WordPress installation package. Therefore, deleting wp-config-sample.php will be undone as soon as WordPress automatically updates.

Another option is to leave the file in place but change file permissions so that Apache can’t access it. That would prevent that PHP script from executing, but may also cause errors when WordPress attempts to update and overwrite it.

The best solution might be to deny access to it using .htaccess:

<Files ~ "wp-config-sample.php">
deny from all
</Files>

Going Further

As previously explained, wp-config-sample.php is used to create wp-config.php during installation. That means that the copy (wp-config.php) could also be vulnerable to the same attack or other similar zero-day attacks.

More troubling is that if a WordPress update fixes a vulnerability in wp-config-sample.php, that fix will not update in the wp-config.php copy since that copy is never modified.

wp-config.php is meant to be included by other WordPress files like index.php. After defining the database connection parameters, the script calls wp-settings.php to set up the WordPress environment, including a database connection. That’s how we got a connection to the database during the attack.

But wp-config.php is never meant to be called directly. To protect it against direct execution attacks like we saw in this attack, we can add a line at the top of wp-config.php to stop the processing on direct execution:

if (get_included_files()[0] == '/path_to_site/wp-config.php') exit();

Final thoughts

Despite the challenges presented by protecting an application like WordPress, Core Audit anomaly analysis provides effective security. It has a reasonably low false positives level and, in this case, has alerted of an attack against the application.

It’s important to note that Core Audit doesn’t have special support for PHP or WordPress. It doesn’t use signatures and this attack could have been a zero-day vulnerability. Also, our configuration wasn’t looking for any particular vulnerability in the database or the application. We only had a general-purpose anomaly detection that alerted us as soon as the application behaved differently. That was enough to identify the attack.

As you’ve seen, monitoring changes in the database activity profile of the application lets us detect many changes in the application behavior. While some changes occur naturally, others indicate a security problem or an attack.

• The Problem
• Solutions
• Best Practices
• Database Solutions
• Web Application Firewall (WAF)
• In Application Firewall
• Contact the Author

Application Example

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 5555

The Attack

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 0 OR 1=1

(FALSE and FALSE and FALSE and FALSE)
or
(TRUE)

The String Variation

The database will consider ssn = ‘5555’ as a valid comparison and automatically convert the string ‘5555’ into the number 5555. So maybe if we embed the value as a string things would look different. Let’s fix the application to run this SQL:

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’
  AND last = ’Doe’
  AND ssn = '5555'

In this case, the database will return an error if we fill the form with “0 OR 1=1” because it cannot convert this string into a number. Have we prevented SQL injection?

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = '0' OR 'a'='a'

With this slight modification, the attack will still extract all the credit cards because ‘a’ is always equal to ‘a’.

Truncating the SQL

SELECT card FROM cards WHERE
  account = $account2 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 5555

Parameter Fragmentation (HPF)

SELECT card FROM cards WHERE
  account = $account2 AND
  first = ’$first2’ AND
  last = ’$last2’ AND
  ssn = $ssn2

Splitting the SQL

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 1 UNION SELECT SSN FROM USERS

SQL Batch

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 0; CREATE LOGIN hacker WITH PASSWORD = 'password'

SELECT c1 FROM t1; SELECT c2 FROM t2

SELECT c1 FROM t1 SELECT c2 FROM t2

Variations

Non-Trivial Input

…/cards?id=0&first=JOHN&last=DOE&ssn=5555

Parameter Pollution (HPP)

?id=0&first=JOHN&last=DOE&ssn=5555&ssn=7777

5555,7777

…/cards?id=0&first=JOHN&last=DOE
&ssn=0 OR /*
&ssn=*/ 1 /*
&ssn=*/ = /*
&ssn=*/ 1

SELECT card FROM cards WHERE
   account = 0 AND
   first = ’JOHN’ AND
   last = ’DOE’ AND
   ssn = 0 OR /*,*/ 1 /*,*/ = /*,*/ 1

No Keywords

SELECT card FROM cards WHERE
  account = $account2 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 1

Truncating without Comments

SELECT card FROM cards WHERE
  account = $account2 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 1

Misleading Numerical Inputs

SELECT card FROM cards WHERE
  account = cast(1 as varchar(10)) OR 1=1 AND
  first = ’John’ AND
  last = ’Doe’ AND
  ssn = 1

Login Screens

Login screens are also vulnerable to SQL injection attacks. There are two ways a user can be verified by the application, and either can be breached.

Query Validates Password

SELECT username FROM users WHERE
  $w

Query Retrieves Password

SELECT password FROM users WHERE
  username = '' union
  select '5f4dcc3b5aa765d61d8327deb882cf99'

Introduction to Solutions

It is important to understand that SQL injection is, fundamentally, an exploitation of a security flaw in the code. Just like there are no guaranteed ways to ensure the code has no bugs, it is difficult to ensure the code has no SQL injection vulnerabilities. However, just like with any other bug, the best solution is to fix the flaw in the code. The alternative is to try and prevent, detect, or make it more difficult to exploit the vulnerabilities, but all these approaches have limitations.

The next sections will review different approaches and discuss the benefits and limitations of each. But before diving into the details, let’s perform a high-level review to get the lay of the land.

Solutions in the Code & Best Practices

As mentioned above, the best solution is to have good code. Proper coding techniques can reduce and even eliminate the risk of SQL injection. This section will discuss these in detail.

Bind Variables

SELECT card FROM cards WHERE
  account = :1 AND
  first = :2 AND
  last = :3 AND
  ssn = :4

Escaping Input

SELECT card FROM cards WHERE
  account = 0 AND
  first = ’John’ AND
  last = ’O''Brian’ AND
  ssn = 5555

Numeric Input

$sql .= " id= ".$_GET["ssn"];

$sql .= " id= ".intval($_GET["ssn"]);

sql += " id = "+ssn;

sql += " id = "+Integer.parseInt(ssn)

Input Validation

if ($_GET['ssn'] > 0 &&
$_GET['ssn'] < 9999) return;

$sql .= " id= ".$_GET["ssn"];

Error Messages & Blind Injection

Errors & Vulnerability Detection

Database Solutions

SQL injection is ultimately a database attack that takes advantage of the flexible nature of SQL. This section will discuss various technologies that can help detect and/or prevent SQL injection in the database.

Static SQL Analysis

AI & SQL Intention

Anomaly Analysis

Web Application Firewall (WAF)

As mentioned above, Web Application Firewall is a technology that was born to deal with the threat of SQL injection. It started when the only other technology at the time was static SQL analysis which was quickly proven to be ineffective.

Some vendors go as far as claiming they can fix the SQL injection problem without fixing the application code. These claims are very appealing to customers, but they are entirely false. These vendors market their solutions as WAF which includes the word “Firewall” in the title and gives customers a false sense of security.

The idea behind WAF was that while it is impossible to detect SQL injection through static analysis of the SQL sent to the database, it should be possible to detect the injection when doing static analysis on the input sent to the application. That’s a nice theory, but the examination we’ll perform below will show it is incorrect.

WAF Concept

WAF Methods & Challenges

All WAF solutions face the same basic challenges. The difference between the tools is in how they balance those challenges and how good is the implementation.

Parameter Type

Special Characters

Keywords

Mixed Strategy

Automatic or Configurable

Implementation

The WAF Sanitizer

1 O/**/R 1/**/=/**/1
un/**/ion SEselectLECT * F'RO'M

1 OR 1=1
union SELECT * FROM

In-Application Firewall

Filter & Parameter Control

Depth Control

Context & Action Control

In-Application Firewall or WAF

Summary

More about Core Audit Contact Us