Search code examples
node.jspostgresqlparameterssql-injectionnode-postgres

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

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.

Solution

  • 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.