php - Mysql encryption / storing sensitive data,

Question

I have the following in place for my PHP website:

• SSL enabled

• Cookies :
  

  session_set_cookie_params($cookieParams["lifetime"], 
  $cookieParams["path"], $cookieParams["domain"], $secure, $httponly);
  

• Passwords SHA512 on transit, then password_hash() and finally PASSWORD_BCRYPT

• Mysqli Prepared statements
• Inputs all sanitized when INSERTING / UPDATE into Mysql
• htmlentities etc..used to avoid xss where possible.

I'm now looking to use AES_Encrypt function to encrypt sensitive data by having the $key to encrypt and decrypt stored outside the webroot directory.

This could potentially store patient data, does what I have in place seem secure enough?

---

Question:

How are you sanitizing the inputs when you INSERT/UPDATE? If you're using Prepared Statements, you should not escape the data manually as well.

Answer:
example:

  $firstname = ucwords(filter_input(INPUT_POST, 'firstname', FILTER_SANITIZE_STRING));

Answer 1

I have always understood NOT TO USE MySQL's built in encryption fuctionality because the point of encryption of data at rest (in the SQL) is that if the server is compromised, the data is not at [as much] risk.

The problem with the MySQL built in functionality is that it doesn't apply to when the data is passed to and from the "at rest" state, so the plaintext of any data can be recorded in MySQL logs (and elsewhere on the storage system, such as query lookups are not encrypted so you can from numerous lookups and their count results deduce column values) before/as it is encrypted. You can read more about this here.

Regarding encryption, you should use some tried and tested library like defuse/php-encryption.

From what I've read in my own research on this topic, the link provided by Magnus to defuse/php-encryption is one of the best ways of preventing MySQL ever causing you to compromise your data, by never letting the MySQL program/server ever see the plaintext value of your data.