Parameterized query in Postgresql with a json array

Question

I would like to invoke array_prepend into a json[] using a parameterized query. I am using pg-promise npm package but this uses the normal node-postgres adapter under the hood.

My query is:

db.query(`update ${schema}.chats set messages =
  array_prepend('{"sender":"${sender}","tstamp":${lib.ustamp()},"body":$1}',messages) where chat_id = ${chat_id}`
 , message));

Same with "$1".

It works with a non-parameterized query.

Above code produces :

{ [error: syntax error at or near "hiya"]

Main reason for this is to avoid sql injection (docs say that they escape adequately when using the parameterized queries).

Answer 1

There are 2 problems in your query.

The first one is that you are using ES6 template strings, while also using sql formatting with ${propName} syntax.

From the library's documentation:

Named Parameters are defined using syntax $*propName*, where * is any of the following open-close pairs: {}, (), [], <>, //, so you can use one to your liking, but remember that {} are also used for expressions within ES6 template strings.

So you either change from ES6 template strings to standard strings, or simply switch to a different variable syntax, like $/propName/ or $[propName], this way you will avoid the conflict.

The second problem is as I pointed earlier in the comments, when generating the proper SQL names, use what is documented as SQL Names.

Below is a cleaner approach to the query formatting:

db.query('update ${schema~}.chats set messages = array_prepend(${message}, messages) where chat_id = ${chatId}', {
        schema: 'your schema name',
        chatId: 'your chat id',
        message: {
            sender: 'set the sender here',
            tstamp: 'set the time you need',
            body: 'set the body as needed'
        }
    }
);

When in doubt about what kind of query you are trying to execute, the quickest way to peek at it is via pgp.as.format(query, values), which will give you the exact query string.

And if you still want to use ES6 template strings for something else, then you can change the string to:

`update $/schema~/.chats set messages = array_prepend($/message/, messages) where chat_id = $/chatId/`

That's only one example, the syntax is flexible. Just remember not to use ES6 template string formatting to inject values into queries, because ES6 templates have no knowledge about how to properly format JavaScript types to comply with PostgreSQL, only the library knows it.