Re: Correctly producing array literals for prepared statements

On Tue, Feb 22, 2011 at 10:16 PM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:
> I'm investigating the possibility of developing a utility function for
> our C++ client library, libpqxx, that produces array literals that can
> be used in prepared statements. This problem appears to be a bit of a
> tar pit, so I'm hoping that someone can help me out. My goal is to
> produce a template function that accepts arbitrarily nested standard
> library containers, that contain at the most nested level
> constants/literals of some type that can be fed into a stream, such as
> an int or a std::string.
>
> I'm aware that I cannot assume that types are delimited by a single
> quote, even for built-in types. I thought that I would put the onus on
> the client to specify the correct delimiter, by checking pg_type
> themselves if necessary, but default to ',' . Is this a reasonable
> approach?
>
> Escaping/quoting individual elements seems tricky. I have produced a
> generic and superficially well behaved implementation by using double
> quotes for constants. However, I have now opened the door to malicious
> parties injecting multiple array elements where only one is allowed,
> or causing malformed array literal errors by simply including a double
> quote of their own. It's not clear where the responsibility should
> rest for escaping constants/ensuring that constants don't contain
> double quotes. Can someone suggest a better approach? I can't very
> well use single quotes, because they are escaped/doubled up when we
> pass the array literal to something similar to PQexecPrepared(), and
> they shouldn't be - strings end up looking like this: "'has errant
> single quotes on either side'".

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.

> Since Postgres only supports encodings that are ASCII supersets, I
> don't believe that I have to consider encoding - only my clients do.
>
> Can someone please point me in the direction of an established client
> library/driver where all corner cases are covered, or at least enough
> of them to produce a net gain in usefulness? There may well be
> additional subtleties that have not occurred to me.

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.

merlin