Re: Suggesting a libpq addition

On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer <marc@msys.ch> wrote:
> Am 06.12.10 15:37, schrieb Merlin Moncure:
>> On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <magnus@hagander.net> wrote:
>>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <marc@msys.ch> wrote:
>>>> I am suggesting adding a function to libpq:
>>>>
>>>> PGresult *PQvexec(PGconn *conn, const char *fmt, ...);
>>>>
>>>> It behaves similar to PQexec, but it allows for printf style varargs and
>>>
>>> How is that not a horrible idea, compared to using PQexecParams()? You
>>> have to remember to do all your escaping and things manually, whereas
>>> PQexecParams() does it automatically.
>>
>> It's only horrible if you stick to printf style formatting and you are
>> using sting techniques to inject parameters into the query.  Non
>> parameterized queries should obviously be discouraged.  However, it's
>> entirely possible to wrap the parameterized interfaces with vararg
>> interface (I should know, because we did exactly that) :-).  This
>> gives you the best of both worlds, easy coding without sacrificing
>> safety.  You might not remember the libpqtypes proposal, but libpq was
>> specifically extended with callbacks so that libpqtypes could exist
>> after the community determined that libpqtypes was too big of a change
>> to the libpq library.  I think ultimately this should be revisited,
>> with libpqtypes going in core or something even richer...I've been
>> thinking for a while that postgres types should be abstracted out of
>> the backend into a library that both client and server depend on.
>>
>> With libpqtypes, we decided to use postgres style format markers:
>> select PQexecf(conn, "select %int4 + %int8", an_int, a_bigint);
>>
>> Everything is schema qualified, so that user types are supported (of
>> course, this requires implementing handling on the client).
>>
>> Data routed through the binary protocol, with all the byte swapping
>> etc handled by the library.  No escaping necessary.  We also added
>> full support for arrays and composites, which are a nightmare to deal
>> with over straight libpq, and various other niceties like thread safe
>> error handling.
>
> That would be a *HUGE* piece of software compared the relatively small
> thing I am suggesting...

well, it's already written. All you would have to do is compile it.

> As for escaping (or not escaping) of string arguments, that can be seen
> as a bug or a feature.  I do not wan't automatic escaping of string
> arguments in all cases, e.g. I might to construct an SQL statement with
> dynamic parts "WHERE xy" or "AND a = b".

libpqtypes doesn't escape at all.  It uses the internal parameterized
interfaces that don't require it.  For particular types, like bytea
and timestamps, this much faster because we use the binary wire
format.  Less load on the client and the server.

> hypothetical example:
>
> filter = "WHERE name like 'Balmer%'";
> if (sort == SORT_DESC)
>        sort = " ORDER BY name DESCENDING";
>
> PQvexec(conn, "SELECT name, nr, id FROM address %s%s", filter, sort);
>
> So what I am aiming at right now is a PQvexec() function that basically
> has printf() like semantics, but adds an additional token to the format
> string (printf uses %s and %b to produce strings.) I am thinking of
> adding %S and %B, which produce strings that are escaped.
>
> That would be a small function, and reasonably safe.  Or rather, the
> safety is in the hands of the programmer.

What you are suggesting doesn't provide a lot of value over sprintf
the query first, then exec it.  You can do what you are suggesting
yourself, wrapping PQexec:

A hypothetical wrapper would be implemented something like:
va_list ap;
char buf[BUFSZ];
va_start(ap, query)
vsnprintf(buf, BUFSZ. query, ap);
va_end(ap);
return PQexec(buf);

This is a bad idea (security, escaping, performance)...we wrote a
faster, safer way to do it, with richer type support.  Or you can do
it yourself.

merlin