From: Jan Bilek <jan.bilek@eftlab.com.au>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: ERROR: unsupported Unicode escape sequence - in JSON-type column
Date: Tue, 28 Feb 2023 00:55:02 +0000
Message-ID: <d2eb06c5-88ca-d798-689c-f8322fbd5bf3@eftlab.com.au> (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 2/27/23 22:13, Laurenz Albe wrote:
On Mon, 2023-02-27 at 06:28 +0000, Jan Bilek wrote:
Our customer was able to sneak in an Unicode data into a column of a JSON Type and now that record fails on select.
Would you be able to suggest any way out of this? E.g. finding infringing row, updating its data ... ?
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.
First, find the table that contains the column.
Then you can try something like
DO
$$DECLARE
pkey bigint;
BEGIN
FOR pkey IN SELECT id FROM jsontab LOOP
BEGIN -- starts block with exception handler
PERFORM jsoncol -> 'creationDateTime'
FROM jsontab
WHERE id = pkey;
EXCEPTION
WHEN untranslatable_character THEN
RAISE NOTICE 'bad character in line with id = %', pkey;
END;
END LOOP;
END;$$;
Yours,
Laurenz Albe
Hi Laurenz,
Thank you and yes, that's how we managed to go through that - one of our devs found similar approach described here: https://stackoverflow.com/questions/31671634/handling-unicode-sequences-in-postgresql (see the null_if_invalid_string function there + credits to Hendrik) and we reapplied it. FYI with a bit of tinkering we've been able to retrieve following (corrupted) data:
[cid:part1.XnBLfLs0.ptVnd3Gz@eftlab.com.au]
(It comes from a PROD system so I don't have it in a text form for you to experiment on that.)
Anyway, your solution points in exactly same direction.
How'd customer managed to do that? Still no idea ... looks like they restarted TCP connection on our middle-ware, but any partial packets should be dropped as not matching TCP length header. Also records are deserialized on receive so that would fail. Still, that record had to make it somehow in the PostgreSQL. We are still looking.
Thanks & Cheers,
Jan
--
Jan Bilek - CTO at EFTlab Pty Ltd.
Attachments:dcuZLHUsas1qwrL0.png (image/png, 18.3K) downloadview image
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: jan.bilek@eftlab.com.au, laurenz.albe@cybertec.at
Subject: Re: ERROR: unsupported Unicode escape sequence - in JSON-type column
In-Reply-To: <d2eb06c5-88ca-d798-689c-f8322fbd5bf3@eftlab.com.au>
References: <e3cb6a78-8ff2-dcf1-2fdd-26e3093d3106@eftlab.com.au> <43a45b6e-08f6-b40d-de6d-ec63e21eb496@eftlab.com.au> <959c233d610ac3e9ee2c1f7e47ea7775e0d1f1f7.camel@cybertec.at> <d2eb06c5-88ca-d798-689c-f8322fbd5bf3@eftlab.com.au>
* 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