SQL Injection: Targeted Measures Instead of Block Lists

Published by: MRT

Published on:

SQL Injection: Targeted Measures Instead of Block Lists

Web attacks mainly exploit two gaps: SQL injection and local file inclusion, whereby SQL injection still has a clear majority with more than two thirds of all web application attacks. That can be, among other things, studies to attacks on web applications, however, and to attacks on financial services remove.

Also the OWASP as publisher the top 10 riskiest web loopholes, lists SQL Injection at number 1 in the latest edition of 2017. Although the phenomenon has been known for more than 20 years, it does not seem to be able to be reduced.

In order to master the threat, numerous software manufacturers and operators still rely on so-called block lists (formerly blacklists), which specify no-gos for incoming inputs from outside. They serve as a basis for either blocking the entries, changing parts of them or removing them. The procedure works as a filter so that no malicious queries reach the database in the end.

This procedure is usually relatively easy to implement, as it initially only makes small changes to the application and does not involve any deep intervention. The side effects for the smooth operation of the existing software are manageable. However, it can only be a short-term approach, because experienced attackers can easily bypass the established blockades.

A demo application helps to understand the paths. The following code is a Java Spring Boot application (version 2.3.8) based on JDBC with a Maria DB backend. However, it can also be implemented in any other combination of a relational database with a framework or programming language that combines the user input as a string to form a query and then sends it to the database. The sample code can be found on GitHub. Developers can clone it with Git and in the directory sqli_victim_webapp_java view and test.

The Spring application has multiple REST endpoints, and one of them is /vulnbyid. The calling client sends an ID as a request parameter, which returns a database entry. The code initially composes the query as follows:

"SELECT * FROM user WHERE id = '" + id + 
  "' GROUP BY username ORDER BY username ASC"

The code then forwards this string to the database:

Connection c = dataSource.getConnection();
rs = c.createStatement().
  executeQuery(blacklist.getBlacklistedQuery());

A query for this endpoint looks like this via the command line:

curl localhost:5808/sqlidemo/vulnbyid -d id=1

That id Can be controlled by the user and flows into the query, enables a typical SQL injection. Attackers terminate the current one id-String and add their own part, for example with

1' AND 1=1 --

this results in the following query:

"SELECT * FROM user WHERE id = '1' AND 1=1 
  -- '" + "GROUP BY username ORDER BY username ASC"

-- introduces a comment in the SQL standard. It’s a common way of cutting off whatever comes after. The database ignores the rest of the line and errors can be avoided in the attack.

1' AND 1=1 -- ` vs `1' AND 1=0 --

Through the ANDLink, the attack checks whether an injection is possible. He uses Boolean algebra: the first part should provide the same feedback as a simple one id=1, and the second part should not return any result.

Once it has been verified that an injection works, the attack goes through two identification phases:

  1. Database
  2. Attack pattern

The database can be determined via feedback. This can take the form of error messages or positive syntax that runs through successfully.

Once the database has been determined, there are five different attack patterns:

  1. Error-Based tries to work on logical errors and, for example, to extract information when a condition fails.
  2. Blind has no return channel, so it runs blind and, for example, via the sleep function on the database and if-Conditions to determine individual characters.
  3. In Batched the database can execute commands in succession, for example separated by a semicolon in the injection string.
  4. The method Inline uses SELECTs in the FROM part of a query.
  5. Union-Based is after all the merging of two tables into one result. This allows legitimate feedback to be put together with data that users should not have access to.

Since attacks via batched and inline queries are mostly not possible with the MySQL selected for the demo application, error-based is often not easy to apply and blind is one of the more complex patterns, the following text only deals with the union-based attack pattern as an example.

Expand this year heise Developer, heise Security and dpunkt.verlag the conference for secure software development heise devSec at three theme days, and on July 1st everything revolves around the theme Web Application Security. Previously is on June 29th DevSecOps in focus. The early bird discount is currently still valid for both days.

If you want to give a lecture on secure software development, you should have until June 13th Call for proposals for the fall heise devSec organized for two days.

The attack first determined the number of columns by making the request so long ORDER BY increased until an error message appears:

curl localhost:5808/sqlidemo/vulnbyid -d 
  id="1' ORDER BY 1 -- "
curl localhost:5808/sqlidemo/vulnbyid -d 
  id="1' ORDER BY 2 -- "
curl localhost:5808/sqlidemo/vulnbyid -d 
  id="1' ORDER BY 3 -- "
curl localhost:5808/sqlidemo/vulnbyid -d 
  id="1' ORDER BY 4 -- "

Unknown column '4' in 'order clause'

Thus there are three columns. Now the question is which of these can be seen in the feedback on the website. In the demo application it is column 3:

The third column is determined as the return channel.

This is where the attack side can extract data. The following is an example of reading out the database version:

curl localhost:5808/sqlidemo/vulnbyid -d 
id="1' UNION SELECT NULL,NULL,(@@VERSION) -- "

The version number of the database could be read out via Union Select.

Disclaimer: This article is generated from the feed and not edited by our team.