Re: Correctly producing array literals for prepared statements

On 23 February 2011 15:34, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> You can send nested arrays safely.  You just have to be very formal
> about escaping *everything* both as you get it and as it goes into the
> container.  This is what postgres does on the backend as it sends
> arrays out the door in text.  It might be instructive to see what the
> server does in terms of escaping.  Note that the way this works it's
> not impossible to see 128+ consecutive backslashes when dealing with
> arrays of composites.

Sounds tedious.

> yes: libpqtypes.  it manages everything in binary.  i've been thinking
> for a while that libpqtypes could be wrapped with variadic templates
> or other c++ trickery.  Because libpqtypes does everything in binary,
> it completely sidesteps all the escaping nastiness.

The fact that libpqtypes does everything in binary mode is
interesting, but doesn't really help me.

Variadic template support is still quite patchy, and I don't think
that it is of particular use here. My proof-of-concept implementation
uses recursive template instantiation and type traits, and just uses
C++98 features. I've attached it for your information. I might be able
to use partial template specialisation to support regular arrays too.
That hasn't been a priority, because C++ generally discourages their
use, and because it's trickier. Arrays don't "know their own size",
and I want to provide a uniform, simple interface. On the other hand,
I've seen interesting things done with template specialisation on
static integral values, such as the size of arrays on the stack, so
perhaps it's possible to support arrays while having a uniform
interface.

To be clear: I don't want to take responsibility for correctly
escaping the array literal. The user has a responsibility to use a
prepared statement/explicit escaping to do that, just as they do with
a regular text value, for example. There is no additional threat of a
traditional SQL injection attack, because we cannot break out of the
array literal itself. However, within the array literal, it is
currently possible to break out of a constant/value literal using a
double quote, to perhaps inject additional values (more than
intended), or to cause malformed array literal errors. Sure, I could
write my own function to escape the constant which is wary of double
quotes, but that would have many of the same challenges as writing a
general purpose drop-in replacement for PQescapeStringConn(). It might
be just as misguided.

--
Regards,
Peter Geoghegan