On Wed, Jul 20, 2016 at 1:34 PM, Jordan Gigov <colad...@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. http://www.efficito.com/learn_more
