10 ms·

While it means well, I think some of this advice is pretty bad, or at least unbalanced. From the very first section: > Encrypt all data at rest in the database

• xupybd 9y ago
  Totally with you on the DB encryption but I'm not sure about your comments on prepared statements >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.
  • sho 9y ago
    My apologies - my brain focussed on "stored procedures" and that's what my rant was addressing. Prepared statements are great and I'd use them whereever possible - totally agreed.
    • ams6110 9y ago
      Nothing wrong with stored procedures. Like anything else, they might not be right for every application.
      • cookiecaper 9y ago
        The image of sprocs continues to suffer from "store all application logic in the database" syndrome, which was all the rage in the early and mid 00s before APIs started to take off. There's nothing necessarily inherently wrong with that approach, but most people have gravitated away from it. DBA/dev split probably had something to do with it.
        • e12e 9y ago
          I'd venture the real reason is many devs know too little of normalisation, data structure and structured data. 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.
          • cookiecaper 9y ago
            If document databases like MongoDB have taught us anything, it's that developers hate any sort of data organization, which is ridiculous and ironic since most of their day-to-day is about manipulating data. Mongo is popular because it allows the developer to stuff a value without thinking about its structure and role, generally leading to utter disaster further down the road. 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).
  • cookiecaper 9y ago
    Most (good) ORMs/drivers will be transparently using prepared statements anyway. It explicitly informs the DB "this is unsafe user input" instead of trying to approximate that message with escapes.
• aeronautic 9y ago
  The cost of full encryption is minimal on platforms like AWS Aurora. If the platform supports it - this is a good way to go. The incremental cost of encryption with Aurora is very low (< 3%). At rest, means that the data on disk is encrypted if the DB itself is compromised. 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.
  • sho 9y ago
    > At rest, means that the data on disk is encrypted if the DB itself is compromised 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.
• aeronautic 9y ago
  I take your point that this could be explained a lot better. In the next revision when I fold in the feedback, I'll make this clearer. One suggestion, that I'll do is pair each checklist item with an implementation note that explains it better. Thanks for pointing out the flaws.
  • sho 9y ago
    Ah, I didn't realise you were the author! 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.
    • aeronautic 9y ago
      No problem, all feedback is good. Lucky I've got my asbestos pants on ;-)
• enraged_camel 9y ago
  >>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? 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/security/encryption/transparent-data-encryption-tde https://docs.microsoft.com/en-us/sql/relational-databases/se...
  • aeronautic 9y ago
    This can probably explain more succinctly than I can. https://aws.amazon.com/about-aws/whats-new/2015/12/amazon-aurora-now-supports-encryption-at-rest/ https://aws.amazon.com/about-aws/whats-new/2015/12/amazon-au...
• pjungwir 9y ago
  Totally agree about the encryption. I've set up databases to run on encrypted disks (LUKS or nowadays on AWS you can get encrypted EBS), but I feel like that is really just to tick the "encrypted at rest" compliance checkbox, because if the machine is turned on, anyone can read the data (subject to normal file permissions of course). As far as I can tell the only threat this is protecting against is someone physically pulling the drive and walking off with it. 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?
  • aeronautic 9y ago
    If the hacker gets access to the database due to a network config error, then they will be able to query the database, but only get records with some sensitive fields encrypted. 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.
    • stephenr 9y ago
      Wait, so you encrypt the user's email before it's inserted into the table? 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?
      • k__ 9y ago
        Can't you encrypt the entered email and search with that?
• viraptor 9y ago
  > 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? 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.
  • 9y ago
• ianamartin 9y ago
  I'm going to go the other way on the SQL advice, even though the author has clarified below that he was talking about prepared statements. 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.
• user15672 9y ago
  Some this advice seems to come out of a cargo cult development handbook. That may sound a little harsh, but it's better to have a tendancy to take any article that states "Always do X" or "Never do Y" with a handful of salt. 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.
  • SimbaOnSteroids 9y ago
    Thanks for this, I'm planning on developing a small web application over the summer for one of my gaming interests and as I was reading this I was wondering how practical any of it would be for my project. Don't get me wrong I plan on building in security but I'm planning on building a Rails app were the most sensitive data contained is an API key, it would hardly seem practical to build Fort Knox.
  • aeronautic 9y ago
    I'd recommend you start with the items at the very end of the check list. Make a list of the threats and plan who you need to defend against. That will then allow you to cull the list down.
• atemerev 9y ago
  I think, the author is right, and you might be not. "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.
  • user15672 9y ago
    > Finally, ANY type of input sanitization is wrong way to do security, and should be employed only as an absolute last resort. 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.
    • bpicolo 9y ago
      He's correct in the sense of trying to catch SQL injection via input validation - that's a losing game. He isn't saying "don't validate your data at all", that's a different issue. Ultimately, your OWASP issues (XSS, SQL Injection) related to input are going to be prevented by appropriate escaping and data handling across your entire stack by default (key word is default, "trust the devs" is not the right answer). Input validation isn't the ticket. This is a prime example of the Robustness Principle. https://en.wikipedia.org/wiki/Robustness_principle https://en.wikipedia.org/wiki/Robustness_principle
    • atemerev 9y ago
      Would you be so kind to explain me the attack vector if the user input is never possibly treated as part of the code? 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.
• holri 9y ago
  >> Fully prevent SQL injection by only using SQL prepared statements and stored procedures > That is an extraordinarily inefficient and costly way to develop. Why is this inefficient? Can you elaborate?
  • adamzochowski 9y ago
    I am not the original poster, but some queries can't be moved to stored procedures (depends on SQL software and library abilities). For example: 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.
    • deleted 9y ago
      [deleted]
    • aeronautic 9y ago
      That is right. The key point is to reduce exposure to SQL injection by not formatting queries. Prepared statements help solve a whole class of bugs at a lower level.
  • drostie 9y ago
    As mentioned beneath, the comment was secretly fixated on the 'stored procedures' side and not the 'prepared statements' side. Stored procedures have many costs associated to them; for example, they don't get version-controlled by default and they don't lend themselves easily to a development/production schism; both of these need to be solved by writing out some sort of database-synchronizer-script which will handle fallover. In addition usually you cannot issue breaking changes to an SPROC without scheduled downtime, so you have to intentionally have a flow of "create new SPROC, make code use the new SPROC, deploy across the codebase and audit for the old SPROC's use, finally remember to drop the old SPROC so that old code doesn't get recycled by someone who is looking for the first thing that does what they want." Compare that to just "commit a new patch, then upgrade servers as soon as possible".
• CiPHPerCoder 9y ago
  > ALL data? How are you supposed to query against it then? This is actually possible, it's just very easy to make over-complicated: https://paragonie.com/white-paper/2015-secure-php-data-encryption#index-encrypted-information 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.
• Avalyst 9y ago
  At rest basically means on disk. People might not think about this but AWS actually has a physical disk somewhere which someone could yank from the data center and read from. Not that likely but also not hard to protect yourself from.
  • nulagrithom 9y ago
    If someone is yanking and reading disks at AWS then the game was over a long time ago. Physical access always wins. 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...
    • Programmatic 9y ago
      Disk encryption also prevents disposal issues from affecting you, which is a separate problem than physical access.
    • Avalyst 9y ago
      Amazon has employees as well, yes? Employees with access to data centers? Employees that may be convinced to make some "mistakes" in the disposal of old disks combined with the early replacement of a few specific drives? 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.
  • jdc0589 9y ago
    Yes, and no. Most block storage services provided by cloud hosts shard data across tons of physical media. Some of them (e.g. Google Cloud) encrypt everything at rest by default too.
• FooBarWidget 9y ago
  > 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 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.asp 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 https://www.postgresql.org/docs/9.3/static/sql-prepare.html
  • ams6110 9y ago
    History has many examples of "safe escaping" that didn't turn out to be so safe after all. I'd rather use proper prepared statements than rely on string escaping.
  • Programmatic 9y ago
    I looked at the two linked pieces of documentation, and to my naive eyes they look like they perform the same task: 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?
  • nathan_long 9y ago
    ActiveRecord uses actual, PostgreSQL prepared statements in many cases. Eg if you execute `User.where(email: "foo@bar.com")`, it will prepare, bind and execute the query separately, with "foo@bar.com" being sent to PG as a bound parameter. You can see this if you flip the proper flags in the PG config and tail its log. 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.
• bradleyjg 9y ago
  "ALL data? How are you supposed to query against it then?" 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.
  • pyre 9y ago
    Right, but the existence of homomorphic encryption isn't something should prompt developers to start implementing it as a "best practice" now. (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)
  • dom0 9y ago
    I'm fairly certain, and I believe most experts of the field are too, that "general purpose" homomorphic encryption will never happen.
• wkd 9y ago
  > Encrypt all data at rest in the database 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...
  • aeronautic 9y ago
    Sorry that is not quite what was intended. I've revised the text to say: 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.
• sroussey 9y ago
  Personally, I'd rather have people have their little apps with too much security than major apps with too little. Also, having people trained in good practices would make hiring easier.
• sroussey 9y ago
  Database encryption at rest is a normal practice for PII data. MySQL example: https://www.mysql.com/products/enterprise/tde.html https://www.mysql.com/products/enterprise/tde.html Percona article: https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-encryption/ https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-e...