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

WebTech gravatar 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.

<!-- 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.

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;

namespace ContoseEncryptionHarness
{
    class Program
    {
        /// <summary>
        /// Represents the encryption key for test purposes
        /// </summary>
        static readonly byte[] _key;

        /// <summary>
        /// Represents initialization vector for test purposes
        /// </summary>
        static readonly byte[] _vector;

        /// <summary>
        /// Static constructor. It sets the values of the privately scoped
        /// <see cref="_key"/> and <see cref="_vector"/> members to be 
        /// used by the data protection methods.
        /// </summary>
        static Program()
        {
            // This is by no means a suggestion of how to implement the
            // encryption key storage as it is, generally speaking, none
            // of anybody's business except the developer authoring the
            // data protection in accordance with company policies :)

            // this is what is usually stored somewhere safe and secure
            string password = "somewhatC0m1exPassw0rdO07*78_6dfg82+l3I|Lkwe4w";

            // different salt can be used if needed. For example, consider the
            // system which symmetrically encrypts web app user passwords and 
            // wants to ensure that even if 2 users have the same password, the
            // encrypted values of these passwords will be different.
            string salt = "someSaltValue";

            // Generate MD5 hash using the password and salt values
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(
                Encoding.ASCII.GetBytes(password), /* password bytes */
                Encoding.ASCII.GetBytes(salt), /* salt bytes */
                "MD5", /* MD5 is a good algorithm to use */
                3      /* 3 iterations should be plenty */);

            // Get the pseudo-random values for key and IV ensuring
            // that the key is 32 bytes and IV is 16 bytes in length
            _key = pdb.GetBytes(32);
            _vector = pdb.GetBytes(16);
        }

        /// <summary>
        /// Actual harness to test data protection methods
        /// </summary>
        /// <param name="args">string array of arguments; not used</param>
        static void Main(string[] args)
        {
            Console.WriteLine("Please enter some text to encrypt or " +
                "hit the return key to end. ");
            string plainText = Console.ReadLine();

            while (plainText != String.Empty)
            {
                try
                {
                    // encrypt some plain text. Use return value to store in 
                    // the database in the column of varbinary data type
                    byte[] encrypted = Encrypt(plainText);

                    // show on the screen how it looks in friendly base64 format
                    string encryptedText = Convert.ToBase64String(encrypted);
                    Console.WriteLine("Here is that string encrypted: " + encryptedText);

                    // decrypt encrypted data to test decryption method
                    string decryptedText = Decrypt(encrypted);

                    // show decrypted data on the screen
                    Console.WriteLine("Here is that string decrypted: " + decryptedText);
                    Console.WriteLine("");

                    Console.WriteLine("Please enter more text to encrypt " +
                        "or hit the return key to end. ");
                    plainText = Console.ReadLine();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error occured: " + ex.Message + 
                        " Hit any key to end.");
                    plainText = String.Empty;
                    Console.ReadLine();
                }
            }

        }

        #region Data Protection Methods

        /// <summary>
        /// Encrypts plain text passed into method as a parameter.
        /// </summary>
        /// <param name="plainText">Plain text to encrypt</param>
        /// <returns>Encrypted data in byte array format.</returns>
        static byte[] Encrypt(string plainText)
        {
            byte[] result = null;

            if (string.IsNullOrEmpty(plainText)) return result;

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

            // RijndaelManaged inherits Rijndael which in turn inherits
            // SymmetricAlgorithm, which implements IDisposable, so there
            // is no free lunch, using is a must here
            using (RijndaelManaged provider = new RijndaelManaged())
            {
                // Cipher Block Chaining mode adds an extra layer of security
                // It XORs IV and first block of plain text before encrypting it
                // and then uses the encrypted block as IV for next plain text 
                // block. This allows same blocks to be encrypted differently.
                provider.Mode = CipherMode.CBC;

                using (ICryptoTransform encryptor = provider.CreateEncryptor(_key, _vector))
                {
                    // define memory stream to store encrypted data
                    using (MemoryStream encryptedStream = new MemoryStream())
                    {
                        using (CryptoStream cryptoStream = new
                            CryptoStream(encryptedStream, encryptor, CryptoStreamMode.Write))
                        {
                            // encrypt the data and write it to the memory stream.
                            cryptoStream.Write(dataToEncrypt, 0, dataToEncrypt.Length);
                            cryptoStream.FlushFinalBlock(); // update the state and clear the buffer
                            cryptoStream.Close();

                        } // cryptoStream is disposed

                        result = encryptedStream.ToArray();
                        encryptedStream.Close();

                    } // encryptedStream is disposed

                } // encryptor is disposed

                provider.Clear();

            } // provider is disposed

            return result;
        }

        /// <summary>
        /// Decrypts previously encrypted data.
        /// </summary>
        /// <param name="encrypted">Encrypted data in byte array format</param>
        /// <returns>Decrypted data as plain text</returns>
        static string Decrypt(byte[] encrypted)
        {
            string result = string.Empty;

            if (encrypted == null || encrypted.Length == 0) return result;

            using (RijndaelManaged provider = new RijndaelManaged())
            {
                // Use the same mode to decrypt the data
                provider.Mode = CipherMode.CBC;

                using (ICryptoTransform decryptor = provider.CreateDecryptor(_key, _vector))
                {
                    // define memory stream to store decrypted data
                    using (MemoryStream decryptedStream = new MemoryStream())
                    {
                        using (CryptoStream cryptoStream = new
                            CryptoStream(decryptedStream, decryptor, CryptoStreamMode.Write))
                        {
                            // decrypt the data and write it to the memory stream.
                            cryptoStream.Write(encrypted, 0, encrypted.Length);
                            cryptoStream.FlushFinalBlock(); // update the state and clear the buffer
                            cryptoStream.Close();

                        } // cryptoStream is disposed

                        result = Encoding.UTF8.GetString(decryptedStream.ToArray());
                        decryptedStream.Close();

                    } // decryptedStream is disposed

                } // decryptor is disposed

                provider.Clear();

            } // provider is disposed

            return result;
        }

        #endregion
    }
}

End Edit -->

Just my 2 cents.

Oleg

more ▼

answered Jan 16, 2011 at 02:25 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(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

dvroman gravatar image

dvroman
1.1k 2 2

@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);
        }
    }
}
more ▼

answered Jan 31, 2011 at 10:17 PM

WebTech gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x38
x7

asked: Jan 16, 2011 at 01:21 PM

Seen: 6880 times

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