Rails Encrypted Columns that Still Allow for Searching

Question

I have a rails application that requires that I encrypt some columns in the database.

I already have, based on many examples, the user password and email encrypted. That seemed easy even for a noob like me.

The specific challenge is that I'm developing an application for attorneys (really one atty), and I want to encrypt their client's names and addresses so that if the database is stolen, that data is not readable. The problem is that the end user (the attorney) will want to search by client.

I'm assuming that based on my requirements, despite the debate (though probably a lack of knowledge), that encryption in the database is the right way to go. I think otherwise the searching will be very difficult in the application. It seems possible to setup the database so that keys can be passed into the database from somewhere else when it starts up, so that the keys aren't in a file on the server somewhere.

Actually, I'm mot even sure that the kind of encryption I'm looking for is possible or practical. I've read a bit and searched, but thus far, I've found no clear solutions. Below are the most promising things I've looked at. All seem powerful and creative solutions. Please help me correct any mis-impressions and point out, if such exists, solutions that might work. As always, thanks.

attr_encrypted

Application encryption, but I don't think practical search is possible.

pgcrypto

Makes use of postgresql's database encryption, but it's not Rails 4.0, and I can't seem to make it so. Not certain it would do what I want anyway.

jmazzi/crypt_keeper

Makes use of postgresql's database encryption, but doesn't allow for the the kind of searching of the client data that would work.

Answer 1

According to attr-encrypted/README.rdoc search is possible. There is a warning that it will only work if all records are encrypted with the same key per attribute.

The example given is:

class User < ActiveRecord::Base
  attr_encrypted :email, :key => 'a secret key'
  attr_encrypted :password, :key => 'some other secret key'
end

# You can now lookup and login users like so:
User.find_by_email_and_password('[email protected]', 'testing')

Answer 2

There's a technique called blind indexing you can use for this. An advantage of this approach is the database never sees the secret key.

Here's a Ruby implementation I created. You can create indexes on expressions for fuzzier searches.

class User < ApplicationRecord
  blind_index :initials, attribute: :name, expression: ->(v) { v.split(" ").map(&:first) }, ...
  blind_index :phone_last4, attribute: :phone, expression: ->(v) { v.last(4) } ...
end

Be very selective with how you use this, as it leaks the fact certain rows contain the same indexed data.

This is not 100% the same as doing LIKE queries. An efficient way to index for LIKE queries is trigrams. However, if you create trigrams and blind index them, an attacker could use frequency analysis to decipher the data.