This content has been marked as final. Show 7 replies
2. Stored 2 versions: 1 hashed (so its searchable and scalable), 1 aes_encrypted, so its exportable.
3. Yes, the hash is searchable, that means that you can du a search like "is this SSN in the database" without any en/decyption other than a quick hash of the criteria.
4. Yes, store SSN numbers secure + offsite on given intervals. Nightly dumps?
Just ask, I'm happy to help.
Why would I want to store both the hash and encrypted version? Why wouldn't I just encrypt the SQL search parameter and then attempt to match the encrypted input to a stored encrypted value? Wouldn't two encrypted versions of the same string be the same if I used the same encryption algorithm?
You hash a value you know, then compare it to existing hashed values. But if you don't know the value to begin with, then you need a key. Thats where the aes/3des/blowfish encrypted version comes in.
I would store the hash, because IMHO it will be faster to do the lookup against the hash, the AES encrypted string should be stored base64 encoded as well, so it will be somewhat complex / expensive to do the lookup.
I think the AES version should only be used for archival purposes only.
Why not store the AES encrypted version as a UUencoded VARCHAR? If both the hash and AES strings are stored the same way, why would one string search be faster than another? The AES encrypted versions of the SSN's are 26 characters long. The HASH is 32 characters. It seems like searching the UUencoded AES string would be faster than the same Hashed version. Rather than opinion, do we have any actual "experience" to justify one way or the other? I don't only want to use encryption for backup purposes only, it’s also a second line of defense in case the database security is compromised. The AES encryption scheme should be an integral part of the standard data security model. Are there any readers with real-world experience who can interject?
On the hash issue...
If you are only searching for exact matches then hash buys you nothing.
Search would be something like "where #MyEncryptFunc(SSN)# = EncryptedDB_Column".
Just have an index on EncryptedDB_Column and performance is fine.
In fact, a hash can be risk because all hashes are now crackable and having both an encrypted and a hashed column decreases the time needed to crack either scheme.
If you need to do a range search for some reason, then you would build up an index of "partial-string hashes". You probably don't need this for SSN though.
Overall, the key things are:
- Never store keys in the payload database.
- Never have the keys in any code: SQL, CFM, etc.
- You need at least 3 systems, ideally on 3 separate machines:
(a) The payload database, (b) The web app code, (c) The secure key
- The Web app accepts/sends all data to/from browsers via SSL.
- Changing the password will require a full pass through the SSN
column to reencrypt but you must do this frequently anyway.
- You need a way to associate the correct password with a given
DB backup but do not store these together.
- Put audit mechanisms in place.
- Have at least two enemies overseeing each other for admin
duties. I like the old air force tactic of giving each a pistol
with orders to shoot at the first sign of a security breach.
Thanks MikerRoo, I came to the same conclusions about HASH security and searching.