On Wed, Jul 20, 2016 at 1:34 PM, Jordan Gigov <coladict@gmail.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.