Best way to search encrypted data inside MySQL

Question

I am encrypting using PHP before saving the encrypted data to MySQL. I am convinced this is a better way in the long run vs using MySQL's AES_* functions.

My question now is, is there an efficient way to search encrypted data aside from storing a searchable hashed version of the data? E.g., Two columns per data: first_name_encrypted, first_name_hashed.

$hashed_search = myhash('John');    
$q = "SELECT * FROM table WHERE first_name_hashed = '$hashed_search'";

This is what I do now, is there a better way?

Answer 1

My question now is, is there an efficient way to search encrypted data aside from storing a searchable hashed version of the data? E.g., Two columns per data: first_name_encrypted, first_name_hashed.

Close, but no cigar. See: How to search encrypted information with a blind index.

One example, using an authenticated encryption library instead of just using MySQL's built-in AES_*() features:

$first_name_hash = hash_hmac('sha256', $firstName, $secretKey);
$stmt = $db->prepare('SELECT * FROM table WHERE first_name_idx = ?');
$result = $db->execute([$first_name_hash])
    ->fetch(PDO::FETCH_ASSOC);
if ($result) {
    $first_name = Crypto::decrypt($result['first_name_encrypted'], $otherSecretKey);
}

A blind index based on HMAC-SHA256 is preferable to a simple hash.

Also: Use authenticated encryption. This is not negotiable.