Securely store sensitive data in a database

Question

I am currently creating a retirement home site. I have already developed a scheme for storing personal data in my database, but I would like to get your opinion on this.

Basically, I have a patient table that stores publicly available (= insensitive) patient information. Other information (e.g. name, address) is private and must be securely stored. I am using a public / private key pair generated by PHP OpenSSL and submitted by the website manager. The passphrase is known only to people who are allowed access to personal data (mainly health care providers). I would like to keep them in another table. The first question is , BLOB best type of column (with MySQL) for storing binary data. Or should I convert them (e.g. base64 ) and save them in a VARCHAR column?

My patient_secure_data table looks like this:

  id INT AUTO_INCREMENT
 patient_id INT (FOREIGN KEY to patient.id)
 key VARCHAR (63)
 data blob
 env blob

This is the key value table where the value is sealed by openssl_seal . I need to save the third parameter ( $env_keys ) in order to be able to decrypt the data. So the second question is , why do I need env_keys if I have a private key openssl_open when I call openssl_open ?

The third (and last) question is is the database safe schema? I mean, can I guarantee that no one who does not have a passphrase can see private data?

Note. I will also use the same key pair to encrypt files stored on disk. But the database or files, I do not see the difference in security.

Hi,

Guillaume.

_{Sorry if my language is not perfect, I am not a native speaker of English ... I hope I have made it clear.}

Answer 1

1 - BLOB is my preference because encoding it on base64 will increase both space and processing time (since you will also have to decode base64 before decryption)

2 - openssl_seal does not give you the key that was used to encrypt data. The purpose of env_keys is to store the encrypted form of the generated key. When you call openssl_open, you give it that envelope key and the private key that needs to decrypt the envelope key. The private key must be mapped to the public key that was used to generate the envelope key.

3- If your private key requires a passphrase, then technically your data is relatively safe. Even if they have an envelope key and a private key, they won’t be able to use it ... but how safe is your passphrase? The only thing you need to understand is that you can almost never guarantee a completely secure scheme, but you can definitely make it tough for hackers. Use your imagination here. Btw, your passphrase in the text in your code?

Answer 2

• Anything over 500chars should probably be a blob (or clob).

• As for keys, you should only store the public key in your public database. Thus, you do not need to worry about anyone (passphrase or not) that allows you to decrypt the data. The private part of the key must be stored in a sensitive database. You only need the public part to decrypt the data encrypted by the private part. I would not save the entire key (public + private) on the server disk containing insensitive information, as this would compromise the security of the key.