297

I'm no techie and would like your expertise in understanding this. I recently read a detailed article on SQLi for a research paper.

It strikes me as odd. Why do so many data breaches still happen through SQL injection? Is there no fix?

  • 83
    You know that malaria is still around right? and that actually kills people. – zzzzBov Jun 29 '16 at 14:11
  • 67
    Buffer overflow exploits have existed for over 30 years, yet they are still a thing, despite the fact that it really is not that hard for the vendors to fix. – RBarryYoung Jun 29 '16 at 15:14
  • 33
    I'd ask the same thing about SQL. – JamesFaix Jun 29 '16 at 19:38
  • 36
    Non tech-savy customers simply wont pay for code changes until they see something bad happen that hurts their bottom line: "It's not in the budget!", "I dont see the problem, it's been working fine the last 15 years". They don't know that they have been hacked via SQL injection because they never check their server logs, and the hacker is never nice enough to send them an email ... – iHaveacomputer Jun 30 '16 at 2:06
  • 55
    Picking people's pockets is still "a thing" after thousands of years of trouser-wearing. – Max Williams Jun 30 '16 at 11:25

16 Answers 16

466

There is no general fix for SQLi because there is no fix for human stupidity. There are established techniques which are easy to use and which fix the problems (especially parameter binding) but one still has to use these techniques. And many developers are simply not aware of security problems. Most care that the application works at all and don't care much about security, especially if it makes things (even slightly) more complex and comes with additional costs like testing.

This kind of problem is not restricted to SQLi but you'll find it with buffer overflows, certificate checking, XSS, CSRF... . It is more expensive to do secure programming because of the additional testing and of the additional expertise needed by the (thus more expensive) developer. And as long as the market prefers it cheap and does not care much about security you get the cheap and insecure solutions. And while security by design helps a lot to make it better developers often work around this design because they don't understand it and it is just in their way.

  • 82
    Lack of awareness and Laziness... – Matthieu M. Jun 27 '16 at 6:34
  • 222
    "There is no general fix for SQLi because there is no fix for human stupidity." That's a beautiful quote! – miva2 Jun 27 '16 at 9:36
  • 43
    There are general fixes for sqli just like there are fixes for buffer overflows, they involve working on higher level constructs that do not allow for those to occur. Working with most managed languages fixes buffer overflows, techs like linq fix sqli. Sqli will be there as long as people use sql directly but it's already gone for those who want it so – Ronan Thibaudau Jun 27 '16 at 9:50
  • 18
    It's not always human stupidity thou (at least not by the part of the developer)! I know many brazilian companies who do a cheap tatic here: They hire instead of full-fledge developers, they hire a bunch of trainees to avoid taxes and pay less their employees. And these are big companies! Such juniors have yet no knowledge (some of them are on their first semester of their studies) or even awareness of such problems, and are placed with the responsability to build systems that will be, of course, flawed. I have seen this too much here. I could even give 5 to 6 names. It's a really sad thing. – Malavos Jun 27 '16 at 14:20
  • 47
    @Malavos: I don't necessarily mean stupidity of the developer. Hiring developers based on salary instead of knowledge is stupid too. – Steffen Ullrich Jun 27 '16 at 15:42
277

Because it's not a problem.

  • When was the last time a company with a SQL injection vulnerability got hauled up in court, and slapped with a big fine for being reckless with user data, and the directors' warned, fined or locked up for negligence?

  • When was the last time a company lost a big contract because their company website login page didn't validate passwords properly?

  • When was the last time a qualified regulator/auditor from a professional organisation had to approve and sign off a public facing computer system before it could be put into use?

You would think that "people will die" would be a good enough reason to make buildings with fireproof materials, alarms and good escape routes. It wasn't. We introduced regulation to force non-flammable building materials, fire safe designs with fire breaks, fire alarms.

You might think "people will die" would be a good enough reason to make everyone care about building structural design. It isn't. It just isn't. We have to have regulation to have qualified engineers sign off on building designs, that they be designed and built for specific uses, and when things fail, society takes the engineers to court.

You would think that "people will die" would be a good enough reason to make food processing clean and safe, but it wasn't.

SQL Injection is less obvious, less publicly visible, and has less severity impact, and is in a completely unregulated industry.

Even to companies which do care about it, they can't usefully advertise "No known SQL injection vulnerabilities in our code" as a marketing bullet point anyone cares about. It's not the sort of question customers ask salespeople. It's not a competitive advantage for them, it's a cost, an overhead. Protecting against it makes them less competitive, slower moving, doing more work for the same functionality.

The incentives are all aligned for it to keep existing. So it keeps existing.

Make SQL injection a problem for companies, and they will make it go away.

[Edit: But there's an EU regulation that websites have to warn you if they use cookies. And they do. So regulating public facing computer systems to make them more annoying can come into effect - even if the current regulation is pretty useless.]

  • 3
    I wonder when the time will come for computer science-related legislation to be introduced – prusswan Jul 5 '16 at 17:57
  • 19
    Careful what you wish for @prusswan. In the US, at least, the vast majority of tech-regulating laws that are in place or attempted have been put in place by lobbyists with little to no understanding of the big picture. – Paul Jul 6 '16 at 0:46
  • 1
    well, some legislation is better than no legislation – prusswan Jul 6 '16 at 6:27
  • 24
    @prusswan I refute your argument thus: No legislation is better than bad legislation. – megaflop Jul 6 '16 at 11:13
  • 14
    @prusswan Really? You would rather have any legislation than no legislation? What if that legislation required your encryption to have a backdoor? What if that legislation forced you to use md5 for all hashing? What if that legislation required you to keep all passwords in plaintext, unhashed and unsalted? Are you absolutely sure that some legislation is better than no legislation? – Kyeotic-StopHarmingMonica Jul 6 '16 at 17:20
119

SQL injection is still around because the software world still doesn't understand that programmatic generation of tree-structured values (like queries or markup) should be done by constructing syntax trees as first-class objects, not by concatenating strings that represent fragments of a language.

There has been a bit of progress in recent years with the increasing availability of query builder tools like LINQ to SQL or SQLAlchemy, but that's on the programming language side. Relational databases still don't offer a standard, compelling alternative interface that's not fundamentally based on sending queries as strings.

Prepared statements with query parameters are barely an improvement, because they're only easy to use if the structure of your queries—which tables are joined, what filtering conditions, what columns to project—is fixed. When you have an application that needs to construct query text at runtime, prepared query parameters are a big pain to use.

So if a standardized, non-textual, tree-structured protocol could be constructed for describing and communicating queries to the database, and it was designed to be easier to use than textual queries, then that would solve the problem in the long term. But the problem won't go away until the industry adopts something where the path of least resistance is safe. As long as we insist on unsafe-by-default systems where writing safe code takes unnecessary effort, problems will be with us. (Think of all the buffer overflows that don't exist at all in memory-managed languages!)


Note that the same fundamental problem as SQL injection plagues the Web, under the name of cross-site scripting—which is really just Javascript injection into dynamic HTML pages. A very common pattern is Javascript programmers who, instead of working with the DOM by treating it as a tree of objects, resort to the innerHTML property to set it to HTML text that's built by naïve string concatenation. A lot of XSS vulnerabilities would never have existed if the innerHTML property had never been put into the browsers' DOM implementations.


Also, for folks who haven't seen Tony Hoare's talk on null pointers, it's simultaneously orthogonal (null pointers, not SQL injection) but at the same time incredibly relevant:

  • 12
    Relational databases still don't offer a standard, ... on sending queries as strings. Database are accessed by networks, so the API to generate secured query is always on application sides. Furthermore that would require them to implements that solution in every languages. They have already implemented drivers to communicate, perform transactions and allow to do parametrized query easier. I don't think we can really ask the more on their side. – Walfrat Jun 27 '16 at 6:58
  • 2
    I don't think there would be anything wrong with using innerHTML if it were limited to creating anonymous text-only objects. It's a lot more practical to be able to set a field to This is <b>important</b>: whatever rather than having to create and insert a separate nested object for the bold-face text. Unfortunately, I know of no mechanism for creating that kind of aggregate object which would simply be incapable of creating more "dangerous" kinds of objects. – supercat Jun 28 '16 at 22:21
  • 3
    Cannot upvote this answer enough. I have been saying this for decades, it is so great to hear at least one other person saying it too. – RBarryYoung Jun 29 '16 at 15:09
  • 3
    As far as the most trivial vulnerabilities from naive use of string concatenation, they're still common in code written by people who don't know what they're doing because the less-safe APIs haven't been removed. If DB interface functions in PHP and other languages completely removed the old-style flat-string APIs (breaking tons of existing code), a lot of homebrew code written by people that don't really know how to program would be somewhat safer. – Peter Cordes Jun 29 '16 at 19:26
  • 6
    I am a SQL developer and do not agree with this "When you have an application that needs to construct query text at runtime, prepared query parameters are a big pain to use." It is no harder to create secure query in a program than it is by hand. – paparazzo Jun 30 '16 at 13:41
57

When testing, it is very easy to test for what you expect to happen. For example, when filling in a "name" field in a database you will probably choose something you are familiar with, like "John Doe". This works, and your application seems to work fine.

Then, one day, someone names their child Robert'); DROP TABLE Students; -- (little Bobby Tables).

enter image description here

Of course, you don't test your app for names like that, so the security hole that such a name exposes slips through all your testing.

There is an interesting comment here: The Unfalsifiability of Security Claims

It's easy to prove a security hole exists (you just test for a name like the above). It's also easy to prove that a particular hole has been fixed. It's hard to prove no other security holes exist.

  • 37
    @Walfrat Too bad O'Brien might want to open an account. – a CVn Jun 27 '16 at 13:42
  • 2
    +1 for an excellent answer. Lack of proper test cases is (IMHO) a better reason than human stupidity or mistakes. It's hard to claim that something which is functionally correct for the existing test cases is a "mistake", and not using best practices isn't really "stupidity" either, but rather just lack of knowledge of the subject. If the test cases always included SQL injection, the problem wouldn't arise nearly as often, and best practices would more likely be adhered to. – TTT Jun 27 '16 at 14:47
  • 1
    @MichaelKjörling I think @Walfrat's point was that it's fairly easy to do some testing for SQLi by just including the odd ' in your test input data. I don't think he meant to promote filtering apostrophes at the application level (although I can see how you could interpret it as such). – marcelm Jun 27 '16 at 20:20
  • 9
    My answer was really trying to address the question: why is the problem still around after 17 years? Not: how best to fix SQL injection. – Nick Gammon Jun 27 '16 at 22:00
  • 1
    Nothing like a concrete example to quickly and clearly explain a problem. Cannot upvote THIS answer enough... – duanev Jun 29 '16 at 16:58
29

Steffen makes good points in his answer, but I'd like to add to it. The why, I think, can be broken in to the following topics:

  • Lack of knowledge or education of developers
  • Churn in an enterprise development environment
  • Pressure to deliver ahead of schedule
  • Not enough emphasis from the top on security

So let's break those down.

Developer training

There's a lot of emphasis on user education these days. Teach users how to maintain strong passwords. Teach users how to identify phishing. Teach users how to .... You get the idea. Some enterprises, a lot probably, but I can only speak to my professional experience and I haven't worked at a lot of enterprises ;), do have training programs. But those training programs can be incomplete or not reach the depth of knowledge needed. That's not to disparage the hard work that goes in to building those programs. But to say that just like in school environment, different people learn differently. And unless you have a continued education program for developers, it's going to be hard to communicate "use parameterized queries, and here's how to do it in PHP, Java, Python, Ruby, Scala, NodeJS, ...". It's hard work developing, delivering, and maintaining developer programs that effectively reach the audience.

Developer churn

Above, one of the things that I alluded to was reaching the audience effectively for different learning types. One of the reasons for that is a lot of enterprises have a high churn rate for developers, because the developers are contractors that get shifted from project to project at different companies. And companies are not always at the same security maturity. One company may not have a security program at all, while another may have an excellent security program and the developer is suddenly bombarded with new information that'll be required of them for all of six months before they move to another company. It's sad, but it happens.

Project delivery

Project delivery on schedule, or even ahead of schedule. The quickest path to completing the project usually, sadly, isn't completing the project with security controls. It's getting it done in the most broken way that still works. We know that it'll cause more work, more time, and more money later when it comes time to maintain the project and fix problems, but management just wants the project out.

Another item I touched on is developing security training programs for a myriad of programming languages. A lot of enterprises don't have one or two set languages. So developers like to (or are encouraged) try out the new hotness. That includes languages and frameworks. This means security programs must continually evolve.

Management buy-in

And here we are at management. Every time, it seems like in a public breach, there were controls that could have been implemented, that aren't that hard, but were missed. Pushes to deliver products first and worry second always, despite lesson after lesson after lesson, comes back on product companies. Management must push from the top to take the time to build in security at the beginning. They must understand that more work, more time, and more money will be spent fixing problems, maintaining the product, and paying fines. But cost-benefit analyses point to the problem being product delivery, not the fines or maintenance work required. Those equations must change, and that comes, in part, to education (wooo, full circle) at the MBA level. Business managers must be taught that to be successful in a landscape of ever-increasing breaches, security must be front and center.

Conclusion

The why, despite SQLi being nearly 20 years old, is fraught with several reasons. As security practitioners, we can only do so much to educate and raise awareness of what happens when security is not considered as an integral part of the SDLC.

  • 6
    "Developer training"... Right... sometimes folks would be better off without it. A friend of mine finished a supposedly accredited (whatever that means) Java course in Germany earlier this year. The stars of the SQL part of the course? String concatenation and a method called safe() that would make strings "safe" by escaping quotes - and only quotes. In 5 minutes I found 2-3 different ways to do SQLi on the sample code provided by the teacher. Who knows what automated SQLi probing tools would make out of that pile of, uh, code... – thkala Jun 28 '16 at 23:57
  • 5
    Then those programs are operated by incompetent people. But that doesn't mean that developer training doesn't work. I have seen it work. Rather than assuming developer training doesn't work, look at the reason for its failure. So, right ... – h4ckNinja Jun 29 '16 at 17:22
  • 2
    I'd add "DB vendors trying to cram everything into one call". If the way to run some sql is to always call a function that takes arbitrary SQL, developers have to do a lot of safety work. Vendors could easily add a function (maybe named "select"?) that allows only one select -- no statement-separators, no comments, no "drop", no "update".... Easy to implement, easy to use, pretty safe, and covers a lot of what users (especially beginners) need -- so could reduce attack surfaces significantly. – TextGeek Jul 1 '16 at 22:17
  • 3
    @TextGeek The integration between SQL Server and C#/.NET is pretty awesome. You have several tools to use safe selects and updates and whatever - still, lots of developers just ignore those and go right away to the unsafe, generic "just execute this SQL" calls. – T. Sar - Reinstate Monica Jul 6 '16 at 11:02
23

I agree with a lot of the answers, but one very important point isn't made: code doesn't magically fix itself, and there is a lot of code out there which is 17 years old. I have seen many companies write clean and safe new code, whilst the application could still be attacked in some of it's older sections. And worst of all: fixing old code is expensive, because it requires developers to delve into code that was written in a different era with different coding styles and different technologies. Sometimes fixing old code to not cause SQL injections requires one to recreate entire libraries that were bought back in the day (this is something I had to do a couple of years ago).

Not to say that all new code is free of SQL injections, but I personally haven't seen any professionally written new code in the past 4 or 5 years which contained them. (The only exception being where developers have to do a quick and dirty fix in old code and use the same style/technology in which the rest of the code is written.)

16

I believe it's because many developers learn just enough to get the job done, for some value of "done". They learn how to build SQL code, often from outdated online tutorials, and then when the code "works" to the extent that they can say "I can put stuff in the database, and I can generate the page of results", then they're satisfied.

Consider this guy on the ladder:

Guy on a dangerous ladder

Why's he doing that? Why doesn't he have proper scaffolding? Because he's getting the job done. Put the ladder up against the wall over the stairs, and it works just fine. Until it doesn't.

Same thing with INSERT INTO users VALUES($_POST['user']). It works just fine. Until it doesn't.

The other thing is that they're not aware of the dangers. With the guy on the unstable ladder, we understand gravity and falling. With building SQL statements from unvalidated outside data, they don't know about what can be done.

I spoke to a web developer user group last month, and of the 15 devs in the audience, two had heard of SQL injection.

11

I think the main reason is that developer training doesn't start with best practices, it starts with language understanding. Thus, new programmers, believing they have been trained with the tools to create something proceed to create the queries the way they've been taught. The next and most dangerous step, is to allow someone to develop anything without review and therefore continued opportunity to make more poor choices without knowing that there is something wrong with it and produce further habits that ignore industry-wide accepted best practices. So, to sum it up - poorly trained programmers operating in an environment that does not value anything but the end product.

It has nothing to do with intelligence or "human stupidity". There is a systematic approach that has been well defined over the years and it is negligent for anyone who produces software to ignore that process in the name of faster or cheaper implementation. Perhaps some day the legal ramifications of this behavior will enable us to have more controls in place like the medical or construction industries where failure to comply with these rules and accepted practices will result in a loss of license or other penalty.

  • 4
    agreed. google "sql examples" and you will get lots of examples of SQL expressions, none of which are safe, because they are all just strings. If that is how you learn to write SQL, then that is what you are going to use on your first project... – Mark Lakata Jun 30 '16 at 17:26
  • 1
    @MarkLakata That's my observation as well. New people rely heavily on Google. Google tends to pull up absolutely horrible examples... Dynamic queries selecting * for one column out of really wide tables. It's mind-boggling how those horrible SQL examples keep floating to the top of the Google searches that new developers use. – Brian Knoblauch Jul 13 '16 at 13:25
8

Why did SQL injection vulnerabilities not got extinct yet? Metaphorically speaking, for the same reason that car crashes are still around since the very first car in 1895 and even the most innovating and modern self-driving cars today, Tesla model S (on autopilot) or Google self-driving car crash from time to time.

The cars are created (and controlled) by humans, humans make mistakes.

Web sites and (web) applications are built by human programmers. They tend to make poor mistakes in the security design or tend to break things with "quick-dirty-fixes" when something was secure but actually introducing a new vulnerability for example because time/budget for developing a fix was limited, or the developer had a great hangover when he wrote the fix.

Is it always caused by developers? Essentially yes, but not always by the first-line developer. For example, a local supermarket asked a web development company to create a website for them. The developers rent some shared hosting space from a hosting company to host the site on and they install WordPress and some useful plugins.

Now the developers of the web development company don't necessarily have to make a mistake to introduce a SQL injection vulnerability in order to be vulnerable. What could go wrong here? A few examples:

  1. The hosting company didn't update to the latest PHP version and the used PHP versions turn out to be vulnerable to SQLi in general.
  2. The hosting company configured some public additional software like phpMyAdmin and didn't update that.
  3. The used WordPress version turns out to be vulnerable to SQLi but the update was missed or there is not patch available yet.
  4. A used WordPress plugin is vulnerable to SQLi.

Now the question that is raised, who is responsible? The supermarket, the web development company, the hosting company, the WordPress community, the WordPress plugin developers or the attacker who misused the vulnerability, rhetorically speaking? - This isn't a statement, it's exaggerated and just some questions that are likely to be asked in case something goes wrong.

Often the above discussion (questions about responsibility, although slightly exaggerated) are also a risk factor since some developers tend to have a "that's not my code"-attitude. You can imagine how complicated that makes the situation sometimes.

  • 14
    Using Wordpress itself should be considered a vulnerability. – André Borie Jun 27 '16 at 10:28
  • 6
    I don't think car crashes is a good comparison. Since 1895 there has been no way to ensure car crashes cannot happen, and there has been progress in reducing the number of incidents (e.g. white lines on the road, dipped beam headlights, better junction and traffic light design), and their impact (airbags, seatbelts, crumple zones). By contrast, SQL injection can be completely fixed by a design change, full stop. Modern cars are not crashing because companies are poorly incentivised, clueless, lazy or incompetent - but that is why SQLi exists. – TessellatingHeckler Jun 27 '16 at 17:34
  • 10
    @EvanderConsus The web development company. They were contracted to provide a website for use on the internet, and they provided one that was not fit for purpose. To suggest that they might not be responsible because their tools were bad, after they chose no-cost, unwarranted, unsuported tools from unknown people of unknown skill, with many known previous vulnerabilities, is just daft. "We contracted you to build a new building and it's fallen down", "*it's not our fault, we used free steel from some amateur home blacksmiths, how could we possibly know it wouldn't be strong enough?" "oh, ok" – TessellatingHeckler Jun 27 '16 at 17:48
  • 1
    I don't really understand what car crashes has to do with SQL injection. – Ellesedil Jun 27 '16 at 19:04
  • 8
    @WanderNauta Evander asked: *Now the question that is raised, who is responsible?" - I'm not stating that free steel is always worse, only saying that you cannot offset your responsibility by invoking "open source!". If you pick up free steel off the ground, don't put it through metallurgical testing, and later find that it's weak, you are responsible. In this answer, a flaw in a Wordpress plugin? The plugin author never agreed to any contract that it was fit for any purpose. A vendor tool might not be better, but if they sold it as fit for purpose, you are not responsible, they are. – TessellatingHeckler Jun 27 '16 at 19:35
7

Firstly no one writes secure requirements properly, they say something like "The product shall be secure" Which in no way is testable

Secondly Profession developers are not stupid, and to say so is rather disingenuous, they are all likely to have university degrees, and have been solving problems we haven`t even begun to look out... The problem is that they have never been taught what it is to develop software securely. This starts out at schools, then university and then whatever job they take, where any training is "on-the-job' because software firms are too scared to train developers in case they leave.

Developers are also under increasing pressure to do more work in less time, they are busy fixing one issue and moving on to the next, there is little time to reflect as the next problem comes along.

Developers are not incentivised to test beyond what they are developing, if they find an issue, they are likely to be the developer to fix it. The developer mantra here is "Do not test what you are not prepared to fix"

Thirdly testers are also not trained to find security vulnerabilities, for much the same reason as software developers. In fact a great deal of testing (in my opinion) is simply repeating the testing that the development team.

Forthly, time to market is a huge factor, if you are out there first you are making money, developing securely is seen as having a big impact on speed of development - I mean really, who has time for a threat model! ;)

Finally it's not just SQL injections, buffer overflows have been known about since the 1960's and you can still stumble over them with alarming regularity.

  • 11
    "Secondly Profession[al] developers are not stupid... they are all likely to have university degrees" The smartest developer I ever hired had a two-year degree from a tech school. Some of the worst had doctorates. A piece of paper imbues neither intelligence nor common sense, and I've seen a pervasive lack of both. Unfortunately academia tends to value paper over experience, so new developers are being taught the wrong things because the people doing the teaching don't know any better themselves. – DVK Jun 27 '16 at 20:50
  • still proves that they are not stupid. It shows the huge disparity between academia and industry in the way that software developers are taught. students are being taught the language de-jour, which is likely to be javascript... And that is whole impractical when faced with real-world problems or reliability, performance scalability, un-clean data etc etc... all the things that you would end up having to manage in applications. – Colin Cassidy Jun 28 '16 at 4:54
  • 3
    Universities seem to just teach you how to sound smart while not knowing anything of practical use. – crush Jun 28 '16 at 15:35
  • no. In the context of the original question, there's just very little focus on product security in a university syllabus – Colin Cassidy Jun 28 '16 at 19:34
  • 1
    @colincassidy your comment should read, there isn't any focus on product security in a university syllabus. – Jim B Jun 30 '16 at 17:22
7

Yes, anthropologically, humans are stupid.

Yes, politically, the incentive structure does not sufficiently penalize vulnerable applications

Yes, the process is flawed-- code is written in a hurry; bad/old code is not always thrown away.

And, yes, technically, treating and mixing data as code is harder to do by default.

But, there's a more positive view (lets ignore the 99% of SQLi vulnerabilities that the answers above explain). SQLi still exists on extremely well-designed and carefully developed websites because we are awesome. Hackers rule. You only need to look at the hundreds of attack vectors and thousands of SQLi payloads that have been developed over the last seventeen years to regain some faith in the human race. Every year brings with it new techniques presented DEFCON/BlackHat/RSA/IEEESSP. Bug bounty programs for Facebook, Google and the like have all had to shell out at least once for a critical SQLi.

Partly, it's because of the complexity and number of layers in our system, each mutating data in newer and more interesting ways. We increasingly need more done, faster, using fewer resources. And as long as we cannot feasibly test all paths into the system, no one is going to certify a solution to injection problems.

6

Because such security issues are not covered during most 3-year education cycles and equivalent studies, and many developers followed such track (including myself). Given how wide the field is, actually 3 years is not even enough to cope with the actual study program.. So things like security are dropped.

It is unfortunate, but since some of the new developers won't ever try to learn new things by themselves, those people will always write SQLi-prone code until a more educated colleague points the issue out (or until an actual SQLi happens).

During my studies (many years ago), our teachers always told us to use PreparedStatements when creating manual SQL queries, because it is "best-practice", but they did not say why. This is better than nothing, but pretty sad, still. I'm not sure if those teachers even knew themselves.

We learned how to display stuff on a jsp, but not what Cross-Site-Scripting is..

I am "lucky" to be a passionate dev with some time in my hands, so I learned all these things by myself a long time ago, but I'm sure that many developers just do their 8-hours-a-day (for legitimate reasons by the way), and as long as nobody shows them what is wrong, it won't change.

  • Sometimes, some developers underestimate the risk of SQLi. – Stephan Jun 29 '16 at 14:45
  • The uneducated usually do. It reminds me of a post on this very site, where a student was having issues with queries. I showed him how to made the code work, and pointed out that it was SQLi-prone, suggesting to refactor the code with PreparedStatements. His answer was "but it is just for a class exercise, this code won't be reused, I don't care". This is the kind of attitude we must fight against. Coding as properly as possible on all occasions is important, especially if you don't highly master what you are doing. – niilzon Jul 8 '16 at 7:01
4

If you use prepared statements correctly, SQL injection is not possible.

"If the original statement template is not derived from external input, SQL injection cannot occur."

https://en.m.wikipedia.org/wiki/Prepared_statement

Unfortunately people usually don't use prepared statements correctly, if at all.

SQL injection would be a thing of the past if they did.

And yes, php/MySQL have had a prepared statement implementation for a very long time, over 10 years if memory serves...

4

The other answers have pointed to almost all the reasons. But there is something else, which I think is the most dangerous security concern of all. Developers attempt to add more and more features to technologies, and sometimes deviate from the actual purpose of the technology. A little like how a client side scripting language ended up being used for server side coding or was allowed access to remote resources as well as client side local storage. Instead of layering these as separate technologies, they were all put into one big honeypot. Looking into some of the advanced SQL injections, we can see how they have played a part in the steady accent of SQLi attacks.

With SQL though, I guess the biggest mistake was the coupling of commands and parameters. It's a little like calling run(value, printf) instead of printf(value).

Oh and one last thing, while it's quite easy to convert between different type of databases, the changes required in the server side code are mammoth.

Someone should abstract between different type of databases and make it easier to switch between different dbs. Say a php plugin which takes in as input QL commands and the type of database, and may be a whitelisted filter to sanitize the input.

2

Personally I think this is a specific case of a more general problem in programming, that IDE's and languages are overly permissive. We give our developers immense power in the name of flexibility and efficiency. The result is "what can happen will happen", and security lapses are inevitable.

1

PDO (or other "safe" methods) is no more secure than mysql_ (or other "unsafe" methods). It makes it easier to write safe code, but it is even simpler to just concatenate the unescaped user provided strings into the query and not bother with the parameters.

  • 7
    I'm not sure this answers the question. Was this meant as a comment on another answer? – schroeder Jun 28 '16 at 7:19
  • 4
    I believe he meant to say that the reason SQL Injection still exists is because the above "safe methods" don't fully protect from SQL Injections, and that people need to be aware of fully protecting yourself, and making sure you use a DB that is able to handle the needed situations. – XaolingBao Jun 28 '16 at 20:07
  • Making safe code harder to write results in more bugs and thus less secure code. – Cees Timmerman Jul 6 '16 at 12:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.