Hacker News Reader

Comment by j_s | Hacker News Reader

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?

View on HN

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