Обсуждение: 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

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

"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
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