> Store and distribute secrets using a key store designed for the purpose. Don’t hard code in your applications. Curious: Is there a widely-used off the shelf solution/pattern for this? Or a "idiot's guide to writing one"? It's always seemed to me like super bad practice to hard-code a (for example) AWS secret into your app. However if you set up a basic web service to deliver the AWS secret to the app, wouldn't your…
Sure, classic chicken and egg problem. At some point you need an unencrypted secret.
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…
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.
Nothing wrong with stored procedures. Like anything else, they might not be right for every application.
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 physicall…
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…
Never mind that email addresses are sent in the clear all over the internet every day in normal SMTP traffic?
I do not like this list. > [ ] Use minimal privilege for the database access user account. Don’t use the database root account. This advice seems outdated. In general, every significant security breach will get the attacker root access. Playing games with database accounts gets you no security at all, while introducing lots of friction and headache.
Sorry, you don't throw away mitigation techniques because they aren't foolproof. This is still excellent advice. Stop using sa and root accounts for your apps.
I agree with Spydum. The reason I agree is - Layers of security are required. You should not use a root account for your web application. You should also use escaping, properly formed queries, and prepared statements. You use best practices in an orderly manner and you will be much safer than just picking one "silver bullet". Not every attack provides root access to the database or server. Why make it easier for folks? Least privilege is a viable tool in your bag.
Prepared statements are amazing. Found out about them very soon after I started programming (incidentally my first language was PHP), and switched to PDO right away. This was years ago, I don't understand how people are still using the deprecated and insecure mysql_* functions, you can still find them all over SO and my university "web" class was teaching them as well...
You'll be glad to hear that they've finally been removed since PHP 7.0 so slowly but surely they're being consigned to history.
And there seems to be much better awareness about the perils of mysql_* functions amongst PHP devs these days.
However, 5.6 is still in security support until 31st December 2018, so all that bad advice on SO and elsewhere is still relevant and lurking, waiting to be found by inexperienced devs.
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 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…
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; credenti…
I work in health care, where "data at rest" does not mean you won't query it soon, but instead that it isn't either in transit (datastore to app, app to app, app to customer, etc; all of these have transport encryption) or being worked with in memory currently. If the data is in a store, it's almost certainly "at rest".
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 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…
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 progr…
Agree 100%. I've written longer responses like this on various web forums and email lists, but these days don't have the energy to keep hammering on this nail. Thanks for taking the time.
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 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…
>> 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?
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.