What is the correct syntax for dollar-parameters in PostgreSQL queries?

Question

I am struggling with the correct syntax for a dollar-parameter. It sometimes works, sometimes not.

I am using the 'pg' package for NodeJS, but since AFAIK parsing the query happens on the server side, I don't expect this to matter.

Code:

const client: PoolClient = ...;
await client.query('SELECT \'foo\' = $1', [projectId]);
await client.query('SET "gvc.currentProjectId" = $1', [projectId]);

The first query is obviously useless, but I added it to experiment with the syntax. An error occurs in the second query, so the first one seems to work. The second one fails with:

unexpected exception error: syntax error at or near "$1"

Is it even possible to use dollar-parameters in the value for SET? I want to secure my application against SQL injection attacks, so manually escaping the projectId is a last resort.

As for "manually escaping", see stackoverflow.com/q/70556273/1048572 — Bergi
– Bergi, Commented Jan 18, 2024 at 15:39

Martin Geisse · Accepted Answer · 2024-01-18 14:19:28Z

1

As explained in https://dba.stackexchange.com/a/333947, SET can indeed only take literal values, no parameters or query results. That post links to the set_config command, which does not have such a restriction.

https://pgpedia.info/s/set_config.html

Neither the post not the documentation explain why both exist, nor why SET cannot take parameters since set_config demonstrates that it is possible to do so.

Simply using set_config over SET solves the problem.

answered Jan 18, 2024 at 14:19

Martin Geisse

1,5291 gold badge12 silver badges35 bronze badges

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

3 Comments

user7070613 Over a year ago

This is not about SET taking parameters, it is about pg failing to do the substitution in query('SET "gvc.currentProjectId" = $1', [projectId]) and passing $1 to the database directly. Best guess pg does not recognize SET as a command it can use parameters with.

Martin Geisse Over a year ago

The linked post suggests that this is a general restriction of the SET command, not related to the pg package. What you wrote is interesting because it suggests that parameters must be supported per-command by the database client -- I thought that the client just sends the command including any dollar-sequences as plain text, and the argument values separately, and finding the parameters in the query happens completely server-side. Can you give any sources to learn more about that?

user7070613 Over a year ago

Yeah that must be it. My experience with this is using Python and psycopg2, there it works as the parameters are bound client side. When I use psycopg(3) which binds server side I get the same error. The source for pg is here node-postgres. I did a quick poke around and could not find the relevant part.

Collectives™ on Stack Overflow

What is the correct syntax for dollar-parameters in PostgreSQL queries?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related