1

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));
memaxt
  • 57
  • 2
  • 11
  • 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. – M. Eriksson May 07 '17 at 16:30
  • 1
    Regarding encryption, you should use some tried and tested library like [defuse/php-encryption](https://github.com/defuse/php-encryption). – M. Eriksson May 07 '17 at 16:31
  • When handling passwords, are you rolling your own hashing and salt or are you using `password_hash()` and `password_verify()`? – M. Eriksson May 07 '17 at 16:37
  • Yes i'm using password_hash() / password_verify() – memaxt May 07 '17 at 16:43
  • Nice. It seems like you're set! – M. Eriksson May 07 '17 at 16:43
  • I hope you've set your cookies to be [secured cookies](http://php.net/manual/en/function.session-set-cookie-params.php) rather than plaintext cookies. – Martin May 07 '17 at 16:45
  • wahooo - with using php-encryption library as mentioned in a comment above - is patient data safe enough to be stored?, cookies are set to secure – memaxt May 07 '17 at 16:46
  • session_set_cookie_params($cookieParams["lifetime"], $cookieParams["path"], $cookieParams["domain"], $secure, $httponly); – memaxt May 07 '17 at 16:47
  • `$secure` doesn't tell us anything. This value should always be **`true`** – Martin May 07 '17 at 16:48
  • Yes, it should definitely be safe to store, as long as you handle the key properly. It's recommended that you generate a proper key file that you store somewhere safe. There should be some recommendations in either the docs or around internet. It's a pretty well used library. – M. Eriksson May 07 '17 at 16:49
  • 1
    A final recommendation is not to use the default baseline `cost` parameter (10) in your `password_hash` and to use a higher value, depending on the power of your server processors. Typically aim for a value 12-15. It makes your hashes more secure. – Martin May 07 '17 at 16:51
  • Thanks @Magnus, I will give the suggested library ago! hopefully I will be able to get it set up!. Would you be able to provide me use a select query example using the encryption method you suggested? – memaxt May 07 '17 at 16:51
  • @memaxt make use of the https://security.stackexchange.com/questions StackExchange Site `:-)` – Martin May 07 '17 at 16:52

2 Answers2

8

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.

Martin
  • 22,212
  • 11
  • 70
  • 132
3

+1 to Martin's answer, but I'll add some info for what it's worth.

MySQL 5.7 has implemented encryption at rest for InnoDB tablespaces (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html).

MySQL 8.0 will reportedly also implement encryption at rest for InnoDB redo log and undo log files (https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html).

This still leaves unencrypted the query logs and the binary log. We'll have to wait for some future version of MySQL for that.

Why does it take so long? The head of the security engineering for MySQL said at a bird-of-feather session at the Percona Live conference last month that they are being very careful to implement encryption right. This means implementing features for encryption, but also key security and key rotation, and other usage. It's very complex to get this right, and they don't want to implement something that will become deprecated and make everyone's encrypted databases invalid.

Ben in CA
  • 688
  • 8
  • 22
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Thank you Bill. As I said, I think it makes leaks far less possible (and data much tidier) if the encrypting is done on the PHP/abstraction layer rather than in the SQL itself. Esp. if they are different servers, and the keys are stored on the PHP server and the data is stored on the MySQL server, this also means an attack would [probably] need to compromise both servers to get the data.... – Martin May 07 '17 at 17:05
  • 1
    v8.0.14 added encryption to binary logs: https://dev.mysql.com/doc/refman/8.0/en/replication-binlog-encryption.html – Shadow Jan 04 '21 at 22:52