CODEWITHSUNDEEP
javascriptnode.jspostgresqlsequelize.js
K20GH
K20GH

Reputation: 6263

Escaping generated single quotes in Postgres JSONB

I have a JSONB column in my table which I update with JSON from a 3rd party source. The problem I have is that sometimes, these fields can contain ' marks which break the query as below:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=57dc7401fc4de72137de367da7192092

I'm using Node to connect to my Postgres instance using Sequelize. I know I can escape the characters and enable standard_conforming_strings on my instance, but i'd not do this. Like below:

const obj = encodeURIComponent(JSON.stringify(data.data));
const query = `UPDATE fb_designs SET items = jsonb_set(items, '{listings, 0}', '${obj}') WHERE id = '${data.did}'`;
return sequelize.query(query, { type: sequelize.QueryTypes.SELECT})

Is there a way using either Javascript, Sequelize or directly in Postgres that I can double up these ' to easily escape them?

Upvotes: 1

Views: 3002

Answers (1)

404
404

Reputation: 8542

The single quotes conflict with the opening/closing quotes of the string. One way around this is to use dollar quoting instead:

'{"your":"you're"}' -> $${"your":"you're"}$$

Using your example: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=468b83fca5d2d8d3a94d5b16a6b1772f

From your JS code:

const query = `UPDATE fb_designs SET items = jsonb_set(items, '{listings, 0}', $$${obj}$$) WHERE id = '${data.did}'`;

Everything inside those dollar quotes will be taken verbatim. You can also put text between the dollars to they don't conflict with other dollar quotes surrounding them (if they exist) or double dollars in your json, e.g. $my_json${"a":1}$my_json$

Upvotes: 8

Related Questions