Thread: PQexecParams, placeholders and variable lists of params
Hi, PQexecParams expects a query string with "$1", "$2"... placeholders, which refer to as many params in the param list. This keeps SQL injection at bay. Is there a way to express "variable length" lists? IOW, if I want to do a query like "SELECT * FROM customers WHERE id IN ($1, $2) AND name like $3;" is there a way to do that without knowing beforehand how many values go into the IN list? It would be very welcome for you to rub my nose against the place in The Fine Manual where I could have found that :-) Thanks & cheers - tomás
Attachment
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$;
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>:
Hi,
PQexecParams expects a query string with "$1", "$2"... placeholders,
which refer to as many params in the param list. This keeps SQL
injection at bay.
Is there a way to express "variable length" lists? IOW, if I want to do
a query like
"SELECT * FROM customers WHERE id IN ($1, $2) AND name like $3;"
is there a way to do that without knowing beforehand how many values go
into the IN list?
It would be very welcome for you to rub my nose against the place in The
Fine Manual where I could have found that :-)
Thanks & cheers
- tomás
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
Attachment
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
Attachment
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 specifieshow many parameters following are part of the array. The value should point to a structure, that contains pointersto 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 (correctlyescaped) values and replace the entries in the original values/lengths/formats arrays temporarily before passingit 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 andcopying 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'sa PITA). I'm sure a lot of users (and authors of client libraries like myself) would really appreciate some improvements in handlingarray 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 specifieshow many parameters following are part of the array. The value should point to a structure, that contains pointersto 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 beforepassing 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 andcopying 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'sa PITA). ...but in the general case it sounds useful, yes :) Cheers - t