Searching encrypted values

[betjaminrichards]

I have the following code:

user = FactoryGirl.create(:user)
returned_user = User.where(:encrypted_provider => User.encrypt_provider(user.provider), :encrypted_uid => User.encrypt_uid(user.uid)).first

But the returned user never returns anything.

When I deconstruct the command it seems like User.encrypt_provider(user.provider) is returning a different encrypted value to that stored in the DB, even though the two strings are the same.

What gives?

[illoyd]

The short answer is: not possible.

Values are encrypted using the IV and salt values, which are randomly generated on first encryption and typically saved to the database in the _iv and _salt fields. As you would expect, calling encrypt without the same IV and salt will yield a different answer. Give it a try and you'll see that calling User.encrypt( 'whatever' ) gives a very different answer than User.first.encrypted_whatever.

To do what you are asking you will need to load all User objects then loop through them to test the decrypted value. Or better yet, find your user on an unencrypted field such as an ID or username. Otherwise, you can try to create a deterministic hash of the search fields, such that you can be certain using the hash function over the same string yields the same hash. Of course that is defeating the purpose of using encryption in the first place!

-Ian

[betjaminrichards]

Thanks for the info, though the suggested solutions don't work from me.

Before creating a user I have to check to make sure they're not present first. I do this by checking against their facebook & uid ('provider' = facebook, 'uid' = 1000232323232 etc.). The problem I have is that the application must not store data in the DB that would allow an individual's ID to be discernable, so I have to encrypt their 'uid' attribute.

The application could have 100,000 users at start so loading them all every time an individual logs and checking the uid doesn't work for me.

Could such a feature be added, considering the IV and salt values are present in the DB?

[betjaminrichards]

Actually, creating a Bcrypt hash version of these values would be a solution wouldn't it? (sorry, cryptography isn't my strong point)

[illoyd]

If you are trying to do something with the 'Sign in with Facebook' service, FB should have documentation on requesting and storing the FB-provided authentication keys.

If you never need to know the original values then a strong deterministic uni-directional hash is probably your better option.

Anyway, check FB's documentation on the sign-in service as it probably explains best practices for storing the data.

-Ian