In PHP/ Mysql, how to access encrypted data in database after changing the salt value stored in a config file?

Currently I’m encrypting user sensitive data before storing it in the database:

// salt retrieved from config file
$salt = 'a1b915580757c17c38a986faab21493d'; 

$sql = "insert into `appointments` (`id`, `appointment_date`, 
`appointment_email_address`) values (null, :date, AES_ENCRYPT(:email_address, 
'" . $salt . "'))";

Obviously description of the retrieved data is done using the same salt.

I’d prefer to change the salt value periodically, but how would I be able to access data in the database that was encrypted using a previous salt value?

Many thanks for your support, Durian.

Answer

This is a common problem with any encryption: if you want to rotate the encryption keys over time, how can you do this without loosing access to your already encrypted data.

Unfortunately, in this case you either have to use the new salt in an incremental manner, meaning only the new records are encrypted with the new salt, or you need to decrypt your data with the old salt and encrypt everything once more with the new one.

To be honest, using a field-level encryption this way is not as secure as the various examples claim it to be, since it is very difficult to manage the encryption keys in a secure and efficient manner.