Skip site navigation (1) Skip section navigation (2)

Re: Correctly producing array literals for preparedstatements

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>,PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Correctly producing array literals for preparedstatements
Date: 2011-02-23 15:40:30
Message-ID: 20110223154029.GC9375@aart.is.rice.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Feb 23, 2011 at 09:34:06AM -0600, Merlin Moncure wrote:
> On Tue, Feb 22, 2011 at 10:16 PM, Peter Geoghegan
> <peter(dot)geoghegan86(at)gmail(dot)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
> 

Avoiding the escaping by using binary parameter transmission is
the best method. Shameless plug: libpqtypes is great!
I hope that it can be eventually included in the core distribution.
It is not uncommon to get "It's an add-on package???" and avoidance
of pieces outside of the standard dist regardless of its value.

Regards,
Ken

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2011-02-23 15:44:01
Subject: Re: pl/python quoting functions
Previous:From: Andrew DunstanDate: 2011-02-23 15:36:01
Subject: Re: Correctly producing array literals for prepared statements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group