Re: Enhansement proposal: ordering by parameter

On Wed, Jul 20, 2016 at 1:34 PM, Jordan Gigov <coladict(at)gmail(dot)com> wrote:

> Just about every web developer encounters scenarios where they need to let
> the user sort the results by the column they choose and that leads to
> dangers of possible SQL injection, but also the loss of prepared statement
> caching and optimizations (at least under Java with JPA). I have tried
> using numeric parameters in the ORDER BY expression, but they have no
> effect (last tested under 9.3). What if we make it so that they can take
> numeric parameters?
>
> I realize that might cut the query planner's optimizations short, when
> used, but it's would provide your users with additional security. After
> that the problem remains the direction of the ordering, since that one is
> keyword literals, but keeping just two string constants with the queries
> for ascending and descending is better than creating new strings every time
> depending on the column used or keeping 14 versions of the same query.
>

I am all for security but the planning costs on tables larger than trivial
sizes, tendency to add bugs, and mixing of concerns (literals vs
identifiers) make me think this would be a very bad idea.

As for plan caching, this strikes me as a much bigger foot gun. Suppose
you have a table with a million records.

SELECT * FROM foo ORDER BY ? LIMIT 10;

How would you expect this to be planned? Suppose you run it with ? being
replaced with 1 on the first run and that's the primary key, so it gets
planned as an index scan over primary key with a limit.

You cannot re-use that plan with a different ordering parameter right? So
either you get a good plan that cannot be re-used without giving you the
wrong results, or you have to trade off for uniformly majorly bad plans
(sequential scan over the table, followed by order, followed by limit).
--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more