Best way to find encrypted data inside MySQL

Question

I encrypt using PHP before storing the encrypted data in MySQL. I am convinced that this is the best way in the long run using MySQL AES_ * functions.

Now my question is: is there an efficient way to search for encrypted data besides saving an available hashed version of the data? For example, two columns for 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'm doing now, is there a better way?

Answer 1

Now my question is: is there an efficient way to search for encrypted data besides saving an available hashed version of the data? For example, two columns for data: first_name_encrypted, first_name_hashed.

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

, AES_*() MySQL:

$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);
}

, HMAC-SHA256, .

: . .

Answer 2

, , , .

, , , ...

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

-, ? (, "https" ).

, - ? , . ?

, , , , (.. "first_name" ). , . , (, , , ..) .

, , - , .

TL;DR: - . , , .