Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPTVn-004o5G-3u for pgsql-general@arkaria.postgresql.org; Thu, 04 Jul 2024 20:57:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sPTVl-002xra-4M for pgsql-general@arkaria.postgresql.org; Thu, 04 Jul 2024 20:57:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPTVk-002xrK-JI for pgsql-general@lists.postgresql.org; Thu, 04 Jul 2024 20:57:01 +0000 Received: from mail-dm6nam04on20700.outbound.protection.outlook.com ([2a01:111:f403:2409::700] helo=NAM04-DM6-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPTVi-000RkO-D3 for pgsql-general@postgresql.org; Thu, 04 Jul 2024 20:57:00 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=ZDihkvwScGAZ9xYwyhXU56uWoEExk54RapUltAtLVOrJSOqF+63XZCcxIWAdvIfRAqVmdO4mK30Cm4Cb4RWkER+eELZglioRpKiJw+LqQY3Iy5xPS6OSozQ1L2YQDAPnQqu0csJVMD087ifXY67NklRlWFl4S1RGfNmI9JXVmPT77b6gBOoKZV0JXdbCEQBZqMwQocVgA7pcL30h6RDp37wrlCXEnyZUhx9E3259oaH+CkLvuB/mxlmfjDwP1nswc5C2CxFshM5ac9WiEOdcK44d3DHwmrEOwvXaS9mb1oVF3aPOMbZGlw/y3SDxz9M1heu5mhUfKWXOUHehaIWCNQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=1SQ7Ltv0YmQZxpnIWfXlqs9HZQ4511JTXYDEgZmfyCs=; b=nNqoujuU+m32PnoHzAeAYRkND9oQKxP2U+op0h8JQ6a6+uIfOIk0aDPEOvhH9iURUSDicSXWShVK0IqmfW9LbAJ//tXi4EXiZfZvqUG52V0q+RAhC5MuGzexsA4PQ2yTMnJD6FtM4b0oiOFpuSiDOBW8o7ZlCYmPiszbGKAbaZufe1QM+Y7zyOx7jckm0qyzGl8Sy+rxXT6aYzrNcvAXBJ+/14LMCOWhTbkLIBWnUXoQz2L0USZzbEXITYUzcDg3vr5+ZNlCB7UErQk5IHwJ/SWjkghSUODQBFJ4eMwXC7evROaw+yFM8/ujhbr6RlLWro3/LTKK8/PcooWVXFfGrA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=termtegrity.com; dmarc=pass action=none header.from=termtegrity.com; dkim=pass header.d=termtegrity.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=termtegrity.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=1SQ7Ltv0YmQZxpnIWfXlqs9HZQ4511JTXYDEgZmfyCs=; b=MzjMs9yadCtAlpFuc6+CuNw1WfmKhzX2RaRhoLvXIU+vxPaFXDucsdHlAXNggzksjITvtnZj7S6bhGZ41TrPMEV4iUh6qJLbpm2Rxm6eqRyX0JqNBFJomb9jNVOIDFWD9gLjQ/oSXBjgmEyoN7wIaI0Zj24k2+ap+Mg96rGfg7g= Received: from SA1PR19MB4959.namprd19.prod.outlook.com (2603:10b6:806:1a6::7) by CO1PR19MB5061.namprd19.prod.outlook.com (2603:10b6:303:f7::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7741.25; Thu, 4 Jul 2024 20:56:51 +0000 Received: from SA1PR19MB4959.namprd19.prod.outlook.com ([fe80::9bfb:4100:766d:23a4]) by SA1PR19MB4959.namprd19.prod.outlook.com ([fe80::9bfb:4100:766d:23a4%5]) with mapi id 15.20.7741.027; Thu, 4 Jul 2024 20:56:51 +0000 From: Vasu Nagendra To: "pgsql-general@postgresql.org" Subject: JSONPath operator and escaping values in query Thread-Topic: JSONPath operator and escaping values in query Thread-Index: AQHazk2XVkvgdTbNr0C0ZUWwP+gMWw== Date: Thu, 4 Jul 2024 20:56:51 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=termtegrity.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SA1PR19MB4959:EE_|CO1PR19MB5061:EE_ x-ms-office365-filtering-correlation-id: 07ae531c-051a-49a2-0cf4-08dc9c6bd40b x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|366016|1800799024|38070700018; x-microsoft-antispam-message-info: =?Windows-1252?Q?ZOxHgXx9zWLFutjt1AsiVPPqAo/cCbdLCP0wzs9LRGo1gDYl8lQ/rAeu?= =?Windows-1252?Q?/Zdy53gJLCVpCFotLlBekrtl4HNHvepp+KWe0tZ4Te7D9QcmJcbSJWRc?= =?Windows-1252?Q?kygr8F+5JO+H0vMK9fT40QdQNe+zVvlGxKVCSbNNEhEIu8qZY4+JVtGh?= =?Windows-1252?Q?hG9EgvNrb85+b3vwESVKy+qgmw5kqZcpMte1rd/Zmx9ntm7zLXfCsi4H?= =?Windows-1252?Q?WBMxyP5LhP2ChjRWsgwlacMu8YdLdMYxD7lyQPcVSUsSA0hHyHiHyoGA?= =?Windows-1252?Q?wepJbqrsxEkrSgCQ0/8f44Z7lxc/fx78Xdi8r3LhZThaPn4zgKL9wEXw?= =?Windows-1252?Q?wAxmMwUrqFdshR8Irk2DvFDu8QyVXufpP856rjigAfPJ8sbd//HmfViH?= =?Windows-1252?Q?nkRur+Gstw5LO50525AX7BkEFVf2ZTghEOJMLR2hpHIBqczeGPZKR3qP?= =?Windows-1252?Q?ufD/uUTt+RDlb9Tb5MRNovYp1sSW8DuoThK7D1MfjUJxrpI8L9qFZiXa?= =?Windows-1252?Q?v2zntI5PmwcQM3qCtO5aRYyITcFbmTYCF37mSjEQoQkscvZqf49nB2Eo?= =?Windows-1252?Q?TtZbYUvoiVR++mJXlTSYBtMaSVPIpCc8Xmm8ZSLTfckjEkCu/ZlcHQrp?= =?Windows-1252?Q?KgPhSsaWuIOOFb0a/1FeSSK6FdK3YVZUGvFQuHzMcqxNtlo4nTeh/Ivs?= =?Windows-1252?Q?qxHBLrGBSgjp3OgqSU3KNXoSk11tFQDAPPQj6l/47OdAUXNyw1YX6IXJ?= =?Windows-1252?Q?uFJOtA3rVw3D5/E3JFgEyly+0TP+KlCurw0t1ubv7yq+A4Lxp+5we8b1?= =?Windows-1252?Q?/cplNe4n6rxW9WOJHFJGmJjVE6AnXY+sqY2c7YJcc+I6885HVsqaHHLf?= =?Windows-1252?Q?WLclvM9clrMJErAG3s3hZkdpAaAN2++yPDYNAQbmsjnts7B/fzgJ3To9?= =?Windows-1252?Q?6Y5s7b+LOTbZ17aWlbcQ9iR2fO1GM4SufgKg8WKWP3g/FFGpnvuFyP4+?= =?Windows-1252?Q?Jjs3oEQJfbLS4kpw5C9usVmccgHTEZ7mQgQ9DfB6tg1BSgIu4fESuzbB?= =?Windows-1252?Q?tkxCvAtcHYSDOedU+0kLZ9rAY5HlQyZk+CNTbQR8kcP9+cE8qfgjrzCD?= =?Windows-1252?Q?o+NZDzDC4fScR6lsRDCg/WZBSb6scziDfmE3z7FNF3YekmJOITUyA0GK?= =?Windows-1252?Q?UU03Gc7t8Vy20nS3onpk0HdB8rpY2j2mzQZQxLrFII0Vd68/VIbYJPCN?= =?Windows-1252?Q?m2fsQ+9Z9ulolgEuJ0QLwB2HWNU5JnsPedQ081jwoda5JnWyCtmUltST?= =?Windows-1252?Q?Q+Q8e1JlJh/m9j1krJSMUxhMo20tIfZD2fLLbdi4X+Y76T8MHNO8ilC7?= =?Windows-1252?Q?eIDysuEZ449omEQPcE70WMnZT6C1KSqgYLYNiGJ2Wz9c25q3HTTq2ut5?= =?Windows-1252?Q?oGy5EebCEQ35GmFZW5kcJAX0URLMZ4Bpk7y1N0vMNrY=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SA1PR19MB4959.namprd19.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?+ZaWht8d1+XUJTF+f6wvRwj2CINNOYIdJ2y03AbZ7TX5XXQfqpHKbcDN?= =?Windows-1252?Q?rPX8bvophpspC8TbfSRM56ORStMY4gLjx3wlSnigHnPrfTHhU7v/xdif?= =?Windows-1252?Q?yOeH530OSGKvykAhglWVLkVBbvBTGtENURPuqbzw+69sEBJOfQKux86q?= =?Windows-1252?Q?rpdD6qdEuNaCkxpHrbWH+FDkmCiY3laX6YH8mJno1vd0aAlnCfKmoH4r?= =?Windows-1252?Q?qqiYFs0191M++k8KJBOdCEYxuOCf8sBWm1iedCOk6H0t8zk3RWTIwFYn?= =?Windows-1252?Q?h3nRZHWrRobc+OEVEuRAApcC2NEtAVQ8SS5M6R7jN4lTXgOLBS/XYbG3?= =?Windows-1252?Q?rL7mn9IwrzfpQQ5WX+z3CYJM796KsRZgRKofMyurQS/aRWIuznZ/TTls?= =?Windows-1252?Q?iVZIyUJyNyGPgKo41dBuFH7PoNrDoohZi+Zh+4doOyL0j3mdKfpkTJ7J?= =?Windows-1252?Q?nWkIDMtBMtNABsaemR1ZPbp22sH3BKTToVC3HGICM4SIRuyf0ZIC/RLj?= =?Windows-1252?Q?v3fGn2WnQuc8ZGldoPYH/edOaqedYclb+zciQt4Dc+LXfLwWMeKFL2Ju?= =?Windows-1252?Q?IhVWYGXrUEL3XB+Ze+kaj1lX7izMbj1URKey1aC+GsFQqQ2noHfx7WvQ?= =?Windows-1252?Q?M63W1QpzXVecYkXH8SVuNzHvAVVQlnt4hsKJoH4TWqDlmgOnBVVLd7ed?= =?Windows-1252?Q?3c4sXsf/4ylsXe05DMS9kYepNwbpkZ2gVGKHo7akAhF6tpoBteyBoUCb?= =?Windows-1252?Q?z7TGS8F1StyNitCu828RTxH2SwVVkuEb23ZjZK8swvOGcTMztr9cEnCB?= =?Windows-1252?Q?Ul280cXxwdm8ZEYS8TgxG3kh5rsp/MXs/JotUOyPLmYOVJoleC+lW6Yb?= =?Windows-1252?Q?nzd2gOW27p9dUiA0ASI1j3FjxsmRlXdW26jSqE1DjCa93nAvIe4HtK2E?= =?Windows-1252?Q?fRS3nN/uotCDby6oeNMfYJU9uRjzipsDv9NtSaLx0dXTXWvuWVVSwRbO?= =?Windows-1252?Q?8spb4YyazcyP7JWIZqnsza62bUedMPBKteyvhHSPUPPnf40U7yT6EJsy?= =?Windows-1252?Q?/29PetzbItUE1dj3ezn+Odl9SAu2vh9KO03PVLhjhJ0hDj3JJVsV1AZi?= =?Windows-1252?Q?VO0bvsS06RsSq95DFjJrjXbd3kvlPYULBNKqW1FF73S3NAL33rGt/CTk?= =?Windows-1252?Q?/oXtu6MS8almGhzvhvF6V/dH+lUajov2LEmzvhx2/K6qAhePB0PZNAmX?= =?Windows-1252?Q?DWGNXLc/poPOEj5NdYanWPawnYL6cPpkyyvdJC0zEFHSfy6+w+R0d2TC?= =?Windows-1252?Q?vWE3L8DFBJXnYpd8pTkrkSnI8eZV/uNmUSgivNvKM/upYvL9nlmlWk4k?= =?Windows-1252?Q?rxH1QOozsyScK6Djo4N12lag0MA4a1bp3BsOE/CPRzH+3DUt5FWgxryY?= =?Windows-1252?Q?B2RHjfNnt0sxxj0ai9Eb+WFQxpHikIZBzG+iFmdHug6yl2dxmex4TnQe?= =?Windows-1252?Q?r0Z4POnvW878FSlj+JQFjZ+fQNIstPIo6CQeNc3BmUEtM/EOm+qRzJCQ?= =?Windows-1252?Q?wWUhywnQ1HEX1GNFbspL88+uiDxTIajl0N0f9BJKPxzrjJ3QgsrOxlf+?= =?Windows-1252?Q?N26Zqe6xKgj/qTdjixZpCzCiiAf/u6Sp0V9B7GmjQip85/ncm8sKEkr0?= =?Windows-1252?Q?1UUylgp7+sJLoTmQ5Y4X9ZFelLAPwM87BPMZfOzKIzpR1WB2W8qE8C46?= =?Windows-1252?Q?fN4PnSuhoyTMwav9WuCtOOBasZxMDtH8h479uwowUmhgTaIIhBC7dGvs?= =?Windows-1252?Q?HpFnnQ=3D=3D?= Content-Type: multipart/alternative; boundary="_000_SA1PR19MB49595E7E55B85B21784C2EE5CADE2SA1PR19MB4959namp_" MIME-Version: 1.0 X-OriginatorOrg: termtegrity.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SA1PR19MB4959.namprd19.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 07ae531c-051a-49a2-0cf4-08dc9c6bd40b X-MS-Exchange-CrossTenant-originalarrivaltime: 04 Jul 2024 20:56:51.2459 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 8bdb4fca-927b-41e6-88fb-2f623b293aab X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: h/2gdpzewkplh2B1IovlwdCXd/nlsBVN1KnhAvoBJClRUM25T/fOOTJ3oYcg7ucLaQCdhHUYdEPEew+w5Xt34w== X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO1PR19MB5061 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_SA1PR19MB49595E7E55B85B21784C2EE5CADE2SA1PR19MB4959namp_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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 >=3D 3= )'; This is an invalid query (syntax error) SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a-b >=3D = 3)'; The thing that is making it invalid is the key =93a-b=94. Same error occurs= for key =93@ab=94. In looking at the following link https://github.com/pos= tgres/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 >=3D= 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 talkin= g about the SQL allowed/disallowed special characters =96 not specific to t= he JSONPath query. Looking at the source code here for function printJsonPathItem https://gith= ub.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 w= ould 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 examp= le) that is formatting the string for the query '$ ? (@.n.a\-b >=3D 3)=92, = is it correct to format anything that is present in the JsonPathItemType en= um documentation? Of course this assumes all the standard security things a= bout sanitizing user input and handling the path conversion for arrays corr= ectly =96 meaning =93a.*.b=94 must be replaced with =93a[*].b=94=85 If this is documentation I should contribute to, I am happy to =96 I=92d im= agine 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 --_000_SA1PR19MB49595E7E55B85B21784C2EE5CADE2SA1PR19MB4959namp_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Good afternoon,

I am running into the following issue with a JSONPat= h exists query.

 

This is a valid query 

SELECT '{"n": {"a-b": 1, "@ab&q=
uot;: 2, "ab": 3}}'::jso=
nb @? '$ ? (@.n.ab >=3D 3)';

 

This is an invalid query (syntax error) 

SELECT '{"n": {"a-b": 1, "@ab&q=
uot;: 2, "ab": 3}}'::jso=
nb @? '$ ? (@.n.a-b >=3D 3)';

 

The thing that is making it invalid is the key =93a-= b=94. Same error occurs for key =93@ab=94. 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<= o:p>

 

SELECT '{"n": {"a-b": 1, "@ab&q=
uot;: 2, "ab": 3}}'::jso=
nb @? '$ ? (@.n.a\-b >=3D 3)';

 

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

 

Looking at the source code here for function printJs= onPathItem https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/json= path.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 esca= pe the string.  

 

Question: Is this a valid assumption? If I have a py= thon program (for example) that is formatting the string for the query '$ ?= (@.n.a\-b >=3D 3)=92, 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 u= ser input and handling the path conversion for arrays correctly =96 meaning= =93a.*.b=94 must be replaced with =93a[*].b=94=85

 

If this is documentation I should contribute to, I a= m happy to =96 I=92d 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

 

--_000_SA1PR19MB49595E7E55B85B21784C2EE5CADE2SA1PR19MB4959namp_--