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

Correctly producing array literals for prepared statements

From: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Correctly producing array literals for prepared statements
Date: 2011-02-23 04:16:58
Message-ID: AANLkTiktDh-qOW-Tn68SrP3E99yvHg++kxyTwM8Kk8e_@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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'".

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.

-- 
Regards,
Peter Geoghegan

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2011-02-23 04:36:38
Subject: Re: Correctly producing array literals for prepared statements
Previous:From: Josh BerkusDate: 2011-02-23 03:54:16
Subject: Re: TODO: You can alter it, but you can't view it

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