0

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

user973347
  • 49
  • 1
  • 7
  • 1
    Use a parameterized query: https://node-postgres.com/features/queries – Frank Heikens Nov 14 '22 at 22:11
  • Not sure if you really mean ["variadic"](https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS) when you say "accepting JSON array". Can you share the function definition sql please? – Bergi Nov 14 '22 at 22:17
  • Thanks for adding the definition of the `event_array` type, but could you please add the definition of the `schema.function` function (at least the declaration, you can omit the implementation)? – Bergi Nov 15 '22 at 13:08
  • 1
    [Related thread](https://stackoverflow.com/q/74458798/5298879). I noticed this one ended up focusing only on the *safe call* part, leaving out the parts about passing an array of a specific type as argument and usefulness of `variadic` in this scenario. – Zegarek Nov 23 '22 at 13:22
  • @Zegarek Yeah, I would have, if the OP had come back to clarify. I didn't realise that they posted a new question instead. – Bergi Nov 23 '22 at 22:16

1 Answers1

1

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

await client.query('CALL schema.function($1::json);', [JSON.stringify(arr)]);
Bergi
  • 630,263
  • 148
  • 957
  • 1,375