Inspired by the many data leaks where whole databases/tables with personal user information were leaked, what are best practices to protect those assets? I‘m especially concerned if someone was able to get access to backend code (which definitely should not be the case) and therefore acquire the SQL credentials.

I‘m storing personal account data that needs to be accessed only by the corresponding user.

My idea is to encrypt table data symmetrically with username and password of the user. This makes it very difficult though when handling password resets...

Are there other ideas or common implementations for this scenario?

  • This question is quite broad, because everything that improves security of a web application applies to your question. – Sjoerd Sep 30 '20 at 8:25
  • Thank you, I will narrow it down to meet my requirements. – dmuensterer Sep 30 '20 at 8:58

I‘m especially concerned if someone was able to get access to backend code and therefore acquire the SQL credentials.

The database system should be accessible from a limited number of systems. Specifically, it should not be accessible from the internet. If someone has the SQL credentials, they still won't have access to the server.

Common sources of leaks are:

  • SQL injection
  • Unauthenticated server exposed, such as MongoDB, ElasticSearch
  • Export CSV or document exposed, e.g. through Google Drive
  • Open Amazon S3 bucket
  • Data exfiltration after hack

My idea is to encrypt table data symmetrically with username and password of the user.

Most web applications have their data plaintext in the database, and simply check with business logic in the application whether a user may access a certain object. What you propose would give better security, but would also introduce more complexity. Think about whether it's worth it.

This makes it very difficult though when handling password resets...

When encrypting data with the user's password, that data is gone when the user forgets his password. In some cases, this is acceptable and the user should just create a new account. Otherwise, this is sometimes solved by giving the user a decryption key when setting up their account, which they are meant to print out and save. This is a "backdoor" key which can decrypt the account when the password is forgotten.


You want to protect the data. But from what? From who?
You need to define a possible scenario, and then you can try to find a way to protect from a specific danger, from a specific way an attacker could try to steal your data.

What about physical hacking? Can someone walk in and steal your server? Can someone make a copy of the live data? Can someone access the backups? (because you have backup copy, onsite, offsite, and all those backups are password protected, right?)

If you want to encrypt the data, you need to define at which level you want to encrypt data.
On certain RDBMS you can encrypt the whole DB on the disk, and store the key in the DB server. This may protect you if someone steals a copy of the files of your DB. But won't protect you if someone steals the DB credentials.
Or you can pass the encryption key with the connection string, but these kinds of things could vary a lot between different RDBMS.

You could modify your app/website to encrypt all the data in each rows/columns of the DB.
Now you have a lot of security, and a lot of problems:

  • How do you sort a table of encrypted data? you don't.
  • Column indexes? useless.
  • Can you do a SELECT ... WHERE Encrypted_Name_Column like '%Fox%'? no, you can't.
  • How do you encrypt a Date/Time column? Do you convert the date to string and then encrypt the string? Good luck with decrypting when daylight saving kicks in...
  • How do you manage IV (initialization vector) of encryption algorithm? If you encrypt the same data, with the same keys, and the same IV, then you get the same encrypted output. So an attacker may not be able to decrypt the data, but could be able to detect that certain values are the same, because the encrypted values are the same.

You can do all this, but is it really worth it?

How do you manage the encryption keys?
Usually you don't directly use the password to encrypt the data. Usually you generate a random secure key to encrypt the data, and then use the password to derive a key to encrypt the key you used for the data.
This way you can change the password without the need to re-encrypt all the data.
Now, if you lose the password, you need a fallback plan... like a copy of the data encryption key, encrypted with a master-password, used just for password reset. (but now, you have a master-password, that can decrypt all the data of all the users... where do you store it? on an air-gapped server?)

Would it be better to split up the services, and try to reduce the attack surface?
Why does your app/website need the DB credentials? You could create an app/website that never access directly the DB, but only access the data via a webservice. The webservice to access the data could be on a second server not accessible from the internet. The DB credentials, used by the second server, could be stored on a third server and accessed by the network with a specific account...
You could build up the security layer by layer.

Probably I'm just scratching the surface, website/app/db security is a pretty broad argument.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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