I am interested in knowing the proper way to store encrypted data. In your opinion, it it best to store encrypted data (ie. Credit Card) as binary data (Varbinary) or should it be encrypted and converted from binary to a String and stored as a VarChar value?
FYI... I am using the .net Managed Rijndael class to encrypt data.
asked Jan 16 '11 at 01:21 PM in Default
From what you describe, it appears that you don't use the encryption options available to SQL Server but opt to use the front end code to manage protection of your data. This is a perfectly acceptable design.
The size of plain data subjected to protection in your case ranges from 13 to 19 bytes theoretically, and probably 13 to 16 bytes in reality. Older VISA cards can have 13 digits, American Express - 15 and others - 16. There are no cards with 14 digits. Since the default cipher block size of Rijndael algorithm is 16 bytes, so it needs 32 bytes key and 16 bytes initialization vector to provide you a maximum strength of 256 bit, it means that some of your plain text data will become 16 bytes and some - 32 bytes in length. This also means that even if you configure the algorithm to operate in Cipher Block Chaining mode as you should, it will be used only once or twice depending on the data. For example, if you need to encrypt American Express (or older VISA) numbers then encrypted data will be 16 bytes in length (just one block) and if you need to encrypt Master Card then the size of the encrypted data will be 32 bytes (2 blocks).
The native way to encrypt the data is to call Encoding.ASCII.GetBytes(plainText) and then feed it to the encryption method which accepts a byte array and returns a byte array. This array can then be stored in the database as varbinary(32) so you will have data length of 16 and 32 bytes in your table. If you need to store encrypted data as varchar then you will have some size overhead because you will have to call Convert.ToBase64String(encryptedBytes) first before storing the data in the database, and the latter will increase the size from 16 to 24 and from 32 to 44 bytes respectively, so the data type of your column in this case should be varchar(44). In my opinion, this overhead is not justified and therefore, I would definitely opt to store the data in varbinary(32) rather than make an extra call to Convert and then allow the size of the stored data to increase without a good reason.
And finally, with outsourcing the data protection activities to the front end, you will have to remember that your data is as secure as the security of the key storage and your encryption is as strong as the strength of that key.
<!-- Begin Edit
Here is the sample of the front end code you requested earlier. This is pretty similar to your own snippet in the comments below, it just has some bells and whistles to make a complete console app, and appears to be leak free.
End Edit -->
Just my 2 cents.
Personally I like to take a 2 step approach to storing encrypted data.
The application is responsible for the encryption/decryption, and the database simply stores the data in whatever format comes out of the encryption process (usually text). The advantage of this is that anyone with access to the database can only see encrypted data and has no easy way of simply reading it, and the people with the decrypt knowledge (i.e app devs) don't have access to the database.I know it's not 100% secure, but it just adds another level to the security.
answered Jan 17 '11 at 01:04 AM
Kev Riley ♦♦
What we did is store all encrypted data in hexadecimal format. This removed any of the problems that can arise with characters and the coding. In that way it doesn't matter what the field type is.
answered Jan 17 '11 at 10:07 AM
Below is the code I am using for this encryption object. When I encrypt the data, and later decrypt it, the decrypted data is not the same as the original value that was passed into the Encrypt function. As an example, when I encrypt Password then Decrypt the encrypted string, the resulting value is Password�\ �O�:.
At this time, I have been unable to determine the exact issue and was hoping you could offer me a quick hand.
I apologize for not commenting this, but I am unable to fit this amount of text in a comment.
answered Jan 31 '11 at 10:17 PM