Escaping `%00` in SQL for Postgresql

[Araq]

Vulnerability

Escaping a decoded url param %00 in a query generated with db_postgres fails with Postgresql error code: unterminated quoted string at or near "'".

Affected module:

• db_postgres
• possible other db_* modules
• Nim v1.4.2

Why should this be treated as a vulnerability

Jester accepts POST requests and makes the params available to the end-user. If the end-user uses the params in a SQL-query and with execAffectedRows() Postgresql will error out.

Jester is an accepted Nimble-packages by core-Nim, and end-users should therefore be protected from injection'ish problems. In worst case scenario this could be used as a DOS-tool were the POST is repeated and the server crashes.

Setup: 1) Setup a connection to a Postgres DB, provide a POST-route with jester TABLE

CREATE TABLE IF NOT EXISTS inject(
id SERIAL PRIMARY KEY,
name TEXT;
);

JESTER:

var db: DbConn = .. init connection to postgres ..
routes:
  post "/inject":
     execAffectedRows(db, sql("UPDATE inject SET name = ?"), @"value")

2) Make a forced POST, e.g. with OWASP.

HEADER:

POST https://127.0.0.1/inject HTTP/1.1
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:84.0) Gecko/20100101 Firefox/84.0
Accept: */*
Accept-Language: en-US,en;q=0.5
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
X-Requested-With: XMLHttpRequest
Content-Length: 34
Origin: https://127.0.0.1
Connection: keep-alive
Referer: https://127.0.0.1/inject
Cookie: _ga=GA1.1.103239213.1611330562; 
blockPushAskToday=22
Host: 127.0.0.1

BODY:

item=name&value=%00

Mitigations: Instead of execAffectedRowsSafe() the tryExec() could be used to catch the error, but that stops the main purpose of execAffectedRowsSafe().

[Araq]

This fix would require a rewrite of a db_postgres module and postgre doesn't care about working with binary zeros. See https://www.commandprompt.com/blog/null-characters-workarounds-arent-good-enough/

If you simply pass data to your database without validation... Good luck.