The Microsoft CNG APIs constitute a collection of more than a dozen APIs that handle all the aspects and supporting functions to calculate hash values and perform encryption and decryption meeting modern high demands and standards.
This series details one method to implement these features in applications supported by VBA (Visual Basic for Applications), for example Microsoft Access and Microsoft Excel.
The theory for and the clever mathematics behind hashing and encryption will not be touched; this is covered massively in books and articles published over a long period of time by dedicated experts. A search with Bing or Google will return a long list of resources to study.
The series has been split in five parts. This allows you to skip parts you are either familiar with or wish to implement later if at all.
The three first deal, as stated, with hashing and encrypting/decrypting based on the CNG API, while the forth explains how to combine these techniques with the little known feature of Access, storing binary data, as this in many cases will represent the optimal storage method for hashed or encrypted data. The last demonstrates how to save and verify passwords totally safe using these tools.
Hash values and encrypted data are, by nature, not readable, which allows for using storing methods not normally used for plain text. This is because a hashed value or an encrypted text has no code page or character set - it is "raw" binary data.
Most database engines - and that includes the JET/ACE engine of Microsoft Access - feature options for storing such data, often to hold large chunks of picture data or complete files. These data types are typically labelled:
In Microsoft Access, the limits are:
Note, that the expected or experienced usage of the Binary field type is so rare, that is has been excluded from the official documentation for Access specifications.
However, while these data types are optimal for storing hashed or encrypted data seen from the perspective of the database engine, other factors - like easy copy-paste and transport - may dictate to store these data as Base64 encoded text in normal Short Text or Long Text fields. Thus, both approaches will be discussed here.
But, first, the field may need to be appended an existing table, and DAO is the perfect choice when using Microsoft Access.
To append a field, suitable for storing either hashed values or encrypted data to an existing table, four functions have been created, one for each data type:
Here, the two for the text data types:
' Append a short text field to an existing table.
' Generic function.
' Returns True if the short text field exists or was created.
'
' 2022-04-01. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function AppendShortTextField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
Optional ByVal FieldSize As Long) _
As Boolean
Dim Success As Boolean
Success = AppendStorageField(Database, TableName, FieldName, dbText, FieldSize)
AppendShortTextField = Success
End Function
' Append a long text (Memo) field to an existing table.
' Generic function.
' Returns True if the long text field exists or was created.
'
' 2022-04-01. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function AppendLongTextField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String) _
As Boolean
Dim Success As Boolean
Success = AppendStorageField(Database, TableName, FieldName, dbMemo)
AppendLongTextField = Success
End Function
The two functions for the binary fields are similar and won't be listed here.
Usage is straight, for example:
Dim Success As Boolean
Success = AppendShortTextField(CurrentDb, "YourTable", "YourField", 44)
Don't forget, that hash values, using the same hashing algorithm, always have the same length which will fit a Short Text field, thus it may be preferable to specify the size of the field - as shown above.
The four functions are this simple, because they all call a common function, AppendStorageField, that validates the arguments and does the real work:
' Append to an existing table a field suitable for storing encrypted data.
' Generic function.
' Returns True if the storage field exists as specified or was created.
'
' The field type can be any of these:
'
' dbBinary Binary
' dbLongBinary Binary (BLOB - Binary Large OBjects)
' dbText Short Text
' dbMemo Long Text (Memo)
'
' The field size can be specified for Binary and Short Text only:
'
' Maximum value Binary: 510 (Default)
' Minimum value Binary: 1
' Maximum value Binary: 255 (Default)
' Minimum value text: 1
'
' 2022-03-17. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function AppendStorageField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
ByVal FieldType As DAO.DataTypeEnum, _
Optional ByVal FieldSize As Long) _
As Boolean
' Field size limits.
Const MaximumSizeBinary As Long = 510
Const MaximumSizeText As Long = 255
Const MinimumSize As Long = 1
Const DefaultSize As Long = 0
Dim Table As DAO.TableDef
Dim Field As DAO.Field
Dim Success As Boolean
' Validate field type.
Select Case FieldType
Case dbBinary, dbLongBinary, dbText, dbMemo
' OK.
Case Else
' Ignore other field types.
Exit Function
End Select
' Validate database and object names.
If Database Is Nothing Or TableName = "" Or FieldName = "" Then
' Nothing to do. Exit.
Exit Function
End If
' Validate table name.
For Each Table In Database.TableDefs
If Table.Name = TableName Then
Exit For
End If
Next
If Table Is Nothing Then
' Table name not found in this database.
Exit Function
End If
' Validate field name.
For Each Field In Table.Fields
If Field.Name = FieldName Then
Exit For
End If
Next
If Field Is Nothing Then
' Create the field.
Set Field = Table.CreateField(FieldName, FieldType)
If FieldSize = DefaultSize Then
Success = True
ElseIf FieldSize >= MinimumSize Then
' Set the size of the field for relevant field types.
Select Case FieldType
Case dbBinary
If FieldSize <= MaximumSizeBinary Then
Field.Size = FieldSize
Success = True
End If
Case dbText
If FieldSize <= MaximumSizeText Then
Field.Size = FieldSize
Success = True
End If
End Select
End If
If Success = True Then
Success = False
Table.Fields.Append Field
Success = True
End If
ElseIf Field.Type = FieldType Then
' The field exists.
If Field.Size = FieldSize Then
Success = True
ElseIf FieldSize = DefaultSize Then
Select Case Field.Type
Case dbBinary
Success = (Field.Size = MaximumSizeBinary)
Case dbText
Success = (Field.Size = MaximumSizeText)
End Select
End If
End If
Set Field = Nothing
Set Table = Nothing
AppendStorageField = Success
End Function
For other database engines, like SQL Server and MySQL, you will have to use the SQL ALTER TABLE ... syntax in a pass-through query. That will, however, not be discussed here.
Now, having a text or binary field created, it's time to read and write the data.
The scenarios may, of course, be many, but here we will imagine, that we have a table with records having unique IDs, and - having an ID - we wish to write/read data to/from this specific record.
First, handling of text data will be described. Next, storing binary data will be described.
Two functions are at hand for this operation: SaveTextField and ReadTextField.
The first will write to either a Short Text or a Long Text field and has the special feature, that it will only return success, if the passed text to be saved actually has been saved, including saving a Null for an empty string or, alternatively, an empty string if the field allows either of these options. If the field size is too small to accommodate the text, or the ID was not found, no success will be returned.
Apart from this, it performs a standard field update using DAO:
' Save, for an ID, a text value to a text field of a table in the specified database.
' If argument Text is an empty string, Null will be saved if possible.
' Returns True if success, False if not.
'
' 2022-04-02. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function SaveTextField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
ByVal Id As Long, _
ByVal Text As String) _
As Boolean
Dim Records As DAO.Recordset
Dim Sql As String
Dim Success As Boolean
' Wrap table and field names in brackets.
TableName = Replace("[{0}]", "{0}", TableName)
FieldName = Replace("[{0}]", "{0}", FieldName)
Sql = "Select " & FieldName & " From " & TableName & " Where Id = " & Id & ""
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset)
If Records.RecordCount = 1 Then
Select Case Records(FieldName).Type
Case dbText, dbMemo
If Nz(Records(FieldName).Value) = Text Then
' Nothing to update.
ElseIf Records(FieldName).Type = dbText And Len(Text) > Records(FieldName).Size Then
' Text is too long to save in this field.
Else
Records.Edit
If Text = "" Then
If Not Records(FieldName).Required Then
Records(FieldName).Value = Null
ElseIf Records(FieldName).AllowZeroLength Then
Records(FieldName).Value = ""
End If
Else
Records(FieldName).Value = Text
End If
Records.Update
End If
Success = (Nz(Records(FieldName).Value) = Text)
End Select
End If
Records.Close
SaveTextField = Success
End Function
A typical example shows how simple the function will be to implement:
' Save, for an ID, the AES encrypted value of the text passed to a text field.
' A key must be passed.
' If argument Text is empty, Null will be stored.
' Returns True if success.
' Returns False if no key is passed.
'
' 2022-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function SaveEncryptedBase64( _
ByVal Id As Long, _
ByVal Key As String, _
Optional ByVal Text As String) _
As Boolean
' Table and field names. Modify as needed.
Const DefaultTableName As String = "Library"
Const DefaultFieldName As String = "ContentBase64"
Dim EncryptedText As String
Dim Success As Boolean
EncryptedText = Encrypt(Text, Key)
Success = SaveTextField(CurrentDb, DefaultTableName, DefaultFieldName, Id, EncryptedText)
SaveEncryptedBase64 = Success
End Function
The second will read from either a Short Text or a Long Text field and has the special feature, that it will always return a string, though this will be empty if Null is saved or the ID was not found.
Apart from this, it performs a standard field reading using DAO:
' Read, for an ID, the text from a field of a table in the specified database.
' Returns the value as text if the ID is found.
' Returns an empty string, if the ID is not found.
'
' 2022-04-02. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ReadTextField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
ByVal Id As Long) _
As String
Dim Records As DAO.Recordset
Dim Sql As String
Dim Text As String
' Validate database and object names.
If Database Is Nothing Or TableName = "" Or FieldName = "" Then
' Nothing to do. Exit.
Exit Function
End If
' Wrap table and field names in brackets.
TableName = Replace("[{0}]", "{0}", TableName)
FieldName = Replace("[{0}]", "{0}", FieldName)
Sql = "Select " & FieldName & " From " & TableName & " Where Id = " & Id & ""
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbReadOnly)
If Records.RecordCount = 1 Then
Select Case Records(FieldName).Type
Case dbMemo, dbText
' Read data as is.
If Not IsNull(Records(FieldName).Value) Then
Text = Records(FieldName).Value
End If
End Select
Else
' Return an empty string.
End If
Records.Close
ReadTextField = Text
End Function
Again, a typical example shows how simple the function will be to implement:
' Read, for an ID, the encrypted text from a text field.
' Returns the decrypted text if the key is right.
' Returns an empty string, if the ID is not found, or the key is wrong.
'
' 2022-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ReadEncryptedBase64( _
ByVal Id As Long, _
ByVal Key As String) _
As String
' Table and field names. Modify as needed.
Const DefaultTableName As String = "Library"
Const DefaultFieldName As String = "ContentBase64"
Dim EncryptedText As String
Dim Text As String
EncryptedText = ReadTextField(CurrentDb, DefaultTableName, DefaultFieldName, Id)
Text = Decrypt(EncryptedText, (Key))
ReadEncryptedBase64 = Text
End Function
The two example functions are intended to serve as skeletons for top-level functions reading and writing Base64 encoded encrypted text (as described in Part 3 of this series) and should be easy to modify for any practical implementation.
Two functions are at hand for this operation: SaveBinaryField and ReadBinaryField.
The first will write to either a Binary or a Long Binary (OLE Object) field and has the special feature, that it will only return success, if the passed data to be saved actually has been saved, including saving a Null for an empty byte array or, alternatively, an empty array if the field allows either of these options. If the field size is too small to accommodate the data, or the ID was not found, no success will be returned.
Apart from this, it performs a standard field update using DAO:
' Save, for an ID, a byte array to a binary field of a table in the specified database.
' If argument Data() is an empty array, Null will be saved if possible.
' Returns True if success, False if not.
'
' 2022-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function SaveBinaryField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
ByVal Id As Long, _
ByRef Data() As Byte) _
As Boolean
Dim Records As DAO.Recordset
Dim Sql As String
Dim Skip As Boolean
Dim Success As Boolean
' Wrap table and field names in brackets.
TableName = Replace("[{0}]", "{0}", TableName)
FieldName = Replace("[{0}]", "{0}", FieldName)
Sql = "Select " & FieldName & " From " & TableName & " Where Id = " & Id & ""
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset)
If Records.RecordCount = 1 Then
Select Case Records(FieldName).Type
Case dbBinary, dbLongBinary
If IsNull(Records(FieldName).Value) And StrPtr(Data) = 0 Then
' Nothing to update.
Skip = True
ElseIf StrPtr(Data) = 0 Then
' Set field value to Null.
ElseIf Records(FieldName).Type = dbBinary And (UBound(Data) - LBound(Data) + 1 > Records(FieldName).Size) Then
' Array is too large to save in this field.
Skip = True
End If
If Not Skip Then
Records.Edit
If StrPtr(Data) = 0 Then
If Not Records(FieldName).Required Then
Records(FieldName).Value = Null
End If
Else
Records(FieldName).Value = Data
End If
Records.Update
End If
If IsNull(Records(FieldName).Value) And StrPtr(Data) = 0 Then
Success = True
ElseIf UBound(Records(FieldName).Value) - LBound(Records(FieldName).Value) = UBound(Data) - LBound(Data) Then
Success = True
End If
End Select
End If
Records.Close
SaveBinaryField = Success
End Function
A typical example shows how simple the function using the function EncryptData will be to implement:
' Save, for an ID, the AES encrypted value of the text passed to a binary field.
' A key must be passed.
' If argument Text is empty, Null will be stored.
' Returns True if success.
' Returns False if no key is passed.
'
' 2022-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function SaveEncryptedBinary( _
ByVal Id As Long, _
ByVal Key As String, _
Optional ByVal Text As String) _
As Boolean
' Table and field names. Modify as needed.
Const DefaultTableName As String = "Library"
Const DefaultFieldName As String = "ContentBinary"
Dim EncryptedData() As Byte
Dim Success As Boolean
If EncryptData((Text), (Key), EncryptedData) = True Then
Success = SaveBinaryField(CurrentDb, DefaultTableName, DefaultFieldName, Id, EncryptedData)
End If
SaveEncryptedBinary = Success
End Function
The second will read from either a Binary or a Long Binary (OLE Object) field and has the special feature, that it will always return an array, though this will be empty if Null is saved or the ID was not found.
The function is also capable of reading data from a text field as these data will be casted to a byte array. However, for the current topic, encryption of data, this option doesn't offer any advantages and will not be discussed further.
Apart from this, it performs a standard field reading using DAO:
' Read, for an ID, the (casted) byte array from a field of a table in the specified database.
' Returns the value as a byte array if the ID is found.
' Returns an empty byte array, if the ID is not found.
'
' 2022-04-02. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ReadBinaryField( _
ByVal Database As DAO.Database, _
ByVal TableName As String, _
ByVal FieldName As String, _
ByVal Id As Long) _
As Byte()
Dim Records As DAO.Recordset
Dim Sql As String
Dim Data() As Byte
' Validate database and object names.
If Database Is Nothing Or TableName = "" Or FieldName = "" Then
' Nothing to do. Exit.
Exit Function
End If
' Wrap table and field names in brackets.
TableName = Replace("[{0}]", "{0}", TableName)
FieldName = Replace("[{0}]", "{0}", FieldName)
Sql = "Select " & FieldName & " From " & TableName & " Where Id = " & Id & ""
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbReadOnly)
If Records.RecordCount = 1 Then
Select Case Records(FieldName).Type
Case dbBinary, dbLongBinary, dbMemo, dbText
' Read data as is.
If Not IsNull(Records(FieldName).Value) Then
Data = Records(FieldName).Value
End If
End Select
Else
' Return an empty array.
End If
Records.Close
ReadBinaryField = Data
End Function
Again, a typical example shows how simple the function will be to implement, here using the function DecryptData:
' Read, for an ID, the encrypted text from a stored hash value of the password.
' Returns a byte array if a hash value is found.
' Returns an empty byte array, if the ID is not found, or the password is empty.
'
' 2022-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ReadEncryptedBinary( _
ByVal Id As Long, _
ByVal Key As String) _
As String
' Table and field names. Modify as needed.
Const DefaultTableName As String = "Library"
Const DefaultFieldName As String = "ContentBinary"
Dim EncryptedData() As Byte
Dim DecryptedData() As Byte
Dim Text As String
EncryptedData = ReadBinaryField(CurrentDb, DefaultTableName, DefaultFieldName, Id)
If DecryptData(EncryptedData, (Key), DecryptedData) Then
' Return the decrypted text.
Text = DecryptedData
Else
' Return an empty string.
End If
ReadEncryptedBinary = Text
End Function
The two example functions are intended to serve as skeletons for top-level functions reading and writing binary encrypted data (as described in Part 3 of this series) and should be easy to modify for any practical implementation.
While the above functions are well suited for single-record reading and writing using VBA, they are less useful in queries where you also - when reading data - must prepare for Null values.
To meet this demand, four functions are provided for usage in queries:
They are all listed here in full without further comments, as the in-line comments for each function fully describe the validating of the arguments and the typical usage in a query.
' Encrypt and Base64 encode a text directly to a Short Text or Long Text table field
' using a passed key.
' Accepts Null values for both data and key.
'
' If success, the encrypted text is returned.
' If no data, no key, or wrong key, Null is returned.
'
' Typical usage (update query):
'
' Parameters
' TableNameId Long,
' [Text] LongText,
' [Key] LongText;
' Update
' TableName
' Set
' TableName.[Date] = Date(),
' TableName.Content = [Text],
' TableName.ContentBase64 = VEncryptBase64([Text],[Key])
' Where TableNameId = [Id];
'
' 2022-04-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function VEncryptBase64( _
ByVal Text As Variant, _
Key As Variant) _
As Variant
Dim EncryptedText As Variant
If Nz(Key, "") = "" Or Nz(Text, "") = "" Then
' Either no key or no data.
EncryptedText = Null
Else
' Return encrypted data as Base64 encoded text.
EncryptedText = Encrypt(Text, Key)
If EncryptedText = "" Then
' Encryption failed.
EncryptedText = Null
End If
End If
VEncryptBase64 = EncryptedText
End Function
' Encrypt a text directly to a Binary or Long Binary (BLOB) table field
' using a passed key.
' Accepts Null values for both text and key.
'
' If success, the encrypted data is returned.
' If no data, no key, or wrong key, Null is returned.
'
' Typical usage:
'
' Typical usage (update query):
'
' Parameters
' TableNameId Long,
' [Text] LongText,
' [Key] LongText;
' Update
' TableName
' Set
' TableName.[Date] = Date(),
' TableName.Content = [Text],
' TableName.ContentBinary = VEncryptBinary([Text],[Key])
' Where TableNameId = [Id];
'
' 2022-04-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function VEncryptBinary( _
ByVal Text As Variant, _
Key As Variant) _
As Variant
Dim EncryptedData() As Byte
Dim Data As Variant
If Nz(Key, "") = "" Or Nz(Text) = "" Then
' Either no key or no data.
Data = Null
ElseIf VarType(Key) <> vbString Or VarType(Text) <> vbString Then
' Key or Text is not text.
Data = Null
Else
' Arguments are valid. Attempt to encrypt the text.
If EncryptData((Text), (Key), EncryptedData) = True Then
' Success.
Data = EncryptedData
Else
' Decryption failed.
Data = Null
End If
End If
VEncryptBinary = Data
End Function
' Decrypt a Base64 encoded encrypted text directly from a Short Text or Long Text table field
' using a passed key.
' Accepts Null values for both data and key.
'
' If success, the decrypted text is returned.
' If no data, no key, or wrong key, Null is returned.
'
' Typical usage:
'
' Parameters [Key] Text ( 255 );
' Select *, VDecryptBase64([TextFieldName], [Key]) As Content
' From TableName
'
' 2022-04-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function VDecryptBase64( _
ByVal EncryptedText As Variant, _
Key As Variant) _
As Variant
Dim Text As Variant
If Nz(Key, "") = "" Or Nz(EncryptedText, "") = "" Then
' Either no key or no data.
Text = Null
Else
' Return decrypted data as text.
Text = Decrypt(EncryptedText, Key)
If Text = "" Then
' Decryption failed.
Text = Null
End If
End If
VDecryptBase64 = Text
End Function
' Decrypt an encrypted text directly from a Binary or Long Binary (BLOB) table field
' using a passed key.
' Accepts Null values for both data and key.
'
' If success, the decrypted text is returned.
' If no data, no key, or wrong key, Null is returned.
'
' Typical usage:
'
' Parameters [Key] Text ( 255 );
' Select *, VDecryptBinary([BinaryFieldName], [Key]) As Content
' From TableName
'
' 2022-04-07. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function VDecryptBinary( _
ByVal EncryptedData As Variant, _
Key As Variant) _
As Variant
Dim Data() As Byte
Dim Text As Variant
If Nz(Key, "") = "" Or IsNull(EncryptedData) Then
' Either no key or no data.
Text = Null
ElseIf VarType(Key) <> vbString Or VarType(EncryptedData) <> vbString Then
' Key is not text, or - as a binary field will be read as text - ,
' EncryptedData holds invalid data.
Text = Null
Else
' Arguments are valid. Attempt to decrypt the data.
DecryptData (EncryptedData), (Key), Data
If StrPtr(Data) = 0 Then
' Decryption failed.
Text = Null
Else
' Return decrypted data as text.
Text = CStr(Data)
End If
End If
VDecryptBinary = Text
End Function
Three queries are included in the demo application:
The two first are queries where you need to pass data as parameters:
PARAMETERS
LibraryId Long,
[Text] LongText,
[Key] LongText;
UPDATE
Library
SET
Library.[Date] = Date(),
Library.ContentBase64 = VEncryptBase64([Text],[Key]),
Library.Content = [Text]
WHERE
LibraryId=[Id];
PARAMETERS
LibraryId Long,
[Text] LongText,
[Key] LongText;
UPDATE
Library
SET
Library.[Date] = Date(),
Library.ContentBinary = VEncryptBinary([Text],[Key]),
Library.Content = [Text]
WHERE
LibraryId=[Id];
The third lists the sample entries:
PARAMETERS
[Key] LongText;
SELECT
Library.Id,
Library.Date,
VDecryptBase64([ContentBase64],[Key]) AS Base64Content,
VDecryptBinary([ContentBinary],[Key]) AS BinaryContent
FROM
Library;
Note that, when you run this query, the content fields are blank if you enter the wrong or no key.
For all queries, while working with the demo, use a single space as key.
A full set of functions for reading and writing encrypted data as Base64 encoded text or as binary data has been presented. The functions cover all common needs for storing hash values or encrypted data in typical applications written in VBA.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)