console - Ruby Rails query selection criteria includes encrypted field -

i using rails 2.3.4

i looking less specific answer question, more general guidance in matter.

in our (mysql) database, encrypt identifiable information (pii), such name, address, phone number, etc. requests dynamic queries time.

for example, "can query db me entry values given date, users in list of area codes".

so, selection criteria include of pii encrypted data, such area code of phone number.

for discussion, user , entry tables have following fields of interest:

user.id,user.phone

entry.user_id,entry.value,entry.created_at

because of encryption, can't connect sql client , query "easy" way entries on 11/21/2010 users in list of area codes (555,222,333), this:

select e.value,substr(u.phone,2,3) users u inner join entries e on e.user_id = u.id date(e.sent_at) = '2010-11-21' , substr(u.phone,2,3) in ('555','222','333')

i'd in console query might give me similar data.

it appears encrypted fields cannot used selection criteria, i'm guessing not decrypted until time display them (rather query them).

my brute force painful way following multi-step process:

1. select id's , area codes of active users. user.all(:select => "id, phone",:conditions => ["state='active'"])
2. do screen grab of terminal,
3. copy text editor, massage,
4. paste excel,
5. use excel "filter" grab user id's of desired area codes,
6. paste them flat file, massage comma-separated list, ,
7. paste sql client query user_id select * entries user_id in () , date(created_at) = ''.

"there's got better way!" suggestions? in advance,

if you're using activerecord, @ attr_encrypted gem. lot you.

the following sets encrypted attributes:

class user < activerecord::base   attr_encrypted :phone, :key => 'a secret key'   attr_encrypted :password, :key => 'some other secret key' end 

and can lookup , login users on encrypted values this:

user.find_by_email_and_password('test@example.com', 'testing') 

https://github.com/shuber/attr_encrypted