loading...
Cover image for SQL is Insecure

SQL is Insecure

kellogh profile image Tim Kellogg ・2 min read

SQL is insecure, tell everyone. If you use SQL, your website will get hacked. Tell everyone.

I saw the news that the US Elections Agency was hacked by a SQL injection attack and I kind of lost it. It's been well over two decades since prepared statements were introduced. We've educated and advised developers about how to avoid SQL injection, yet it still happens. If education failed, all we can do is shame developers into never using SQL.

I actually really like SQL, I've even made a SQL dialect. SQL's relational algebra is expressive, probably more so than any other NoSQL database I know of. But developers have proven far too often that it's simply too difficult to know when to use prepared statements or just concatenate strings — it's time we just abandon SQL altogether. It isn't worth it. It's time we called for all government's to ban use of SQL databases in government contracts and in healthcare. There must be utter clarity.

Part of the problem is the curse of the junior developer. They're experienced enough to realize that their employers will reward rapid development, but inexperienced enough to not understand the tremendous cost associated with sloppy code.

As a senior develper you might note that a relational database provides the flexibility you need to be successful. You know how to use prepared statements to prevent SQL injection. They are a little more work than simply concatenating user input with executable SQL code, but not much more. The decision seems obvious: use SQL.

But 2 years from now, after you quit this job, a very junior developer picks up your code with some very tight deadlines and a ton of management pressure. Does he know about prepared statements? Maybe, maybe not. Either way, it is a lot easier and faster to slop the code together and get shit done, so that's what he does.

It's time we accepted fate and let SQL die. Software runs our world now. When our software fails, people's lives actually get messed up. Companies will always want software to be made for cheap, but it's our ethical responsibility as senior developers to prevent future mistakes from being made under pressure.

Security is paramount. Your customers are worth it. Abandon SQL.

Discussion

pic
Editor guide
Collapse
legolord208 profile image
jD91mZM2

I really REALLY don't wanna go back to storing JSON files. And I don't see any problem with prepared statements at all. And I'm bloody 14 years old. That's pretty young. I might not be a fast coder, but I do appreciate security even at this age and backwards. So I think instead of dropping the world's largest database engines, we learn to use prepared statements. PERIOD.
I've - only one single time not used prepared, but on my own hardcoded input - used anything else than prepared when there is a variable involved.

The ONLY thing they could do easier is making an SQL client where you call methods like
SELECT("test").FROM("database").EXEC()
That would work

Thank you for reading, I guess ¯\_(ツ)_/¯!

Collapse
jestingrabbit profile image
Bill C

This is pretty much the approach that Ecto takes (which is the popular ORM that Elixir is using). Its the most expressive ORM I've used, and it doesn't neuter the power of the language.

Highly recommend putting your sql behind some kind of ORM.

Collapse
legolord208 profile image
jD91mZM2

Thank you for your input! Cool to know something like this already exists!

Collapse
kellogh profile image
Tim Kellogg Author

So cool seeing a young developer like yourself interested in writing quality code. I think you're thoughts are on the right track in terms of API. One of the tough things that you'll learn in the next few years is that, even though you put a lot of effort into making good decisions, there's 100 others that are paid per line of code. Just because a tool is useful doesn't mean it should be used.

For instance, manual memory management. We've done a good job making developers scared of writing C and having to manage memory using malloc/free. That's saved us a lot of buggy programs and a ton of security vulnerabilities (e.g. sloppily reused buffers). We need to do the same thing with SQL. I mean, C# has Linq, that seems to have worked reasonably well.

Collapse
legolord208 profile image
jD91mZM2

I think SQL is neither hard enough, or critical enough to just abandon.
And if somebody started paying me for doing a sloppy job, I'd switch job. And god knows what I'd do if there are no "good" jobs...

Collapse
djoram profile image
DJ Oram

Airplanes are too dangerous. As an experienced pilot you know how to fly one, but let a rookie fly it without any guidance or support then the chances are it won't end well. Abandon air travel.

Collapse
skyrpex profile image
Collapse
rossta profile image
Ross Kaffenberger

Honestly, I appreciate the effort to raise awareness for a critical issue - SQL injection is a real problem. Still, how sincere/realistic is the proposed solution? This is like Dan Quayle promoting abstinence. It's not like any alternative, e.g., NoSQL databases, don't have their own security concerns. Wouldn't improvements to existing clients/tools/frameworks be a better approach?

Collapse
kellogh profile image
Tim Kellogg Author

Fair points. Consider this - if it's been decades and we still no one has been able to make it secure, maybe it's simply insecure. I think the concerns around other database technologies are actually fixable.

Collapse
arvesystad profile image
Arve Systad

That it's easy to design insecurely doesn't mean that noone has been able to make it secure. There are many techniques to avoid various security flaws in SQL (stored procs, using proper arguments instead of string concatination, using proper access control to directly write to tables....). So it IS fixable, it's just a matter of getting people to learn these things before actually using it.

Thread Thread
kellogh profile image
Tim Kellogg Author

We're still suffering SQL injection attacks that make news headlines more often than monthly, so it's obviously not a solved problem. What you're suggesting is clearly not working, so let's try something new. I suggest deprecating SQL and creating a new interface to relational databases that discourages user input via string concatenation, and you think it's a bad thing...why?

Thread Thread
arvesystad profile image
Arve Systad

In an ideal world, I'd agree. But in the real world we live in, this has a huge cost: Legacy code, tools built around SQL, enourmous amounts of real life applications handling tons of data every day.

All (serious) SQL-tutorials, books and classes DO discourage string concatenation as a way of building queries. It can be parameterized as it is today, so the problem IS solved. People are just not using the solution, sadly.

I'd rather see an evolutionary move against what you're saying with the existing SQL of today; not a complete deprecation and rewrite/recreation. If we're at the complete recreation point, this could be done to so many things - and it's often similar reasons for it not to be done (existing systems and code bases).

Thread Thread
kellogh profile image
Tim Kellogg Author

Yeah, agreed. I think the main takeaway is to never build a platform where the easiest or default way of doing things is insecure. Depreciation can be impossible.

Collapse
davehay profile image
David Hay

SQL injection is an avoidable flaw. Just because programmers are too lazy or incompetent to avoid this doesn't invalidate the technology.

If we abandoned every technology with security issues there wouldn't be many left. Lets start with anything developed in C and C++.

Collapse
kellogh profile image
Tim Kellogg Author

Honestly, I think we have, to a large extent, stopped using C and it's resulted in far fewer security vulnerabilities. We established a healthy fear of manual memory management. C++ is a different story though.

Collapse
pysysops profile image
Tim Birkett

Let DNS and NTP die too... they're insecure and often used in DDoS attacks. Also my mail was opened and money stolen, let's stop using postal services.

Avoiding SQL / XSS are usually lesson 6 in any "Learn x in 24 hours" book.

I guess I fail to see the point of this article. "SQL" is such a broad term. It's a query language. It's like saying "stop using HTML". What products are bad? What languages?

The failure here isn't SQL or not using an ORM / prepared statements. The failure is the process.

Clearly a lack of effective code planning, code review, CI/CD pipeline testing, OWASP testing, input validation, decent WAF rules...

The failure is the shared responsibility of the BA/PM who spec'd the product, the dev who made the commits, the senior who reviewed or merged the code, the QA, whoever built the CD pipeline, the person who promoted deploys to production, the ops who run the infrastructure platform, the security guy... basically everyone should feel the pain.

Security really is everyone's concern. There should be a security specific inception or retro on every sprint that looks at the full stack.

Application code, do we need better testing at build time? Will a WAF like ModSecurity / nginx reverse proxy allow our app to function fully while evading common issues? Can we use OSSEC IDS or ELK to identify and respond to misbehaving clients from access logs? Can we block IP addresses and ranges that we know will not need to use our app (blocklist.de) 100%. Any of the above alone would have reduced the risk of hacker success to almost 0.

Securing any internet faving app is the same. Secure code, secure config, blocklist, IDS / IPS and Log Visualisation. You'll evade 99.9% of attacks. Automated or human.

If I've got you thinking, check out:

blocklist.de
ossec.github.io
waf.comodo.com
elastic.co

Collapse
arvesystad profile image
Arve Systad

This is not a problem with SQL, this is a problem with management culture and inexperienced/uneducated developers. While SQL injections are common, similar injections can be done to any data query language if things aren't secured properly.

Shitty software with major flaws can and will be written on any platform or language. Tools (or lack thereof) alone cannot solve this problem.

Collapse
kellogh profile image
Tim Kellogg Author

The glaring problem with SQL in particular is that the simplest, most obvious way to use it is wildly insecure. We have so many tools for using SQL incorrectly, yet we're still hurting actual people because we can't do the tools correctly. At some point, we need to realize the danger and end the problem for good

Collapse
arvesystad profile image
Arve Systad

This goes for all programming and data query languages. It just happens that SQL is what queries and manipulates data, and not fades in a textbox in your browser, so the severity of bad design is potentially far worse. IMO, if we follow your theory ("it's easy to do incorrectly, so let's end it"), we would have to kill off every major programming language and development platform.

Todays database engines (be it MS, Oracle, MySQL, Postgres or others) are so mature, well tested, reliable and efficient that simply abandoning the only way to query them would be a huge leap backwards in time. SQL when used correctly is an incredibly powerful tool, so people using it incorrectly should not be the reason to "end it for good".

Collapse
ben profile image
Ben Halpern

If I ran a software consultancy tasked to take on a greenfield project, what approach and technologies might I choose if I want to avoid using SQL, but still want to use a practical approach that's not going to slow down development too much?

Collapse
kellogh profile image
Tim Kellogg Author

To be quite honest, all options are going to be slower or have some thorn. SQL injection is simply too terrible of a problem to be allowed to survive. We've tried educating developers. It didn't work. More education isn't going to fix this. Yet in the meantime we've got huge problems - elections being compromised, identities being stolen, livelihoods being crushed. We, as developers, need to to take responsibility for our own code as well as those who might modify our code.

Ideally we should start seeing relational databases where injection attacks aren't possible (or at least difficult). But in the meantime there is Redis, Cassandra/DynamoDB, TitanDB, RethinkDB, and literally hundreds of other options.

Collapse
miffpengi profile image
Miff

I would like to know this too. Neither NoSQL nor an ORM allows full access to the power of a modern database engine.

Collapse
benfavre profile image
Webdesign29

T.L.D.R. : Insecure code is insecure

The Middleware manifesto !
GraphQL/Apollo/Falcor as a sanitization layer could maybe help by automating/protecting standard interactions ?
It will actually make project bootstrap somewhat longer to get going, but some parts will be re-usable, ....

Collapse
n0tt7 profile image
n0tt

"If you use SQL, your website will get hacked", what a statement.

Security flaws don't happen just by "using" something, but because some developers are not concerned by security at all or just ship bad code - not an SQL issue per se.

The issue is not that "SQL is insecure", but rather that "a bad SQL implementation is insecure". You can take SQL or anything, bad code do bad things, as simple as it is.

The problem is : using SQL without an ORM lib or database API - anything that makes the interface between the code/logic and the data/model secure.

Developers can ship bad code because of :

  • lack of experience and/or logic
  • misunderstanding business requirements
  • security concepts unawareness
  • a malicious intention

It's up to the team in charge of security to constrain developers to use ORM/API to build SQL queries, and it' not a big deal to catch any security risks in a dedicated hook when committing.

A malicious intention will exploit SQL as any other possible flaw in your code, end of the story.

Collapse
kaganasg profile image
Gary Kaganas

IMHO it may be easier to update the major SQL flavors to patch the vulnerability (as hard as that may be), than to change dev behavior on this one. It's like saying that the whole world has to switch to a gluten-free diet. Probably easier to remove gluten from wheat (as hard as that may be).

Collapse
tbodt profile image
tbodt

How is this patchable?

Collapse
lynxieawsm profile image
Lynxie Awesome

TLDR: Junior developers are insecure

Collapse
ennor profile image
Enno Rehling (恩諾)

The industry is built on juniors, though. My alternate summary is: Senior developers do not spend enough time teaching juniors or reviewing their code.

Collapse
danderson profile image
Dale Anderson

Wow. Biased much? I don't like SQL either, but this is a stretch.

Collapse
antonfrattaroli profile image
Anton Frattaroli

Injection risk is independent of the persistent store platform. Why not just write the article on how code reuse and standards specs are bad? Might be more entertaining.

Collapse
imakm profile image
Ahmed Kabir Maruf

What is the alternative to SQL?