Introduction

SQL Injection is one of the most well-known attack vectors and it poses a significant security challenge. The only way to understand the benefits and deficiencies of each solution is to understand the problem and the approach each solution uses to solve it.

The Problem

The best way to understand the problem is by looking at examples. To demonstrate how SQL injection works, here’s an example of a simple application.

Let’s assume our application is a simple web page that allows customer support representatives to see the credit card numbers of customers who call in.

For security reasons, the representative must enter the customer’s account number, first name, last name, and the last 4 digits of the customer’s social security number.

When all the information matches the customer’s data, the credit card numbers used by the customer will be shown.

Seems like a pretty secure design as the customer support representative has to get the information from a real customer to see their credit cards. How could a customer support representative use this form to get information about any other customer?

The problem is not in the conceptual design but in the actual implementation of the code.

To make this page work, the application running inside the web server needs to run a SQL against the database. That SQL might look like the above example.

However, this SQL seems like the correct SQL to run and there’s no apparent way to get credit card information without entering the customer’s information.

So what’s the problem with the implementation?

When the form is filled this way and the SQL is executed, the database compares the conditions against every row in the table.

Let’s assume the first row is for customer 1001 named JOE SMITH with SSN 5555:

• Is the customer number 0? No, it’s 1001
• Is the first name JOHN? No, it’s JOE
• Is the last name DOE? No, it’s SMITH
• Is the SSN 0? No, it’s 5555
• Is 1=1? Yes, 1 is always equal to 1.

In SQL, the order of operations will evaluate the AND before the OR, resulting in something like this:

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

The ultimate result of this comparison is TRUE! That means that the credit card number of JOE SMITH will be displayed.

Since this condition will be true for all the rows in the table, every credit card in the table will be shown.

Because we filled the form in a way the programmer did not expect and did not protect against, we were able to trick the application into showing us every credit card in the database.

Another complication an attacker might face is if there’s another AND condition after the injected code. In the above example, we injected into the account number that has several AND conditions after it.

The double dash (- -) in SQL, comments out the rest of the line, so any additional conditions can be easily disposed of.

In MySQL, # is another way to comment out the rest of the line with identical effect to – –

Another way of adding a comment to a SQL is by enclosing it in /* */. If a comment starts, most databases will consider it to be a comment until the end of the SQL (with any number of lines). So adding /* instead of – – will also comment out the rest of the SQL.

Another consideration in MySQL is that /*! */ is not a comment as the ! symbol disables the comment. This can be a useful means of bypassing security measures that ignore comments.

Another option is to comment out entire sections of the SQL and split the attack across multiple parameters.

In SQL, any text between /* and */ is a comment and is ignored.

The nice thing about this attack is that not only did it disable entire portions of the SQL, but it also split the attack across parameters making it more difficult to detect by WAF technology. More on that later.

What if the vulnerable form doesn’t access the table or columns we want? Let’s assume we want to read the SSN column from the USERS table because that table contains the full social security number and not only the last 4 digits.

How can a form that reads the cards table show us information from a completely different table?

The above SQL uses the UNION operator to combine the output from two different SQLs into one result set:

In this case, the first SELECT returns no data, but the second will dump the entire SSN column from the USERS table. The union operator will combine both into a single result set and the application will display all the rows in the SSN column as if they were credit cards.

In Microsoft SQL Server and Sybase, SQLs are sent to the database in batches. A batch is a sequence of SQLs sent to the database in a single request, and all requests are batches.

You can use a semi-column to separate SQLs in the batch:

SELECT c1 FROM t1; SELECT c2 FROM t2

However, the semi-column is optional and this batch will yield the same result:

SELECT c1 FROM t1 SELECT c2 FROM t2

In these databases, SQL injection can take on an entirely new level of malevolence. The injected fragment can run an entirely different SQL allowing it to change data, alter the database, and more.

This example assumes the application is connected to SQL Server with a privileged account. Note that the semi-column is optional but used for clarity

When executed, no credit cards will be returned but a new user called ‘hacker’ will be created in the database with the password ‘password’.

This method can be used to modify data, permissions, and much more. Anything that the application account has privileges to do can be done via this type of attack. The attacker can even execute the shutdown command and shut down the database.

If complete control over the database isn’t enough, SQL Server also has the xp_cmdshell procedure that allows running operating system commands and xp_regread that can read the registry. This grants the attacker complete access to the machine.

While applications should never use a privileged account in any database, this rule becomes extremely important in SQL Server databases. Application using SQL Server databases must never use a privileged account. Unfortunately, many applications in SQL Server use the highly privileged SA account.

So far we’ve mostly discussed using OR 1=1. That is the classic example that is always used and it might create the impression that if a SQL does not contain this expression there is no SQL injection.

In reality, there are endless variations on conditions that are always true and this is just a little taste of those.

We’ll start with trivial examples that use simple operators:

We can also use some database functions to create slightly more complex expressions:

However, all these examples use literals (constants). If we had a tool that was smart enough to calculate every expression the database can perform, it would be able to determine that these expressions are always true.

This means that in theory, it could be possible to detect that the above examples are SQL injections. But that just means we need to take another tiny step forward.

The expressions below use column values and are, therefore, no tool can calculate the expressions to determine they are always true. It is impossible to distinguish these expressions from proper application logic.

While these expressions will probably always be true, it’s impossible to determine that without understanding the data in each column. There will be additional discussion on this subject in the section on static analysis.

All the examples so far entered data into fields in the form. This is the most trivial way to interact with the application, but far from being the only one.

The above HTML form will usually send the query to the application web server using a GET or POST request method. A GET request might look like:

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

You’ve probably seen strings like that in the URL of your web browser. The same GET request can also be sent using AJAX in a way that is not visible in the URL but can be seen just as easily in the developer console (try hitting F12 in your web browser).

POST requests are usually formatted the same as GET requests but are not sent in the URL. They can also be seen easily in the developer console of any web browser.

When attackers hack, they usually send GET and POST requests directly. This allows them to manipulate the fields in ways that the application form might not allow.

These non-trivial inputs are slightly more difficult to identify and modify, and as a result slightly more difficult to exploit. However, this lack of accessibility also means that developers are unlikely to properly protect or test them.

A few examples of information that cannot be edited in a form but can be changed in a GET or POST request (among other ways):

• Hidden input fields – these are input fields that are not visible to the user. Applications use these to keep context information about the state of the application or the user’s flow through it. While the user cannot see these fields on the screen, it is not difficult to modify their content.
  For example, a session ID could be stored in a hidden field and the application could use it in a query to the database.
• Select fields – These are single-choice combo boxes or multiple-choice selections. While the user can only select from the options displayed, an attacker can send any value.
  For example, if you can only choose between two options that send a value or 0 or 1, an attacker can send the value of 0 or 1=1.
• Links – We often click on links, buttons, trees, images, and other components in web applications. These links often have values embedded in them, and those values can be easily manipulated by an attacker.
  For example, the “Next Page” link we often see would have embedded in it the first item number of the next page (e.g. start=10). That first item number could be modified to include a SQL injection.
• Cookies – Cookies are another way of storing state information in the web browser and sending it to the web server. For example, this is the standard place applications use to keep session IDs.

These types of inputs are never tested by QA or by regular users and most automated vulnerability detection systems will also fail to test these. This means that flaws in these types of inputs can lie dormant for years without even being noticed or addressed.

As a result, these non-trivial inputs tend to be far more likely to be vulnerable and provide fertile ground for targeted attacks by experienced hackers.

One type of attack that can only be accomplished by manipulating the GET/POST requests is Parameter Pollution. For example, what happens if the above application receives two SSN fields in the request instead of one? In other words, the GET request would look like:

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

The answer depends on the technology stack used by the application and the methods used to extract the parameters. Some technologies will return a comma-separated list of the duplicate parameter. So the above request will return:

5555,7777

This creates an opportunity for an attack similar to Parameter Fragmentation (HPF) but without requiring multiple parameters or knowing their order in the SQL. For example, sending these 4 SSNs (broken into lines for clarity):

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

Will result in this SQL:

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

That is a fragmented SQL injection attack using a single parameter.

So far all the attacks used SQL keywords like OR and UNION. Even though they are simple words in English that could appear in normal data they make the input look suspicious.

But with a little investigation and effort, it’s possible to create a SQL injection that has no keywords or even spaces.

In this example, we just check whether a column is equal to itself and comment out the rest of the SQL. Since the account is always equal to itself, this SQL will return all the cards in the table.

Another repeating motif in these examples has been the usage of comments. We many times used comments to remove unwanted portions of the SQL. But there’s another way.

We highlighted the OR in blue. In the order of precedence, ORs are last so anything separated by an OR is evaluated separately. The red portion always evaluates to true, and so the entire WHERE clause is always true.

One approach for defeating some defense systems is to create confusion about the nature of the parameter.

The Account parameter doesn’t look like numerical input and automatic defenses that aim to validate it as a string will consider this to be a safe string to execute as it has no tags (‘) that terminate the string.

cast() is a standard database operator that converts the number to a string. The database will consider this to be valid input and convert the string back to a number to compare it to the account.

One option is for the query to validate the password. If the password matches the row is returned, otherwise, nothing is returned.

The SQL will contain the hash or a hash function in the where clause. When filling out the login screen like in the above example, the password-validating portion of the SQL is disabled.

This double dash (- -) will comment out the rest of the SQL and the row for admin will be returned suggesting that the password has been authenticated.

Another option for validating passwords is to retrieve the hash from the database and validate it in the code.

This SQL will return the MD5 of the word ‘password’. When the application compares this md5 to the password we provided they will match. We simply return the password hash we want instead of whatever is stored in the database.

Solutions in the Code

As mentioned above, the best solution is to have high-quality code and proper coding techniques that avoid the possibility of SQL injection.

The best way to avoid a SQL injection attack is to use bind variables. This is the perfect solution and it completely prevents SQL injections. Slightly less robust methods include escaping input and input validation. It is also recommended to reduce client error reporting.

More on all these techniques, their value, limitations, and how to implement them later.

Database Solutions

SQL injection is ultimately a database attack that takes advantage of the flexible nature of SQL. Therefore, the most obvious place to look for a solution is in the database.

Several technologies can help detect and/or prevent SQL injection in the database. The problem with most is that they are not effective. Unlike most other database solutions for detecting SQL injection, the unique technology in Core Audit is extremely effective.

More on these various database technologies and their differences later.

Web Application Firewall (WAF)

The difficulty in identifying SQL injection at the database level created a new product category — Web Application Firewalls (WAF).

The idea is that it would be easier to detect the SQL injection attack when examining the application input rather than when looking at the SQL that was sent to the database.

It’s a nice theory, but it only works on simple or specific examples. When looking to prevent all the different types of SQL injection attacks described in this paper the problem becomes insurmountable. More on that later.

In-Application Firewall

A new technology developed by Blue Core Research aims to take WAF technology to the next level and incorporate it into the application.

The Core Audit Java Agent provides detailed auditing data about everything that happens in the application. It can also apply firewall-type blocking rules around the entire application or sensitive areas in the code.

An In-Application Firewall can benefit from context and information that are only available inside the application to protect the right methods correctly.

Detect

This wide range of approaches don’t only differ in their technology and effectiveness, but also in the value they deliver. However, there is also an inverse correlation between value and effectiveness.

Detective approaches aim to alert you of a possible SQL injection. The downside is that they don’t block it and you’ll need to take action against the attacker as well as fix the code.

The perceived value seems to be lower but the benefit is that the detection can be tuned to be sensitive and provide a fairly effective mechanism. A powerful detective engine such as the one found in Core Audit is likely to notify you of any SQL injection attempt.

Prevent

Preventive approaches are divided into two primary categories: blocking and curing. Depending on the type of solution both options might be available, like in the Core Audit Java Agent.

The benefit of blocking is that SQL injection attempts that can be recognized by the blocking engine will be prevented.

While there are significant differences between various products, none can claim to automatically pass all legitimate activity and block all attacks. More on that later.

In addition to their limited effectiveness, preventive solutions run the risk of blocking legitimate activity. The more versatile the activity and the more strict the blocking, the more likely it is that some valid activity will be blocked.

The Core Audit Java Agent, for example, offers calibration between lenient and strict methods so that you can apply the appropriate level of filtering to the relevant activity.

Sanitize

Finally, some solutions aim to cure the problem without blocking it. With these solutions, malicious injections are removed from the input and allowed to run safely. Core Audit also has this capability.

While this is a very nice advertisement, in many solutions it comes at a heavy price of introducing additional vulnerabilities. The problem is that an attacker can craft an attack that will leverage the sanitizing process as part of the attack.

This means that In addition to the limited effectiveness of SQL injection detection, using sanitizing can reduce the effectiveness of the solution by offering the attacker additional tools to bypass the security. More on that later.

Escaping the Parameter Cage

When looking at the various examples it becomes clear that SQL injection is about changing the SQL contract by escaping the parameter cage.

If the parameter is a string, then a SQL injection will need to terminate the string before it can modify the SQL. That usually means having a tag (‘) in the input.

Numerical parameters are terminated by whitespace (space, tab, newline, etc.) or other characters that are not part of the literal (for example, a /).

Detecting an escape from the parameter cage is one method for recognizing SQL injection in application firewalls but it relies on correctly identifying the parameter type.

Another common method is the identification of suspicious keywords or special characters. This method also has significant limitations.

The concept behind WAF is to examine the user input before it’s incorporated into the SQL. The idea is that a SQL fragment will look suspicious in the user input but impossible to detect once it blends into the complete SQL.

For example, having OR first!=last in the where clause of a SQL could be a normal part of the application logic and impossible to distinguish from it. However, that phrase would be highly suspicious in regular user input.

Many hackers are lazy and use SQL Injection scanning tools. These tools go through every parameter on every page searching for potential vulnerabilities. Accordingly, many WAF implementations turned from something that protects against SQL injection to something that detects the type of unusual input these tools generate. This isn’t protection against SQL injection nor is it effective since there are many tools (including custom ones).

However, this brings up an interesting question – Why purchase a WAF that checks user input against known patterns in scanning tools when you can find all those vulnerabilities yourself? All you need to do is run a SQL injection scanning tool. These tools are available on the internet for free.

There are several fundamental problems with this concept:

• SQL Injection doesn’t have to look like a SQL fragment — A SQL Injection could be as simple as “id –” (see the section on Keywords). It doesn’t have to have a keyword like OR, UNION, etc. It doesn’t have to have special symbols like = or !=. It could be something that will trick even a human inspection of the input.
• Lack of Grammar — Unlike SQLs, user input doesn’t follow any predictable grammar. There’s no way to know what the input means or why the user entered it. This makes any type of static analysis extremely limited and entirely based on heuristics, signatures, and search patterns.
• Different parameters have different meanings — User input varies a lot across different parameters in the application and across all applications. Sometimes it’s a page number, sometimes a search request, and sometimes someone’s name. Therefore, it’s difficult to define rules that are always true for any input.
• Safety is code dependent — While some parameters may be vulnerable to SQL injections, others might be properly handled and safe. The input O’BRIAN might be suspicious because it has a tag (‘) and, therefore, is trying to search for code vulnerabilities. At the same time, it might be safe if the code handles this particular input properly.

Blue Core Research developed a new type of technology that can supplement or replace a WAF. This technology runs inside the application instead of outside and offers control over the protection applied to different parts of the code without changes to the source.

The Core Audit Java Agent uses native JVM capabilities to add security at runtime to the regular JAR files you’re currently using.

The Core Audit Java Agent can add several types of security, but since this paper is about SQL Injection, we will only go into detail about the Basic Blocking capabilities that aim at addressing this threat.

The types of security the Core Audit Java Agent can add are:

• Auditing (L2-3) — Full Capture of the application sessions and the activity they perform. Core Audit native application auditing levels 2-3 include report generation, alerting, forensics, analysis of behavioral profiles, and more.
• Basic Blocking — Apply WAF-style firewall rules at various points in the application. More details below.
• Blocking Policies (L4) — Apply custom rules about who can do what in the application. For example, restrict which IPs or users can run what activity at what times.
• Reactive Blocking — Configure policies that automatically block IPs or users with too many failed logins or errors across multiple application servers.

One of the benefits of WAF is that it filters the activity before it reaches the application. The In-Application firewall cannot make this claim.

However, since WAF always runs outside the application, it’s missing the context in which the activity runs, and the ability to apply different filters at different depths in the application stack.

The In-Application firewall offers granular control over the types of filters so they can meet the specific needs of each portion of the application. There’s no need to compromise on a filter that is “generally okay” and doesn’t give the appropriate level of protection.

The In-Application Firewall runs within the application so there’s no need to deploy additional machines and filter the network traffic. It all runs on the same machine and as part of the application.

The In-Application Firewall comes with full application auditing delivered by Core Audit Levels 2-3 and can be upgraded to apply context-based blocking policies or reactive policies with Core Audit Level 4. All these capabilities are only possible with the Core Audit technology stack.

SQL injection is a difficult attack to combat, and rewriting the application using bind variables is the only true solution to the problem.

If converting the application to entirely use only bind variables is not a viable course of action, Core Audit technologies in the application and the database are the most effective in detecting and preventing possible SQL injection attacks.

Each independent approach has limitations, so the best solution is a mixed approach of improving the code, deploying effective detection in the database, and preventive measures in the application.

Beyond SQL injection, Core Audit technologies can help secure databases and applications to ensure your sensitive data is not compromised by external or internal actors.