[btilly]

Click the top of any reverse-sortable column in a web user interface to see one obvious example.

People don't do those in JavaScript?

That's what I did a decade ago. Well, then I moved it back to the server after we got tired of the performance problems that JavaScript had back then. But today it wouldn't be an issue. And when we moved it back, we were careful not to have an SQL injection attack. If memory serves we actually did the resort in Perl. (In our defense, much of the data we were serving lived in flat files, or were generated on the fly from a compute server, instead of coming from a database.) However at another company I had the same problem, and I did the obvious "process CGI parameter, insert appropriate ORDER BY statement". Where the definition of appropriate was by column position, from which I worked out the field to sort by, so I didn't have to trust the client for the name of that column.

I should back up. We didn't have any SQL injection attacks that I knew of and were reasonably careful. But that code base did not get audited, so I can't really know that. However after the next company that I worked for got bought by eBay, they did a penetration test on us. The worst thing that they found was an open redirect that could be used to let a spammer construct a link to any web page with us as the referer.

I'm happy to use this as a testament that programmers really can avoid SQL injections. However their surprise that they didn't find any SQL injections in our code supports your claim that most teams fail to do so successfully.

[tptacek]

I'm not sure what you think I'm arguing; that it's impossible to avoid SQLI? Of course not. All I'm saying is that parameterized queries aren't the end of the story; the teams that believe they don't have SQLI because they use parameterized queries are the ones that are going to lose their apps to SQLI.

[btilly]

What is the full story?

It seems to me that two tricks nail it. First use parametrized queries. And secondly if you have information you need to send/receive from the client that isn't easily parametrized, have a limited list of possible things that can be accepted back, which is checked in code while building the query.

If you're doing those two things, I don't see how much work it is to avoid SQLI. Furthermore if you're using a reasonable ORM, then you should get both of those pretty much for free. (Well you have the overhead of learning the ORM itself.)

[tptacek]

There's almost nothing wrong with your second "trick" (when we write recommendations, we usually suggest people never allow users to directly write syntax, and that they structure their app so that the parameters the user sees couldn't possibly land in SQL; use "1" for "ASC" and "0" for "DESC", etc).

But it's not nearly as powerful a statement to say "use parameterized queries and then do everything else right" as it is to say "just use parameterized queries and you won't have this problem", is it?

[Dylan16807]

How about "only pass user-sourced data in parameters"? So anything that isn't a parameter is coming from the app, possibly as a response to user data, but not actually from user data. This statement seems just as powerful as the original. Anything misleading about it? Somewhere you can't use parameters and can't simply use app-supplied query pieces?

[tptacek]

You see how we're converging on "just use proper input validation", which is the all-time least useful piece of advice in software security?

[Dylan16807]

Because input validation is hard to do. A rule to not pass input EVER (outside of parameters), even in a supposedly validated form, is something different.

[there]

People don't do those in JavaScript?

when you have to add pagination to sorting, you have to send the sort column to the database server.