6 ms·

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 limit

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.

james_in_the_uk 7y ago
GDPR says otherwise. Cost of remediation of legacy code vs. 4% of worldwide turnover.
- cube2222 7y ago
  _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_Regulation https://en.wikipedia.org/wiki/General_Data_Protection_Regula...
  - lucb1e 7y ago
    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.
    - cube2222 7y ago
      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".
      - borumpilot 7y ago
        http://enforcementtracker.com/ http://enforcementtracker.com/
bluedino 7y ago
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)
- radicalbyte 7y ago
  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.
  - mirashii 7y ago
    Please don't. Use placeholders and the DB's prepared statements API _everywhere_. Manual escaping and validation is a recipe for disaster.
    - Rychard 7y ago
      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.
    - radicalbyte 7y ago
      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.
radicalbyte 7y ago
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.
Avamander 7y ago
Then the law must say you can't write code that doesn't utilize prepared statements.
- munk-a 7y ago
  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.
  - Avamander 7y ago
    Of course, it was just a gross simplification. However SQLi that causes data theft as such could however be punished, GDPR kinda does that.
munk-a 7y ago
> 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.
lucb1e 7y ago
> 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.
- cafard 7y ago
  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?
  - morrbo 7y ago
    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/adonet/ado-net-code-examples 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/36264.sql-protect-your-data-against-sql-injection.aspx 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.
earthboundkid 7y ago
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.
dbg31415 7y ago
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/ 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/ https://sudo.pagerduty.com/for_everyone/
hermitdev 7y ago
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.

james_in_the_uk 7y ago

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

cube2222 7y ago
_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_Regulation https://en.wikipedia.org/wiki/General_Data_Protection_Regula...
- lucb1e 7y ago
  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.
  - cube2222 7y ago
    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".
    - borumpilot 7y ago
      http://enforcementtracker.com/ http://enforcementtracker.com/

bluedino 7y ago

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)

radicalbyte 7y ago
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.
- mirashii 7y ago
  Please don't. Use placeholders and the DB's prepared statements API _everywhere_. Manual escaping and validation is a recipe for disaster.
  - Rychard 7y ago
    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.
  - radicalbyte 7y ago
    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.

radicalbyte 7y ago

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.

Avamander 7y ago

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

munk-a 7y ago
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.
- Avamander 7y ago
  Of course, it was just a gross simplification. However SQLi that causes data theft as such could however be punished, GDPR kinda does that.

munk-a 7y ago

> 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.

lucb1e 7y ago

> 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.

cafard 7y ago
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?
- morrbo 7y ago
  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/adonet/ado-net-code-examples 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/36264.sql-protect-your-data-against-sql-injection.aspx 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.

earthboundkid 7y ago

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.

dbg31415 7y ago

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/ 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/ https://sudo.pagerduty.com/for_everyone/

hermitdev 7y ago

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.