My Account
Search for
Home Products Support Company Contact Buy

Encrypting and Decrypting Data to and from a Database

"Automatically decrypt data when retrieving it from a database."
- Mike Van Hoff, System Architect, Beverly Enterprises, Inc.

February 12, 2007
Iron Speed Designer V4.X

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 in January 4, 2007: Encrypting Passwords Before Saving to the Database, and the second 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 providing 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 http://www.rcsdev.com/files/Kryptonite.zip 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 the 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 the 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.

About the Author
Mike Van Hoff
MCSE, CCNA, CCDA, CIEH, CISSP

Mike is an Application Systems Architect for a major Healthcare Provider based in Fort Smith Arkansas. Mike is also a Sr. Unix Administrator with focus on AIX and SUSE Linux as well as Web Master for his company’s external Websites. He enjoys making things work securely.

Contact the author.



       Copyright © 2005-2012, Iron Speed, Inc. Iron Speed® is a registered trademark. All rights reserved. Privacy Statement