From: Erik Wienhold <ewie@ewie.name>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jan Bilek <jan.bilek@eftlab.com.au>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: ERROR: unsupported Unicode escape sequence - in JSON-type column
Date: Mon, 27 Feb 2023 16:17:10 +0100 (CET)
Message-ID: <1435849180.1100046.1677511030342@office.mailbox.org> (raw)
In-Reply-To: <959c233d610ac3e9ee2c1f7e47ea7775e0d1f1f7.camel@cybertec.at>
References: <e3cb6a78-8ff2-dcf1-2fdd-26e3093d3106@eftlab.com.au>
<43a45b6e-08f6-b40d-de6d-ec63e21eb496@eftlab.com.au>
<959c233d610ac3e9ee2c1f7e47ea7775e0d1f1f7.camel@cybertec.at>
> On 27/02/2023 13:13 CET Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> I'd be curious to know how the customer managed to do that.
> Perhaps there is a loophole in PostgreSQL that needs to be fixed.
Probably via some data access layer and not directly via Postgres. It's easy
to reproduce with psycopg:
import psycopg
with psycopg.connect() as con:
con.execute('create temp table jsontab (jsoncol json)')
con.execute(
'insert into jsontab (jsoncol) values (%s)',
[psycopg.types.json.Json('\0')],
)
with con.execute('select jsoncol from jsontab') as cur:
print(cur.fetchall())
try:
with con.execute('select jsoncol::jsonb from jsontab') as cur:
pass
raise AssertionError("jsonb should fail")
except psycopg.errors.UntranslatableCharacter:
pass
Another reason to prefer jsonb over json to reject such inputs right away.
The documentation states that json does not validate inputs in constrast to
jsonb.
Of course the OP now has to deal with json. The data can be sanitized by
replacing all null character escape sequences:
update jsontab
set jsoncol = replace(jsoncol::text, '\u0000', '')::json
where strpos(jsoncol::text, '\u0000') > 0;
But the data access layer (or whatever got the json into the database) must be
fixed as well to reject or sanitize those inputs in the future.
--
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, laurenz.albe@cybertec.at, jan.bilek@eftlab.com.au
Subject: Re: ERROR: unsupported Unicode escape sequence - in JSON-type column
In-Reply-To: <1435849180.1100046.1677511030342@office.mailbox.org>
References: <e3cb6a78-8ff2-dcf1-2fdd-26e3093d3106@eftlab.com.au> <43a45b6e-08f6-b40d-de6d-ec63e21eb496@eftlab.com.au> <959c233d610ac3e9ee2c1f7e47ea7775e0d1f1f7.camel@cybertec.at> <1435849180.1100046.1677511030342@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox