Hacker News Reader

Comment by shakna | Hacker News Reader

Bobby Droptables!

How is SQL vulnerabilities like this still happening to systems that need to be secure?

It seems obvious to us, but computer literacy in out country (and the world) is quite low. I think this underlines how bad it is. Can you imagine if the English literacy rate were only a few %? It wouldn't be uncommon to hear of people doing strange things since they wouldn't be able to read warning signs.

You're right, but it is depressing.

I find it akin to hiring a librarian who cannot read. Either because they're cheap, or they have the right friends.

In this case, I think it's probably because most librarians can't read, or at least many in publicly funded libraries. It's hard to point to one illiterate librarian when their colleagues are also illiterate.

> Can you imagine if the English literacy rate were only a few %

People would start to confuse "your" and "you're" or "its" and "it's".

Quite a scary thought.

We would definitely be lead into the abyss.

> People would start to confuse "your" and "you're" or "its" and "it's".

I am genuinely trying to determine if this is a joke. :) People already do confuse those! I have unfortunately seen many arguments on Facebook over the correct usage.

Since I was downvoted, you're not the only one who fails to detect e-sarcasm :-)

I think the point is this is not reddit, jokes not very constructive in a debate, however funny.

:) In hindsight it should have been pretty obvious haha.

This is pretty much almost already the case for mathematics; understanding of basic mathematical concepts in the general public is piss poor. No one is up in arms about that.

A combination of:

1. Incompetent contractors

2. Well-meaning non-technical hiring managers with slim budgets

3. Talent that's not lining up to work for the gov't because it's most definitely awful compared to the opportunities available

The story of the US gov't dismal IT infrastructure. In 2017, IT budget is projected to be 90 billion.

https://www.whitehouse.gov/sites/default/files/omb/budget/fy...

How much of that do you estimate is wasteful?

It'd sure be nice to register to vote/get healthcare/etc on my phone.

Some ideas -

- Outsourcing offshore to lower costs through a 3PP

- Junior developers with inadequate training

- Sheer incompetence

Incompetence can be extended to management - i.e. a lack of policies and auditing to undertake due diligence.

Sometimes SQLi isn't always obvious even if they are aware of the attack vector if the developer is not thinking defensively. For example inputs from a cookie or HTTP headers such as HTTP referer etc.

4) Intentional negligence.

> "Sometimes SQLi isn't always obvious"

Pretty simple: 100% of the time use prepared statements, NEVER build an SQL query yourself.

Tada, 0% SQLi.

Yet researchers have actually investigated the problem of automatically generating prepared statements from existing code and it turns out to be weirdly challenging.

The problem isn't that some people are idiots. The problem is that we, as an industry, don't have great security solutions.

In the words of Bruce Schneier - "Security is a process, not a product" [1]

Process that could help mitigate this bugs -

- Security / safe coding training

- Code audits

- Testing - static and dynamic

- Policies that are communicated such as "Always use prepared statements" or "use X framework"

[1] https://www.schneier.com/crypto-gram/archives/2000/0515.html

While this is true, the developer implementing this can still make a mistake. I've seen (esp. on long multiline queries that get modified over some time), a mix of prepared variables for things like userids and string concat for things like table names but the dba or the dev doesn't realize the attacker has control over the table name due to how they are handling user input on that particular endpoint. Maybe the table name is passed in on one endpoint because it's old and janky. Then it fools people because they see it's a str passed to prepared statement func and assume it's safe. I've seen this in some place in a large number of the apps I've worked on.

If you have to let the attacker control a direct substring of SQL, then use a whitelist of allowed characters – for tablenames, [a-zA-Z0-9_] is usually good enough, and then put that in quotes (as some databases reserve keywords such as "user" or "password", which is bad if you want to name columns like that).

I’ve had quite a few codebases I’ve worked on where I had to replace naive code [1], and until now, it’s always been easily possible to ensure that the entire space of possible inputs is limited enough to prevent SQL injections.

Sure, there are rare projects where you have to do such very complicated systems, but for 99%, it’s possible to get guaranteed protection from SQL injections.

    ________________________

1: "db->query('SELECT 1 FROM users WHERE username = "' + $_POST['username'] + '" AND password = "' + md5($_POST['password']) + ';"');" was real code I’ve seen

But then you add a few layers of frameworks on top of that, each with its own mix of convenience, abstraction and SQLI protection. Occasionally, people will use available the escape hatch mechanisms to deeper levels when the convenience (or their understanding of it) does not align well with what they need to do. It's not impossibly hard to get confused about where the handholding stops in a complex stack like that.

I might naively underestimate the amount of pure "naked SQL" code still written, but I suspect that in this age, more SQLi accidents happen in "95% safe" environments. Kind of like the class of accidents we expect from 95% autonomous cars, where an unfocused and out of practice driver is expected to take over the wheel when the robot brain decides that driving is to difficult.

Because no part of "securing an enterprise network" includes any meaningful filter on the shitty software the organization is going to buy and run.

If something "needs to be secure" then IT is likely up to its ears in reams of policies and reports, Cisco firewalls, VLANs, Group Policies, Symantec antivirus products, Websense, and password complexity regimes. It seems only elite tech companies and the military/intelligence community are making serious steps towards "How can I choose and commission software that is not so likely to contain serious vulnerabilities?" Even obvious ones.

Mainstream IT security practices can, at best, put up a perimeter around the steaming pile of garbage fresh from the lowest bidder so that it can't infect anything else, and only insiders can exploit it, and maybe there will even be a record of them doing things that look like exploit attempts.

Built by the lowest bidder?

Who then outsourced the work to Russia and China.

Virtually every organization i've seen, private or public, that isn't focusing on secret or higher clearance data, doesn't spend more than a weekend on appsec. Even organizations that have security teams dedicated to trying to secure its appsec infrastructure often do it as a black box, or after it is already in production.

Aside from that? It should just be expected that any system that uses a structured query language and can receive user input is vulnerable to injection.

> How is SQL vulnerabilities like this still happening to systems that need to be secure?

Because third party code review wasn't a requirement in the contract or spec.

How anyone these days accepts code from a vendor, verifies that it works, and then puts it into production and pays the contract baffles me.

Because many developers don't honestly believe that SQL represents a code execution boundary. Much of the time, the tendency is to think of SQL as simple I/O with a disk. And one rarely needs to worry about escaping interpretted expressions when streaming into and out of an I/O pipe.

They don't think of it as "executable code" because of the limited and monotonous nature of CRUD operations typically associated with data persistence.

They just think of it as yet another inconvenient form of declaritive data already at rest. Perhaps akin to mark-up. Raw data, like XML.

With JSON, some developers innately clue themselves in, because JavaScript, but they don't honestly understand the root of the hazard. They're just thinking FIRE BAD! Then they hack some JSONP into place because it's fun to do that sort of thing.

Most novice developers (my younger self included, tisk tisk) strongly resist protections against even HTML injection, until they see a massacre play itself out in the real world, when someone drops the ball.

This is also why smart money in appsec is focused on langsec and framework-integrated security controls, such as by forcing security patterns (e.g. html-context output encoding) by default and by compelling developers to work harder or, should they decide to break the rules, to do so more visibly.

("work harder to be wrong" specifically)

Reading XML ain't secure against malicious data either.

I don't know why you're downvoted, because this is exactly correct.

https://www.vsecurity.com//download/publications/XMLDTDEntit...

Granted, most modern parsers disable the features that can trigger this by default. But there's still a lot of code out there compiled against libraries that did not, and some of that code is still updated and extended today.

PHP. It's just too easy to do it wrong in PHP.

It's not just PHP. It's too easy to do in many languages. It almost seems odd there isn't a 'libsodium but for DB access' or 'linter that makes your CI server explode on dangerous DB API use'. Things like that don't have to be foolproof but they'd be better than the current anything-goes default.

It's easy to do in any language/framework which accepts raw strings as SQL input. Which is pretty much all of them, except for ORMs and similar (and even those usually have an escape hatch, which sufficiently lazy/clueless developers can and will find eventually).

If a raw string is allowed, then the standard facilities - string concatenation, formatting etc - can be used to write injection-vulnerable code.

The only mitigation that does not require educating the end developers is to completely banish strings from the API, and require everything to go through a DSL layer instead. Said layer can then ensure that all values are properly quoted and/or parametrized prepared statements are used as needed.

"Said Leopard to Baviaan (and it was a very hot day), "Where has all the game gone?"

And Baviaan winked. He knew.

Said the Ethiopian to Baviaan, "Can you tell me the present habitat of the aboriginal Fauna?" (That meant just the same thing, but the Ethiopian always used long words. He was a grown-up.)"

By the same token, the Texas Chainsaw Massacre is the fault of Makita because chainsaws just make it too easy to go on a murderous rampage.

Stihl

Lots of languages have easy string interpolation, and don't do anything to prevent that being used around sql statements. I'm not sure why php is being called out in this particular case. Sure, it has warts, but in this specific area, it's no better or worse than most languages. It supports bind variables and the documentation pushes you that way.

It's been awhile since I used PHP but even if you're not using a framework I'm pretty sure you can just wrap your strings with mysql_escape_string or some similarly named function.

It has also been a long time since I've used php, but when I did there were a few problems here.

One is that mysql (opposed to oracle or pgsql) is kind of unusual in allowing quotes indiscriminately for all column types, so in order to do this right you have to only do it for string type columns and prevent injection on different types some other way.

Another is that most of the APIs around the more sprintf-style quoting (eg: `query("SELECT * FROM table WHERE x = %", someStr);`) are tied into prepared statements, which carry their own problems that also differ per engine.

It's actually not as simple as you might think. On top of that, php used to have the whole automatic quoting thing that just made a hash out of things.

>I'm pretty sure you can just wrap your strings with mysql_escape_string or some similarly named function

Prepared statements have been a thing in PHP for a long time, now. Concatenating strings into SQL queries is no longer ever the right answer.

If you don't use an ORM, PDO, or generally act like PHP hasn't changed in the past decade, yes it is.

Reply on news.ycombinator.com