Re: Suggesting a libpq addition

On Mon, Dec 06, 2010 at 10:14:55AM -0500, Merlin Moncure wrote:
> 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
> 

I have used the libpqtypes library and it is very easy to use.

+1 for adding it or something like it to the PostgreSQL core.
I have people who will try and roll their own because it does
not come with the core. While it is a hoot to see what reinventing
the wheel produces, it is also prone to mistakes.

Regards,
Ken