Hacker News Reader

Comment by Illniyar | Hacker News Reader

It still boggles my mind that sql injection is a thing in 2016, let alone in a modern codebase from a great software company with amazing engineers.

Somehow we (the programming profession) are doing this whole thing wrong, and I'm not sure why.

View on HN

I find it hilarious how people think there are all these "amazing engineers" around. Sometimes it even seems like hero worship. The reality is that the vast majority of engineers are mediocre even at high-profile tech companies and hot startups. That's not a bad thing and I don't mean it as a criticism; modern civilization has largely been built by mediocre engineers. And even the best engineers make frequent mistakes.

This sentiment is simultaneously terrifying and comforting.

While there are many ways to catch this sort of thing (code review, static analysis, education) I have to place the "blame" here on ActiveRecord. The 'order' API takes an arbitrary string by default. 99.999% of the time, this value can be restricted to a column name or can safely be escaped. For the 0.0001% of the time that something more complex (e.g. Sorting by a calculation like sum(column)) should require a separate, more cautious or complex API call. The common assumption is that all ActiveRecord APIs are "safe" and that simply isn't the case. Brakeman couldn't catch this since it's in Sinatra code and not rails code. Code review didn't catch this because the API was assumed safe by reviewers. Education didn't catch this because not everyone has seen rails-sqli.org. Mind boggling, but still very possible.

Basically any language that has easy string interpolation and takes SQL via a string has this as a potential issue.

The implementations across languages are remarkably similar, I suppose due to the lineage of the original C apis from the various DB vendors.

I don't see any easy way to fix it. If it's possible, and easy to do, it will keep happening.

Perl has a tainted mode where any outside data is marked as tainted. You can only detained it in specific ways. Copying the data from one variable to another keeps the tainted flag.

Pretty much only explicit checks are able to remove the tainted flag from the data. As such you limit the security problems to faulty checks. It is impossible to forget to check something.

Above is used in Bugzilla. Despite all of that, it's still pretty easy to make mistakes. IMO it's rather unfortunate that not more languages have such a feature. This as Perl code if often pretty difficult to read (it doesn't have to be!).

wow, that's a really great idea. I'm surprised that other languages or even frameworks haven't picked that up.

Maybe it can be pitched to Rust.

A lot of frameworks have picked up the notion of "html safe" strings. But this is generally limited html escaping and display rather than SQL.

We have been enjoying 2017 for quite some time now

Reply on news.ycombinator.com