Encrypting Sensitive Data in a Database, Part 2By Scott Mitchell
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.
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
Customerstable might have the following schema:
|CustomerID||int, 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:
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
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
Customers table (ideally you should use a stored
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.
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.