When parameterization fails: SQL injection in Nim's db_postgres module using parameterized queries

I discovered a potential SQL injection vulnerability in Nim's standard library module db_postgres. This affects applications using this module with older PostgreSQL databases or configurations where standard_conforming_strings is disabled.

I originally discovered and disclosed this to the Nim security email in July 2023. As this edge case and quirk with this particular set-up seemed interesting, I decided to post this here.

Overview

Nim's db_postgres module supports executing queries using parameterization syntax to protect against SQL injection. For example:

getRow(sql"SELECT username FROM users WHERE username=?;", "user")

In this case, the ? symbol gets replaced with the supplied argument, resulting in:

SELECT username FROM users WHERE username='user';

This happens through the dbFormat procedure, defined here, which calls the dbQuote procedure for each ? symbol with the supplied parameter, defined here.

The dbQuote procedure escapes single quotes (replacing ' with ''), which is correct behavior. It also attempts to escape null characters with \\0 - but this isn't quite right. Without considering PostgreSQL's standard_conforming_strings setting, this can lead to unexpected behavior since backslashes don't have special meaning when standard_conforming_strings is enabled.

For reference, the Java PostgreSQL driver takes a more comprehensive approach. Here's what Utils.escapeLiteral in the official Java library org.postgresql.core does:

if (standardConformingStrings)
{
    // With standard_conforming_strings on, escape only single-quotes.
    for (int i = 0; i < value.length(); ++i)
    {
        char ch = value.charAt(i);
        if (ch == '\0')
            throw new PSQLException(GT.tr("Zero bytes may not occur in string parameters."), PSQLState.INVALID_PARAMETER_VALUE);
        if (ch == '\'')
            sbuf.append('\'');
        sbuf.append(ch);
    }
}

The Java implementation considers the standard_conforming_strings setting and rejects null characters entirely. If this setting is disabled, it enters a different code path where many more characters are properly escaped.

It should be noted that this approach of escaping parameter values and manually inserting them into the SQL statement is not how true parameterization should work. Proper parameterization separates the SQL query from the parameter values completely, with the database driver handling the parameters as distinct entities from the query text.

The vulnerability

When standard_conforming_strings is set to "on" (the default since PostgreSQL 9.1), Nim's module works safely. However, on older PostgreSQL versions or when this setting is manually set to "off" (which happens more often than you might expect due to compatibility requirements), SQL injection vulnerabilities can occur because the module fails to account for other characters with special meanings.

You can find more information about this configuration option in the PostgreSQL manual.

Proof of concept

I'll demonstrate the vulnerability using the backslash character, which can break the syntax of a query and escape the quoted string's ending quote.

First, I set up PostgreSQL 15.3 with standard_conforming_strings set to "off" in the postgresql.conf file, and created a simple table to emulate a user account system:

test=# SELECT * from users;
 user_id | username |         password
---------+----------+--------------------------
       1 | admin    | supersecretadminpassword
       2 | user     | userpassword

Here's a simple vulnerable Nim program:

import std/[db_postgres, os]

let db = db_postgres.open("localhost", "testuser", "testpass", "test")

echo db.getRow(sql"SELECT username FROM users WHERE username=? AND password=?;", paramStr(1), paramStr(2))

db.close()

When used with normal parameters, it works as expected:

$ ./poc 'user' 'userpassword'
@["user"]

However, by introducing a backslash, we can bypass the authentication and select the admin account instead:

$ ./poc '\' ' OR user_id=1; --'
@["admin"]

The generated SQL query becomes:

SELECT username FROM users WHERE username='\' AND password=' OR user_id=1; --';

If the PostgreSQL configuration option escape_string_warning is enabled, you'll see a warning in the logs:

WARNING:  nonstandard use of \' in a string literal at character 43
HINT:  Use '' to write quotes in strings, or use the escape string syntax (E'...').

However, in production environments, I've observed many cases where this configuration is still set to "off" or the warnings are simply ignored.

It's worth noting that the vulnerability is also present when using PostgreSQL's E syntax, which allows developers to selectively disable standard conforming strings in any part of a query, even if the global standard_conforming_strings setting is enabled.

Impact

Applications using Nim's db_postgres module with PostgreSQL databases where standard_conforming_strings is disabled may be vulnerable to SQL injection attacks. This could lead to unauthorized data access, authentication bypass, or potentially other backend compromises.

Mitigation

As mentioned, parameterization works fine when standard_conforming_strings is turned on. I don't think there are many Nim applications out there which use PostgreSQL with this setting set to off, but if you think you might be affected, it's definitely worth checking.