Re: ORDER BY in prepared statements

On 01/21/2015 03:09 PM, Bryn Jeffries 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.

In addition to what David said, applications/frameworks may provide that
functionality. For example in Django:

https://docs.djangoproject.com/en/1.7/ref/models/querysets/#order-by

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com