When parameterization fails: SQL injection in Nim's db_postgres module using parameterized queries当参数化失败时：使用参数化查询在 Nim 的 db_postgres 模块中进行 SQL 注入
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.
我在 Nim 的标准库模块 db_postgres 中发现了一个潜在的 SQL 注入漏洞。这会影响使用此模块的旧版 PostgreSQL 数据库或禁用了 standard_conforming_strings 配置的应用程序。
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.
我最初于 2023 年 7 月发现并向 Nim 安全电子邮件披露了这一点。由于这个特殊设置的边缘情况和怪癖看起来很有趣，所以我决定在这里发布它。
Nim's db_postgres module supports executing queries using parameterization syntax to protect against SQL injection. For example:
Nim 的 db_postgres 模块支持使用参数化语法执行查询以防止 SQL 注入。例如：
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.
这是通过 dbFormat 过程（在此处定义）实现的，该过程使用此处定义的提供参数为每个 ? 符号调用 dbQuote 过程。
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.
dbQuote 过程转义单引号（将 ' 替换为 '' ），这是正确的行为。它还尝试使用 \\0 转义空字符 - 但这并不完全正确。如果不考虑 PostgreSQL 的 standard_conforming_strings 设置，这可能会导致意外行为，因为在启用 standard_conforming_strings 时反斜杠没有特殊含义。
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:
作为参考，Java PostgreSQL 驱动程序采用了更全面的方法。以下是官方 Java 库 org.postgresql.core 中的 Utils.escapeLiteral 所做的事情：
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.
Java 实现会考虑 standard_conforming_strings 设置并完全拒绝空字符。如果禁用此设置，它会进入不同的代码路径，其中更多字符会被正确转义。
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.
需要注意的是，这种转义参数值并手动将其插入 SQL 语句的方法并不是真正的参数化应有的工作方式。正确的参数化将 SQL 查询与参数值完全分开，数据库驱动程序将参数作为与查询文本不同的实体进行处理。
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.
当 standard_conforming_strings 设置为“on”（自 PostgreSQL 9.1 以来的默认设置）时，Nim 的模块可以安全运行。但是，在较旧的 PostgreSQL 版本上或当此设置手动设置为“off”（由于兼容性要求，这种情况比您预期的更常见）时，可能会出现 SQL 注入漏洞，因为模块无法解释具有特殊含义的其他字符。
You can find more information about this configuration option in the PostgreSQL manual.
您可以在 PostgreSQL 手册中找到有关此配置选项的更多信息。
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:
首先，我设置了 PostgreSQL 15.3，并在 postgresql.conf 文件中将 standard_conforming_strings 设置为“off”，并创建了一个简单的表来模拟用户帐户系统：
test=# SELECT * from users;
user_id | username | password
---------+----------+--------------------------
1 | admin | supersecretadminpassword
2 | user | userpassword
Here's a simple vulnerable Nim program:
这是一个简单的、有漏洞的 Nim 程序：
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:
生成的 SQL 查询变为：
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:
如果启用了 PostgreSQL 配置选项 escape_string_warning ，您将在日志中看到一条警告：
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.
值得注意的是，使用 PostgreSQL 的 E 语法时也存在此漏洞，这允许开发人员在查询的任何部分有选择地禁用符合标准的字符串，即使启用了全局 standard_conforming_strings 设置。
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.
使用 Nim 的 db_postgres 模块和禁用了 standard_conforming_strings 的 PostgreSQL 数据库的应用程序可能容易受到 SQL 注入攻击。这可能导致未经授权的数据访问、身份验证绕过或其他潜在的后端危害。
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.
如上所述，当 standard_conforming_strings 处于打开状态时，参数化工作正常。我认为没有多少 Nim 应用程序使用 PostgreSQL 并关闭此设置，但如果您认为自己可能会受到影响，那么绝对值得检查一下。
from: When parameterization fails: SQL injection in Nim's db_postgres module using parameterized queries当参数化失败时：使用参数化查询在 Nim 的 db_postgres 模块中进行 SQL 注入
发表于 2025-4-1 22:00:30