[Devilboy]

It's bulletproof if you don't use string concatenation in your prepared statements.

EDIT: No this doesn't limit you to 'simple queries'! How do you figure that? There are only a VERY small subset of problems you can't solve like this. So small that in 10 years I've only had to do it once and I write SQL Server 5 hours a day.

Want to give me an example please?

[tptacek]

So, in other words, it's bulletproof if you only use simple queries.

In MySQL, for instance, LIMIT and OFFSET have to be integer constants; the wire protocol won't allow you to bind variables to them. Does your SQL engine allow you to parameterize a table name? Can you parameterize columns? What about ASC and DESC? And this is just simple stuff. What about pages with "Advanced Search" that have to implement query builders?

[danparsonson]

All of those scenarios can be handled in such a way that you're only concatenating known strings rather than user input - e.g.

stmt = "SELECT col1 FROM table ORDER BY col2 " + (isDescendingSort ? "DESC" : "ASC")

As long as all your user input has been filtered through type checking, enumerations, etc. (aside from parameters), is that not a safe approach?

[tptacek]

Of course you can build these queries safely.

Of course you should use prepared statements when possible.

But web devs do have a bad habit of saying "we're safe, we used prepared statements", and then losing their app within 5 minutes because of the code than handles sortable columns in their table views.

[Devilboy]

I've never used MySQL (and God willing I'll never have to) but in SQL Server you can limit rows by setting @@ROWCOUNT before your SELECT statement. SQL Server also allows CASE in your ORDER BY clause that can do pretty much anything you could want.

I've never had a situation where the client enters the column names to return in the UI. I mean the users should not have to know the column names in your database so surely you'll do that some other way instead? It's pretty rare (and probably wrong) to have hundreds of columns being returned, so we'd just return them all and show/hide the relevant ones on the application-side.

Same for table names. Why would you need to have a parameterized table name? This has never come up in all my years of SQL Server. Sounds like bad DB design or something exotic that I've never had to do. I mean how would you index queries like that anyway?

For your 'Advanced Search' I'd probably use a temp table or table variable and do the query in multiple steps using 'IF' switches depending on the flags or setting passed to it.