Let's say there is a web application, with encrypted data.
e.g. a website where patients leave notes to their doctors.

A patient logs in, leave a note, that note is encrypted using symmetric encryption and inserted to the database, the user can still see his note if he ever comes back, the note is decrypted and he can see it. The doctor logs in, go to a patient's note and it gets decrypted and printed.

But the app is vulnerable to SQL injection. A hacker finds it, he opens an account in the web application and creates a dummy note. He then uses the SQL injection and copies someone elses encrypted note and paste it's content to his note (his row in the table). When he goes back to the application to view it, it is decrypted.

7 Answers
7

If I understand your question correctly, your system uses the same key to encrypt and decrypt the message for both patient and doctor.

In which case your attack is theoretically possible assuming you could read another user's message and insert it into the DB as your own message.

Mitigation in terms of encryption against this would be a unique key per user or using public/private key pairs to encrypt the data with each user again having a unique key for additional security and limitation of damage in the event of a breach.

Other mechanisms would include appropriate defence against SQL injection by sanitising input and also a user verification system to ensure any registering user is a legitimate patient, this reduces the pool of attackers but shouldn't be relied on alone.

unique key per user, you mean every time a user signs up I generate a key for that specific user and store it,.
– Mehdi BounyaFeb 1 '18 at 12:42

4

Yes, otherwise your attacker needs only a single key and they can read everything which would be a nightmare if there were to be a breach. A strong key per user massively limits damage. You need to be careful about how you store the keys though, if they are compromisable via the SQLii vector you effectively have the same problem as the single key.
– iainpbFeb 1 '18 at 12:45

1

What if I generate a key1, store it securely. then generate a key per user, store it in the database and encrypt it with key1, would that be a good solution?
– Mehdi BounyaFeb 1 '18 at 12:46

1

Assuming that it a strong key, your encryption method is effective and that master key is securely stored, I think that solution would work.
– iainpbFeb 1 '18 at 12:52

7

@iainpb: I would rather recommend using parameterized queries than attempting to sanitizing inputs :x
– Matthieu M.Feb 1 '18 at 14:57

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.

I think everybody agrees that SQLi should be fixed wherever it is found. But would it not also be a good idea to have a system that is resiliant to SQLi? I think that is what the OP is asking for.
– AndersFeb 1 '18 at 12:38

I know that protecting from SQLi is a must, I'm just trying to figure out if my idea of using encryption is wrong, both keys with application and database would be stolen the key in this scenario is not stolen, the hacker found an SQLi in an input box and he could run queries but the enc. key is not accessible to him.
– Mehdi BounyaFeb 1 '18 at 12:44

For example, you could use different key for each user, and store these keys in separate database. So if someone got SQL Injection to your main db, keys would be stored on another db. Another way would be to store sensitive information behind API. This way, if someone finds SQL Inection to your front app, they wont be able to pull the database, or make any modifications to it.
– AriaFeb 1 '18 at 12:57

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.

Actually this is a good idea, if I understand, once the note gets inserted you can't edit it.
– Mehdi BounyaFeb 1 '18 at 13:34

Yes - this tries to address your issue through access rights and privilege management at a user and database level. You should still have encryption to protect the data but these are other controls to manage access to and mallability of the data.
– AndyMacFeb 1 '18 at 13:36

How do you propose to associate notes with valid sessions? An attacker which completely compromised the database could copy a valid session from another note…
– ÁngelFeb 2 '18 at 0:01

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?

This is a fictional scenario where I'm not using anything I don't have any code, but usually, I use PDO, I use HTTPS (with the right headers), and yes I do store the password if necessary and it is hashed correctly, you could've posted a comment I think that would do the work.
– Mehdi BounyaFeb 1 '18 at 16:54

Do you use PDO::prepare and PDO::bindParam? Do you mean you store the password or a one-way hash of the password or both? How about using the password as the key for the note and never storing the password?
– user169799Feb 1 '18 at 16:58

Man.. I let a framework take care of the PDO stuff, I store the result of that one way hashed function, I use PHP's native password_hash, and again I'm not using the password as a key to store the notes because there are no notes, I made up this whole scenario to understand solutions about such an attack
– Mehdi BounyaFeb 1 '18 at 17:00

The solution is to use PDO::prepare and PDO::bindParam, which will prevent all non-sophisticated SQL injections. If you trust a framework then you never really know.
– user169799Feb 1 '18 at 17:06

2

Use the password? What about when the user changes their password or forgets it?
– iainpbFeb 2 '18 at 7:50