In Russian: https://blog.deteact.com/ru/dql-injection
Modern web applications are less prone to injections, everyone uses prepared queries and ORM, but we still encounter such vulnerabilities in the wild.
SQL dialects built into ORM libraries are of particular interest. This is an additional abstraction, which is also subject to injections, and there may be vulnerabilities when translating expressions from the dialect to a specific implementation of SQL.
Intro
ORM is a library linking objects and their attributes in code with tables and fields in the database.
The ORM abstraction allows representing database relational tables as ordinary objects and treating them as objects.
ORM allows you to separate database and application tasks, so that the programmer does not even need to write SQL queries, but simply perform actions with objects, while the corresponding SQL queries will be generated by the ORM library.
Why use ORM?
It is clear that the absence of necessity to manually write hundreds of SQL queries simplifies the development process, especially in large projects.
At the same time, the queries generated by the library are more difficult to optimize, and the library itself adds an overhead.
ORM itself does not protect against injections, but when using correctly, it supports prepared statements and parameterized queries.
Doctrine and DQL
There are many ORM libraries for different programming languages and frameworks. We’ll focus on the Doctrine project written in PHP and the exploitation Doctrine Query Language injections. Doctrine is used by default in the popular Symfony PHP framework.
You can use Doctrine both by performing actions on objects in PHP code (using QueryBuilder) and manually executing DQL queries. It is also possible to execute raw queries directly in SQL.
DQL is based on HQL (Hibernate Query Language in the Hibernate Java Library) and is a subset of SQL, but it still has a lot of features that can help with the operation of injections.
DQL supports the usual operators SELECT, UPDATE, DELETE, but there is no implementation of the INSERT and UNION operators, the LIMIT expression (it is necessary to use the setMaxResults method). The UNION operator is not implemented by the authors of the library because of the strict typing of DQL (and UNION implies the ability to select non-homogeneous data).
DQL also supports subqueries and the expressions JOIN, WHERE, ORDER BY, HAVING, IN, etc.
DQL syntax documentation: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html
Below is a list of built-in DQL functions that can be used after the SELECT, WHERE and HAVING expressions. You can also use the AVG, COUNT, MIN, MAX, SUM functions after the SELECT and GROUP BY expressions.
As in many databases, you can create your own User Defined Function implementation in PHP and make it available from DQL.
DQL Injections
Here is how to create an SQL query to retrieve data in Doctrine when working with objects in the code:
The difference between a DQL query and an SQL query is shown below:
|
1 |
$dqlQuery = "SELECT p FROM App\Entity\Post p WHERE id = '$query' ORDER BY p.publishedAt DESC"; |
|
1 |
$sqlQuery = "SELECT * FROM post WHERE id = '$query' ORDER BY publishedAt DESC"; |
Obviously, in both cases, there is a concatenation of some variable with a request. If this data comes from user input, it is possible to exploit a DQL injection.
The principles of DQL injection operation, of course, do not differ from the exploitation of SQL injection, but it is necessary to understand that an attacker can not fully control the query that will be sent to the database. DQL operates on the models and not the actual database tables, thus, for example, it will not be possible to extract data from tables for which the corresponding models are not defined in the application code.
Let’s see what happens when you create such a query (QueryBuilder is called from the Post class method):
The DQL query is converted into an Abstract Syntax Tree, which is converted into an SQL query is generated in the grammar of the connected DBMS afterwards.
Injection Techniques
Depending on the DBMS used, the type of query, injection context, and settings (debug mode), different injection exploitation algorithms such as Boolean Based and Error Based are possible.
- Boolean Based
The substring function and subqueries allow to brute force model attribute values character by character:
|
1 |
1 or 1=(select 1 from App\Entity\User a where a.id=1 and substring(a.password,1,1)='$') |
The screenshots show that we obtained the value of the first character of the password hash (“$”). In the SELECT operator, we used the full model name User. There is no easy way to get a list of all the models.
- Error Based (SQLite)
When using SQLite DBMS, there is one more feature – the SQLite dialect is quite poor, and DQL provides the same interface regardless of the DBMS used. Therefore, in the absence of any native functions in SQLite, you have to write their implementation in PHP.
It concerns the functions udfSqrt, udfMod, udfLocate (corresponding DQL-functions: SQRT, MOD, LOCATE). When passing incorrect data to these functions, an exception occurs at the PHP level rather than at the DBMS level, so the result of the entire SQL query may be leaked if errors are displayed.
An error:
SQL query result containing the password hash:
It is clear that without a debug mode the application is unlikely to display this data, but nevertheless, it is possible to use Error Based injection by brute force (extract a bit of information on the presence or absence of an internal error).
- Injecting after ORDER BY
DQL grammar does not support the use of complex expressions and subqueries after ORDER BY and GROUP BY so that the exploitation in this context is not possible, the parser will allow only the literals.
- Injecting after IN
A subquery can be passed as the argument of the IN expression, which opens the doors for various injection techniques, such as Error Based technique:
|
1 |
$dqlQuery = "SELECT p FROM App\Entity\Post p WHERE p.id IN (select sqrt(a.password) from App\Entity\User a where a.id=2)"; |
- Injecting after UPDATE
The UPDATE operator allows an attacker to write the result of the subquery to the value of the model attribute, so that the data can be extracted entirely through a side channel (by writing the secret data to the table with public data):
|
1 |
UPDATE App\Entity\Post p SET p.title = (SELECT u.password FROM App\Entity\User u WHERE u.id = 2), slug = testslug, summary = testsum, content = testcon WHERE id = 25 |
Conclusions
Using ORM is not a cure-all for SQL injection. It is necessary to carefully validate and sanitize the data transmitted by users and use prepared statements.
Many developers are accustomed to the fact that frameworks do all the work for them and you don’t have to worry about the safety of your code.
You can read more about what methods are safe in DQL in the documentation: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/security.html.
What else?
If your development team is short of security assistance, please contact us for Application Security and DevSecOps services and products: https://appsec.deteact.com/.