Protecting encrypted data in case of SQL injection

The problem here is that every note is encrypted with the same encryption key. That makes the app more vulnerable than it would need to be.

Consider this setup instead:

• Every user, doctors and patients alike, have their own public - private key pair.
• Public keys are stored encrypted with an application wide key stored outside the database..
• Private keys are stored encrypted with a personal key derived from the users password.
• Every single note is encrypted with it's own unique random key. That key is stored encrypted in two version - one encrypted with the patients public key, and one with the doctors public key.

Don't take this as a blueprint for the perfect system. There are many ways to do this, and possibly better ones than this. But it is an example of a system where read and write access to the database only (e.g. in the case of SQLi) would not instantly give the attacker access to all the plaintext data.

Edit: If you want all doctors to be able to read all notes, things get a bit more shaky. Anyone with write access to the entire database will be able to turn themself into a doctor. So you need to restrict write access to the doctors table as much as possible!

Don't give the DB user the web app is using write access to that table, and use another more restricted system (e.g. only available on some intranet) to create new doctors. Or require that every row in the doctor table is cryptographically signed or something like that. Or even better, read Lie Ryans great answer.

Conclusion: The take away lesson here is that crypto requires more than just an algorithm, a message and a key. It's when you start thinking about how to handle the keys things gets complicated, and you need solutions that are heavily context dependant.

I think you're getting too sucked into a specific attack. I mean, you're worried that a SQL login that has full CRUD access to the notes table (and possibly more?) will try to read other doctors' notes?

• What if they selectively delete a note for a specific day?
• What if they replace a doctor's note with one they created?
• What if they change which doctor issued a note?
• What if they scramble all the notes and try to pull a ransomware threat?

Yeah, it'd be good to encrypt the doctors' notes using different keys (even if it's just as simple as: [AppWideKey]+[DoctorID]) But your focus should probably be, "If a SQL Injection Attack succeeds, the hacker will be logged into SQL as AppUserX. How can I minimize the damage that AppUserX can do?" And the answer's usually: have them use Stored Procedures to get to their data, only let the stored procs do the minimal amount to accomplish the app's needs, and don't give the user direct access to the tables.

You need to encrypt each note with its own secret key. You should use a new symmetric key for each note. This symmetric key is called the session key.

Now the problem becomes how to distribute the session key to the recipients and only to the recipients. For this, you need public key cryptography.

You should issue each participant in the system (patients and doctors) his/her own personal keypair. The public key may be stored on the server unencrypted, but the private key should not be stored on the server unencrypted. You should either encrypt the private key on the server using the user's password, or the users should be required to store their private key securely for themselves.

A patient can send a private message to a particular doctor by generating a new session key and encrypting a message with it, then encrypting the session key with the doctor's public key. You can also send a message to multiple recipients by encrypting the session key against multiple public keys.

Optionally, you may want to have a Certificate Authority that checks a participant's identity (e.g. by requiring a photo ID) and signs the participant's public key to assert that the identity check had been done, and attach signed attributes that asserts the participant's role(s).

At this point, you have a mechanism for point to point encryption, these are essentially how S/MIME and PGP scheme works.

You can build a group messaging on top of this, so that patients and/or doctors can send a message to a large group (e.g. a patient can send encrypted message to the podiatrist group, without allowing the dental group to read the message), without anyone having to know the individual member's identity. This is called multi-cast encryption.

The way you build group messaging is that you need to create a group keypair. A group keypair consists of a private key that's distributed to all members of the group and a public key that's distributed to anyone who needs to send messages to the group. This allow members and non members to send a message to everyone on that group only. To send a group message, you encrypt the session key for that message with the group's public key.

Note that in most cases, you'll need to create a new group key every time the membership of a group changes (e.g. someone leaves the group, or a new person entered the group.)

Encryption as you describe is not protecting you from much of scenarios. It will protect you in case MySQL database is stolen but the encryption keys are not. But since the database is behind application, it's the application which gets usually broken first, and then database, so in real-life scenario, both keys with application and database would be stolen.

To protect application from this, you would need to resolve SQL Injection, which is easy to do and straightforward. It's easy to make sure all application is resilient to SQL Injections, all you need is to modify every query to the database in e.g. way it's using prepared statements.

No matter how large and complex the application is, or how many languages and frameworks it is using, this is simple job and very effective. Having SQL Injections sorted is the very basic thing to do.

There are many other ways to protect hacker from doing what you describe. For example, auditing variables passed to the application, this is usually done by something called WAF, you can also embed SQL Injection attack detection into your code on the top of the processing chain.

If you've strong access controls in place, only the patient themselves should be able to create new notes against their profile. A doctor should be able to read the note and add comments to the note but not delete or update the note. This should make the original note immutable - grant the application insert but not update privileges on the notes table. This should also mitigate the injection attack.

You could also associate notes with a users sessions such that a doctor could only see notes from a user associated with valid sessions and any inserts performed through SQLi would not be shown - a database normalisation job could remove these.

For users who might have additional privileges via the web interface - e.g. a superuser, you could limit access to the superuser resource to specific IP addresses (i.e. head office system) using httpd.conf or similar.

Are you using parameterized queries? This should prevent non-sophisticated SQL injection.

You should have 1 unique key per user. Do the users sign up with an email address & password? You could use the password.

Generating a key for each user when they sign up and storing it in the database sounds stupid. It would only add 1 more step required to perform the original attack. Actually, you should not even store the user's password in the database. Are you? And are you using HTTPS?