5 ms·
User selected columns is easy, just select all required columns each time and display as needed, the performance impact of this is minimal. The fact that the qu
by bni 14y ago
User selected columns is easy, just select all required columns each time and display as needed, the performance impact of this is minimal. The fact that the query need not be reparsed is probably a bigger benefit performance wise.
Paging is equally easy, using ROWNUM < X in Oracle or equivalent in other RDBMS.
The order by is a good point. The obvious solution here would be to always have the same sorting as default, and do the user sorting client side (by client here, I mean calling application).
- j_s 14y agoYes, the requirement of paging server-side (when a result set is ridiculously massive or the client/network is so overloaded that alternatives are impractical) is the big one: page X of Y changes depending on the sort, so sorting has to be done server-side too. Do you agree that this is a scenario requiring dynamic SQL?
- derpmeister 14y agoIt doesn't hurt to prepare a statement, execute it once and then deallocate it. Intersect the user-selected columns with a whitelist of possible columns, use a simple flag for sorting and map it to ASC/DESC. Prepend the primary key, bind parameters, execute, harvest the results, deallocate temporary resources. This is somewhat less efficient, but if the alternative is to be open to sqli attacks it'd be my pleasure.
- tptacek 14y ago"Whitelist of possible columns". Congratulations, we are no longer relying on bound parameters to prevent SQL injection. THREAD FINISHED! :)
- derpmeister 14y agoYou could do without using a CTE, it's just not as convenient most of the time. Alternatively, you could pass the column names to a function (using a prepared statement, no less) and let that function figure out which columns are valid. If your tables change frequently, this would save more time in the long run.
- tptacek 14y agoWe are talking about manual, purpose-built functionality designed to prevent SQLI in queries that use bound parameters. Don't move the goalposts: nobody is saying it's particularly hard to avoid SQL injection, just that bound parameters aren't a panacea.
- Devilboy 14y agoIf your tables has SO many columns that you can't simply return them all and show the relevant ones only at the application layer, .... well then just maybe there's something wrong with your database design.
- j_s 14y agoI agree this is the best way; it's still dynamic sql (the database is not paramaterizing the columns, the application is). I think you accurately summarized best practices, but it always comes down to implementation and people make mistakes. (See another person discussing this here in this thread: http://news.ycombinator.com/item?id=4203929 http://news.ycombinator.com/item?id=4203929 )