Returning Postgres result in nodejs with parameterized query

[webb24h]

How can I return results with parameterized query in Nodejs? If I'm concatenating parameters into the query text directly, I can return results just fine. Problems arise with parameterized query. If I have to concatenate into the query, it opens my entire software to SQL Injections which defies the purpose of using parameterized queries. Could it be that pg is reading the $1 as part of the literal string instead of a placeholder because it is wrapped in quotes? If so, how do I fix this?

Similar issues here, here and here

Tried solutions here and here. None worked at returning results for me.

Query runs fine if I remove RETURNING* or runs fine if I'm concatenating parameters directly in query.

Right now, server returns this error

error: syntax error at or near "RETURNING"

server.js

//load module
const { Pool, Client } = require('pg');

//get variables
var userid = JSON.stringify(coords.userid);//23
var long = JSON.stringify(coords.longitude);//-81.363124
var lat = JSON.stringify(coords.latitude);//28.627777
var coords = JSON.stringify([long,lat]);//[-81.363124,28.627777]

const text = "UPDATE users SET info = JSONB_SET(info, '{geometry,coordinates}', '"+coords+"') WHERE id=$1 RETURNING*";
const values = [id];

pool.query(text, values, (err, res) => {

if (err) {

//log errors
console.log(err.stack);

//return error to client

} else {

//success
//console.log(res.rows);


}
});

[charmander]

Which version of PostgreSQL are you using?

Also, what’s the value of coords? You should probably be passing that as a parameter.

[webb24h]

Which version of PostgreSQL are you using?

Also, what’s the value of coords? You should probably be passing that as a parameter.

I'm using psql (PostgreSQL) 12.6

As for the value of coords, I have updated the post with full codes and variables.

[webb24h]

Which version of PostgreSQL are you using?

Also, what’s the value of coords? You should probably be passing that as a parameter.

I'm new to node-pg, could you help with this parameterized query?

[charmander]

const text = "UPDATE users SET info = jsonb_set(info, '{geometry,coordinates}', $1) WHERE id = $2 RETURNING *";
const values = [coords, id];

I can’t reproduce the syntax error with PostgreSQL 13.

[webb24h]

const text = "UPDATE users SET info = jsonb_set(info, '{geometry,coordinates}', $1) WHERE id = $2 RETURNING *";
const values = [coords, id];

I can’t reproduce the syntax error with PostgreSQL 13.

Just upgraded to PostgreSQL 13.3 and still receiving same syntax error. I'm now using your codes as well. Scratching my head heavy.

[charmander]

I'm using psql (PostgreSQL) 12.6

Just to be sure: you’re referring to the version of the PostgreSQL server and not the psql client used to access it, right? This isn’t some remote instance of a “PostgreSQL-compatible” database? Does the query work against the same server when run with psql?

I’m glad you found an alternative solution to your problem in the form of making two queries, but it’d be nice to know what was going wrong too. :) Plus, one query is usually better, when possible.

[webb24h]

I'm using psql (PostgreSQL) 12.6

Just to be sure: you’re referring to the version of the PostgreSQL server and not the psql client used to access it, right? This isn’t some remote instance of a “PostgreSQL-compatible” database? Does the query work against the same server when run with psql?

I’m glad you found an alternative solution to your problem in the form of making two queries, but it’d be nice to know what was going wrong too. :) Plus, one query is usually better, when possible.

Nope. I removed the solution because turns out, nothing is being returned, even with the new strategy. I've spent all day trying to fix this issue. I'll probably have to just concatenate the parameters directly in the query because it's the only way it will not give a syntax error. I don't want to do this but I have to move forward with development as I am closed to production deadline.

I'm only using one installation of postgres and only one cluster as I have uninstalled any previous versions before updating to postgresql 13.3.

[webb24h]

Any solution? How do you do it? I'm opened to suggestions :)

[charmander]

Self-contained working example with minimal modifications compared to the original: https://gist.github.com/charmander/fe90916664864ef56ef610b9eb2393c4?ts=4

[webb24h]

Self-contained working example with minimal modifications compared to the original: https://gist.github.com/charmander/fe90916664864ef56ef610b9eb2393c4?ts=4

Thanks for all your efforts. It doesn't work for me :/ Im going the concatenating route. I cant spend another day on this issue. I believe it has something to do with the module. I'll have a better look when this software is launched. I care about making my client happy right now.

[boromisp]

If you've given up on prepared statements, there are better options than string concatenation, eg: https://github.com/datalanche/node-pg-format

[webb24h]

Self-contained working example with minimal modifications compared to the original: https://gist.github.com/charmander/fe90916664864ef56ef610b9eb2393c4?ts=4

Ok you can close this as resolved. Turns out this solution works! I dont know what happened the first time I tried it but that's a keeper! THANKS!!!