Example of Encrypting and Decrypting a String into a SQL Table Using ASP.NET

I’ve put together this article because even after a lot of searching, I wasn’t able to find a simple, working example of how to encrypt and then decrypt a string that you are saving in your database in ASP.Net. Sure, there are some partial examples, but partial code usually doesn’t answer the question properly. Added to that, a lot of examples are out there that plain don’t work, confusing the issue and wasting a lot of time. So in this article I’ll give an example of a fully working test Web page that saves an encrypted string to a SQL Server database table, and that also shows the decrypted field back to the user.

Like I mentioned, this isn’t rocket science, but when I was looking for something to start with, I couldn’t find any example code that is clear, simple, and functional. And sadly, the official Microsoft documentation was not so great and even the Stackoverflow discussions didn’t help much.

Below I’ve posted the full code for a working example of saving an encrypted string to an imaginary database table that I made up. Disclaimer: I’m sure there are way more involved and secure ways to do this. The example I’m giving is intended as a starting point to get a feel for the most simple and straightforward way of encrypting some of your data. Also note that I wrote the example in VB.Net and used a WebForm (for no reason other than I like VB.Net). I ran the code through a simple translator to get the C# version, but haven’t tested it, so I can’t confirm it works as well as the VB version.

Overview of the Steps:

Ok, so for starters, in your Visual Studio project, create a new class to handle your Encryption/Decryption. I have pieced this class together directly from the Microsoft support site (https://msdn.microsoft.com/en-us/library/ms172831.aspx). Just like in their article, the class is called Simple3Des and (surprise) is meant to encode and decode strings being passed to it using 3DES. You can change this to AES if you like since there is also an inbuilt .NET method to handle this for you.

Note: As I mentioned earlier, I find the Microsoft support documentation a bit confusing, so below I’m showing you the full class code that I managed to get working. I also show how to use it to encrypt and save a string into your database, and then get it out again to show to your users.

The example encryption uses a stock Triple DES algorithm that should fit the bill for most needs, unless you are working with exceedingly critical data. But many electronic payment systems use Triple DES, so I’d classify it as reasonably secure standard to go with at this time, especially if you are just encrypting simple non-sensitive data. Go ahead and have a look at this Wikipedia page for more details on Triple DES: https://en.wikipedia.org/wiki/Triple_DES. Probably the least secure part of this example is the test password: ‘123’ being used.

Setting up the MS-SQL table to hold the Test Data

For starters, create the test table in your SQL Server instance. In this example the EncHat_Color field will hold our encrypted string data:
CREATE TABLE [dbo].[TEST_Encryption](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [varchar](10) NULL,
[FirstName] [varchar](50) NULL,
[EncHat_Color] [varchar](200) NULL,
)
This imaginary table will store the encrypted string values in a simple VARCHAR field to let us test saving and retrieving encrypted data.

The working VB.Net Example Class and WebForm

The VB.Net Encryption/Decryption Class

Now create your Encryption/Decryption class:
Imports Microsoft.VisualBasic
Imports System.Security.Cryptography
Public NotInheritable Class Simple3Des
    Private TripleDes As New TripleDESCryptoServiceProvider

    Sub New(ByVal key As String)
        ' Initialize the crypto provider.
        TripleDes.Key = TruncateHash(key, TripleDes.KeySize \ 8)
        TripleDes.IV = TruncateHash("", TripleDes.BlockSize \ 8)
    End Sub

    Private Function TruncateHash(
    ByVal key As String,
    ByVal length As Integer) As Byte()
        Dim sha1 As New SHA1CryptoServiceProvider
        ' Hash the key.
        Dim keyBytes() As Byte =
        System.Text.Encoding.Unicode.GetBytes(key)
        Dim hash() As Byte = sha1.ComputeHash(keyBytes)
        ' Truncate or pad the hash.
        ReDim Preserve hash(length - 1)
        Return hash
    End Function

    Public Function EncryptData(
    ByVal plaintext As String) As String
        ' Convert the plaintext string to a byte array.
        Dim plaintextBytes() As Byte = System.Text.Encoding.Unicode.GetBytes(plaintext)
        ' Create the stream.
        Dim ms As New System.IO.MemoryStream
        ' Create the encoder to write to the stream.
        Dim encStream As New CryptoStream(ms, TripleDes.CreateEncryptor(), System.Security.Cryptography.CryptoStreamMode.Write)
        ' Use the crypto stream to write the byte array to the stream.
        encStream.Write(plaintextBytes, 0, plaintextBytes.Length)
        encStream.FlushFinalBlock()
        ' Convert the encrypted stream to a printable string.
        Return Convert.ToBase64String(ms.ToArray)
    End Function
Public Function DecryptData(
    ByVal encryptedtext As String) As String
        ' Convert the encrypted text string to a byte array.
        Dim encryptedBytes() As Byte = Convert.FromBase64String(encryptedtext)
        ' Create the stream.
        Dim ms As New System.IO.MemoryStream
        ' Create the decoder to write to the stream.
        Dim decStream As New CryptoStream(ms,TripleDes.CreateDecryptor(),System.Security.Cryptography.CryptoStreamMode.Write)
        ' Use the crypto stream to write the byte array to the stream.
        decStream.Write(encryptedBytes, 0, encryptedBytes.Length)
        decStream.FlushFinalBlock()
        ' Convert the plaintext stream to a string.
        Return System.Text.Encoding.Unicode.GetString(ms.ToArray)
    End Function
End Class

The VB.NET Example Webform for Encrypting/Decrypting a Line of Text

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        If Not Page.IsPostBack Then
            popUserList()
        End If
    End Sub

    Private Sub popUserList()
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ConnectionString
        Using connection As New SqlConnection(strConn)
            Dim dtUserList As DataTable
            Dim strInlineQuery As String = "SELECT '0' Id, '' Userid, ' - NONE -' firstname UNION select Id, Userid, firstname from TEST_Encryption order by firstname"
            Dim objDataSet As New DataSet()
            Dim cmd As New SqlCommand()
            cmd.Connection = connection
            cmd.CommandText = strInlineQuery
            cmd.CommandTimeout = 30
            cmd.CommandType = CommandType.Text
            Using adapter As New SqlDataAdapter()
                adapter.SelectCommand = cmd
                adapter.Fill(objDataSet)
                dtUserList = objDataSet.Tables(0)
                ddlUsers.DataSource = dtUserList
                ddlUsers.DataValueField = "Id"
                ddlUsers.DataTextField = "firstname"
                ddlUsers.DataBind()
            End Using
        End Using
    End Sub

    Protected Sub btnSave_Click(sender As Object, e As EventArgs)
        Dim idSelected = ddlUsers.SelectedValue
        If idSelected = "0" Then
            ' do add
            addNewUser()
        Else
            ' do edit
            updateUser()
        End If
    End Sub

    Private Sub updateUser()
        Dim idSelected = ddlUsers.SelectedValue
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ConnectionString
        Dim wrapper As New Simple3Des("123")
        Dim cipherText As String = wrapper.EncryptData(tbHat_Color.Text)
        Using conn As New SqlConnection(strConn)
            Dim cmdCommand As SqlCommand = conn.CreateCommand()
            cmdCommand.CommandText = "UPDATE TEST_Encryption SET UserId=@UserId, FirstName=@FirstName, EncHat_Color=@EncHat_Color WHERE Id=@Id"
            cmdCommand.CommandType = CommandType.Text
            cmdCommand.Parameters.Add("@Id", SqlDbType.VarChar).Value = idSelected ' tbUserId.Text
            cmdCommand.Parameters.Add("@UserId", SqlDbType.VarChar).Value = tbUserId.Text
            cmdCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = tbUserFN.Text
            cmdCommand.Parameters.Add("@EncHat_Color", SqlDbType.VarChar).Value = cipherText
            cmdCommand.Connection.Open()
            cmdCommand.ExecuteNonQuery()
        End Using
        popUserList()
        tbUserId.Text = ""
        tbUserFN.Text = ""
        tbHat_Color.Text = ""
    End Sub

    Private Sub addNewUser()
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ConnectionString
        Dim wrapper As New Simple3Des("123")
        Dim cipherText As String = wrapper.EncryptData(tbHat_Color.Text)
        Using conn As New SqlConnection(strConn)
            Dim cmdCommand As SqlCommand = conn.CreateCommand()
            cmdCommand.CommandText = "INSERT INTO TEST_Encryption (UserId,FirstName,EncHat_Color) VALUES (@Id,@FN,@EncHat_Color)"
            cmdCommand.CommandType = CommandType.Text
            cmdCommand.Parameters.Add("@Id", SqlDbType.VarChar).Value = tbUserId.Text
            cmdCommand.Parameters.Add("@FN", SqlDbType.VarChar).Value = tbUserFN.Text
            cmdCommand.Parameters.Add("@EncHat_Color", SqlDbType.VarChar).Value = cipherText
            cmdCommand.Connection.Open()
            cmdCommand.ExecuteNonQuery()
        End Using
        popUserList()
        tbUserId.Text = ""
        tbUserFN.Text = ""
        tbHat_Color.Text = ""
    End Sub

    Protected Sub btnLoad_Click(sender As Object, e As EventArgs)
        Dim idSelected = ddlUsers.SelectedValue
        Dim wrapper As New Simple3Des("123")
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ConnectionString
        Using connection As New SqlConnection(strConn)
            Dim dtUserList As DataTable
            Dim strInlineQuery As String = "SELECT TOP 1 Id, Userid, firstname,EncHat_Color from TEST_Encryption WHERE Id=@Id order by firstname"
            Dim objDataSet As New DataSet()
            Dim cmd As New SqlCommand()
            cmd.Connection = connection
            cmd.CommandText = strInlineQuery
            cmd.CommandTimeout = 30
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = idSelected
            Using adapter As New SqlDataAdapter()
                adapter.SelectCommand = cmd
                adapter.Fill(objDataSet)
                dtUserList = objDataSet.Tables(0)
                For Each rowView As DataRowView In dtUserList.DefaultView()
                    Dim myRow As DataRow = rowView.Row
                    tbUserId.Text = myRow.Item("Userid")
                    tbUserFN.Text = myRow.Item("firstname")
                    Try
                        tbHat_Color.Text = wrapper.DecryptData(myRow.Item("EncHat_Color"))
                    Catch ex As Exception
                        tbHat_Color.Text = "Could not load Hat_Color"
                    End Try
                Next
            End Using
        End Using
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Simple Triple DES Encoding/Decoding example</title>
</head>
<body>
<form id="form1" runat="server">
        <asp:Label ID="lblUsers" runat="server" Text="Users:"></asp:Label> <asp:DropDownList ID="ddlUsers" runat="server"></asp:DropDownList>
        <asp:Button ID="btnLoad" runat="server" Text="Load" OnClick="btnLoad_Click" />

<hr />

        <asp:Label ID="Label1" runat="server" Text="User Id: "></asp:Label><asp:TextBox ID="tbUserId" runat="server"></asp:TextBox>
        
<asp:Label ID="Label2" runat="server" Text="User First Name: "></asp:Label><asp:TextBox ID="tbUserFN" runat="server"></asp:TextBox>
        
<asp:Label ID="Label3" runat="server" Text="Hat_Color: "></asp:Label><asp:TextBox ID="tbHat_Color" runat="server"></asp:TextBox>
        
        <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
    </form>

</body>
</html>

C# Version of the Code:

Ok, so here is the C# version of the VB.NET code above. Just a reminder, I wrote/tested mainly with the VB.NET version so I’m not 100% sure the C# version below runs without a tweak or two. I used the cool translator located at: http://www.carlosag.net/Tools/CodeTranslator/ to do the translation… I think it works very well!

The C# Encryption/Decryption Class

Here is the Encryption/Decryption Class to add to your Visual Studio Project:
using Microsoft.VisualBasic;
 using System.Security.Cryptography;
 public sealed class Simple3Des {
     
     private TripleDESCryptoServiceProvider TripleDes = new TripleDESCryptoServiceProvider();
     
     Simple3Des(string key) {
         //  Initialize the crypto provider.
         TripleDes.Key = this.TruncateHash(key, TripleDes.KeySize, 8);
         TripleDes.IV = this.TruncateHash("", TripleDes.BlockSize, 8);
     }
     
     private byte[] TruncateHash(string key, int length) {
         SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider();
         //  Hash the key.
         byte[] keyBytes = System.Text.Encoding.Unicode.GetBytes(key);
         byte[] hash = sha1.ComputeHash(keyBytes);
         //  Truncate or pad the hash.
         object Preserve;
         hash[(length - 1)];
         return hash;
     }
     
     public string EncryptData(string plaintext) {
         //  Convert the plaintext string to a byte array.
         byte[] plaintextBytes = System.Text.Encoding.Unicode.GetBytes(plaintext);
         //  Create the stream.
         System.IO.MemoryStream ms = new System.IO.MemoryStream();
         //  Create the encoder to write to the stream.
         CryptoStream encStream = new CryptoStream(ms, TripleDes.CreateEncryptor(), System.Security.Cryptography.CryptoStreamMode.Write);
         //  Use the crypto stream to write the byte array to the stream.
         encStream.Write(plaintextBytes, 0, plaintextBytes.Length);
         encStream.FlushFinalBlock();
         //  Convert the encrypted stream to a printable string.
         return Convert.ToBase64String(ms.ToArray);
     }
     
     public string DecryptData(string encryptedtext) {
         //  Convert the encrypted text string to a byte array.
         byte[] encryptedBytes = Convert.FromBase64String(encryptedtext);
         //  Create the stream.
         System.IO.MemoryStream ms = new System.IO.MemoryStream();
         //  Create the decoder to write to the stream.
         CryptoStream decStream = new CryptoStream(ms, TripleDes.CreateDecryptor(), System.Security.Cryptography.CryptoStreamMode.Write);
         //  Use the crypto stream to write the byte array to the stream.
         decStream.Write(encryptedBytes, 0, encryptedBytes.Length);
         decStream.FlushFinalBlock();
         //  Convert the plaintext stream to a string.
         return System.Text.Encoding.Unicode.GetString(ms.ToArray);
     }
 }

The C# Example Webform for Encrypting/Decrypting a Line of Text

And here is the C# WebForm to test Encrypting a user-provided line of text and then showing it back to the user as text that has been Decrypted from the database:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html>
<script runat='server'>
     
     protected void Page_Load(object sender, System.EventArgs e) {
         if (!Page.IsPostBack) {
             this.popUserList();
         }
     }
     
     private void popUserList() {
         string strConn = ConfigurationManager.ConnectionStrings("Conn").ConnectionString;
         Using;
         ((void)(connection));
         new SqlConnection(strConn);
         DataTable dtUserList;
         string strInlineQuery = "SELECT \'0\' Id, \'\' Userid, \' - NONE -\' firstname UNION select Id, Userid, firstname from TEST_Encryption order by firstname";
         DataSet objDataSet = new DataSet();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = connection;
         cmd.CommandText = strInlineQuery;
         cmd.CommandTimeout = 30;
         cmd.CommandType = CommandType.Text;
         Using;
         ((void)(adapter));
         new SqlDataAdapter();
         adapter.SelectCommand = cmd;
         adapter.Fill(objDataSet);
         dtUserList = objDataSet.Tables[0];
         ddlUsers.DataSource = dtUserList;
         ddlUsers.DataValueField = "Id";
         ddlUsers.DataTextField = "firstname";
         ddlUsers.DataBind();
     }
 }
 EndSubpopUserList();
tbUserId.Text = "";
tbUserFN.Text = "";
tbHat_Color.Text = "";
EndSubpopUserList();
tbUserId.Text = "";
tbUserFN.Text = "";
tbHat_Color.Text = "";
EndSubEndUsing;
EndSubUnknown
     
     protected void btnSave_Click(object sender, EventArgs e) {
         object idSelected = ddlUsers.SelectedValue;
         if ((idSelected == "0")) {
             //  do add
             addNewUser();
         }
         else {
             //  do edit
             updateUser();
         }
         
     }
     
     private void updateUser() {
         object idSelected = ddlUsers.SelectedValue;
         string strConn = ConfigurationManager.ConnectionStrings("Conn").ConnectionString;
         Simple3Des wrapper = new Simple3Des("123");
         string cipherText = wrapper.EncryptData(tbHat_Color.Text);
         Using;
         ((void)(conn));
         new SqlConnection(strConn);
         SqlCommand cmdCommand = conn.CreateCommand();
         cmdCommand.CommandText = "UPDATE TEST_Encryption SET UserId=@UserId, FirstName=@FirstName, EncHat_Color=@EncHat_Color WHERE Id=" +
         "@Id";
         cmdCommand.CommandType = CommandType.Text;
         cmdCommand.Parameters.Add("@Id", SqlDbType.VarChar).Value = idSelected;
         //  tbUserId.Text
         cmdCommand.Parameters.Add("@UserId", SqlDbType.VarChar).Value = tbUserId.Text;
         cmdCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = tbUserFN.Text;
         cmdCommand.Parameters.Add("@EncHat_Color", SqlDbType.VarChar).Value = cipherText;
         cmdCommand.Connection.Open();
         cmdCommand.ExecuteNonQuery();
     }
     
     private void addNewUser() {
         string strConn = ConfigurationManager.ConnectionStrings("Conn").ConnectionString;
         Simple3Des wrapper = new Simple3Des("123");
         string cipherText = wrapper.EncryptData(tbHat_Color.Text);
         Using;
         ((void)(conn));
         new SqlConnection(strConn);
         SqlCommand cmdCommand = conn.CreateCommand();
         cmdCommand.CommandText = "INSERT INTO TEST_Encryption (UserId,FirstName,EncHat_Color) VALUES (@Id,@FN,@EncHat_Color)";
         cmdCommand.CommandType = CommandType.Text;
         cmdCommand.Parameters.Add("@Id", SqlDbType.VarChar).Value = tbUserId.Text;
         cmdCommand.Parameters.Add("@FN", SqlDbType.VarChar).Value = tbUserFN.Text;
         cmdCommand.Parameters.Add("@EncHat_Color", SqlDbType.VarChar).Value = cipherText;
         cmdCommand.Connection.Open();
         cmdCommand.ExecuteNonQuery();
     }
     
     protected void btnLoad_Click(object sender, EventArgs e) {
         object idSelected = ddlUsers.SelectedValue;
         Simple3Des wrapper = new Simple3Des("123");
         string strConn = ConfigurationManager.ConnectionStrings("Conn").ConnectionString;
         Using;
         ((void)(connection));
         new SqlConnection(strConn);
         DataTable dtUserList;
         string strInlineQuery = "SELECT TOP 1 Id, Userid, firstname,EncHat_Color from TEST_Encryption WHERE Id=@Id order by firstname";
         DataSet objDataSet = new DataSet();
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = connection;
         cmd.CommandText = strInlineQuery;
         cmd.CommandTimeout = 30;
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = idSelected;
         Using;
         ((void)(adapter));
         new SqlDataAdapter();
         adapter.SelectCommand = cmd;
         adapter.Fill(objDataSet);
         dtUserList = objDataSet.Tables[0];
         foreach (DataRowView rowView in dtUserList.DefaultView()) {
             DataRow myRow = rowView.Row;
             tbUserId.Text = myRow.Item["Userid"];
             tbUserFN.Text = myRow.Item["firstname"];
             try {
                 tbHat_Color.Text = wrapper.DecryptData(myRow.Item["EncHat_Color"]);
             }
             catch (Exception ex) {
                 tbHat_Color.Text = "Could not load Hat_Color";
             }
             
         }
         
     }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
     <title>Simple Triple DES Encoding/Decoding example</title>
</head>
<body>
<form id="form1" runat="server">
         <asp:Label ID="lblUsers" runat="server" Text="Users:"></asp:Label> <asp:DropDownList ID="ddlUsers" runat="server"></asp:DropDownList>
         <asp:Button ID="btnLoad" runat="server" Text="Load" OnClick="btnLoad_Click" />

<hr />

         <asp:Label ID="Label1" runat="server" Text="User Id: "></asp:Label><asp:TextBox ID="tbUserId" runat="server"></asp:TextBox>
         
<asp:Label ID="Label2" runat="server" Text="User First Name: "></asp:Label><asp:TextBox ID="tbUserFN" runat="server"></asp:TextBox>
         
<asp:Label ID="Label3" runat="server" Text="Hat_Color: "></asp:Label><asp:TextBox ID="tbHat_Color" runat="server"></asp:TextBox>
         
         <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
     </form>

</body>
</html>

So that’s all that there is to it. Encrypting and Decrypting data from a VARCHAR field in your database table is pretty straightforward. If you have any questions or comments feel free to drop me a note in the comments section below.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s