How is an SQL injection carried out

Security

Relational database management systems (RDBMS) such as Oracle, MySQL and Microsoft SQL Server are among the most popular on the market according to the current DB Engines ranking. Since they are considered to be very reliable and avoid inconsistencies in the data records, they have been the established standard for databases in most companies for decades.

The Structured Query Language (SQL) database language is generally used to query and process the data in it. For example, users communicate via a product search screen in a web shop with a server, which in turn queries a database and sends the results back to the web shop as search results.

In this way, the stored information is susceptible to so-called SQL injection (SQLi), which injects arbitrary code into database queries. This makes it possible to read out or change information without permission. In the worst case, the intruders gain control of the entire database.

Simple long-runner

The attack method was discovered in 1998 and has been considered one of the most persistent threats ever since. Since 2010, SQLi has consistently taken first place among the top 10 security risks for web applications that are regularly published by the independent Open Web Application Security Project (OWASP). In August 2019, the exploit was at the top of the most exploited vulnerabilities in the monthly Global Threat Index of the Israeli security provider Checkpoint for the fourth time in a row.

Not wrongly. In March 2019, for example, a gap was discovered and closed in the online shop software Magento, which could be used to read customer data from the retailers' databases. In 2018 there was a glitch (a software or programming error that leads to malfunction of computer programs) on the website of the Canadian internet service provider Altima, which made it possible to use a so-called blind SQL injection (more on this later) to a access extensive customer database.

One reason why SQLi is so popular with hackers could be that it is a very simple attack. At the 26th DEF CON hacker conference, which took place in Las Vegas in 2018, an eleven-year-old child was able to hack and manipulate a copy of the website for displaying the election results in the US state of Florida in just ten minutes via SQLi.

On the other hand, the defense measures are as simple as they are effective. In the following, different types of SQLi are described and options for defense are presented.

Shady guys

Regardless of the type of SQL injection, the attacker infiltrates any SQL code into the database query of a web application. This can happen in a number of ways.

The simplest form of attack is via a User input. Web applications usually accept input via a form. The front end then forwards the input to the database in the back end for processing. If the web application does not clean up the input, it is possible to delete, copy or change database contents via injected SQL inputs.

Attackers can too Change cookiesso that they infect the query of the web application. Cookies store information about the client status on the local hard drive. As a rule, web applications load cookies in order to process this information. A malicious user or malware can modify them to inject SQL commands into the backend database. Same is over Server variables like HTTP headers possible. Forged headers that contain any SQL can smuggle this code into the database if the web application does not clean up this input either.

Among the type of blind SQL injection There are attacks on web applications that do not actively fend off SQLi, but do not visibly output the results of the injection. Instead, they either show no obvious reaction or, for example, general error messages that the syntax of the SQL query is incorrect. In this case, the page does not provide any data, but it is displayed slightly differently depending on the results of a logical statement injected into legitimate SQL.

With this method, the information is therefore not identified directly, but via a series of true-or-false queries and extracted from the database. This method is considered to be very time consuming. However, as soon as the vulnerability and the required information have been found, the attack can be automated using a number of tools.

SQL injection attacks second order are among the most devious as they do not come into action immediately, but rather at a later point in time. Such harmful but inactive SQL commands can be correctly coded by the application and saved as valid SQL in the database. If another part of the application, which may not be protected against SQLi, then executes the stored SQL statement in a different context, the delayed attack starts.

Example of a simple SQLi attack

Suppose a company has built a web application in which customers can access their profile by entering their customer number. The front end of the app forwards the entered number to the back end. The database executes an SQL call there and sends the result back to the application, which shows it to the user.

So our user enters his number 1234567 in the frontend.

The query in the backend could look something like this:

SELECT *

FROM customers

WHERE customer_id = '1234567'

Let's assume that the user enters the following customer number in a field on the web form instead:

1234567 '; DELETE * customers WHERE '1' = '1

The database in the backend would then execute this SQL:

SELECT *

FROM customers

WHERE customer_id = '1234567';

DELETE *

FROM customers

WHERE 'x' = 'x'

Databases execute several SQL statements in sequence if they are separated by a semicolon (;). If the input is not sanitized for the single quotation mark ('), attackers could delete the entire table.

This is a deliberately simple example, but every SQLi works on the same principle: Uncleaned input via a web application causes arbitrary SQL code to be executed in the database.

Fight fire with fire

Because SQLi attacks are relatively straightforward, it didn't take long to automate them. Tools such as SQLninja, SQLmap or Havij are available to both attackers and defenders and enable companies to test their web applications for vulnerabilities against SQLi.

For example, SQLmap is a good option as it supports almost any major database, including MySQL, Oracle, PostgreSQL, Microsoft SQL Server, Microsoft Access, IBM DB2, and SAP MaxDB. It is available for all major operating systems and detects the most popular injection loopholes in web applications. This enables you to quickly determine whether the measures to clean the input are actually working.

Discover SQLi

There are two ways to detect SQL injection attacks. A web application firewall (WAF) can examine incoming traffic for manipulative input according to previously defined rules and, if necessary, block it. In terms of monitoring the database itself, an intrusion detection system (IDS) is useful. A network-based IDS monitors all connections to the database and sounds an alarm in the event of suspicious activity. A host-based IDS keeps an eye on the web server's log files and reports irregularities.

Prevent SQLi

A comprehensive and detailed description of all relevant protective measures can be found in the OWASP SQL Injection Prevention Cheat Sheet. In the following we present a selection of the most important ones.

As with the majority of all attack surfaces on corporate IT, the same applies here: Good patch hygiene solves many problems. Vulnerabilities in applications and databases that hackers can use for SQL injection are regularly uncovered and remedied. It is therefore important to keep patches and updates up to date at all times.

For the basic protection It is important to regulate the database access via SQL in such a way that without exception all entries are cleaned up before execution. This means not allowing unusual characters that indicate manipulation and using measures such as escape sequences to ensure that entries are not accidentally taken over as commands. It is also advisable to filter all entries by context. A telephone number, for example, should only allow numerical entries. So-called prepared statements prevent the purpose of a query from being changed, even if an attacker enters commands to the contrary.

In the event that there is a loophole despite cleaning, the following applies Account privileges from database users. The principle of minimum permissions should be applied. The application has just enough room for maneuver to do its job - no more. For example, the web application can only be given read but not write rights. In addition, commands such as "DROP TABLES", which deletes the entire database, should not be executable.

Also Stored Procedures, functions that execute a sequence of stored commands in the database with a single call make SQLi more difficult - if not entirely impossible. If the web application only has to execute a few SQL queries, stored procedures should be written for them. Usually only the database administrator is authorized to create and edit them. It is important to ensure that many databases already have ready-made stored procedures that the attackers are likely to be aware of. If these are not absolutely necessary, they should be removed.

Care is the best protection

SQL injection is one of the simplest attack vectors on company data and therefore even less experienced attackers can exploit it to cause great damage. But it is just as easy for companies to close this open flank. All you have to do is set up the communication between the web application and the database with a little care and implement a few protective measures.