Encrypt module; Is there a way of using db_query() on encrypted values?

Question

I've installed the encrypt module because we will be handling a lot of personal data. For those in the EU there is serious consequences under GDPR for failing to protect users data.

I've also installed Real AES and Field Encryption.

So I have got the values encrypted and followed the instructions. I can see the encrypted values in the database. For example:

a:6:{s:4:"text";s:88:"fYGWIfFfoQuar4yy/ySsyTtK2Qt/flgz9mg4GdRD6hlDOM8f8fevf9Q6O35Yp+WDwByB7SXGg0yPQLXfCC3FlQ==";s:6:"method";s:17:"authenticated_aes";s:12:"key_provider";s:4:"file";s:7:"options";a:1:{s:6:"base64";b:1;}s:15:"method_settings";s:0:"";s:17:"provider_settings";a:2:{s:4:"path";s:15:"../keys/xscodes";s:6:"method";s:13:"base64_decode";}}

Views also displays the decrypted values. I can also see them when viewing and editing one of my entities.

This particular Drupal install is used to store customer and sales info from many Commerce sites in a centralised place. As a result I'm doing lots of db_query()'s when I receive some order information via a JSON call.

My plan was to encrypt the values given. Then use that encrypted value in my call to db_query().

In the PHP block that comes with Devel I have this while testing:

  $lastname = encrypt('Jones', $options = array('base64' => TRUE), $method = 'authenticated_aes', $key_provider = 'file', $config = 'real_aes_encryption');
dpm($lastname);


    $lastname2 = encrypt('Jones', $options = array('base64' => TRUE), $method = 'authenticated_aes', $key_provider = 'file', $config = 'real_aes_encryption');
   dpm($lastname2);

    $lastnamede = decrypt($lastname);
    dpm($lastnamede);

    $lastname2de = decrypt($lastname2);
    dpm($lastname2de);

So the $lastname gives me:

a:6:{s:4:"text";s:88:"dQApE9dYu1QE2Cz5N5f9rLRW8xuDEYKvDaq3jmJFEC+GBljGqbQ68XfQjv92fNicECsPnoK+s11agTPX0LRQuQ==";s:6:"method";s:17:"authenticated_aes";s:12:"key_provider";s:4:"file";s:7:"options";a:1:{s:6:"base64";b:1;}s:15:"method_settings";s:0:"";s:17:"provider_settings";a:2:{s:4:"path";s:15:"../keys/xscodes";s:6:"method";s:13:"base64_decode";}}

The $lastname2 gives me:

a:6:{s:4:"text";s:88:"RDsHVHv2wvcK9012xAElHs/tx3rltR57DjyhUXGcBVXrN1j/RqBt/UVkLY6hxEmyLm9zQkqXtET9SgtKmB8q/Q==";s:6:"method";s:17:"authenticated_aes";s:12:"key_provider";s:4:"file";s:7:"options";a:1:{s:6:"base64";b:1;}s:15:"method_settings";s:0:"";s:17:"provider_settings";a:2:{s:4:"path";s:15:"../keys/xscodes";s:6:"method";s:13:"base64_decode";}}

Both values then de-crypted as expected. $lastname and $lastname2 being different values presents me with a problem when it comes to db_query() calls.

The query I'm trying to do is this:

$firstname = encrypt('Peter', $options = array('base64' => TRUE), $method = 'authenticated_aes', $key_provider = 'file', $config = 'real_aes_encryption');
$lastname = encrypt('Jones', $options = array('base64' => TRUE), $method = 'authenticated_aes', $key_provider = 'file', $config = 'real_aes_encryption');
$email = encrypt('[email protected]', $options = array('base64' => TRUE), $method = 'authenticated_aes', $key_provider = 'file', $config = 'real_aes_encryption');

$customer_id = db_query("SELECT c.id 
  FROM {eck_customer} c
  LEFT JOIN {field_data_field_customer_first_name} first_name ON c.id = first_name.entity_id
  LEFT JOIN {field_data_field_customer_last_name} last_name on c.id = last_name.entity_id
  LEFT JOIN {field_data_field_customer_email} email ON c.id = email.entity_id
  WHERE email.field_customer_email_email = :email
  AND first_name.field_customer_first_name_value = :first_name
  AND last_name.field_customer_last_name_value = :last_name
  AND type = 'customer'", array(':email' => $email, ':first_name' => $firstname, ':last_name' => $lastname))->fetchField(); 
if ($customer_id) {
dpm($customer_id);
 }

The above query is correct. It works without encryption being involved.

I tried entity field query in case that worked but no joy there.

So the question is, does anyone know of a method to achieve what I want? How can I query the database on encrypted fields?

Answer 1

You can see from your DPM that your encryption method is returning an array containing several different values. You can't use an array for a database field value it would need to be a string in this case. So you need to use just the encrypted hash string from that array in your db_query e.g.

array(':email' => $email['text'])

Answer 2

There wasn't a way to do what I needed out of the box for the reasons mentioned by others. Every time you encrypt a value using AES, you get a different output.

One approach I was introduced to suggested storing a HMAC hash of the value. When you encrypt a value using HMAC you always get the same result. As long as you're using the same key to do so. However it can't be decrypted. I could then search the database using that hash.

So I had to patch the field_encrypt module: https://www.drupal.org/project/field_encrypt/issues/2948313#comment-12522830

With this patch, every field that gets encrypted also has the HMAC value stored into a separate column. The column has the same name with the _hmac suffix. The patch also leverages the key module for the HMAC key. I wanted offsite key management.

An example of my working code to query the database is:

$key_id = variable_get('field_encrypt_hmac_key');
$key = key_get_key_value($key_id);
$first_name = drupal_hmac_base64('Guy', $key);
$customer_id = db_query("SELECT c.id 
      FROM {eck_customer} c
      LEFT JOIN {field_data_field_customer_first_name} first_name ON c.id = first_name.entity_id
      WHERE first_name.field_customer_first_name_value_hmac = :first_name
      AND type = 'customer'", array(':first_name' => $first_name))->fetchField();
  if ($customer_id) {
    return $customer_id;
     }

The one biggest drawback I can think of is that only exact matches are possible.

Hope this helps others

Answer 3

As you've already noted, encrypting the same plain text multiple times will produce a different result every time.

That's because the encryption algorithm is using a randomly generated "Initialisation Vector" (like salting a password before hashing it) which is stored in the first few bytes of the encrypted text to facilitate decryption.

Therefore, I don't think there's any way to query the encrypted text directly in the database because the Initialisation Vector for your query input will always be different to that of the stored values.