javascriptnode.jspostgresqlnode-postgresnode-pg-pool

How to safely call a variadic function from nodejs

Using postgres and node-pg.

CALL schema.function(${sql})

function is a variadic function accepting an array. It is open to SQL injection. How can the injection case be resolved?

My stored procedure uses a custom type:

CREATE TYPE isf.event_array AS 
(
    "id"   BIGINT,
    "topic"         CHARACTER VARYING(255),
    "type"    TEXT,
    "setId"   BIGINT,
    "eventId"       CHARACTER (36),
    "eventType"     CHARACTER VARYING(50),
    "metadata"      JSONB,
    "payload"       JSONB
);
END IF;

I understand the use of parametrised queries, however in this instance it needs to be passed an array.

Many thanks

Solution

  • As always when dealing with passing dynamic values, use a parameterised query:

    await client.query('CALL schema.function($1::json);', [JSON.stringify(arr)]);