SQL Injections

An in-depth analysis

An SQL injection, or SQLi for short, is a type of cyberattack in which an attacker injects malicious SQL code into an application in order to manipulate the underlying database. SQL (Structured Query Language) is a language used to query and manage data in relational databases.

Imagine a website that has a simple login form. When you enter your login details, this information is usually sent to a server, which then executes an SQL query to check whether the specified data exists in the database.

An attacker could try to enter a special string that modifies the SQL query instead of a normal user name and password. For example:

' or '1'='1

If this string is included in the SQL query, the resulting query looks something like this:

SELECT * FROM users WHERE username = '' or '1'='1' AND password = '...'

As the condition '1'='1' is always true, the query would return all users from the database, regardless of the password entered.

SQL injections are one of the most common and dangerous web application vulnerabilities. By injecting malicious SQL code into input fields, attackers can manipulate the underlying database and extract or modify sensitive data. In this article, we will take a closer look at the different types of SQL injections and explain them using examples.

SQL injections can be divided into different categories based on the way in which the attacker extracts information from the database:

In-band SQL injections

With in-band injections, the results of the attack are displayed directly in the application output. This is the simplest type of SQL injection.

Example: Error-based SQLi

Suppose an application uses the following SQL query to check user names:

SELECT * FROM users WHERE username = '$username'

An attacker could simply enter an apostrophe for $username. The resulting query would then be:

SELECT * FROM users WHERE username = '''

The SQL query would be invalid and the database returns an error message. If the the application forwards the error message to the attacker, the attacker can use it to gain information information, e.g. about the technologies used.

Example: Boolean-based SQLi

As shown at the beginning of the article, an attacker can also enter the following value to display all results to be displayed:

' or '1'='1

Example: UNION-based SQLi

An attacker can also try to combine the results of two queries (UNION).

myuser' UNION SELECT version(),current_user()--

This query would return the database version and the database user in MySQL, provided that the number of columns matches. The resulting SQL query is the following:

SELECT * FROM users WHERE username = 'myuser' UNION SELECT version(),current_user()--'

Blind SQL injections

With blind SQL injections, the attacker does not receive any direct feedback, but has to extract the database bit by bit through clever queries.

Example: Boolean-based blind SQLi

The attacker tries to determine whether a certain condition is true or false by analyzing the response time or the response code of the application.

' AND (SELECT ASCII(SUBSTRING(password, 1, 1)) FROM users WHERE username='admin')=105--

This query checks whether the first character of the password of the user "admin" has the ASCII value 105.

Time-based blind SQLi

Similar to Boolean-based injection, the response time of the database is analyzed here. The attacker inserts a delay into the query, which is only executed if a certain condition is met.

' AND IF((SELECT COUNT(*) FROM users WHERE username='admin')>0, SLEEP(5), 0)--

If the user "admin" exists, the database will sleep for 5 seconds, which is noticeable in the response time.

Out-of-band SQL injections

An out-of-band SQL injection (OOB SQLi) is a type of SQL injection attack in which the attacker does not receive the results of the query directly in the application's response. Instead, the attack triggers an action that sends data to a server controlled by the attacker.

However, these attacks are rather rare and very complicated to execute.

Countermeasures

To protect against SQL injections, parameterized queries or prepared statements should be used. This separates user input from SQL code and the user input is not interpreted by the database.

In addition, the following measures can be implemented:

• Stored Procedures: Encapsulate frequently used SQL queries in stored procedures.
• Least Privilege: Only grant users and applications the minimum required permissions.
• Web Application Firewall (WAF): A WAF can help to ward off SQL injections and other attacks.
• Input validation: Check if the data provided by a user meets specific criteria.
• Input sanitization: Remove data that is potentially harmful.

However, you should be careful with input validation and sanitization, as valid content could be blocked. There are names that contain the apostrophe. If you use a blacklist that blocks or sanitizes the apostrophe, users with the name O'Neil, for example, will be affected.

SQL injections pose a serious threat to web applications. By understanding the different types of SQL injections and implementing suitable protective measures, developers and administrators can significantly reduce the risk.