variadic function and parametised safe queries

Question

Using node-pg and Postgres 11.

I have a variadic function in postgres

CALL schema.function(('1'),('2'))

In order to prevent sql injection I need to do something like

await client.query('CALL schema.function($1::smallint);', XXX);

Where XXX is what the call uses for substitutions.

The problem is that my function expects a list of records, not an array ('1'),('2').

Has anyone else encountered this?

Stored procedure:

CREATE OR REPLACE PROCEDURE update_events_variadic(
    VARIADIC _events_array event_array[]
)
LANGUAGE plpgsql AS
$$
DECLARE
EVENT_RECORD RECORD;
BEGIN
FOR EVENT_RECORD IN 
    SELECT
        metadata,
        payload
    FROM unnest(_events_array) as t(
        metadata,
        payload
    )
LOOP
INSERT INTO events
    (
        metadata,
        payload
    )
VALUES
    (
        EVENT_RECORD.metadata,
        EVENT_RECORD.payload
    );

END LOOP;

END;
$$;

Does this answer your question? [How to safely call a variadic function from nodejs](https://stackoverflow.com/questions/74438129/how-to-safely-call-a-variadic-function-from-nodejs) — user3738870, Nov 16 '22 at 11:12
Hi It doesn't because I need to pass a list of records into the call function. — user973347, Nov 16 '22 at 11:24
Can you include an example so the difference is somewhat clearer? — user3738870, Nov 16 '22 at 11:29
Hi. I am unsure how. The difference is that in the other question it is to do with an array, however the implementation of the SP requires a list of records and in that format. I'm just trying to get the data in safely without using something like a string literal. — user973347, Nov 16 '22 at 14:14
Hi jjanes. I thought I did that by stating that the type of _events_array was of a custom type event_array[] :: VARIADIC _events_array event_array[]. Or am I missing something. — user973347, Nov 16 '22 at 14:22
@user973347 But what happens if you use the solution from the other answer? `[JSON.stringify(arr)]` Does it give you an error? If so, it would be useful to have that in the question. — user3738870, Nov 16 '22 at 14:54
The error was { "length": 313, "name": "error", "severity": "ERROR", "code": "42883", "hint": "No procedure matches the given name and argument types. You might need to add explicit type casts.", "position": "6", "file": "parse_func.c", "line": "605", "routine": "ParseFuncOrColumn" } — user973347, Nov 16 '22 at 15:34

Zegarek · Accepted Answer · 2022-11-22T22:37:52.100

If that's the only type of call you expect, variadic is just syntax noise. You can redefine your procedure to accept a regular array of type that fits into table events, that you're trying to insert into. Also, you don't have to loop over a select from unnest() - there's a FORACH loop that lets you iterate over the array directly.

CREATE OR REPLACE PROCEDURE update_events ( events_array events[] )
LANGUAGE plpgsql AS $$
DECLARE
    event_record events;
BEGIN
FOREACH event_record IN ARRAY events_array LOOP
    INSERT INTO events
    (   metadata,
        payload
    )
    VALUES
    (   event_record.metadata,
        event_record.payload
    );
END LOOP;
END; $$;

You can also insert directly from a select without any loop, gaining some performance from that and from switching to plain language SQL that doesn't have the PLpgSQL overhead:

CREATE OR REPLACE PROCEDURE update_events ( events_array events[] )
LANGUAGE SQL AS $$
INSERT INTO events
(   metadata,
    payload
)
select 
    event_record.metadata, 
    event_record.payload
from unnest(events_array) 
  as event_record
    (   metadata,
        payload
    )
$$;

You can either cast each element in the array, or cast the whole array at once:

await client.query('CALL schema.function( ARRAY[$1]::events[] );', XXX);

Demo. If you happen to initially get a malformed record literal error after these changes, it means that some or all of what you're supplying as XXX isn't a valid set of values that can make up events record. Another demo, showing some examples.

I'm naively assuming your events table has only those two columns - if it has more, you'll have to define an intermediate type or table and use that as your array type in procedure definition and call argument cast. Example

In this case variadic would be useful if you wanted to be able to use both types of calls below:

call update_events( --not an array in a strict sense, just a bunch of arguments
     ('metadata1','payload1')::events,
     ('metadata2','payload2')::events,
     ('metadata3','payload3')::events);

The above isn't passing an array in a strict sense, just a bunch of arguments that the function will internally collect and make available as a single, actual array.

call update_events( VARIADIC
     ARRAY[('metadata1','payload1'),
           ('metadata2','payload2'),
           ('metadata3','payload3')
          ]::events[] );

VARIADIC informs the function that nothing has to be collected into an array as the argument list is already provided as one.

"*I'm naively assuming your `events` table has only those two columns*" - assuming there's no identity columns or anything else that needs to be ignored by the insert, this seems like a practical use case to simplify to `INSERT INTO events SELECT * FROM UNNEST(events_array);`? — Bergi, Nov 23 '22 at 22:22
@Bergi Yes, and that's exactly what the second procedure example is simplified to, making the whole thing basically an alias. Still, I would guess the OP or anyone new ending up here, might actually be doing something more in a procedure like that and we're shown only the part that was causing trouble. — Zegarek, Nov 24 '22 at 05:08

variadic function and parametised safe queries

1 Answers1

Linked