2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
mic_e
  • 5,594
  • 4
  • 34
  • 49
  • See [Proper insertion of table name.](https://stackoverflow.com/a/37687311/1995738) – klin Nov 21 '18 at 00:38

2 Answers2

2

I don't have enough reputation to comment on the answer, but the proposed solution doesn't work for me. Using %L results in a quoted string, which causes the following error:

ERROR: syntax error at or near "'topic'"

The %I format should be used instead (SQL identifier, this is documented for table and column names, but it also works for the channel name,). You can also use the quote_ident function. See the documentation on creating dynamic queries here.

The following PL/pgSQL function works for us:

CREATE OR REPLACE FUNCTION listenForChannel(
    channel_   TEXT
) RETURNS VOID AS $$
BEGIN
    EXECUTE format('LISTEN %I', channel_);
END
$$ LANGUAGE PLPGSQL;
Kars de Jong
  • 51
  • 6
1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263