Storing and searching for encrypted data fields like email

Question

I wanted to know what was the best practice followed for storing sensitive fields like email and phone number in the database. Lets say you want to search by email and phone number , and the application sends emails and sms to its users as well. Because this data is sensitive you need to encrypt it. Hashing is not an option because you cant unhash it. Encryption standards like Rjindael or AES makes the data secure, but you cannot search the db by it because the encrypted string produced for the same input is always different.

So in a case like this do I need to store both the hash as well as the encrypted field in the table ? Or is there some other strong encryption technique deployed for fields like these.

Answer 1

Check out CipherSweet. It's a very permissively-licensed open source library that provides searchable encryption in PHP.

Its implementation is similar to Ebbe's answer, but with a lot more caveats:

1. CipherSweet automatically handles key splitting, through a well-defined protocol.
2. CipherSweet supports multiple functional blind indexes (truncated hashes of transformations of the plaintext) to facilitate advanced searching.
   • More about the security implications of its design are available here.

Furthermore, the API is relatively straightforward:

<?php
use ParagonIE\CipherSweet\BlindIndex;
use ParagonIE\CipherSweet\CipherSweet;
use ParagonIE\CipherSweet\CompoundIndex;
use ParagonIE\CipherSweet\EncryptedRow;
use ParagonIE\CipherSweet\Transformation\LastFourDigits;

/** @var CipherSweet $engine */
// Define two fields (one text, one boolean) that will be encrypted
$encryptedRow = (new EncryptedRow($engine, 'contacts'))
    ->addTextField('ssn')
    ->addBooleanField('hivstatus');

// Add a normal Blind Index on one field:
$encryptedRow->addBlindIndex(
    'ssn',
    new BlindIndex(
        'contact_ssn_last_four',
        [new LastFourDigits()],
        32 // 32 bits = 4 bytes
    )
);

// Create/add a compound blind index on multiple fields:
$encryptedRow->addCompoundIndex(
    (
        new CompoundIndex(
            'contact_ssnlast4_hivstatus',
            ['ssn', 'hivstatus'],
            32, // 32 bits = 4 bytes
            true // fast hash
        )
    )->addTransform('ssn', new LastFourDigits())
);

Once you have your object instantiated and configured, you can insert rows like so:

<?php
/* continuing from previous snippet... */
list($encrypted, $indexes) = $encryptedRow->prepareRowForStorage([
    'extraneous' => true,
    'ssn' => '123-45-6789',
    'hivstatus' => false
]);
$encrypted['contact_ssnlast4_hivstatus'] = $indexes['contact_ssnlast4_hivstatus'];
$dbh->insert('contacts', $encrypted);

Then retrieving rows from the database is as simple as using the blind index in a SELECT query:

<?php
/* continuing from previous snippet... */
$lookup = $encryptedRow->getBlindIndex(
    'contact_ssnlast4_hivstatus',
    ['ssn' => '123-45-6789', 'hivstatus' => true]
);
$results = $dbh->search('contacts', ['contact_ssnlast4_hivstatus' => $lookup]);
foreach ($results as $result) {
    $decrypted = $encryptedRow->decrypt($result);
}

CipherSweet is currently implemented in PHP and Node.js, with additional Java, C#, Rust, and Python implementations coming soon.

Answer 2

Actually, encrypting the same message twice with AES with the same key and the same initialization vector (IV) will produce the same output - always.

However, using the same key and the same IV would leak information about the encrypted data. Due to the way AES encrypts in blocks of 16 bytes, two email addresses starting with the same 16 bytes and encrypted with the same key and the same IV would also have the same 16 bytes in the start of the encrypted message. Those leaking the information that these two emails start with the same. One of the purposes of the IV is to counter this.

A secure search field can be created using an encrypted (with same key and same IV) one-way-hash. The one-way-hash ensures that the encryption don't leak data. Only using a one-way-hash would not be enough for e.g telephone numbers as you can easily brute force all one-way-hash'es for any valid phone numbers.

Answer 3

If you want to encrypt your data, place the table on an encrypted filesystem or use a database that provides a facility for encrypted tables.

Encrypting data in the database itself would lead to very poor performance for a number of reasons, the most obvious being that a simple table scan (let's say you're looking for a user by email address) would require a decryption of the whole recordset.

Also, your application shouldn't deal with encryption/decryption of data: if it is compromised, then all of your data is too.

Moreover, this question probably shouldn't be tagged as 'PHP' question.