Risks involved with operatorAliases in Sequelize

What Sequelize is, where and what for it is used

Sequelize is a Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more.

ORM (Object-Relational Mapping) is a programming technique that links databases to the concepts of object-oriented programming languages, creating a "virtual object database". 

What is operatorAliases and what are the risks?

By default, Sequelize will use symbol operators. Using Sequelize without any aliases improves security. Not having any string aliases will make it extremely unlikely that operators could be injected but you should always properly validate and sanitize user input (Source).

The operatorAliases option allows you to limit the use of aliases.

An example of the operatorAliases option enabled:

Let's consider the code of a demo application. The user.model.js file contains:

As you can see, the users table contains three fields, and they all have a string data type.

The auth.controller.js file contains:

The code uses the findOne method on the User model. The findOne method returns the first row from the database that matches the query condition. In this case, the application receives the username and password from the user, and applies them to a query against the users table.

The query in this case will look something like this: SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` = :username AND `users`.`password` = :password LIMIT 1;

Since ORM is used to form queries, trivial SQL injection will not work. If no matches are found in the database, the application will return a "User Not found." error. For all the matches at the database level, the application will compare the password entered by the user and the password stored in the database (in this case, such an algorithm was implemented specifically to test the vulnerability). Then it will either return an authorization token or, if passwords don't match, the "Invalid Password!" message.

The table will contain the following data:

The authorization procedure works properly.

Let's go back to operators and aliases:

Aliases are denoted with an “$” symbol and their syntax is similar to MongoDB.

There are search operators, comparison operators, and many others. Despite the strong data typing in the model, the JSON data transferred from the user to the ORM needs to be normalized. Similar to as discussed in the earlier article, only this time you cannot use different data types.

Batching attack

The following application request initiates a query to the database:

Executing (default): SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` IN ('admin', 'more', 'much more') AND `users`.`password` = 'wrong pass' LIMIT 1;

With this attack, it takes only one server request to check the validity of a batch of logins against a specific password and vice versa. The attack works in the latest version of the library (version 6.3.5 as of December 2020) and with the operatorsAliases option disabled.

Data type conversion attack

If you pass this kind of data to the application:

Nothing will happen in our case. Yes, the logical condition in the database will be correct. However, when the application checks the password and compares different data types, this cannot return true.

Comparison operator attack

The query will be like: SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` = 'admin' AND `users`.`password` != 'aaa' LIMIT 1;

The database returns data because there is a username match (username = admin) and a password different from "aaa". In order to pass the authorization process completely, we need to get a password.

Regex and string search attack

One can get the source data using the aliases of the search operators ($like) or regular expression operators ($regexp).

When the symbol does not match, we receive a "user not found" error.

If the symbol matches, the server returns a "wrong password" error. And the query will be like:  SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` = 'admin' AND `users`.`password` LIKE 'E%' LIMIT 1;

Thus, one can recover data from a table character by character.

Column comparison attack

There is an interesting alias $col that allows you to compare fields.

With this request the query to the database will be like: SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` = 'admin' AND `users`.`password` = `aaaaa` LIMIT 1;

While this request will generate the following query: SELECT `id`, `username`, `email`, `password`, `createdAt`, `updatedAt` FROM `users` AS `users` WHERE `users`.`username` = 'admin' AND `users`.`password` = `password` LIMIT 1;

Thus, fulfilling the logical condition at the database level.

How to eliminate the vulnerability?

To eliminate the vulnerability, you shall:

1. Install the latest version of the Sequelize library, where alias support was removed.

2. Not use aliases by setting operatorAliases: false.

3. Carefully validate the data types and values passed by the user before using them in the ORM.

Thanks for reading!