When developing modern database applications, developers have a lot of tools at their disposal to handle SQL queries securely. Object-Relational Mappers (ORMs) and query builders abstract away much of the query language and let developers focus on the business logic. Under the hood of those, or used directly, prepared statements handle the proper escaping of any user input that becomes part of a database query.
Some databases even come with native support for prepared statements, making it easy to separate queries and parameters from start to end. However, in cases where there is no such native feature, the database client library has to insert the parameters into the query string safely and ensure they are properly escaped.
Usually, string parameters are the ones that can cause trouble because they can alter the syntax of a query if they're not properly escaped. But are there also other data types that might alter the syntax of a query? Let's dive into a subtle vulnerability we found in and reported to several popular SQL client libraries.
Impact
We found the following libraries to be vulnerable to SQL Injection via line comment creation:
- CVE-2024-1597: PgJDBC (<42.7.2, fix also backported to older branches)
- CVE-2024-32888: Redshift JDBC Driver (<2.1.0.28)
- CVE-2025-29744: pg-promise (<11.5.5)
- CVE-2024-27289: pgx (<4.18.3)
- CVE-2024-44905: pg (unpatched)
- CVE-2024-44906: pgdriver (unpatched)
In the right circumstances, an attacker can inject into SQL queries and execute malicious statements. For the attack to work, a prepared statement of a certain structure must be used, which we will detail below. In addition, the library has to use the simple query protocol when communicating with the PostgreSQL server.
As part of our new SonarQube Advanced Security offering, the included Software Composition Analysis (SCA) functionality now detects known vulnerabilities in third-party open source dependencies, such as the vulnerabilities explained in this blog post. We support a wide range of package ecosystems, including Maven/Gradle, npm, and Go.
Technical Details
To understand what type of character combinations can change a parsing context in SQL, let's look at a bunch of syntax constructs that change the parsing state for longer sequences:
Strings. The classic, breaking out of strings leads to SQL injection because strings can contain almost any characters that can now become SQL syntax. Delimited with double quotes or single quotes, depending on the SQL dialect.
Identifiers. Similar to strings. Less likely to be injected into since user input is mostly used for values, not for column names. Delimited with quotes or backticks, depending on the SQL dialect.
Comments. There are line comments, starting with --, which comment out the rest of the line. Some Databases also support # as the start of a line comment. There are also multi-line or block comments, starting with /* and ending with */. These comment out everything between the start and end delimiters, and some databases even allow nested comments.
Looking further at comment syntax, we can see an interesting difference between different database implementations: MySQL requires a trailing space after the two dashes (--) that start a line comment. They explain the reason for this in their documentation:
"[...] the -- start-comment sequence is accepted as such, but must be followed by a whitespace character such as a space or newline. The space is intended to prevent problems with generated SQL queries that use constructs [...]"
They also list an example of such a problem. Let's use this prepared statement as an example:
The update statement is supposed to charge a user account and has two parameters. $1 is replaced with the charge, and $2 is replaced with the account ID. After filling them in, the statement looks like this:
The balance of the account with ID acc-1337 will be decreased by 42. However, what happens when the first parameter is negative? The filled-in statement would look like this:
The syntax has become ambiguous! Should the database parse it as subtracting -42, or is there a line comment? To avoid this ambiguity, MySQL requires a whitespace after the -- start-comment sequence. But what about other databases?
Other popular SQL-based databases do not seem to require whitespace! SQLite, PostgreSQL, Oracle Database, and Microsoft SQL Server all support -- to start a line comment but do not prevent the ambiguity. So, is there a way that an attacker could exploit this?
Looking Closer at PostgreSQL
To answer this question, we examined PostgreSQL client libraries more closely because there are plenty of open source ones. But very early on, we noticed that PostgreSQL might be immune due to its native support of prepared statements.
PostgreSQL supports two query modes: simple and extended. In the simple mode, an SQL string is sent to the database, and the result is returned. If there are user-controlled parameters in the query, the client has to insert them into the query string before sending it. On the other hand, there's the extended query mode that sends a prepared statement and its parameter values separately. This means that the values are never interpolated into the query because the database treats them separately, which in turn means that parameter values can never alter the syntax of a query.
However, many PostgreSQL client libraries either only support the simple query mode or let users disable the extended mode. In fact, some database tooling requires the use of the simple mode, such as earlier versions of PgBouncer or Datadog's Database Monitoring for specific configurations.
When running in simple query mode, libraries have to interpolate parameter values into the query themselves. Let's take a look at how PgJDBC, the most popular PostgreSQL driver for Java, handled the query SELECT 1-? for a parameter value of -1. This is the query that is sent to the database:
Looking at the result, we can see that PostgreSQL indeed parses the -- sequence as the start of a line comment:
We also confirmed that other libraries suffer from the same flaw: the JS library pg-promise and the Go libraries pgx, pg, and pgdriver. After our disclosure, Amazon's Redshift JDBC Driver was also patched.
Gauging the Impact
After confirming that it is possible to alter the syntax of a prepared statement by causing the creation of a line comment, we wanted to know if it's just possible to comment out parts of a query or if attackers could even inject new syntax. We experimented with several queries until we realized another fact about PostgreSQL: multi-line string literals are supported!
Let's consider the example query from the beginning again:
When a charge of -1 and an account ID of foo\nbar are passed as the parameter values, the resulting interpolated query looks like this:
When the PostgreSQL database parses this query, it will ignore the comment, resulting in this:
As we can see, the query syntax has been altered! In its current state, it will result in a syntax error, proving the syntax modification. However, since the account ID is user-controlled, an attacker could provide a value that modifies the query without causing syntax errors. Here, it comes in handy for the attacker that PostgreSQL strings can be multi-line:
With this, it is clear that attackers can inject malicious SQL statements. Luckily, the requirements are quite high, and such queries are likely not very widespread. However, to be on the safe side, it is still important to update your dependencies to fixed versions. In the case of pg and pgdriver, there are no patches available as of today, so we recommend switching to an alternative, such as pgx.
With SonarQube SCA, you will be alerted if any of your dependencies contain a known vulnerability. SonarQube will recommend the dependency version with the fix in it, or recommend switching to an alternative if there is no fix.
Patch
The affected libraries opted to break the ambiguity of line comment syntax in PostgreSQL by adding a space in front of negative numbers or by wrapping them in parentheses. For example, pg-promise was able to patch the vulnerability by changing only two lines of code:
number(num) {
if (typeof num === 'bigint' || Number.isFinite(num)) {
- return num.toString();
+ const s = num.toString();
+ return num < 0 ? `(${s})` : s;
}
// [...]
}Timeline
| Date | Action |
| 2024-02-15 | We ask the maintainers of pgx, pg, pgdriver, and pg-promise for security contacts |
| 2024-02-15 | We report the issues to the maintainers of PgJDBC and pgx |
| 2024-02-15 | The pg-promise maintainer requests vulnerability details to be given in a public GitHub discussion |
| 2024-02-15 | We open a public GitHub discussion in the pg-promise repository |
| 2024-02-21 | We report the issues to the maintainer of pg and pgdriver |
| 2024-02-21 | The PgJDBC maintainers release fixed versions (42.2.28, 42.2.28.jre7, 42.3.9, 42.4.4, 42.5.5, 42.6.1, 42.7.2) |
| 2024-03-04 | The pgx maintainer releases a fix in version 4.18.2 |
| 2024-03-22 | The pg-promise maintainer releases a fix in version 11.5.5 |
| 2024-05-15 | The Redshift JDBC Driver maintainers release a fix in version 2.1.0.28 |
| 2024-06-03 | We sent a final reminder about the elapsed disclosure deadline to the maintainer of pg and pgdriver |
Summary
Even if developers use all available tools to write a secure application, vulnerabilities can still exist due to issues in third-party packages. In this case, the use of prepared statements still allowed for SQL injection in certain scenarios. This underlines the importance of including third-party code in your security testing to get a complete picture.
This is why we at Sonar are adding security capabilities such as Advanced SAST and SCA to help you keep your code secure. With Advanced SAST, our analysis engine continues to follow attacker-controlled data flows into third-party open source packages. With SCA, you get alerted when you are using known vulnerable dependencies, such as the ones presented in this blog post.