Htykuut

Common sense dictates that SQL query strings should never be assembled by hand. Thus, all database interfaces offer parameter substitution, and all users use it, without exceptions.*

I'm using PostgreSQL v10.5, nodejs v8.12.0, node-postgres 7.6.1.

Parameter substitution works as expected for SELECT statements:

> await db.query("select from users where id = 'mic'");

(success, 1 row returned)

> await db.query("select from users where id = $1", ["mic"]);

(success, 1 row returned)

But it doesn't work for LISTEN statements:

> await db.query("listen topicname");

(success)

> await db.query("listen $1", ["topicname"]);

(error: syntax error at or near "$1")

The name of the topic I want to listen to is dynamic. It is coming from semi-trustworthy sources, which should not be user-controllable. But why go against all established best practice and take any chances?

Unfortunately, from my tests I fear that PostgreSQL simply can't do parameter substitution for LISTEN queries.

Is there any solution or workaround for this?

*) This statement may only be true in some utopic future society.

You are right that this cannot be done in PostgreSQL.

As a workaround, write a PL/pgSQL function that uses dynamic SQL like this:

EXECUTE format('LISTEN %L', topicname);

The format function escapes strings properly; in this case, the %L format that produces a properly quoted string Literal is the appropriate one.