0

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('pj@some.com', $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?

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'])
| improve this answer | |
  • I've just updated my question. I've included a sample value from the database. Also I'm using Real AES and Field encryption modules. It seems that the Field encryption module is storing that whole array in the DB. What is confusing me is that the same value 'Jones' encrypts to different values? in the above example... Thank you so much for your time – gMaximus Feb 27 '18 at 13:37
  • Also the dpm() returns that as a value. Not a PHP array because it isn't shown as such in krumo – gMaximus Feb 27 '18 at 13:45
  • I'm not overly familiar with the field encryption module. Have you looked in the database to see what is exactly stored in the field "field_customer_first_name_value" for example? Is it a string of the hash or is it a serizalised array? As the code you show in your question after "So the $lastname gives me:" is a serialized array. Drupal's normal text field storage is a string unless this field encryption is doing something else – Leigh Feb 27 '18 at 14:27
  • It is the serizalised array. I've included a sample value of the field in the db above. – gMaximus Feb 27 '18 at 14:34
  • Ah ok I see, yes if you are getting different values each time you do an encrypt that will definitely stop you comparing existing values in the database. (I've guessing with the setting you've got for you encryption its using some sort of random value or timestamp or something as part of its encryption method). But then it's surprising they manage to decrypt ok. Sorry I'm not sure I can help much further as Ive not used those modules. it might be worth digging around in their code to see whats happening or hopefully someone else can answer – Leigh Feb 27 '18 at 14:50
1

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

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.