How to encrypt and decrypt data stored in a MySQL table?

MySQL has two built-in functions: AES_ENCRYPT() and AES_DECRYPT(). The column that stores the encrypted data must be of type VARBINARY or BLOB.

INSERT INTO users (name, secret)
VALUES ('Robin', AES_ENCRYPT('my secret', 'strong-key'));

SELECT AES_DECRYPT(secret, 'strong-key') AS secret
FROM users WHERE name = 'Robin';

This works, but it has weak points. The key goes inside the SQL, so it can be seen in query logs. And in MySQL 8 the default mode is AES-128-ECB, which is not a strong mode.

A better way is to encrypt the data in PHP before saving it, so MySQL only stores the encrypted text. The sodium extension is built in PHP 7.2 and above.

<?php
$key = sodium_crypto_secretbox_keygen();                         // save this key safely
$nonce = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);

$cipher = sodium_crypto_secretbox("my secret", $nonce, $key);    // save $nonce and $cipher in the database

echo sodium_crypto_secretbox_open($cipher, $nonce, $key);
?>

Output:

• Previous Interview Question
• Next Interview Question

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.