Обсуждение: Array value syntax and escaping

Hello all,

I am currently adding array value handling to the PostgreSQL interface
for the Chicken Scheme compiler[*] and I was wondering if there's a more
detailed documentation for the exact syntax of arrays than the short
natural language explanation in the manual.

There doesn't appear to be a helper function in libpq to escape (and
unescape) string values for use inside array values and I'm concerned
that my homebrew procedures might not be foolproof.

If I understand correctly, the connection's character encoding is also
used to determine how strings sent by "PQsendQueryParams parameters"
(I don't know if there's an unambiguous name for those) are parsed, and
it is conceivable that either through bogus or malicous input a
multibyte array string could be wrongly escaped, which means one array
value could "break out" of its proper position in the array, resulting
in an array of different length or contents than intended.

IMHO, it would be a Good Thing to have helper procedures in libpq.
That would fix the problem once and for all in one place.

I briefly considered "abusing" the PQescapeIdentifier procedure for
escaping since the syntax for literals inside arrays seems to be exactly
like that of SQL identifiers, but I'm not 100% sure about that and I
also think the PQescapeIdentifier procedure shouldn't be overloaded for
this purpose.

Cheers,
Peter

[*] http://chicken.wiki.br/eggref/4/postgresql
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

Peter Bex <Peter.Bex@xs4all.nl> writes:
> I am currently adding array value handling to the PostgreSQL interface
> for the Chicken Scheme compiler[*] and I was wondering if there's a more
> detailed documentation for the exact syntax of arrays than the short
> natural language explanation in the manual.

There is not, but you could always look at the source for array_out and
array_in if you want definitive answers ;-).  Personally what I'd do is
always double-quote each non-null array element; then the rules reduce
to "backslash any backslashes or double quotes".  If you're working in
a sane client encoding (not SJIS for instance) this is pretty trivial.

> IMHO, it would be a Good Thing to have helper procedures in libpq.

The problem with that is that the necessarily-arbitrary API would
probably add as much or more complexity as would be saved.  If C had
a simple and universally-followed convention for variable-size arrays,
it'd be easier to provide useful helpers ...

> I briefly considered "abusing" the PQescapeIdentifier procedure for
> escaping since the syntax for literals inside arrays seems to be exactly
> like that of SQL identifiers, but I'm not 100% sure about that and I
> also think the PQescapeIdentifier procedure shouldn't be overloaded for
> this purpose.

Well, that wouldn't work anyway, since backslashes aren't special in
identifiers.

            regards, tom lane

On Thu, Apr 01, 2010 at 04:37:23PM -0400, Tom Lane wrote:
> There is not, but you could always look at the source for array_out and
> array_in if you want definitive answers ;-).  Personally what I'd do is
> always double-quote each non-null array element; then the rules reduce
> to "backslash any backslashes or double quotes".

That's what I'm doing right now.

> If you're working in a sane client encoding (not SJIS for instance)
> this is pretty trivial.

That's a relief :)
OTOH, for a generic library, it's not a good idea to make such assumptions..

> The problem with that is that the necessarily-arbitrary API would
> probably add as much or more complexity as would be saved.  If C had
> a simple and universally-followed convention for variable-size arrays,
> it'd be easier to provide useful helpers ...

What I was proposing is a simple escaper for string values, nothing
more.  Putting array decoration around those is trivial, encoding the
strings is the tricky part.

However, this got me thinking: It could use the same system as
environment and argv values in C, or the new connection procedures of
libpq; a null-terminated list of string pointers.

But then the question is how to encode nested arrays.  I guess it's
feasible to pass the array depth as an extra argument to the escaping
procedure, since we know arrays cannot have variable depths between
elements.  This could really work and wouldn't need to be overly complex.

It wouldn't save a lot of complexity, but it would save some wheel
reinvention in a case where there's room for error, just like PQescape*.
You could argue that PQescape* don't save a lot of complexity either,
yet those are considered a good idea.  I don't see how this is any
different.

> > I briefly considered "abusing" the PQescapeIdentifier procedure for
> > escaping since the syntax for literals inside arrays seems to be exactly
> > like that of SQL identifiers, but I'm not 100% sure about that and I
> > also think the PQescapeIdentifier procedure shouldn't be overloaded for
> > this purpose.
>
> Well, that wouldn't work anyway, since backslashes aren't special in
> identifiers.

Good thing I didn't abuse it, then :)

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth