Re: Suggesting a libpq addition - Mailing list pgsql-hackers

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...

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".

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.