5

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
user3397380
  • 51
  • 3

1 Answers1

2

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.

spickermann
  • 100,941
  • 9
  • 101
  • 131
  • Thanks. Do you know if this is the only way to achieve querying on an encrypted column? – user3397380 Nov 11 '14 at 22:05
  • Since every column should have a different `salt` and `iv`, the only other way IMO would be to load all columns and check every decrypted secret against your search string. – spickermann Nov 12 '14 at 00:34