Re: ORDER BY in prepared statements

On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries <bryn(dot)jeffries(at)sydney(dot)edu(dot)au>
wrote:

> Paul Jungwirth wrote
> > I'm not sure how to make a prepared statement that lets you name a
> > column when you execute it. Maybe someone else can chime in if that's
> > possible.
>
> David J. responded
> > You cannot. By definition parameters, in this context, are values - not
> > identifiers.
> > [...]
> > In both situations there is no way for the planner to plan and cache a
> > single query whose order by column varies. No matter what you do at best
> > you can have a single plan for each explicit order by column that you
> wish
> > to specify.
>
> That's what I'd figured. The motivation to use prepared statements in
> application layers is not so much having a single plan but more the
> insulation from SQL injection. The intent of the given ORDER BY example was
> to restricts inputs to valid identifiers rather than part of the query
> expression.
>
> Maybe what we need in ODBC libs and the like is a "protected
> statement" that follows the same construction as a prepared statement but
> additionally checks catalogs to validate identifiers.
>
> Bryn

​The canonical way to do this, in reasonably recent PostgreSQL versions, is
to wrap your desired dynamic SQL statement in a function. Within that
function construct the SQL string with the assistance of the "format(...)"
function. That function has specific placeholders for literals and
identifiers that will ensure that the constructed SQL string is built in a
safe manner.

​
http://www.postgresql.org/docs/9.4/interactive/functions-string.html#FUNCTIONS-STRING-FORMAT

Then you call the function and pass in the arguments are value parameters;
which the function then converts into either literal or identifiers as
instructed to by the format expression.

David J.