What is the best way to encrypt data without using the resource of mysql server ? Is mcrypt with CFB mode the best way with php?

Question

I have a huge database with non encrypted fields.

These data are sensitive and the managers took a decision to encrypt them as soon as we can. And all new data should be automatically encrypted to the same way.

First Method:

If I use aes_encrypt to create a new field and insert the encrypted data to it like the following:

INSERT INTO myTable(name_en, name_es, phone)
VALUES(aes_encrypt(name_en, :key), aes_encrypt(name_es, :key), aes_encrypt(phone, :key))

And afterwards, each insert query should use aes_encrypt(), it will be resourceful to the server.

Second method:

Use libsodium library, which is a great solution to the new data. But the exisiting data will stay the same as libsodium couldn't be used inside a MySQL Query.

Third Method:

Using mcrypt_encrypt with MCRYPT_RIJNDAEL_128 as an equivalent to aes_encrypt, and then insert the encrypted variable into the query without letting MySQL do the encryption part, which will reduce loads and processing time.

But the mcrypt_encrypt isn't secure anymore, specially with the ECB mode.

What is the best method, to encrypt few fields in an exisiting MySQL database, and of course, the new one ? And if I used mcrypt with mode CFB, can use aes_decrypt afterwards to check the data ?

Answer 1

What is the best method, to encrypt few fields in an exisiting MySQL database, and of course, the new one ?

Write a PHP script that reads through your database and encrypts at the application layer (i.e. in PHP), not in the database layer (i.e. not in SQL). Encrypt them all at once, and have your scripts encrypt new fields opportunistically.

But the exisiting data will stay the same as libsodium couldn't be used inside a MySQL Query.

The fact that libsodium isn't available to MySQL shouldn't be a blocker at all. You don't want your database to ever have a chance to steal your keys, after all. MySQL should only see opaque ciphertext blobs.

You shouldn't be trying to encrypt with a SQL query. Do it outside of SQL and replace fields with ciphertext. Then when your process is complete, make sure you don't have plaintext cached anywhere.

Caveat

If you're trying to encrypt an entire database, you'll probably eventually want to use some of the fields in the WHERE clause of a SELECT query. Welcome to searchable symmetric encryption. There's a lot of crazy academic designs here (order-preserving encryption, homomorphic encryption, etc.).

For this use case, I recommend CipherSweet. It's fast, secure, easier to reason about, and doesn't require installing any third-party C extensions to use from PHP.