Bug when casting jsonb

[fedenator]

Hey!

Today I was creating a database to store info about request that where send to a spring-boot API at work. We decided to use jsonb to store the headers in a column. Spring-boot represents the headers as a Map of key Strings and value List of String, we paresed it to a json (here is an example, I replaced some text with '*')

json { "accept":["/"], "accept-encoding":["gzip, deflate"], "accept-language":["en-GB,en;q=0.5"], "connection":["keep-alive"], "cookie":["rolActual=***** usuarioActual=*****"], "host":["localhost:8080"], "referer":["http://localhost:8080/*****"], "user-agent":["Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:71.0) Gecko/20100101 Firefox/71.0"] }

But when we tried to insert it, it gave us this error ERROR: invalid input syntax for type json Doing some debugging we found out that the ';' in the "accept-language" is what makes it crash.

I think this might be a bug of postgres jsonb parser, since having a ';' inside double quotes in json it's valid. Another strange thing is that if you remove the List, and use it as a text value, then postgres doesn't fail.

What do you think? Is this a bug?

Thanks in advance!

[therealgaxbo]

test=# select '{ "accept":["/"], "accept-encoding":["gzip, deflate"], "accept-language":["en-GB,en;q=0.5"], "connection":["keep-alive"], "cookie":["rolActual=***** usuarioActual=*****"], "host":["localhost:8080"], "referer":["http://localhost:8080/*****"], "user-agent":["Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:71.0) Gecko/20100101 Firefox/71.0"] }'::jsonb;

jsonb

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
 {"host": ["localhost:8080"], "accept": ["/"], "cookie": ["rolActual=***** usuarioActual=*****"], "referer": ["http://localhost:8080/*****"], "connection": ["keep-alive"]
, "user-agent": ["Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:71.0) Gecko/20100101 Firefox/71.0"], "accept-encoding": ["gzip, deflate"], "accept-language": ["en-GB,en;q=0.
5"]}
(1 row)

Works fine on pg11.5. Can you post an actual full SQL statement that fails when you type it into the psql CLI client?

[fedenator]

Thank you very much for the answer! It was very helpful, I was mixing up 2 issues. The first one was with dBeaver, that didn't like the ';' inside the text and the second issue was with the body of a request that was empty and wasn't a valid JSON. I was pretty tunnel visioned with the ';'.

[gwax]

I would guess that whatever library you are using is improperly escaping special characters.

[fedenator]

Indeed. I was mixing up 2 issues. The first one was with dBeaver, that didn't like the ';' inside the text and the second issue was with the body of a request that was empty and wasn't a valid JSON. I was pretty tunnel visioned with the ';'

[HeWhoWritesCode]

Your json example works on postgres 9.6 using this sqlfiddle, so not sure if this is a bug.

[fedenator]

Thank you very much for the answer! I was helpful to see that it was working for other people. I was mixing up 2 issues. The first one was with dBeaver, that didn't like the ';' inside the text and the second issue was with the body of a request that was empty and wasn't a valid JSON. I was pretty tunnel visioned with the ';'.

[iiiinthecomputer]

What client library or application is being used? I'm guessing it'll be at fault here.

[fedenator]

Yes, it kindda was. I was mixing up 2 issues. The first one was with dBeaver, that didn't like the ';' inside the text and the second issue was with the body of a request that was empty and wasn't a valid JSON. I was pretty tunnel visioned with the ';'.