public inbox for [email protected]  

help / color / mirror / Atom feed

JSONPath operator and escaping values in query
2+ messages / 2 participants
[nested] [flat]

* JSONPath operator and escaping values in query
@ 2024-07-04 20:56  Vasu Nagendra <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Vasu Nagendra @ 2024-07-04 20:56 UTC (permalink / raw)
  To: pgsql-general

Good afternoon,
I am running into the following issue with a JSONPath exists query.

This is a valid query

SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.ab >= 3)';

This is an invalid query (syntax error)

SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a-b >= 3)';

The thing that is making it invalid is the key “a-b”. Same error occurs for key “@ab”. In looking at the following link https://github.com/postgres/postgres/blob/master/src/include/utils/jsonpath.h#L62, it looks like anything that is in the enum JsonPathItemType if present in the query will cause a syntax error and must be escaped like so


SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a\-b >= 3)';

I also looked at the section 4.1.4 (https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-SPECIAL-CHARS), but this is only talking about the SQL allowed/disallowed special characters – not specific to the JSONPath query.

Looking at the source code here for function printJsonPathItem https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/jsonpath.c#L685, I think this is just processing characters in the path one by one, which would explain why there is no special syntax for how to escape the string.

Question: Is this a valid assumption? If I have a python program (for example) that is formatting the string for the query '$ ? (@.n.a\-b >= 3)’, is it correct to format anything that is present in the JsonPathItemType enum documentation? Of course this assumes all the standard security things about sanitizing user input and handling the path conversion for arrays correctly – meaning “a.*.b” must be replaced with “a[*].b”…

If this is documentation I should contribute to, I am happy to – I’d imagine it belongs in section 9.16.1 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING as a footnote to Table 9.46. Additional jsonb Operators

Thanks!
--Vasu



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: JSONPath operator and escaping values in query
@ 2024-07-05 01:11  David G. Johnston <[email protected]>
  parent: Vasu Nagendra <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: David G. Johnston @ 2024-07-05 01:11 UTC (permalink / raw)
  To: Vasu Nagendra <[email protected]>; +Cc: pgsql-general

On Thu, Jul 4, 2024 at 1:57 PM Vasu Nagendra <[email protected]> wrote:

>
>
> SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a\-b >= 3)';
>
>
Which is better written as:

select '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n."a-b" >=
3)';

Using the same double-quotes you defined the key with originally.

The relevant documentation for jsonpath syntax is here:

https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSONPATH

David J.

>


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-07-05 01:11 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-04 20:56 JSONPath operator and escaping values in query Vasu Nagendra <[email protected]>
2024-07-05 01:11 ` David G. Johnston <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox