Encrypting Column Data

[RudyM]

Hi all,

I will be building a web application that does a personnel data lookup, unfortunately using SSN. So I’ve been searching for how encrypt this data so that I do not use plaintext SSN in the application to accomplish this.

I found this article: http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx .

Does this mean that I can use the encrypted key value as a lookup in the table? I will need something for my web application to use to query for data. And, as I explained, the SSN is the key used to fetch from the db.

[ryantroop]

It really depends on how you plan on consuming and using the data. Why do you need encryption over a hash? Will you be applying the SSN for any given application or is it simply an identifier? If it's only an identifier, then hash it and store the hash. That way, when the user signs in with the SSN, you are doing a key lookup on the hash, and then you never have to worry about keeping the SSN anywhere near readable or deconstructable.

If you are going to use the SSN for anything other than identification, then you will likely have to have some encryption method on your server, saving the encrypted hash and key in the database, and then have a decryption method that consumes both to retrieve that data (so your database still only stores a hash and a "public" key).

[RudyM]

I initially thought about hashing also, but when I looked into MD5, many objected to it because it can 'easily' be broken.

[ryantroop]

So don't use MD5, use SHA-1 or SHA-256 or any other variant... what platform are you doing this on? In PHP, for example, you have this to work with:

http://php.net/manual/en/function.hash-algos.php

Do your research on what is best for you. I believe current industry standard is still SHA-1, but there is no reason not to go with SHA-256 or SHA-512. (Also, I believe SHA-1 is deprecated, and CERT providers and most other hash mechanisms are moving to SHA-256 or the SHA-2 family in general).

You can also reference this (originally for passwords, but gets the point across)
http://security.stackexchange.com/questions/56397/which-hashing-algorithm-is-ideal-for-use-on-the-web

You should also understand why MD5 is no longer viable as a hashing mechanism for "secure" data. It's not so much that it's easily "cracked" as much as it's easily brute forced due to the limited possible outputs from the MD5 hashing mechanism. The problem is not that you can reverse the process, it's that you can generate enough guesses to find a matching output and exploit those matches.

[RudyM]

Thanks ryan,

I will be developing this in PHP, using CodeIgniter MVC and a MSSQL db. I don't plan to have input of SSN; basically a hash value would be entered and the application would query the DB to get records based on this key. I just don't want someone to see the hashed key and try to crack it to reveal the SSN.

I've considered creating a new mapping with an IDENTITY column, but since this is personnel data that will be changing often, its maintenance will be quite a task. But I would ignore any SSN data.