sql - Querying in rails on attr_encrypted column -

i have ruby on rails app , using attr_encrypted gem encrypt user info. has salt , iv 2 way encrypted. gem intercepts dynamic find_by assist querying, not sufficient query case care number of results.

is there way query table return results match given secret?

here example. have users table , has encrypted secret attribute. table has encrypted_secret, encrypted_secret_iv, , encrypted_secret_salt. if user gave secret of "abd123", how can query table see how many others have used "abc123" secret?

you save additional secret_hash of unencrypted secret. if 2 records have same secret have same secret_hash too.

add following 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, can query this:

yourmodel.by_secret('abd123').count 

---

warning

storing md5 hashes of passwords , other sensitive information security risk. if cannot tell plain text secret secret_hash, allows tell when users share same secret. or - worse - md5 hash might available in md5-reverse-lookup-dictionary.

you must trade off security issue against benefit of being able query on column.