Symfony / Doctrine — database encryption
Encrypting database with Doctrine and Symfony
As much as personal data should be kept private, there are some rare case where for some reason we must have access to them in public services. I have my own private project for managing personal data (with source code accessible here), but I’ve came to the situation where due to my incoming mobility I simply must have access to my personal information in other places.
There are few solutions for such problem naturally, like 2 came to my mind instantly:
- moving the files / information on laptop,
- having access to the data over internet,
I’ve picked up the 2nd option due to few cases:
- I don’t have to remember to update data on storage (crons will do that),
- I won’t always have access to laptop etc (I can get access to data in phone),
- I want to get better / more familiar with Vue.js (and this is an opportunity then),
“However the picked solution has one big downside which had / needs to be solved — possibility of data leakage”
Protecting personal data
Honestly I’ve became a bit obsessed with protecting my data from potential leakage (there is always a risk, but You can reduce it, bring closer to zero).
With all of the implemented ideas of mine I came to the point of asking myself “what if the database will leak?”.
Solution for this came almost instantly as I already rely on it partially in my private project: “Database encryption”.
While I’ve already seen few encryption packages for Symfony with Doctrine, I will only focus on the one that I keep using (there is actually small issue in it but it’s easy to solve):
- EncryptBundle (mogilvie — the one I rely on),
- DoctrineEncryptBundle (GiveMeAllYourCats — fork with some fixes),
Both packages share same base functionality which is encrypting / decrypting database cells with special key, however the DoctrineEncryptBundle comes with CLI commands which allow to handle whole database at once — which is a rely nice feature.
I moved on with EncryptBundle as I already use, and I have encryption key generated for it thus I will be able to share one key between internal / external project.
How things work
Using the EncryptBundle is very easy and intuitive. First of all naturally the package must be added to the project via composer, and afterwards we need to generate our encryption key (like described in original Readme “here”).
Our service Yaml will contain now this new section:
Further we got to add special annotation to the entity columns like this:
The bundle supports the doctrine events so this is generally all — nothing more is needed.
“Can the encryption be handled without annotation?”
Yes. I didn’t knew that the bundle handles doctrine events thus I’ve made my own method based on the bundle to handle data encryption manually.
How the encrypted data looks like with incorrect key provided?
It’s just a empty string which is actually cool, as in my case I don’t care if exception is thrown or wrong data is displayed — the goals has been achieved — data is secured.
I’ve had to debug the package at one point so I can say it relies on PHP named OpenSSL:
Even more security
Since I want my data be accessible over the Internet I had to find a way “not to store the key on server” — this would basically look like this:
Now this is the issue that I’ve described on the beginning — the package that I use does not allow to add encryption key on-fly as exception is being throw in yaml parameter is missing — thus at the moment I’ve got it hardcoded but seems like the solutions is on it’s way (prepared by author himself)— even if not, it can be implemented manually. There is also small problem with non booted Kernel (but I’ve just missed booting check).
Instead of putting key under mat I’ve just expanded my login form:
My overall solution here is:
- creating OnBeforeSetKey,
- creating EncryptionKey file,
- reading the file with each request and setting the key
on-fly(replacing the placeholder000000000...), - having a cronjobs which check last user activity and removes the key from server if user is no longer there (this could be even more secure by storing the key in session instead of file, but I also remove the file when user logs-out so I will stick to my method as with all that IP filtering etc is just fine).
Works like a charm — is not optimized (saying that file is being opened each time), but I don’t need it for my tiny sub-project.
Now the cells in database looks like this:
3DDOXwqZAEEDPJDK8/LI4wDsftqaNCN2kkyt8+QWr8E=<ENC>Personal notes
As in my case I didn’t noticed any performance decrees it certainly hits performance in case of fetching / handling large amount of data — it simply must — no idea how much it hits but as we now need to handle encryption each time it might have visible impact.
Even though I’m not to familiar with encryption, logically saying — it most likely depends on used algorithm.
