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