PQexecParams, placeholders and variable lists of params

Hi
A function cannot have an undefined signature, but can accept an array of
arguments:
CREATE OR REPLACE FUNCTION bpd.object_del_by_id_array(
object_array bigint[])
RETURNS SETOF bpd.errarg_action
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
cfg_desc "bpd"."cfg_action"%ROWTYPE;
action_entity RECORD;

action_result RECORD;
result "bpd"."errarg_action"%ROWTYPE;
BEGIN
SELECT * INTO cfg_desc FROM "bpd"."cfg_action" WHERE id = 'delete';

FOR action_entity IN SELECT id, "name" FROM bpd.object WHERE id =
ANY(object_array)
LOOP
action_result = "bpd"."object_del"(action_entity.id);
result."err_id" = action_result.outresult;
result."errdesc" = action_result.outdesc;
result."entity_id" = 20;
result."entity_instance_id" = action_entity.id;
result."entity_instance_name" = action_entity.name;
result."action_id" = cfg_desc."actid";
result."action_desc" = cfg_desc.desc;
RETURN NEXT result;
END LOOP;
END;
$BODY$;
--
Regards, Dmitry!

вт, 23 нояб. 2021 г. в 16:37, <tomas@tuxteam.de>:

On Tue, Nov 23, 2021 at 06:39:27PM +0500, Дмитрий Иванов wrote:

Hi
A function cannot have an undefined signature, but can accept an array of
arguments:

I see. So you propose passing an array as a single param to
PQexecParams, in PostgreSQL's syntax for arrays, e.g.. "{42, 45, 50}".

Makes sense. Problem is, that, again, the application would be
responsible of making sure the individual values don't contain nasty
stuff (for example, if they are strings) before consolidating them to
one PostgreSQL array literal.

I was hoping to get away "on the cheap" on this, letting PostgreSQL take
care of the injection avoidance ;-)

I'm converging in building the query dynamically, but still with
placeholders. I /know/ how many values are coming, and how many
placeholders used so-far in the query, so it'd be fairly easy to just
insert "$m"..."$n" as needed.

Thanks a lot, Dmitri

Cheers
- t

On Tue, Nov 23, 2021 at 7:21 AM <tomas@tuxteam.de> wrote:

Makes sense. Problem is, that, again, the application would be
responsible of making sure the individual values don't contain nasty
stuff (for example, if they are strings) before consolidating them to
one PostgreSQL array literal.

So long as you actually pass the literal value via a parameter the worst
problem you can have is a syntax error in converting the literal into
whatever type is being cast to.

I personally tend to just build up a CSV-like string (my data is usually
controlled enough the using the pipe symbol as a separator
alleviates escaping concerns) and using string_to_array($1,'|') to get the
array of values into the query.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Nov 23, 2021 at 7:21 AM <tomas@tuxteam.de> wrote:

Makes sense. Problem is, that, again, the application would be
responsible of making sure the individual values don't contain nasty
stuff (for example, if they are strings) before consolidating them to
one PostgreSQL array literal.

So long as you actually pass the literal value via a parameter the worst
problem you can have is a syntax error in converting the literal into
whatever type is being cast to.

PG's array quoting rules are odd enough that I can sympathize with not
wanting to deal with them. (Although, if you only have to build an
array and not parse one, taking the always-quote-even-if-not-necessary
approach makes it easier.)

I don't see many other alternatives though. *Somehow* you have to
separate one value from the next. If you don't want to pass 'em as
distinct parameters, then you have to obey some kind of composite-value
syntax.

regards, tom lane

On Tue, Nov 23, 2021 at 10:43:03AM -0500, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Nov 23, 2021 at 7:21 AM <tomas@tuxteam.de> wrote:

Makes sense. Problem is, that, again, the application would be
responsible of making sure the individual values don't contain nasty
stuff (for example, if they are strings) before consolidating them to
one PostgreSQL array literal.

So long as you actually pass the literal value via a parameter the worst
problem you can have is a syntax error in converting the literal into
whatever type is being cast to.

PG's array quoting rules are odd enough that I can sympathize with not
wanting to deal with them. (Although, if you only have to build an
array and not parse one, taking the always-quote-even-if-not-necessary
approach makes it easier.)

I don't see many other alternatives though. *Somehow* you have to
separate one value from the next. If you don't want to pass 'em as
distinct parameters, then you have to obey some kind of composite-value
syntax.

Yes, that is my conclusion, too. Tentatively, I'll go with dynamically
building the query string, but with "$n" placeholders -- counting args
as I go, and pass the args to PQexecParams.

This seems to afford injection protection in exchange of minimal fuss.

Thank you all for your input!

Cheers
- t

On 23. Nov 2021, at 16:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG's array quoting rules are odd enough that I can sympathize with not
wanting to deal with them. (Although, if you only have to build an
array and not parse one, taking the always-quote-even-if-not-necessary
approach makes it easier.)

I don't see many other alternatives though. *Somehow* you have to
separate one value from the next. If you don't want to pass 'em as
distinct parameters, then you have to obey some kind of composite-value
syntax.

Would it be possible to extend PQexecParams() et.al. like this:

You currently have paramValues[], paramLengths[], and paramFormats[] (plus other parameters that I'll ignore here).

The format may currently be 0 or 1 (TEXT or BINARY). What if we allow 2 for ARRAY? The corresponding length then specifies how many parameters following are part of the array. The value should point to a structure, that contains pointers to the values, lengths, and formats of the elements. This also allows nested arrays.

If the client library knows that the server is too old to understand it, it may temporarily assemble a string for those (correctly escaped) values and replace the entries in the original values/lengths/formats arrays temporarily before passing it to the old PQexecParams() implementation.

If the server is new enough the protocol itself can be extended to send the array more efficiently instead of quoting and copying data around.

This would also hide the quoting rules for arrays nicely, as it doesn't require additional methods for escaping. (Currently, escaping for arrays is different from other escaping methods, it needs to be done manually and, frankly, it's a PITA).

I'm sure a lot of users (and authors of client libraries like myself) would really appreciate some improvements in handling array values.

Regards, Daniel

On Tue, Nov 23, 2021 at 05:14:44PM +0100, Daniel Frey wrote:

On 23. Nov 2021, at 16:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG's array quoting rules are odd enough that I can sympathize with not
wanting to deal with them. (Although, if you only have to build an
array and not parse one, taking the always-quote-even-if-not-necessary
approach makes it easier.)

I don't see many other alternatives though. *Somehow* you have to
separate one value from the next. If you don't want to pass 'em as
distinct parameters, then you have to obey some kind of composite-value
syntax.

Would it be possible to extend PQexecParams() et.al. like this:

You currently have paramValues[], paramLengths[], and paramFormats[] (plus other parameters that I'll ignore here).

The format may currently be 0 or 1 (TEXT or BINARY). What if we allow 2 for ARRAY? The corresponding length then specifies how many parameters following are part of the array. The value should point to a structure, that contains pointers to the values, lengths, and formats of the elements. This also allows nested arrays.

That sounds attractive; I think for my particular case it'd be
overengineering, though...

If the client library knows that the server is too old to understand it, it may temporarily assemble a string for those (correctly escaped) values and replace the entries in the original values/lengths/formats arrays temporarily before passing it to the old PQexecParams() implementation.

If the server is new enough the protocol itself can be extended to send the array more efficiently instead of quoting and copying data around.

This would also hide the quoting rules for arrays nicely, as it doesn't require additional methods for escaping. (Currently, escaping for arrays is different from other escaping methods, it needs to be done manually and, frankly, it's a PITA).

...but in the general case it sounds useful, yes :)

Cheers
- t