javascript - Escaping generated single quotes in Postgres JSONB - Stack Overflow

admin管理员组

文章数量:1406060

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:

/?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?

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?

• javascript
• node.js
• postgresql
• sequelize.js

Share Improve this question Follow asked Sep 27, 2019 at 8:46 K20GHK20GH 6,2932121 gold badges8585 silver badges128128 bronze badges 2

• You're inlining arguments in SQL query which is a bad practice. I don't know anything about Sequelize but all database libraries supports providing arguments apart in an array. In the query you should only use placeholders such as '$1', '$2', etc... (syntax vary depending on actual database). This not only provides raw data to the query but also prevents SQL injection attacks. – bitifet Commented Sep 27, 2019 at 10:31
• NOTE: If identifying arguments by its position in an array bothers you, take a look at SQLTT: npmjs./package/sqltt – bitifet Commented Sep 27, 2019 at 10:34

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 8

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$

本文标签： javascriptEscaping generated single quotes in Postgres JSONBStack Overflow