pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (6+ messages)  latest in thread

Message-ID: <1435849180.1100046.1677511030342@office.mailbox.org>
Permalink:  ../1435849180.1100046.1677511030342@office.mailbox.org/
Also on:    postgresql.org/message-id/1435849180.1100046.1677511030342@office.mailbox.org

 · 

reply

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