Hm, my first thought was that you should just be using bind parameters instead
of interpolating variables directly into the query.
But the more I think about it the more I like your idea. It's true that using
parameters takes away most of the use cases for the kind of interface you
suggest. But there are still cases that remain. And in those cases it would be
possible to do it more cleanly and conveniently than with a stock sprintf.
In particular cases like when I want to insert one of a small number of
constants and want to be sure the planner plans and caches separate plans for
each value; or when I want to insert entirely different subexpressions
depending on some parameter; or most commonly of all I want to vary the order
of the ORDER BY expressions and still have every chance of using indexes.
Aside from the convenience I think it would be interesting from an
injection-safety point of view. We can offer a %-escape for "string with SQL
quoting" and a separate %-escape for unquoted SQL text which is documented as
being the wrong thing to use for user-provided data. And we can ensure that
all escapes except for this "raw SQL" escape are all injection-safe.
But anything you provide you should provide both in PQexec form and PQprepare
form as well (and I suppose in PQexecParams form). This might seem pointless,
if you're interpolating some values why not interpolate them all? The answer
is that you quite often want to interpolate a few specific values, often
values that don't have many possible values and might affect the plan, but
definitely don't want to interpolate user-provided values that have many
possible values.
A typical example might be something like:
SELECT * FROM invoices WHERE customer_id = ? ORDER BY { order_by_clauses[column_selected] }
You certainly don't want to a plan a new query for every possible user, but
you don't mind caching 5 different plans for the five display columns
depending on which the user has clicked on.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
