Search Postgresql Archives

JSONPath operator and escaping values in query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux