When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Book Reviews
Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
Web Hosts
Author an Article

Print this Page!
Published: Wednesday, August 17, 2005

Encrypting Sensitive Data in a Database, Part 2

By Scott Mitchell

  • Part 1

  • In Part 1 we looked at an overview of encrypting the contents of a database as well as a look at potential encryption algorithms that could be used, settling on RC4. In this second and final part we'll create the necessary database schema and write the code to perform the encryption and decryption.

    Creating the Customers Database Table

    As discussed in Part 1, when storing encrypted data in the database we'll use a field type of varbinary, whereas the other, non-encrypted fields will maintain their normal data types. Therefore, the Customers table might have the following schema:

    CustomerIDint, PK, IDENTITY(1,1)

    You might optionally want to add a Salt field of type varchar that will hold a unique salt for each customer. Or you could use the CustomerID as the salt, assuming that an existing customer's CustomerID field won't ever change.

    Inserting and Updating Encrypted Data

    Since only the payment-related fields of the customer table are encrypted, typically you won't need to sweat encrypting the data before inserting or updating customer information. The only time you'll need to incur this additional overhead is when inserting or updating a customer's payment information.

    The code to accomplish this is fairly straightforward:

    'Start by creating an instance of the RC4Encrypt class
    Dim rc4 As New main.rc4encrypt 
    'Set the secret key and data to encrypt
    rc4.Password = YourSecretKey & customerID
    rc4.PlainText = Data To Encrypt
    'Get the encrypted results back as a string
    Dim encString As String = rc4.EnDeCrypt()
    'Convert the string into a byte array
    Dim encData() as Byte = System.Text.Encoding.UTF8.GetBytes(encString)
    'Update the database
    Dim sql = "UPDATE Customers SET CreditCardNumber = @CCN " & _
              "WHERE CustomerID = @CustID"
    myCommand.Parameters.Add("@CustID", customerID)
    myCommand.Parameters.Add("@CCN", encData)

    This code snippet leaves out a lot of the minute detail, but hopefully hammers home the key points. To do the encryption I'm using the main.rc4encrypt class from Converting Mike Shaffer's VBScript RC4 Encryption Code to C#. I start by setting the class's Password and PlainText properties to the secret key and data to encrypt, respectively. The secret key used here should include some base string along with the customer's CustomerID (or whatever you decided to use to salt the key). Next, the actual data is encrypted, returning a string that contains the encrypted results. Following that, the string is converted into a byte array and an ad-hoc SQL statement is used to update the appropriate row in the Customers table (ideally you should use a stored procedure).

    Decrypting and Working With the Data in Your ASP.NET Application

    The previous code snippet illustrated how to encrypt the data before storing it in the database. When we need to work with existing payment information we'll need to first retrieve the encrypted database data and then decrypt it in our ASP.NET application, reversing the steps examined shortly ago.

    'Read in the encrypted database data
    Dim sql = "SELECT CreditCardNumber FROM Customers WHERE CustomerID = @CustID"
    myCommand.Parameters.Add("@CustID", customerID)
    Dim reader as SqlDataReader = myCommand.ExecuteReader()
    If reader.Read() Then
      'Ok, let's decrypt this data!  First, read the data into a byte array
      Dim encData() as Byte = reader.GetSqlBinary(0).Value
      'Convert byte array into string
      Dim encString as String = System.Text.Encoding.UTF8.GetString(encData)
      'Creating an instance of the RC4Encrypt class
      Dim rc4 As New main.rc4encrypt 
      'Set the secret key and data to encrypt
      rc4.Password = YourSecretKey & customerID
      rc4.PlainText = encString
      'Get the decrypted results back as a string
      Dim CCN As String = rc4.EnDeCrypt()
      'Work with the data!
    End If

    Here we repeat the same steps for encryption, but in the opposite order. We start by reading in the encrypted value from the database, which has been stored as a byte array. This byte array is then transformed into a string. This encrypted string is plugged into the main.rc4encrypt class along with the same secret key / salt values used for encrypting. Finally, a call to EnDeCrypt() decrypts the value, returning the plain-text value as a string. From there we can work with the decrypted data as needed.

    Encryption: Is It Worth It?

    In the Winter 2005 issue of TechNet Magazine, the SQL Questions & Answers section had a small bit about encrypting data. A reader wrote in with a question about encrypting table data in SQL Server 2000, to which the editor (Nancy Michell), responded:
    "Database administrators sometimes encrypt the data inside a SQL Server database. Usually this is the wrong path to take. If you build a secure box, audit it, and protect access with tight access control, there is really no point in encrypting the data itself. This creates many issues including overhead, sorting, stored procedures, and more."
    So is it worth the overhead and investment in coding time, debugging time, and effort to encrypt the database's contents? It depends on your application's requirements, the sensitivity of the data, and your client's/boss's needs. Encrypting the data does add another layer of protection to your system, even if it does have apparent weaknesses. (For example, with symmetric encryption the encryption is useless if the hacker can get his hands on the secret key used; and guess how useful that data is if you somehow lose the secret key.) Encrypting the contents can also introduce performance overheads and query headaches. For example, imagine you wanted to write a query that returned all customers whose credit card expired within three months. With encrypting the credit card information and using a unique secret key for each customer, you couldn't do that. (Instead, you'd have to read in all of the records into your application, decrypt the credit card expiration, and then filter on those that expire within three months.)

    Additionally, as Nancy points out, you can't work with the encrypted data in your T-SQL statements. With unencrypted data, you can easily examine, edit, and make run-time decisions in stored procedures. Not so with encrypted data, since the actual encryption and decryption happens up at the application layer. (Of course, the data that you encrypt might not need to be used in T-SQL statements or queried upon in reports that encompass many users, in which case many of these disadvantages become moot.)

    Regardless of these issues, your client/boss may be adamant about adding this additional layer of security to the system. That's fine, just be sure to educate them that no security approach is impenetrable, and alert them to the potential downsides.


    In this article we examined a way to further harden your especially sensitive data - by actually storing encrypted data. Specifically, this article looked at using RC4, a symmetric key encryption algorithm, to encrypt and decrypt the data from the ASP.NET application. Therefore, if a hacker compromised the database server, the meaty data would be protected. Of course, no security approach is bullet proof; the aim is to simply make it as hard as possible for a hacker to get his hands on your sensitive information. Encrypting the contents of extemely sensitive database information (such as financial data, social-security numbers, and so on) is one such hardening step.

    Happy Programming!

  • By Scott Mitchell

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article