SQL injection vulnerabilities are probably just as old as the first databases. Despite their familiarity and proven countermeasures, we find this type of vulnerability again and again.
SQL injections occur when user input is used directly in database queries. This can be exploited to adapt the query (almost) at will, for example to read or manipulate data that should not actually be accessible.
The initial situation
SQL injection vulnerabilities are classics among web vulnerabilities. Measures are therefore often taken to prevent them. In our pentests, we see three cases:
- the issue has not been considered, so there is no protection. In this case, exploitation is usually easy.
- parameterized queries are used correctly, which protect against SQL injection vulnerabilities, more on this below.
- own attempts are made before further processing of user input, which are essentially aimed at 2. but unfortunately usually contain weaknesses.
The following is about the most exciting of the cases – case 3 – and the creative process of getting past the built-in hurdles to exploit the vulnerability.
The illustration shows just such an attempt. In line 6, words and characters are defined that are frequently used in SQL injection attacks.
In line 7, the defined words are removed from the received user input, in this case username and password. The resulting values are then inserted into the database query in line 10 and executed.
Filters and their typical weaknesses
The filter in the example is not as effective as it may appear at first glance. Several “tricks” can be combined to ultimately carry out a successful SQL injection. Let’s look at the following three steps:
Case sensitivity
Database queries are generally case-insensitive. SELECT can therefore simply be replaced by select. However, the implemented filter does not take the different spellings into account. Incidentally, a database also accepts mixed case, such as SeLEct.
Include the behavior of the filter
In some cases, the filter is case-sensitive. Often, however, the replacement of dangerous words and characters is only applied exactly once. So, what happens if the input contains SELSELECTECT, for example? After removing the inner SELECT, it remains and passes through the filter unhindered, as it is not applied to the result again.
Escaping quotation marks
Another hurdle in the implemented filter is the exclusion of the single quotation mark (‘). This is usually required to break out of a string context in the database query in order to execute the SQL keywords that have been injected. The two previous tricks cannot be applied to the quotation mark.
Nevertheless, the filter can be bypassed by using a backslash (\) to escape the quotation mark. The backslash is ignored by the filter and therefore remains in the user input. Escaping means that a character is considered to actually be this character and is not interpreted as part of the SQL query, for example to end a string context. The text ‘You\’re welcome.‘ is therefore used by the database as the string You’re welcome.
The vulnerability
But how can the implemented filter be bypassed with the weaknesses explained so that a manipulated database query can be executed?
The database query from the example has the following structure:
SELECT * FROM users WHERE name = '<username>' AND password = '<password>'When calling the above code, we select the parameters as follows
- username: MindBytes\
- password: or 1=1 — comment
The implemented filter does not treat the backslash (\) and the or safely and inserts both in the database query:
SELECT * FROM users WHERE name = 'MindBytes\' AND password = ' or 1=1 -- comment'For the database, however, this query has the following structure for interpretation:
SELECT * FROM users WHERE name = 'MindBytes\' AND password = ' or 1=1 -- comment'Since the quotation mark after MindBytes now is escaped, the WHERE condition takes on an unexpected form. The password parameter is then used to manipulate the database query. The SQL injection filter must be considered here and, for example, the lack of distinction of upper and lower case must be used to one’s own advantage. Furthermore, the end of the predefined database query is made ineffective by a comment (–).
The database query thus returns all entries from the users table, as we were able to insert an OR condition, which means that the WHERE condition is always true.
By manipulating the two parameters username and password at the same time, we have bypassed the filter.
What are the consequences?
Different things can be achieved with an SQL injection. In addition to the information contained in the database, other functions of the databases themselves are often of interest. Possible targets of an attacker can be
Manipulation of processes, e.g. login without valid credentials.
Unauthorized access to data within the same database or other available databases.
Depending on the type of vulnerable database query, data can be changed or deleted.
Overloading the database, for example by resource-hungry queries to disrupt availability.
Depending on the configuration and authorizations of the database user, database functions can be misused that allow files to be read or written on the file system, operating system commands to be executed or network queries to be made, for example to internal systems.
How to do it better: Parameterized queries
SQL injections are a general problem with a general solution: parameterized queries and prepared statements. A self-developed filter is therefore not necessary. The danger here is that keywords or characters are ignored – as demonstrated above.
Parameterized queries are available for common databases and their use is supported by common programming languages. It is left to the database itself to process the user input correctly.
In the figure, first, the structure of the database query is defined. Placeholders must be inserted in places where user input is to be used. These are typically represented by question marks in the database query (line 6). This type of query is also called a parameterized query.
The parameterized query is converted internally by the database into a so-called prepared statement and made available for subsequent (multiple) execution. The structure of the query can no longer be changed. When the prepared statement is executed, the user input is transferred to the database as parameters for the defined placeholders (line 7).
The database uses the user input in such a way that it can no longer influence the structure of the database query. SQL injections can therefore no longer be carried out. At this point, developers can therefore rely entirely on tried and tested countermeasures from the database itself.
MindBytes GmbH | https://mind-bytes.de
