x

Proper way to store encrypted data

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.

Thanks,

Zach

more ▼

asked Jan 16, 2011 at 01:21 PM in Default

avatar image

WebTech
51 2 2 4

Oleg,

Thank you very much for your assistance on this. The encryption object I have created, is generating an encryption key and initialization vector and writing them to registry. Then, I can retrieve them from registry, on a named basis, to use for encrypting and decrypting data.

The code I am using to generate the encryption key and Initialization vector is as follows:

using (RijndaelManaged objRJ = new RijndaelManaged()) { objRJ.KeySize = 256; byte[] _enKey = objRJ.Key; byte[] _IV = objRJ.IV; objRJ.dispose; }

Is there a better way to perform the above task or is this the "best" way?

Jan 18, 2011 at 10:58 PM WebTech

@WebTech There is no good or bad way, it all depends on your design. The snippet you have in your comments surely does the trick. I suppose it is only needed to run once to initially gen a good key/IV and then use it ever since. This way you can pretty much guarantee that your key/IV will be as random and obscure as possible, which is a good thing.

I know that some places like to mimic the standard Microsoft implementation of the key storage implemented in any .NET config files with so-called protected sections (and in IIS with https):

  • Appropriate managed instance generates random key and IV (just like in your snippet)

  • The X509 cert is used to encrypt that key/IV with 1024 bit strong asymmetric RSA

  • When the app frst starts up, the same RSA is used to decrypt the key.

  • The key is then used to encrypt/decrypt the data

The beauty of this approach is that the key used to protect your data is itself heavily encrypted with the dog-slow but unbreakable asymmetric RSA while the algorithm used to protect data is symmetric, meaning that it is lightning fast yet strong.

To summarize: what you have is perfectly acceptable if touching the registry is not a concern.

Jan 19, 2011 at 08:27 AM Oleg
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

Just my 2 cents.

Oleg

more ▼

answered Jan 16, 2011 at 02:25 PM

avatar image

Oleg
17.1k 3 7 28

Do you have an example of the "native way" to encrypt data as you suggest? Is the following a valid way using the RijndaelManaged class?

public byte[] encrypt(string plainText)
{
if (plainText == null || plainText.Length <= 0) throw new ArgumentNullException("plainText");
if (Key == null || Key.Length <= 0) throw new ArgumentNullException("Key");
if (IV == null || IV.Length <= 0) throw new ArgumentNullException("IV");
MemoryStream msEncrypt = null;
RijndaelManaged aesAlg = null;
try
{
aesAlg = new RijndaelManaged();
aesAlg.Key = Key;
aesAlg.IV = IV;
ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);
msEncrypt = new MemoryStream();
using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
{
using (StreamWriter swEncrypt = new StreamWriter(csEncrypt))
{
swEncrypt.Write(plainText);
}
}
}
finally
{
if (aesAlg != null)
aesAlg.Clear();
}
return msEncrypt.ToArray();
}

Jan 16, 2011 at 03:37 PM WebTech

@WebTech Yes, this is valid, but I would definitely modify it to ensure that all disposable types are disposed. In your case, you leave both the instance of ICryptoTransform named encryptor and the instance of raw MemoryStream named msEncrypt not disposed simply relying on Clear method of your RijndaelManaged instance to take care of them. I don't believe that it does the trick though.

Another small change I would probably make is to ensure that the size of the Key is 32 bytes and the size of the IV is 16 bytes. Rijndael has some protection against so-called well known weak keys already built-in, but a small check to ensure that at least the sizes are correct would not hurt. I am terribly busy at the moment, but should be able to type a small sample as an addition to my answer later this afternoon. Again, I would like to point out that your design is perfectly valid and this is exactly how I would have it, i.e. front end protecting the data and storing it in the database in the original byte array format (varbinary(32) in your case).

Jan 17, 2011 at 09:32 AM Oleg
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 17, 2011 at 01:04 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 17, 2011 at 10:07 AM

avatar image

dvroman
1.1k 1 4 4

@dvroman, can you expand on that? It sounds like you could have done it a few different ways:

  • convert test to hex then encrypt and store it

  • encrypt the text then convert that to hexadecimal and store it

  • convert the text to hexadecimal then store it

  • something else?

Jan 17, 2011 at 10:51 AM KenJ

@KenJ I believe he simply means do nothing because this is exactly what one has to do in order to store encrypted data in what @dvroman calls a hexadecimal format. What I mean is this: normally, encryption algorithms expect a byte array as a parameter representing plain text and they return encrypted data in byte array format. If you use this data as is and feed it as a value to the sql parameter of varbinary type then the data stored in the database will be naturally independednt of any encoding settings. Some people just call the varbinary data hexadecimal simply because of how the results of a select statement look.

Jan 17, 2011 at 11:36 AM Oleg

Our stored encrypted data is all in hex. The data is pure 0-9 & a-f. Yes it doubles the storage requirement, but it makes the coding simple.

Jan 17, 2011 at 02:43 PM dvroman
(comments are locked)
10|1200 characters needed characters left

Oleg,

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.

Thanks,

Zach

public class clsEncryption { private byte[] _Key; private byte[] _IV;

     public clsEncryption()
     {

     }

     public clsEncryption(string KeyName)
     {
         try
         {
             using (RegistryKey rk = Registry.LocalMachine.OpenSubKey(@"Software\Wow6432Node\Encryption", true))
             {
                 if (rk.GetSubKeyNames().Contains(KeyName))
                 {
                     rk.OpenSubKey(@"Software\Wow6432Node\Key\" + KeyName, false);
                     _Key = (byte[])rk.GetValue("enKey");
                     _IV = (byte[])rk.GetValue("IV");
                 }
                 else
                 {
                     //if the Registry Key does not exist, return an error
                     throw (new Exception("Specified encryption key does not exist."));
                 }
             }
         }
         catch (Exception ex)
         {
             throw (new Exception(ex.Message));
         }     
     }

     public byte[] Encrypt(string plainText)
     {
         if (plainText == null || plainText.Length <= 0) throw new ArgumentNullException("plainText");
         if (_Key == null || _Key.Length <= 0) throw new ArgumentNullException("Key");
         if (_IV == null || _IV.Length <= 0) throw new ArgumentNullException("IV");

         byte[] result = null;
         byte[] dataToEncrypt = Encoding.UTF8.GetBytes(plainText);

         try
         {
             // Create a RijndaelManaged object with the specified key and IV.
             using (RijndaelManaged aesAlg = new RijndaelManaged())
             {
                 aesAlg.Mode = CipherMode.CBC;
                 aesAlg.Key = _Key;
                 aesAlg.IV = _IV;

                 // Create an encryptor to perform the stream transform.
                 using (ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV))
                 {
                     // Create the stream used for encryption.
                     using (MemoryStream msEncrypt = new MemoryStream())
                     {
                         using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
                         {
                             csEncrypt.Write(dataToEncrypt, 0, dataToEncrypt.Length);    // encrypt the data and write it to the memory stream.
                             csEncrypt.FlushFinalBlock();                                // update the state and clear the buffer
                             csEncrypt.Close();
                         }
                         result = msEncrypt.ToArray();
                         msEncrypt.Close();
                     }
                 }
                 aesAlg.Clear();
             }
         }
         catch (Exception ex)
         {
             throw (new Exception(ex.Message));
         }
         // Return the encrypted bytes from the memory stream.
         return result;      
     }

     public string Decrypt(byte[] cipherText)
     {
         // Check arguments.
         if (cipherText == null || cipherText.Length <= 0) throw new ArgumentNullException("cipherText");
         if (_Key == null || _Key.Length <= 0) throw new ArgumentNullException("Key");
         if (_IV == null || _IV.Length <= 0) throw new ArgumentNullException("IV");

         string result = string.Empty;

         try
         {
             // Create a RijndaelManaged object with the specified key and IV.
             using (RijndaelManaged aesAlg = new RijndaelManaged())
             {
                 aesAlg.Mode = CipherMode.CBC;
                 aesAlg.Key = _Key;
                 aesAlg.IV = _IV;
                 
                 // Create a decrytor to perform the stream transform.
                 using (ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV))
                 {
                     // Create the streams used for decryption.
                     using (MemoryStream msDecrypt = new MemoryStream(cipherText))
                     {
                         using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Write))
                         {
                             // decrypt the data and write it to the memory stream.
                             csDecrypt.Write(cipherText, 0, cipherText.Length);
                             csDecrypt.FlushFinalBlock(); // update the state and clear the buffer
                             csDecrypt.Close();
                         }
                         result = Encoding.UTF8.GetString(msDecrypt.ToArray());
                         msDecrypt.Close();
                     }
                 }
                 aesAlg.Clear();
             }
         }
         catch (Exception ex)
         {
             throw (new Exception(ex.Message));
         }
         //Return the decrypted string
         return result;
     }

     public void CreateNewEncryptionKey(string KeyName)
     {
         try
         {
             using (RegistryKey rk = Registry.LocalMachine.OpenSubKey("Software\\Wow6432Node\\Key", true))
             {
                 if (!rk.GetSubKeyNames().Contains(KeyName))
                 {
                     using (RijndaelManaged objRJ = new RijndaelManaged())
                     {
                         objRJ.KeySize = 256;
                         _Key = objRJ.Key;
                         _IV = objRJ.IV;

                         rk.CreateSubKey(KeyName);
                         rk.SetValue("enKey", _Key, RegistryValueKind.Binary);
                         rk.SetValue("IV", _IV, RegistryValueKind.Binary);
                         objRJ.Clear();
                     }
                 }
                 else
                 {
                     throw (new Exception("Specified encryption key already exist"));
                 }
                 rk.Dispose();
             }
         }
         catch (Exception ex)
         {
             throw new Exception(ex.Message);
         }
     }
 }
 </code>
more ▼

answered Jan 31, 2011 at 10:17 PM

avatar image

WebTech
51 2 2 4

@WebTech Another problem I see is that for some reason your key names don't match in your code. In the constructor of your clsEncryption class you have

OpenSubKey(@"Software\\Wow6432Node\\Encryption", true)

but then after ensuring that there is a subkey with the specified name under it you reopen at a different location @"Software\\Wow6432Node\\Key\\" + KeyName rather than opening it at location @"Software\\Wow6432Node\\Encryption\\" + KeyName This is difficult to understand why do you need to do it this way. On the other hand when you do need to create a brand new key with the different name using your public void CreateNewEncryptionKey method, you create it at @"Software\\Wow6432Node\\Key" + KeyName location.

This small problem aside, here is the source of your problem: you instantiate MemoryStream incorrectly. Replace the line reading

using (MemoryStream msDecrypt = new MemoryStream(cipherText))

with the line reading

using (MemoryStream msDecrypt = new MemoryStream())

Length of the cipher text is usually a bit longer than the length of the plain text, so you get

decrypted value plus rubbish which length = cipher.Length - plain.Length :)

Feb 02, 2011 at 03:18 PM Oleg

Congrats on passing 8k.

Feb 03, 2011 at 05:55 PM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x54
x9

asked: Jan 16, 2011 at 01:21 PM

Seen: 8822 times

Last Updated: Jan 16, 2011 at 01:21 PM

Copyright 2016 Redgate Software. Privacy Policy