Querying in rails on attr_encrypted column

Question

I have a ruby on rails app and I am using the attr_encrypted gem to encrypt some user info. It has a salt and IV so it is 2 way encrypted. The gem intercepts dynamic find_by to assist with querying, but that is not a sufficient query for my case as I care about the number of results.

Is there a way to query the table to return all results that match a given secret?

Here is the example. I have a users table and it has an encrypted secret attribute. The table thus has encrypted_secret, encrypted_secret_iv, and encrypted_secret_salt. If a user gave a secret of "abd123", how can I query the table to see how many others have also used "abc123" as their secret?

Answer 1

You could also save an additional secret_hash of the unencrypted secret. If two records have the same secret then they will have the same secret_hash too.

Add something like the following to your model:

scope :by_secret, ->(secret) { 
  where(secret_hash: Digest::MD5.hexdigest(secret) 
}

before_save :generate_secret_hash

private 
def generate_secret_hash
  self.secret_hash = Digest::MD5.hexdigest(secret)
end

After that, you can query like this:

YourModel.by_secret('abd123').count

---

Warning

Storing MD5 hashes of passwords and other sensitive information is a security risk. Even if you cannot tell the plain text secret from a secret_hash, it allows you to tell when users share the same secret. Or - even worse - the MD5 hash might be available in an MD5-reverse-lookup-dictionary.

You must carefully trade off this security issue against the benefit of being able to query on that column.