SQL Injection in Prepared Statement - CVE-2024–1597

23/03/2024

The Extended Query Protocol

The extended query protocol is part of the general PostgreSQL network protocol called the Frontend/Backend protocol. In the extended-query protocol, execution of SQL commands is divided into multiple steps. These steps are parse, bind and execute.

The mode for executing queries can be defined using the connection property named "preferQueryMode". By default, the mode is set to "extended".

Query Modes at PostgreSQL

  • Simple: A simple query cycle is initiated by the frontend sending a Query message to the backend. The message includes an SQL command (or commands) expressed as a text string.
  • Extended: The extended query protocol breaks down the above-described simple query protocol into multiple steps. The results of preparatory steps can be re-used multiple times for improved efficiency. This is default.
  • ExtendForPrepare: Extended for prepared statements only
  • ExtendedCacheEverything: More of a diagnostic/debugging use, it caches simple statments as well with extended ones.

Why is Simple Query protocol being used?

  1. The client may be using it to communicate with PostgreSQL before version 7.4.
  2. When the database client message has no variables. It simply requires the request to be sent in one go, there isn't any benefit of using extended query mode.
  3. It allows multiple messages to be sent for certain messages separated by a semicolon, which is called statement batching.

How Simple Query works?

It sends a solitary message of type "Q" containing the statement payload. These steps on the database side are executed by the server using the simple query command.

Details of CVE-2024–1597

The vulnerability requires several conditions must be met:

  1. There should be a placeholder for a numeric value immediately preceded by a minus.
  2. There must be a second placeholder for a string value after the first placeholder on the same line.
  3. Both of them must be user controlled.

When the first parameter is set to a negative value, it effectively turns into '--' within the query, causing everything following it to be treated as a comment and ignored. This creates an opportunity for attackers to inject a malicious SQL query into the second parameter. The attacker must prepend a new line ("/n") at the beginning. As a result, the second parameter is presented in the query without any escaping, making it vulnerable to SQL injection attacks.

This vulnerability is specific to the Simple query mode due to the nature of how SQL queries are transmitted. In Simple query mode, the SQL query is sent as a single request, making it susceptible to the attack. Unlike other modes where queries may be processed differently or broken down into multiple steps,

Sample Code;

PreparedStatement stmt = conn.prepareStatement("SELECT -?, ?");
stmt.setInt(1, -1);
stmt.setString(2, "\nWHERE false --");
ResultSet rs = stmt.executeQuery();

The resulting SQL query;

SELECT --1,'
WHERE false --'

Mitigation Strategies

While prepared statements are indeed a crucial defense mechanism against SQL injection vulnerabilities, relying solely on them is not foolproof. Developers must adopt a security-first mindset when crafting SQL queries, always considering the potential for malicious impact. Additionally, it's imperative not to blindly trust user input and to properly sanitize or encode every special character, even when utilizing prepared statements. This multi-layered approach substantially mitigates the risk of SQL injection attacks and strengthens the overall security posture of the application

Patch Information:

The vulnerability has been patched in versions 42.7.2, 42.6.1, 42.5.5, 42.4.4, 42.3.9, 42.2.28, and 42.2.28.jre7. It's recommended to update the version of the software to one of these versions.

Workaround:

As a workaround until the update can be implemented, it's advisable to thoroughly examine every prepared statement for queries that contain specified conditions. Additionally, you can modify the "preferQueryMode" connection property to "extended". However, be cautious as this alteration may potentially disrupt the functioning of the application.


References;
https://jdbc.postgresql.org/documentation/server-prepare/
https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/jdbc/PreferQueryMode.html
https://www.postgresql.org/docs/current/protocol-flow.html
https://foojay.io/today/a-dissection-of-java-jdbc-to-postgresql-connections/
https://github.com/advisories/GHSA-24rp-q3w6-vc56

Share