I have a self access medical portal project for patients to view their visit history and consultation info.

Some fields in the database needs to be encrypted. The reason is in case the DB gets unauthorised access, they should not see the plaintext data.

We will be using PHP 7 for the project. I've read that it's recommended to use in built openssl encrypt and decrypt functions.

For encryption key I was thinking to use Amazon KMS to manage the keys.

For servers we are intending to have it all setup on Amazon. I believe we can setup the IAM roles to have EC2 access KMS service securely.

Users will access site using https. Each time there is sensitive data being queried or before it gets saved into the database, the php script will encrypt and decrypt it using kms and openssl functions.

I'm wondering if this setup is secure enough to encrypt the data? Other concerns would be doing a sql query to search data. With the data encrypted, searches with LIKE %foo% doesn't look possible.

Is there a better way to do it?


1 Answer 1


The setup can be considered secure. You do hold the decrypted data only in memory. Typically you will use a key per row to encrypt the sensitive data. However you cannot search for the data in the database (as you mentioned). It depends on the use case you may be able to solve the issue by hashing the sensitive data. When you want to search for a particular value you can hash the value before you search for it. However you can realize only a search for exact matches and not one with wild cards (the 'like' operator will not work!).

An entire different approach would be to encrypt the whole database. The data is stored encrypted on the hard drive. When you use AWS with RDS you can implement this strategy easily: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html

All backups are encrypted and the data itself is also stored encrypted. However if someone access the database when it is running the data is not encrypted for this particular user. Means when you grant someone the permission to access the data it will be decrypted. However at the end it comes down anyway how you setup your IAM roles. Either you rely on the IAM roles for the KMS or for the RDS. So if setup the IAM roles properly you will be fine. AWS supports since some weeks also the cross region replication of such encrypted database instances.

Normally for PCI DSS and for HIPAA compliance both approaches are accepted. As you looking for a way to search the data as well I would recommend the second approach.

Disk Encryption with RDS

RDS uses an EBS volume to store the data. The data on the EBS volume can be encrypted (see http://docs.aws.amazon.com/kms/latest/developerguide/services-ebs.html). Means when the RDS instance is accessing the EBS volume the data is encrypted resp. decrypted transparently. Means the read resp. write operation triggers a decryption resp. an encryption of the data. You find here the details about how to setup RDS with encrypted EBS volumes: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.Encryption.html Essentially you have to specify during the creation of the instance whether it should be encrypted or not.

After you have setup this you need to setup a user with in the database which allows to access the database. Eventually you need also a IAM role which allows you to modify the database instance through your application. I don't know if you need the later. It really depends on your use case.

The last thing which I recommend is to encrypt the communication between your application and the RDS instance.

Summarized the data is never stored unencrypted. The data is always decrypted when it is used and the unencrypted data is only kept in the memory (RAM).

  • Thanks for recommendations. The second method sounds better. But I'm just concerned on the data being plaintext if it gets dumped. Would you be able to elaborate more on the setup of IAM roles? Especially if I were to go with the second approach.
    – darnpunk
    Commented Mar 5, 2017 at 10:07
  • I have updated my post. What exactly happens when the RDS instance crashes I cannot tell, since I am not an AWS engineer and as such I don't know what they do exactly. However the solution is certified and as such AWS has to solve those issues and you do not need to worry about it. You only need a dedicated IAM role when you want modifying the RDS instance with your application. Commented Mar 6, 2017 at 5:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .