Searching encrypted data in SQL server 2005, HELP!!!
-
I follow the idea of this link and link to search for encrypted data using hash. But the problem is i can only fast search the exactly match data.
For example:if i have a table like this
name score
Laura 60
Smith 50
Peter 90
After i encrypt the score column using symmetric key and add a new column encrypt by hash and index that hash table. I can fast search the exactly value 60 or 90 but the problem is how can i fast search the name with score>50 in the encrypted column score? :pinch:
Please help me solve this problem 🙁 :pinch:
-
someone helps me, please :crying:
-
any field that needs to be searched should not be hashed; otherwise you are right, you can only get an exact match of hashed values; hashes are not sequential so you cannot find the hash(61) > has(60) for example;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank for your reply, but i know that already and my supervisor required me to find a way solving that problem 🙁
-
sangerex (12/17/2010)
Thank for your reply, but i know that already and my supervisor required me to find a way solving that problem 🙁yep caught between the proverbial rock and a hard place; pointy haired boss says use encryption for everything, and then requires the ability to search encrypted values.
they won't listen when you tell them it's one or the other, and not both.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have to agree with Lowell, you are between the proverbial rock and a hard place.
Being a fella who doesn't generally believe in always or never it pains me to say this but there really isn't a resolution without OBSCENE performance implications, to the point that even that resolution would be considered NOT A RESOLUTION.
The purpose of encryption is to make such comparisons impossible, if you could ascertain from the hash some relation to another record I would have to question the encryption algorithms security.
The simply fact is that you can only do EXACT matches unless you keep an unencrypted column.
You might be able to use DECRYPTBYKEY in a where clause but if you had any real quantity of records, this would be impracticle. But I'm still not sure how SQL would handle a < > scenario, it would have to decrypt EVERY record, can you say server on its knees crying for mercy?
It sucks, I know, I've been there, but you are going to have to tell your supervisor it isn't going to happen without an unencrypted column, and even HIPAA doesn't require ALL information be encrypted. He can complain all he wants but just because he wants it doesn't make it happen..
CEWII
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply