Hacker News Reader

Comment by j_s | Hacker News Reader

Without dynamic sql, it is tough to do arbitrary (user-selected) columns, sorting, and server-side paging simultaneously (limiting the result set in the query instead of skipping through results in the app). SQL does not allow use of parameters in all the necessary locations in the query (result column list, order by list, etc.), and some databases are more limited than others.

There are options available as one or more of the above 3 requirements is dropped: http://stackoverflow.com/questions/149380/dynamic-sorting-wi...

View on HN

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

Yes, 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?

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

"Whitelist of possible columns".

Congratulations, we are no longer relying on bound parameters to prevent SQL injection. THREAD FINISHED! :)

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

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

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

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

Reply on news.ycombinator.com