>That is an extraordinarily inefficient and costly way to develop. The correct way to protect against SQL injection is to use a framework/driver which guarantees escaping and to be cautious about what you allow into queries.
I've always found using only prepared statements is feasible and justifiable. What is about using them that adds to cost and inefficiency?
Prepared statements are not a silver bullet. Somethings like limit statements in mysql are very hard to do with prepared statements but for the most part it's a good rule of thumb.
Some of the most horrible systems are when people just went half-way, and you've got logic randomly scattered between two systems with two programming languages etc.
Somehow a mess of structures/data objects paired with functions working on these objects - with only ad-hoc ways of enforcing foreign key relationships are seen as "easier", and "better for maintenance" than leveraging an actual (relational) database management system.
Sure it's more fun writing functions than thinking about data integrity - but the vast number of applications are concerned with managing structured information: from scenes in a game that could be interlocking state-machines to the more mundane user/message pairs or similar.
Design is eschewed for prototyping - but then the prototype is kept as the production system, rather than as a basis for design of a better (simpler) system.
I do think the biggest problem is that it brought everything into the DBA's turf, and made it too difficult for devs to do their jobs without meddling. As long as there is a dev/DBA organizational dichotomy, there will have to be a structural dichotomy in the software (cf. Conway's Law).
It's odd that with the rise of solid, free rdbms' like postgresql (and the current generation of mysql forks) - the general reaction among developers hasn't been to realize that 99% of systems are/should be data-first/data centric - but rather a stubborn struggle to pretend their language supports transaction-guaranteed persisted runtime/memory -- without actually using a real object database like zoodb (for python) or gemstone.
If you don't know enough about your own systems requirements, lists like this are going to have you doing work that you don't understand, doesn't need doing (or worse, is detrimental) and if you do, you probably don't need to be using checklists like this to do your job.
That will then allow you to cull the list down.
Also this business of two layers of encryption: yep, anything encrypted can't be indexed or searched (basically). At best you can encrypt the query input too and compare for strict equality---sort of like hashing passwords. (Is that what he means?) I've never seen anyone hash email addresses though. And encrypting billing details . . . okay, I guess, although if the app has the decryption key, then what security are you really adding?
It seems to me having admin panel access is an even higher level privilege than having root on the box itself, for any VPS host environment I've personally used before. Linode for instance lets you open up a root shell to your running box, which doesn't even use SSH. I'm surprised it isn't total game-over if your admin panel access is compromised.
But there are many providers out there where through the admin panel you can gain access to a system. Many providers provide access to the server's terminal. Even if you can't login, you can reboot the server, and at the boot loader stage you can boot the OS in rescue mode, during which you can mount the hard disk.
Given the number of email addresses that have been hacked and stolen (3.75 billion in Troy Hunt's haveibeenpwned alone), I believe doing one little extra bit of encryption on email addresses is worthwhile. We push that into our ORM layer and can easily flag any field in the database to be encrypted.
On very, very high volume accessed tables, you may not want to do that. But for user logon details -- for us it was a no brainer.
https://www.troyhunt.com/im-sorry-but-your-email-address-is-...
Are you using a different field for 'username' on logins, or do you have to jump through some hoops to find an encrypted email to do a login check against the pw hash?
For the User.email field, we crypt/decrypt in the ORM/DB layer. So we search with the encrypted value that is stored in the db. App code provides plain-text email, ORM encrypts and searches with that value. Symmetric encryption of short strings is pretty fast and this is not a high volume API.
We use rate limiting on the API to protect against DOS on this API.
No thanks.
And you are right, we do this because an ops error can easily make a mistake sometime in the future and probably will one day. We all make mistakes and defense in depth is all about that.
For instance, all @gmail, all @yahoo, all @aol.
Maybe you want to do the cool "hey we noticed your email on haveibeenpwnd, you should change you password here just in case". In which case, anything other than plain text could prevent that from happening.
Hashing an email in that sense gets much more difficult, no?
(That's not a negative as to this blog, it's really good and I've recommended it to multiple clients already, but that level of acumen should already be assumed at that scale. If you don't have it, this blog post is insufficient.)
Hint: not much at all.
Countermail, for example, does the same for its email servers. For Tor relays, there's tor-ramdisk.[0]
0) https://lists.torproject.org/pipermail/tor-talk/2017-Februar...
Are you saying that that threat isn't worth protecting against?
The only real alternative is to manage your own servers in house and degauss/drill through old drives. And this might be more of a hassle (at scale) with ssds.
Consider a faulty disk that is replaced. It might be possible to recover data by using advanced techniques - but might not be possible to remotely wipe it. Now you have to hope your provider manages to safely dispose of the drive.
Sometimes you make cost/benefit consolations. 100% airtight would be great, but 100% airtight doesn't actually exist. Most organizations with finite resources have to make risk/reward decisions when there are as many threats as there are with web development.
I push for encryption at rest on ANY cloud provider storage for one main reason: I have no control over their disk disposal or reuse mechanisms. They can claim they wipe the data, but I have no way to test that reliably.
As for disk encryption plus FS encryption, keep in mind in AWS or Azure, it's possible for a misconfigured IAM or SPN to leak access to the disk blob.. If it was encrypted with a key separately, the risk is mitigated. Again, it's just too easy to implement on almost every cloud provider thes days.
The 2nd layer is if the server-app is compromised. In this case, the data has already been decrypted by AWS. The 2nd layer means that credentials, emails etc are safe in the server-app tier.
You can lookup tokens by decrypting first. I'm not sure I'm understanding your question properly.
Regarding prepared statements, the mysql2 node driver is equivalent to the non-prepared driver in speed and infinitely safer.
I agree you don't necessarily have to do all these things, but at least think about them. We do all these items in our software and have not found it onerous. Happy to provide more details about this if you would like.
Right, so basically block-level encryption of the actual DB data file. While this would be much easier than encrypting the record data at the row level, it only protects against literal copying of the data file, which in my experience is not a common threat vector.
You're right though, it is simple enough to do this with something like aurora - although outside of AWS it may be very troublesome, with possible performance ramifications.
I guess my point is that this article is presented as a basic checklist for all web apps, with insufficient consideration of the ROI for implementing these solutions.
> You can lookup tokens by decrypting first
A user attempts to access your API, presenting an access token. How do you query your DB for that access token if all your tokens are encrypted at the row level? You could encrypt it at the app level and search for the cyphertext, sure, but now your server needs to have the secret, and it needs to be global, seemingly removing the point. Every other aspect of your application (notifications engine, admin portal, anything else that needs the DB) must re-implement this scheme.
Again, it might be useful, but the ROI is questionable, IMO.
> Regarding prepared statements
I misspoke here really - my reaction was against the stored procedures, not prepared statements, which are great and should be used whereever possible.
My main point is that security, and all development really, is about trade-offs. The options here are presented without due consideration of the benefits and costs of the tradeoffs they represent - and they article doesn't ask you to just "think" about them, it's a checklist. If you are a bank with 500 developers and unlimited time, sure, do all of these. If you're a startup - questionable at best.
"This checklist is simple, and by no means complete. It is a list of some of the more important issues you should consider when creating a web application."
But I'll try to make that clearer. I'm limited by space and peoples attention span. Having links to implementation notes will address your concern I think.
Might I suggest a simple "tag" for each suggestion indicating the appropriate market for that point. Eg good - your basic SAAS/casual web app. Great - apps dealing with moderately sensitive data. Extreme - you're building a bank, seeking PCI compliance, storing medical data, etc.
You could break it out at the top, and then it doesn't take much space in the body of the post, but still provides some context for which projects should be adopting this level of paranoia?
> That is an extraordinarily inefficient and costly way to develop.
Why is this inefficient? Can you elaborate?
Most people don't deal with important records though, and generally not people here (though a shout out to people from Stripe and other payments companies!). So I would t recommend stored procedures for this cloud either.
I do recommend prepared statements, but they do have a cost since it is two network calls for one query instead of one. Totally worth it too.
IN clauses can get tricky if prepared statements don't support arrays / lists of columns.
Adhoc queries get tricky where user could specify a parameter or not.
This answer is more about stored procedures than prepared statements.
Most database encryption is "transparent", meaning the applications won't even be aware that their database is encrypted. Here is an example:
https://docs.microsoft.com/en-us/sql/relational-databases/se...
https://aws.amazon.com/about-aws/whats-new/2015/12/amazon-au...
MySQL example: https://www.mysql.com/products/enterprise/tde.html
Percona article: https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-e...
"Data at rest" means the data that you have no intention of querying soon. Which implies "hot" / "cold" data partitioning, which is usually a good idea. Can be complicated, but commonly encountered in financial backends.
Two layers of encryption can be better than one (if logically and temporarily separated). Encryption algorithms are usually not a problem; credentials compromise or out-of-band vulnerabilities CAN be a problem.
Finally, ANY type of input sanitization is wrong way to do security, and should be employed only as an absolute last resort. Prepared statements provide strong semantic separation guarantees, and you can use ';drop table users; _safely_ as your query parameters. Otherwise you'll be stuck between the requirements corner cases (do you know that legal names can include quotes and apostrophes?) and security requirements, and it is the war that is impossible to win.
You can choose how much to validate, which is probably more in the former case, but it should be done.
I can't tell you the number of horrors I'm sure we've stopped in (for example) investment banking pricing by avoiding trying to do calculations on junk when the junk was easy to spot. (We also added one or two, eg when one set of interest rates went above 100%, but that's an after-dinner story!)
Some US sites still won't let me in because (a) they insist that I must have a middle name or (b) screw up the hyphen in my surname.
Many sites and payroll systems assume that everyone has a surname. Never mind its character set and ordering.
Even if you don't make these assumptions you don't need to accept a binary gzipped GB of zeros or worse in a name input field.
Some basic sanity checks are useful, of course. But it is not a good idea to use input sanitization as the only (or even main) method of injection attacks prevention.
Can you define what you mean by "input sanitization"? Because in my mind, a prepared statement is doing just that. You say what part is your SQL, what part is your user input, and you let the DB adapter sanitize the input to build the final statement. You aren't writing sanitization code yourself, you're leaving it up to a library, but that's still what's happening. Or do you have a different term for what's going when a prepared statement routine converts user input into SQL-safe strings?
1) That it was easy to do right: that's why we get paid.
2) That it was an excuse not to do other checks elsewhere: defence in depth is key.
Anyhow, I hope that we're really furiously agreeing.
Ok, that's just plain wrong and absolutely wreckless advice. Everything from software development 101 classes to OWASP data validation can call you on that. If you don't understand why you're wrong, please, please, please stop developing software now until you can understand it.
This is a prime example of the Robustness Principle. https://en.wikipedia.org/wiki/Robustness_principle
What I came up with is this: user name is stored in the database, and some new junior developer in a large team reads it in the backend code, and immediately plugs into another SQL query using string concatenation. BOOM!
But on the other hand, the very same junior developer can forget to sanitize the inputs before storing them (or do it incorrectly), so there.
This is actually possible, it's just very easy to make over-complicated: https://paragonie.com/white-paper/2015-secure-php-data-encry...
I wouldn't recommend encrypting all data. I also wouldn't recommend using unauthenticated encryption, which the article doesn't address.
I agree with you, but I don't think that's what the author meant. Nowadays, when most people talk about "prepared statements" and "stored procedures", they are just conflating those things with escaping. I think they're trying to say, use something with an API that prevents injection bugs, rather than actually use database prepared statements.
I find that 9 out of 10 times, when people talk about "prepared statements" they are referring to something like the PHP bind_param thing (https://www.w3schools.com/php/php_mysql_prepared_statements....), rather than this sort of stuff: https://www.postgresql.org/docs/9.3/static/sql-prepare.html
I'd rather use proper prepared statements than rely on string escaping.
PHP: "Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query", meaning that the statement is prepared server-side and parameters are sent separately.
Postgres: "A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.... Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again."
Is there a difference between the two, and could you elaborate?
In other cases, like `User.where("email like ?", "#{params[:email_like])}%")`, AR will escape the provided value itself and no bind parameter will be used. However, it will still use a prepared statement, which at least means that only one database query will execute; no matter what AR does or fails to do with it's escaping, you won't execute both the `SELECT` and a `DROP TABLE` because a prepared statement can only be one statement.
This is funny, I've actually inherited a project where the original developer had this idea, he used the same function to encrypt everything: even the news posts available on the front page were encrypted. The passwords were using the same encryption functions and needless to say not using a one way hash so fully decryptable...
If your database supports low cost encryption at rest (like AWS Aurora), then enable that to secure data on disk. Make sure all backups are stored encrypted as well.
i.e. this kind of encryption costs very, very little and give you physical security if you need it.
Sorry for coming on a little strong. I should wait a few minutes before tearing into things...
The article is a great beginning, and as we have seen, definitely a conversation-starter! So thanks for creating it.
What I'm scared of, though, is non-technical people finding articles like this and, without a thorough understanding of the tradeoffs involved, presenting it as a list of requirements for their team. That's probably not what you intended - but I've seen this kind of thing happen again and again.
Lucky I've got my asbestos pants on ;-)
There is an area of active research called homomorphic encryption that would allow for operations against encrypted data. Right now it is too slow to be practical, but maybe in the future.
(Even then, my understanding of homomorphic encryption is that it would not be great for generalized queries against the data like you would expect in a database with SQL)
IMO, if you're on AWS (or similar) then at rest encryption is a wholly unnecessary expense, unless you need to tick some kind of regulatory checkbox. I can see it for smaller on premise racks to prevent a "smash and grab" problem, but in a secure datacenter? Nah...
Of course this is very hypothetical and it requires the attacker to know what disk in what rack to target, I'm not saying it's the most likely scenario, I'm saying it can be avoided by flipping a switch and paying a few extra dollars so I'll keep it enabled.
Some of them (e.g. Google Cloud) encrypt everything at rest by default too.
Also, having people trained in good practices would make hiring easier.
Data at rest means data on the disk. (so basically LUKS or similar partition / FDE) In virtualised environments it may not obvious, but the disk you're running on can still be accessed in various ways. Here are some examples (not a complete list):
- VM breakout allows someone to read others' volumes
- logic issue allows others to snapshot your volumes
- disk is marked as bad, then discarded without full erase / physical destruction, then found in the dump
> So two layers of encryption!? Again, how is one supposed to look up an access token or email address if it is encrypted?
You don't have to encrypt the things you index. It's more to do with PII, addresses, etc. which you don't want to accidentally expose, and don't have to access too often outside of systems like billing.
It may also prevent you from storing a copy by accident. For example if you serialise a whole record to the output due to type/variable mistake. Or by revealing it in an error message. Of course these shouldn't happen anyway, but it's a good protection in practice.
> And it strikes me that if you don't trust the first level of encryption, the solution is to fix that, not add another one.
Nobody writes ideal code. There will be bugs. You prepare for that using defence in depth. This may mean separate minimal services handing out the sensitive data with extra audit steps. It may mean encrypting the data with a password not stored in the database, so that a trivial dump doesn't give access to them. "fix that" is not simple, otherwise we'd just "fix" all software :)
> That is an extraordinarily inefficient and costly way to develop.
It's the most trivial way to avoid all SQL injections. It's used in lots of projects. As long as you don't need to use parameters where they're not allowed in your database (for example table names), it's not a bad idea at all.
But if you have an ORM and want to use it - great. Most likely your ORM, or framework you use already takes advantage of prepared statements. If it doesn't you end up like Drupal recently (please correct me if I got the framework wrong) which did their own escaping because of prefixed table names and was broken at the framework level.
> be cautious about what you allow into queries.
This is what's extraordinarily inefficient. People make mistakes. Given big enough project, if you allow text queries, or try to be clever with own escaping systems, someone will make a mistake one day. Prepared queries (and good, tested ORMs) get rid of the whole class of issues here and are much more common solution than you suggest.
It doesn't cost me much, if any, developer time to work with stored procedures. I realize most full stack devs don't have as much in-depth experience with SQL. That was literally all I did for the first several years of my career. Before it ever occurred to me to learn other programming languages and. It was pure SQL all day every day.
If you have the resources to implement even half of this checklist, you probably have the resources to go whole hog on stored procs.
I sometimes do this, and sometimes don't. Partly, it's because I need to stay current with ORMs for the sake of potential job opportunities. And partially because it can be more convenient to keep my head in one language for fleshing out an idea. But for serious projects, I'll go the stored proc route.
In that kind of scenario, I think of the database as a type of microservice that's completely separate from the application layer. You write in the most performant language for that service, even if it's different for the main language the app is written in. And you reap the benefits of the database optimizing the procs as well as the database handling transactions instead of trying to manage that in your application.
How much of a burden this is boils down to whether you personally have the chops (or someone on the team does), and if you get your shit together when it comes to deployment. Stored Proc code should be saved in text files under version control, and your deploy process should make updates to the procs automatically.
If you do things this way, it's not any more painful than versioning your database with migrations, which I do on every project, no matter how small. Using stored procs doesn't have to mean you're working with a mess of unmanageable database code. You can use SQL Alchemy to create models and relationships, Alembic (just speaking to the Python world here), and use a thin wrapper around executing the stored proc, and it's really not that much different from writing prepared statements in Alchemy.
In principle, it's really not different from writing a web app that provides an API that your front end hits (a Python app that resents REST for Angular to call or something else equally common). In this case, I'm just pushing the API down one layer in the stack.
People can argue that the logic that applies to the data should be kept with the logic that defines the data--i.e., that if your model defines the shape of the date, your code that defines its behavior should reside there as well: in the model definition. So the ORM is the correct place for that. Methods on the Class object. I'm sympathetic to that argument, but I also disagree with it, sort of.
I think the rules that govern the behavior of data should exist where the data exists rather than any abstraction layer. Otherwise you have to rewrite them for every new thing that wants to access the data.
And every useful application that people use involving data is going to serve that data to more than the first application you designed to work with it. Keeping these rules in sync across even a very minimal web app that consists only of the app itself, an API, and an analytics platform is already some overhead.
And, every data-driven application already has two means of interface regardless of whether you intend that or not: direct database manipulation. That is going to happen sometimes, whether you want it or not or how bad a practice it is. If there are rules about how things should behave (there are always rules about how things should behave) you have to enforce them at the database level.
If you're doing that correctly already, there's really not that much overhead in using stored procedures. Going that route means that every connection to a database for a certain dataset means that there is a single source of truth for that dataset. Execute procedure x to get data y. That's all any developer on any service needs to know.
It also makes the idea of minimum privilege easier to manage. You don't have to grant select on a table for the API user, for example. You can grant execute to the set of procs that user needs, and only those procs are accessible. Combine that with some well-thought views, and you can greatly limit your attack surface. If someone breaches part of your system and manages to obtain so source code, they don't get any real information about the structure of your database. They only get conn.execute('check_valid_user') or whatever.
The obvious problem here is versioning APIs. Which you must always do. So you have to version your stored procs when there are breaking changes. But the overhead in adding a _v1 or _v1.1 to the end of an .sql file for a proc really isn't that big of a deal compared to all the other stuff you have to do when maintaining an API.
Again, I'm sympathetic to objections to this model. It is not perfect, and I've never once tried to inflict it on coworkers who want to do things differently. I'll do this in my own projects, and when I've gone into a company that's already working with that mindset. I don't hold up the train based on philosophical differences.
There's a lot of criticism I agree with that SOA is not a good place to start. It carries a lot of overhead, and you shouldn't go there until you need to. But to me, the reality is that any web app that comprises a data store, a controller layer, and a front end is already by definition SOA--not the monolith that you want to think of it as. And therefore you should treat it as such and give the various components the attention they deserve.
If you're working on a project you have reasonable expectation will be used enough to require some scaling, this is a totally acceptable way to do things. Let the database do what it's best at, let the controller layer do what it's best at, and then use the front end that's best for your use case.
Because this response isn't long enough already, I'll just add this. I don't think the MVC model is really appropriate for web apps. Even the modified MVVC model is still weird to me. It seems like a model we shoehorned onto what a web app really is. To me, the web app model is data, logic, presentation. The data layer comprises both the data and the rules about the data. The logic layer dictates what happens when an event is triggered by the presentation layer. And it should be relatively thin, particularly when working with one of the slower languages like Python or Ruby. The presentation layer simply defines the user interface and sends messages to the logic layer.
These things really are decoupled by nature in ways they aren't necessarily in native or desktop apps or mobile apps. I think that web developers need to understand that this model really doesn't fit very well. And behave accordingly.
There are obviously exceptions to everything I'm saying here. But the exceptions come at very large scales. Most apps that start out as MVC web apps on whatever stack are not going to become Facebook or Reddit and are not going to have the specific problems that come with that level of scale.
Even so, I would encourage people to rethink what web apps are, and what the MVC/MVVC model really means. I really don't think it's the right model, and the decisions we make about architecture are generally not good ones.
Finally, since this is so long now, I want to point out that I didn't generate these ideas all on my own. I've been heavily influenced by Lex de Haan and Toon Kopplaars in their book, Applied Mathematics for Database Professionals.
I've clearly never done this in the real world, and I am simply thinking about my idea of a way to create a web app. And I've never worked with, thought about, or managed a team.
Do I actually need a sarcasm tag for this? I think I do, sadly. /s
I'm being a sarcastic fuckhead. Jesus.
But there are good reasons to keep telling people that this is a reasonable way to do things.
It's clearly not the only way to do things, but I'm tired of this idea that it's an awful, terrible way to do things.
There are way to do this that don't suck, and there are legitimate benefits.
It's not always the best way to do things.
But I often feel like we're dealing with people who haven't properly considered the options when we get into thee kinds of threads.
> Encrypt all data at rest in the database
ALL data? How are you supposed to query against it then? What does "at rest" even mean in the context of an always-on database? An encrypted partition or something?
I'm not even sure what this is supposed to mean and it is certainly not common practise.
> Use secondary encryption for data identifying users and any sensitive data like access tokens, email addresses or billing details
So two layers of encryption!? Again, how is one supposed to look up an access token or email address if it is encrypted? And it strikes me that if you don't trust the first level of encryption, the solution is to fix that, not add another one.
> Fully prevent SQL injection by only using SQL prepared statements and stored procedures
That is an extraordinarily inefficient and costly way to develop. The correct way to protect against SQL injection is to use a framework/driver which guarantees escaping and to be cautious about what you allow into queries.
I could go on. The costs of implementing these recommendations would be staggering for the questionable benefits they would provide. It is absolutely possible to have excellent security without implementing any of these ideas, and I don't think they're helpful at all - and certainly not "simple".