Select config settings form & HMAC values for querying the DB

I'm writing a custom module that in itself isn't much use to anyone else. I'm receiving data via a JSON end point. I need to use that data to lookup existing entities.

I've encrypted fields on my custom entity types because it is things like name (text field), email (email module), address (Address module)and a telephone number (phone module).

Usually I use db_query() to look up things in my module. However, when the field is encrypted, db_query() becomes useless due to the encryption.

So I wondered if there was a way to achieve what I need. To have encrypted fields whilst still being able to look up entities.

I hope that clarifies things.

Many thanks

I'm considering using field_encrypt_views_filters module to create a view with some contextual filters.

Then call views_get_view_result() and pass in my un-encrypted values as arguments.

What's your thoughts on that approach?

Yes. My two concerns as well. It works by storing decrypted values in temporary tables. I don't know much about temporary tables and if they are at risk to thieves?

I feel stuck. I need to protect my customers data but I also need to be able to look things up in the database. I just hadn't anticipated this.

Surely others must have similar use cases to me. I feel like I'm missing something. Perhaps I'm not approaching this use case correctly?

Would using drupal_hmac_base64($data, $key) be fine from a security point of view? My understanding is that you can't decrypt it?

I'm extending the field_encrypt module to do this. Seems the most logical place. If the above is fine for security, I'm pretty much ready to provide a patch.

I plan to use the key module to store the HMAC key offsite.

Moving over to field_encrypt issue queue. That is where this functionality will logically need to live.

Well I believe that using HMAC is fine in terms of security because it isn't designed to be decrypted.

Attached is a patch that extends the field_encrypt module to also store a hash for the value that has been encrypted.

I then added a settings page for the module. It allows us to select a "Encrypt" configuration for encrypting a field. It then allows us to select a key provided by the key module for the HMAC value. I wanted this configuration so I could use offsite key management for the HMAC key. Whilst also being able to choose a specific "Encrypt" configuration for the field encryption.

This allows us to perform exact match queries to the database. To do so, here is a sample of code I'm using:

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

Before applying this patch, you will need to decrypt all your encrypted fields. Apply the patch and flush the cache to access the settings page. Select the your key and configuration. Then go back and encrypt your fields.

Above all, please backup your database to be safe.