On Mon, Dec 06, 2010 at 10:14:55AM -0500, Merlin Moncure wrote: > On Mon, Dec 6, 2010 at 9:55 AM, Marc Balmer <m...@msys.ch> wrote: > > Am 06.12.10 15:37, schrieb Merlin Moncure: > >> On Sun, Dec 5, 2010 at 5:10 AM, Magnus Hagander <mag...@hagander.net> > >> wrote: > >>> On Sun, Dec 5, 2010 at 10:22, Marc Balmer <m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers