How to prevent SQL Injections when inserting JSON Data in Postgres?

Question

I'm new to postgres-node. Coming from a mySQL background, I'm unlearning old habits and now learning new things. I want to prevent SQL injections when inserting data. I read about parameterized query. Is this applicable to JSON type? I'm doing CRUD operations on a table in postgres that only have 2 columns. I'm manipulating JSON data (SELECT, INSERT and UPDATE). How do you prevent SQL injections when inserting JSON data in a table?

table

id | info
1  | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
2  | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}

query

INSERT INTO orders (info)
VALUES('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}')

There is no SQL injection hazard in the INSERT statement you quote. Perhaps you should show the code that creates the statement. — Laurenz Albe
– Laurenz Albe, Commented Apr 26, 2021 at 6:06

Stock Overflaw · Accepted Answer · 2021-04-26 08:48:03Z

1

JSON type is no different from other types for injection prevention:

// totally wrong: concatenated unsafe input straight into the query
client.query(`INSERT INTO orders (info) VALUES (${allegedlyJsonStringifiedUserInput})`);
// totally right: parameterized query, delegating injection safeties to pg
client.query(`INSERT INTO orders (info) VALUES ($1)`, [allegedlyJsonStringifiedUserInput]);

answered Apr 26, 2021 at 8:48

Stock Overflaw

3,3411 gold badge15 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grogu Over a year ago

Okay thanks man. I wasn't sure. I'm just inserting the whole json object as this one big value in the parameterized query haha makes my life easier. Cheers!

Collectives™ on Stack Overflow

How to prevent SQL Injections when inserting JSON Data in Postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related