Encrypting and Decrypting Data to and from a Database

Introduction

You can easily encrypt data before storing it in a database and automatically decrypt it when retrieving, allowing it to be viewed in plain text when needed. This is particularly useful when storing passwords in the database, where for security reasons, that data should not be readable from the back-end database.

In this scenario, we build upon two earlier articles. The first is by Anh Trinh, published on January 4, 2007: Encrypting Passwords Before Saving to the Database. The second is by Jim Murphy, posted in the Iron Speed Designer Technical Forum on November 25, 2003: Password Database Encryption.

Both methods have specific uses. Mr. Trinh's article deals with encryption using a simple hash function, but has no method of reversing the function to view the password. This might be desirable in some instances, such as when the data has no requirement for future viewing. Mr. Murphy's article is more practical for data viewing.

This article blends the two techniques and provides a simple explanation for those of us new to Iron Speed Designer and Visual Basic .NET.

In our scenario, we look at how to encrypt a password before saving it to the database and provide a method of decrypting it for viewing or editing by authorized personnel. The example here could be extended for other fields in the database that require encryption.

The database structure for the password table can be created using the following SQL script, or by creating your own:

CREATE TABLE [dbo].[passwd] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [UserId] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Password] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Application] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Comment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RoleID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastChange] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Procedure

• Step 1: Create an Iron Speed Designer application based on the above password table.
• Step 2: Using Mr. Murphy's article as a guide, download and build the Kryptonite DLL from here in Visual Studio .NET 2003 as a separate project.
• Step 3: Copy the new DLL to your application's bin folder.

  ...\<App Folder>\Bin

• Step 4: Open the Iron Speed Designer encryption project in Visual Studio .NET 2003. Add a reference to the Kryptonite DLL just created in Step 1. Add a reference to .NET -> System.Security. Save the project, but do not build it!
• Step 5: Reopen the encryption project in Iron Speed Designer. Add the following to the bottom of Public Class passwdRecordControl, located in:

  .NET Framework 1.1:

  ...\<App Folder>\Shared\AddPasswordPage.Conrtols.vb

  .NET Framework 2.0 / 3.0:

  ...\<App Folder>\App_Code\Shared\AddPasswordPage.Conrtols.vb

  Visual Basic .NET:

  Public Overrides Sub GetUIData()
      ' Set the over ride
      MyBase.GetUIData()
      Dim oKryptonite As New Kryptonite.SymmCrypto
      (Kryptonite.SymmCrypto.Providers.TripleDES) 
      ‘ Declare the Encryption DLL
      Dim record As passwdRecord = Me.GetRecord
      Dim password As String = record.Password
      record.Password = oKryptonite.Encrypt(password)
  End Sub

• Step 6: Add the following code to the bottom of Public Class passwdRecordControl, located in:

  .NET Framework 1.1:

  ...\<App Folder>\Shared\EditPasswordPage.Controls.vb

  .NET Framework 2.0 / 3.0:

  ...\<App Folder>\App_Code\Shared\EditPasswordPage.Controls.vb

  This code retrieves the password from the database, decrypts it, and saves it back to the database in an encrypted form when edited.

  Visual Basic .NET:

  Public Overrides Sub GetUIData()
      MyBase.GetUIData()
      Dim oKryptonite As New _
          Kryptonite.SymmCrypto(Kryptonite.SymmCrypto.Providers.TripleDES)
      Dim record As passwdRecord = Me.GetRecord
      Dim password As String = record.Password
      record.Password = oKryptonite.Encrypt(password)
  End Sub
   
  Public Overrides Sub LoadData()
      ' Copy of the LoadData() with the Decryption added
      ' to reveal the current text password
   
          If Not Me.RecordUniqueId Is Nothing AndAlso _
                 Me.RecordUniqueId.Trim <> "" Then
              Me.DataSource = PasswdTable.GetRecord(Me.RecordUniqueId, True)
              Return
          End If
   
          Dim wc As WhereClause = Me.CreateWhereClause()
          If wc Is Nothing Then
              Me.DataSource = New PasswdRecord()
              Return
          End If
   
          ' Retrieve the record from the database.
          Dim recList() As PasswdRecord = PasswdTable.GetRecords(wc, Nothing, 0, 2)
          If recList.Length = 0 Then
              Throw New Exception(Page.GetResourceValue(
                  "Err:NoRecRetrieved", "mvhpass"))
          End If
   
          Me.DataSource = PasswdTable.GetRecord(recList(0).GetID.ToXmlString(), True)
   
      End Sub
   
      ' Populate the UI controls using the DataSource.
      ' To customize, override this method in passwdRecordControl.
      Public Overrides Sub DataBind()
   
          MyBase.DataBind()
   
          ' Make sure that the DataSource is initialized.
          If Me.DataSource Is Nothing Then
              Return
          End If
   
          ' For each field, check to see if a value
          ' is specified. If a value is specified,
          ' then format the value for display.
          ' If no value is specified, use the default value
          (formatted).
          ' Copied and added here to override the password field
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.Application0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.Application0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Application1.Text = formattedValue
          Else
   
              Me.Application1.Text = PasswdTable.Application0.Format
              (PasswdTable.Application0.DefaultValue)
          End If
   
          If Me.Application1.Text Is Nothing _
              OrElse Me.Application1.Text.Trim() = "" Then
              Me.Application1.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.CommentSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Comment)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Comment.Text = formattedValue
          Else
   
              Me.Comment.Text = PasswdTable.Comment.Format
              (PasswdTable.Comment.DefaultValue)
          End If
   
          If Me.Comment.Text Is Nothing _
              OrElse Me.Comment.Text.Trim() = "" Then
              Me.Comment.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.LastChangeSpecified Then
   
              Dim formattedValue As String = Me.DataSource.Format(
                  PasswdTable.LastChange)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.LastChange.Text = formattedValue
          Else
   
              Me.LastChange.Text = PasswdTable.LastChange.Format
              (PasswdTable.LastChange.DefaultValue)
          End If
   
          If Me.LastChange.Text Is Nothing _
              OrElse Me.LastChange.Text.Trim() = "" Then
              Me.LastChange.Text = ""
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.PasswordSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Password)
              'add decryption code here to change the formatted value
              Dim oKryptonite As New
              Kryptonite.SymmCrypto(Kryptonite.SymmCrypto.Providers.TripleDES)
              formattedValue= oKryptonite.Decrypt(formattedValue)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
   
   
              'end additions
              Me.Password.Text = formattedValue
          Else
   
              Me.Password.Text = PasswdTable.Password.Format
              (PasswdTable.Password.DefaultValue)
          End If
   
          If Me.Password.Text Is Nothing _
              OrElse Me.Password.Text.Trim() = "" Then
              Me.Password.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.UserId0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.UserId0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.UserId1.Text = formattedValue
          Else
   
              Me.UserId1.Text = _
                 PasswdTable.UserId0.Format(PasswdTable.UserId0.DefaultValue)
          End If
   
          If Me.UserId1.Text Is Nothing _
              OrElse Me.UserId1.Text.Trim() = "" Then
              Me.UserId1.Text = " "
          End If
   
          Me.IsNewRecord = True
          If Me.DataSource.IsCreated Then
              Me.IsNewRecord = False
   
              Me.RecordUniqueId = Me.DataSource.GetID.ToXmlString()
          End If
   
              ' Load data for each record and table UI control.
              ' Ordering is important because child controls get 
              ' their parent ids from their parent UI controls.
   
      End Sub

• Step 7: Add the following code to the ShowPasswordPage.Controls.vb file. This code provides for retrieving the password decrypted from the database for viewing by authorized users.

  Visual Basic .NET:

  Public Overrides Sub LoadData()
      ' Copy of the LoadData() with the Decryption
      ' added to reveal the current text password
   
   
          If Not Me.RecordUniqueId Is Nothing AndAlso _
                 Me.RecordUniqueId.Trim <> "" Then
              Me.DataSource = PasswdTable.GetRecord(Me.RecordUniqueId, True)
              Return
          End If
   
          Dim wc As WhereClause = Me.CreateWhereClause()
          If wc Is Nothing Then
              Me.DataSource = New PasswdRecord()
              Return
          End If
   
          ' Retrieve the record from the database.
          Dim recList() As PasswdRecord = PasswdTable.GetRecords(wc, Nothing, 0, 2)
          If recList.Length = 0 Then
              Throw New Exception(Page.GetResourceValue(
                  "Err:NoRecRetrieved", "mvhpass"))
          End If
   
   
          Me.DataSource = PasswdTable.GetRecord(recList(0).GetID.ToXmlString(), True)
   
      End Sub  
      ' Populate the UI controls using the DataSource.
      ' To customize, override this method in
      passwdRecordControl.
      Public Overrides Sub DataBind()
   
          MyBase.DataBind()
   
          ' Make sure that the DataSource is initialized.
          If Me.DataSource Is Nothing Then
              Return
          End If
   
   
          ' For each field, check to see if a value
          ' is specified. If a value is specified,
          ' then format the value for display. If no value
          ' is specified, use the default value (formatted).
          ' Copied and added here to override the password field
   
          If Me.DataSource.IsCreated OrElse _
             Me.DataSource.Application0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.Application0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Application1.Text = formattedValue
          Else
   
              Me.Application1.Text = PasswdTable.Application0.Format
              (PasswdTable.Application0.DefaultValue)
          End If
   
          If Me.Application1.Text Is Nothing _
              OrElse Me.Application1.Text.Trim() = "" Then
              Me.Application1.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.CommentSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Comment)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.Comment.Text = formattedValue
          Else
   
              Me.Comment.Text = PasswdTable.Comment.Format
              (PasswdTable.Comment.DefaultValue)
          End If
   
          If Me.Comment.Text Is Nothing _
              OrElse Me.Comment.Text.Trim() = "" Then
              Me.Comment.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.LastChangeSpecified Then
   
              Dim formattedValue As String = Me.DataSource.Format(
                  PasswdTable.LastChange)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.LastChange.Text = formattedValue
          Else
   
              Me.LastChange.Text = PasswdTable.LastChange.Format
              (PasswdTable.LastChange.DefaultValue)
          End If
   
          If Me.LastChange.Text Is Nothing _
              OrElse Me.LastChange.Text.Trim() = "" Then
              Me.LastChange.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.PasswordSpecified Then
   
              Dim formattedValue As String = 
                  Me.DataSource.Format(PasswdTable.Password)
              'add decryption code here to change the formatted value
              Dim oKryptonite As New ryptonite.SymmCrypto
              (Kryptonite.SymmCrypto.Providers.TripleDES)
              formattedValue= oKryptonite.Decrypt(formattedValue)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
   
   
              'end additions
              Me.Password.Text = formattedValue
          Else
   
              Me.Password.Text = PasswdTable.Password.Format
              (PasswdTable.Password.DefaultValue)
          End If
   
          If Me.Password.Text Is Nothing _
              OrElse Me.Password.Text.Trim() = "" Then
              Me.Password.Text = " "
          End If
   
          If Me.DataSource.IsCreated OrElse Me.DataSource.UserId0Specified Then
   
              Dim formattedValue As String = _
                  Me.DataSource.Format(PasswdTable.UserId0)
              formattedValue = HttpUtility.HtmlEncode(formattedValue)
              Me.UserId1.Text = formattedValue
          Else
   
              Me.UserId1.Text = _
                 PasswdTable.UserId0.Format(PasswdTable.UserId0.DefaultValue)
          End If
   
          If Me.UserId1.Text Is Nothing _
              OrElse Me.UserId1.Text.Trim() = "" Then
              Me.UserId1.Text = " "
          End If
   
          Me.IsNewRecord = True
          If Me.DataSource.IsCreated Then
              Me.IsNewRecord = False
   
              Me.RecordUniqueId = Me.DataSource.GetID.ToXmlString()
          End If
   
              ' Load data for each record and table UI control.
              ' Ordering is important because child controls get
              ' their parent ids from their parent UI controls.
   
      End Sub

• Step 8: Build and run the application.

  Notes: In general, if the code additions are placed in the respective code customization sections of each Controls.vb file, the project will provide encryption of the password field when adding or saving an edited record. The Edit page also uses the Show functions, so the code added to the Show functions is necessary in the Edit overrides.