How to check if an email address exists in a table, which contains encrypted email addresses

Question

I have a table which contains encrypted email addresses of users. When a new user signs up, I want to check if the email address entered already exists in the table. I use the openssl_encrypt function in php to encrypt every email address. Since the function returns a different encrypted value even if the same string is passed as an argument, I cannot check if the email address already exists in the table . What is a viable, but secure way to encrypt and check if the email address exists in the table ?

Answer 1

Answered here: Indexing Encrypted Information in our paper on secure data encryption in PHP:

An uncommon problem that many companies encounter is, "How can we encrypt information in a database and still index it for fast retrieval?" For example, if you need to be able to retrieve patient records by social security number in a health care application.

If you have millions of records, decrypting each record one-by-one to perform a simple record lookup would be expensive for both the application and the database server. And storing such a sensitive record is a no-go (unless you enjoy apologizing to your customers and offering them one year of free identity theft protection).

Instead, create a blind index of this information for indexing purposes. First, encrypt the information and store it in one field (say: ssn). Then, store a separate HMAC of the plaintext with a separate key in another field (e.g. ssn_lookup) and create the database index on the second field. (This idea was proposed in an MSDN blog post by Raul Garcia. If you are developing a .NET / SQL Server solution to this problem, Raul's blog post is probably more relevant.)

The key for HMAC indexing purposes should be only available to the application server that needs to query the database, which should be on separate hardware, and never checked into version control.

Note that this only allows exact searches, not partial searches.

public function getAccountByEmail($email)
{
    $search = $this->pdo->prepare("SELECT * FROM table WHERE email_lookup = ?")
        ->execute([
            hash_hmac('sha256', $email, $this->searchKeys['email'])
        ])
        ->fetchAll(\PDO::FETCH_ASSOC);
    return $search;
}