node.jspostgresql

How do you safely insert a variable into an array with postgresql and nodejs?

I can unsafely modify an array like this:

await pool.query(`UPDATE profiles SET friends = friends || '{"${newFriend}"}'`);

however this leaves you open to SQL injection etc. It is ideal to put this into an array at the end like this

await pool.query(`UPDATE profiles SET friends = friends || $1`, [newFriend]);

But this isn't quite right is it? I would need to add the string "{' '}" somehow. What's the proper way of doing this?

Solution

  • pg supports serializing array parameters out of the box:

    await pool.query(`UPDATE profiles SET friends = friends || $1`, [[newFriend]]);

    You can also make the array on the query side with an array constructor:

    await pool.query(`UPDATE profiles SET friends = friends || ARRAY[$1]`, [newFriend]);

    Either way, you get to avoid working with the text representation of arrays manually, which is ideal.

    The array concatenation operator also works with single elements directly, but I find this a little unpleasant.

    // substitute `text` with the underlying type of the array, if not `text`
await pool.query(`UPDATE profiles SET friends = friends || $1::text`, [newFriend]);