In modern web applications, injections are less and less common, everyone uses prepared queries and ORMs, but we still see injections during penetration tests.
Of particular interest are the SQL dialects built into ORM libraries. This is an additional abstraction that is also prone to injection, and vulnerabilities can arise when translating expressions from a dialect into a specific SQL implementation.
Introduction
ORM is a library that associates objects and their attributes in code with tables and fields in a database.
The ORM abstraction allows you to represent relational database tables as ordinary objects and treat them like objects.
ORM allows you to separate the tasks of the database and the application, so that the programmer does not even have to write SQL queries, but simply perform actions with objects, and the corresponding SQL queries will be generated by the ORM library.
What is ORM used for?
It is clear that the absence of the need to manually write hundreds of SQL queries simplifies the development process, especially in large projects.
At the same time, queries generated by the library are more difficult to optimize, and the library itself adds an overhead.
Using an ORM in itself is not a means of protecting against injection, but when used correctly, the libraries provide a means for parameterized and prepared queries.
Doctrine and SQL
There are many ORM libraries for various programming languages and frameworks. Let’s take a closer look at the Doctrine project written in PHP and the injection exploitation in the Doctrine Query Language. 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 SQL queries. It is also possible to execute raw queries directly in SQL.
The SQL language is based on HQL (Hibernate Query Language in the Hibernate Java library) and is a subset of SQL, but it still has quite a few features that can help with injection exploitation.
SQL supports the familiar SELECT, UPDATE, DELETE statements, but there is no implementation of the INSERT and UNION statements, the LIMIT expression (you must use the setMaxResults method). The authors of the library did not implement the UNION operator due to the strict SQL typing (and UNION implies the ability to select data of different types).
SQL also provides support for subqueries, JOIN, WHERE, ORDER BY, HAVING, IN, etc.
Below is a list of the built-in functions in SQL that can be used after the SELECT, WHERE and HAVING clauses. Also, after the SELECT and GROUP BY expressions, you can use the AVG, COUNT, MIN, MAX, SUM functions.
As with many DBMSs, in Doctrine you can create your own User Defined Function in PHP and make it available from SQL.
SQL injection
This is how Doctrine’s creating an SQL query for fetching data looks like when working with objects in code:
And below is the difference between a SQL query and an SQL query:
| 1 | $sqlQuery = “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 query. If it is user data, a SQL injection is possible.
The principles of SQL injection exploitation, of course, do not differ from the SQL injection exploitation, but it is necessary to understand that an attacker cannot completely control the query that will be sent to the DBMS. In fact, the work does not go with the database, but with the models, therefore, for example, it will not be possible to retrieve data from tables for which no models are defined in the code.
Let’s see what happens when creating such a query (QueryBuilder is called from the Post class method):
The SQL query is converted into a syntax tree, after which an SQL query is generated in the grammar of the connected DBMS.
Injection techniques
Depending on the DBMS used, the type of request, the context of the injection and the settings (the presence of a debug mode), various algorithms for exploiting the injection are possible, such as Boolean Based and Error Based.
- Boolean Based
The substring function and subqueries allow you to iterate over the values of model attributes character by character:
| 1 | 1 or 1=(select 1 from App\Entity\User a where a.id=1 and substring(a.password,1,1)=’$’) |
From the screenshots you can see that we got the value of the first character of the password hash (“$”). However, in the SELECT statement, we used the fully qualified name of the User model. There is no easy way to get a list of all models.
- Error Based (SQLite)
When using the SQLite DBMS, there is another feature – the SQLite dialect is rather poor, and SQL 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.
This concerns the udfSqrt, udfMod, udfLocate functions (corresponding SQL functions: SQRT, MOD, LOCATE). When incorrect data is passed to these functions, an exception occurs at the PHP level, and not at the DBMS level, therefore, when displaying errors, the entire SQL subquery result may leak.
Error:
The result of an SQL subquery with a password hash:
It is clear that in the absence of a debug mode, the application is unlikely to display this data, but, nevertheless, it is possible to exploit Error Based injection by brute force (extract a bit of information on the presence or absence of an internal error).
- Injection in ORDER BY
The SQL grammar does not provide for the use of complex expressions and subqueries after ORDER BY and GROUP BY, so exploitation of the injection in such a context is not possible, the parser will only skip literals.
- Injection into IN
A subquery can be passed as arguments to the IN expression, which provides various possibilities for exploiting injection, for example, using the Error Based technique:
| 1 | $sqlQuery = “SELECT p FROM App\Entity\Post p WHERE p.id IN (select sqrt(a.password) from App\Entity\User a where a.id=2)”; |
- UPDATE injection.
The UPDATE statement allows you to write the result of a subquery into the value of a model attribute, so that you can retrieve the entire data via an external channel (by writing secret data to a 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 panacea for SQL injection. It is necessary to carefully validate and sanitize the data transmitted by users, use prepared queries.
Many developers are used to the fact that frameworks do all the work for them, and there is no need to worry about the security of their code.