Hacker News Reader

Comment by overcast | Hacker News Reader

I'm confident that if you poked around the software of the vast majority of organizations in this world, you'd find "SQL injections galore". There is just limitless amounts of old code that no one has the resources to address. Should the information on students be well protected? Of course. Have you ever seen the software running your local dentist, doctor, insurance office? It's just impossible to spend money and fix things that already work.

View on HN

> if you poked around the software of the vast majority of organizations in this world, you'd find "SQL injections galore".

As a security consultant, I disagree. There is quite a clear trend in what kind of vulnerabilities web applications have. XSS is getting slightly better but still has a way to go, CSRF has much more awareness and went from "rarely prevented" to "usually prevented", SSRF is getting more common due to new kinds of architectures (more back-ends talking to each other), but SQL injections are, well, not quite a solved problem, but vastly better than it was ten years ago.

I think this can be attributed largely to better libraries that make the secure way the default (such as parameterized queries) and somewhat to more awareness (tutorials and examples will mention things like escaping variables). Perhaps people also hear more about it in school nowadays, but I don't think schools changed much.

My view of the world is somewhat tainted since companies pay me to find their bugs (they are serious about security), but I also am a normal user of random websites like everyone else. Some websites smell, and I'll investigate and sometimes find vulnerabilities. And I hear what is going on in the field, what leaks have happened, what bugs people are finding. I try not to let my paid work taint my view too much.

The SQL Server back end makes it only too easy to run in SQL Injection mode. Does every ASP.NET site out there use stored procedures (or functions) for all database access?

Things today are a LOT different than they were 10 years ago. Not only are ORMs encouraged out of the box, which are (pretty much) immune to SQL injection unless you consciously run native SQL. There is also a HUGE amount of documentation, warnings, etc. on almost everything - got a few links at the end.

You could argue that the major source of insecure code these days is old answers on stackoverflow which show vulnerable examples. Though, i believe there was an initiative to go back and change old answers which posted vulnerable code but not sure if that ever took off.

Some URLS:

As you said SQL server, i literally googled microsoft ado.net example, first result is showing how to use parameterized queries: https://docs.microsoft.com/en-us/dotnet/framework/data/adone...

Googled the same with sql injection/security: huge article on it all https://social.technet.microsoft.com/wiki/contents/articles/...

As an (ex) security professional myself, i definitely agree with the guy above saying that these days in general you do see a lot less of the "schoolboy" vulnerabilities. Definitely still out there though, even in tier 1 public-facing websites of global banks, but certainly not widespread, and certainly not ubiquitous. I'd say this is due to a combination of hand-holding in frameworks, and better education all round.

I am fairly certain dotNet has been pushing parameterized queries since .net 2.0 in 2005. I know at my current employer we have been doing it since 2008.

Indeed, ORM is encouraged out of the box in many ecosystems... and sure enough, it is viewed as a silver bullet by many developers and this is a pity.

Some ORMs are powerful and even have subsets of language for data transformation, and so on... And now you have even another problem: another layer of abstraction that might fail or have their own bugs and security issues.

I miss writing pure and simple SQL queries whenever I'm forced to use an ORM. This is one of the things I enjoy in the Go ecosystem: if you want SQL, probably go with database/sql and write SQL directly instead of any fancy ORMs out there.

I don't write anything user facing, but write plenty of scripts that query a database, return a dataset, create a data frame, and then do statistical analysis on it. The SQL is all pretty straightforward with no ORM and is really easy to understand and use.

Yeah. Most of the time I see people complaining about SQL, I identify that they are trying to build complex nested joins and other stuff likewise for no good reason besides risky premature optimization... And they end up creating little monsters that will haunt them sometime in the future.

Then, what happens next? They might end up hating SQL for no good reason and embracing the 'NoSQL' flag with any kind of non-relational database out there and make the very same mistakes they were complaining about SQL, only this time it'll be probably in JSON instead.

And they will probably notice earlier the errors and end up thinking SQL is harder and, say, whatever-NoSQL-brand is easy... only because they had more experience and a clear vision of everything when trying out the later.

I just wished more people would just keep things simple. For most common web applications out there a single SQL database suffices well, and you can do most, if not all, queries on two or three lines at most with no complex operations and keep things lean.

For sure, for search engine ElasticSearch is marvelous, and for keeping track of documents without ever losing data, Datomic is a robust and proven solution... but what I see is mostly people writing software for mom-and-pops shops using Mongo with no transaction support whatsoever.

But software from 10 years ago is often just plugging along in a closet somewhere.

Parametrized queries are as safe as stored procedures and can be freely used with or without ORMs

The bar for "working" is limited to perfect-path tests then?

As someone pointed out the other day, we do seem to be getting better about security -- less issues overall than there used to be. Likely a sign people are taking this seriously and frameworks have gotten better.

* Full Disclosure Mailing List || https://seclists.org/fulldisclosure/

Ultimately, a big issue with security is that nobody thinks of it as their job. "Oh cool, I made code my non-technical boss said was good enough! Time to clock out!" And that's really flawed. Security is everyone's job. Everyone. The best system is still susceptible to bad passwords. (=

What's security training look like in your org? Most likely there isn't any. So... pitch it. Here's a good template to get started:

* For Everyone - PagerDuty Security Training || https://sudo.pagerduty.com/for_everyone/

> There is just limitless amounts of old code that no one has the resources to address. [...] It's just impossible to spend money and fix things that already work.

So then, "shady" sellers are pricing software solutions below cost, maybe there needs to be some standards around minimum time length warranties similar to manufacturer warranties. Somehow the refrigerator market managed to standardize onto requiring a certain minimum for each fridge - maybe we need a software association or union to do the same.

It usually doesn't actually cost that much to fix SQL injections and they're really easy to find.

I've prioritised identifying and fixing these problems whenever I'm leading teams. It's an easy sell to management (spend a sprint to greatly lower risk) and a good training exercise for teams.

FWIW, today I was looking at a legacy PHP site and noticed a SQL injection possibility in its query handling which I fixed while fixing another problem. It’s not every day that I see these things, but basically every time I look into a legacy PHP site, I notice one or two.

We have about 200,000 lines of code that look like this:

    250:             Let strsql = "Update TbfFileManagementSystem Set FileName = replace(FileName,'" & Me![PONumber].OldValue & "','" & Me![PONumber] & "')" & _
    " Where FileID = '" & strFileID & "';"
    260:             mdb.Execute (strsql)

Insert a line of code before that statement which validates that the variable is an integer and you're sorted. As an added bonus, check that it's in range.

Please don't. Use placeholders and the DB's prepared statements API _everywhere_. Manual escaping and validation is a recipe for disaster.

Sad as it is to say, but this is a quick way to get your PR stuck in code-review hell.

If the code you're starting with is already in such a state, the chances of the reviewer being:

  1. able to understand what you've done
  2. receptive to unnecessary functional changes to a legacy application

... is effectively 0.

Absolutely - that is the desired end state.

In some languages migrating to the prepared statement API can be a considerable amount of work and that takes a lot of time.

If the code is mainly made up of queries with an integer ID then you can make the code safe using guards with a fraction of the effort meaning that your software is stable faster. Then you can take your time rewriting your data access layer to use the safe-by-design APIs.

I should have made that clear in my original post :)

BTW at some point you might need to get into escaping / validation to support operations not supported by the safe APIs. If/when you encounter this it's a massive red flag. Your system design should reflect that. It should be treated like radioactive waste: be paranoid.

Time to fire up sed and awk?

Not in any code I've written; I only use parameterized queries. I definitely maintain code that just sends parameters as strings. The prevalence of this is alarming.

Then the law must say you can't write code that doesn't utilize prepared statements.

Nothing is this simple in technology ever - it is extremely difficult to declare blanket rules like this.

I do think it's reasonable to try and get third party software auditing to become more normalized, if a contractor is writing scheduling system to control class enrollment I'd appreciate another contractor looking it over... but even that has it's issues.

Of course, it was just a gross simplification. However SQLi that causes data theft as such could however be punished, GDPR kinda does that.

GDPR says otherwise. Cost of remediation of legacy code vs. 4% of worldwide turnover.

_up to_ 4% of worldwide turnover or 20 million $ (whichever is higher).

For local businesses the second one probably sounds much more scary.

https://en.wikipedia.org/wiki/General_Data_Protection_Regula...

I still think that will never be enforced on companies that didn't have it coming. I never heard of the EU fining a company in a way that truly hurt them unless they knowingly and exceedingly violated the law.

As an example, Cambridge Analytica was started in 2013 and was presumably a startup when it started doing public manipulation, so that's an example of whom I think has it coming if they get a company-bankrupting fine. Your mom and pop shop having a data breach due to a negligent SQL injection won't have to close up just because of that. I would be interested to hear a case where a company was fined to bankruptcy when they did not totally deserve it. Until then, I feel like reciting this over and over (people often bring it up in a negative context) is just spreading FUD about doing business in the EU.

I can't find any sources now, but there have been numerous fines for minor data breaches. (Like sending a mail to all your clients and putting everybody on CC, not BCC, so each client gets to know all others).

But they absolutely haven't been fined to bancrupcy, just a few thousand Euro.

That's why I underscored the "up to".

http://enforcementtracker.com/

Reply on news.ycombinator.com