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.
Encryption, in this context, is the process of creating a scrambled but reversible code that identifies the input. To encrypt the input, a key is used.
Note that, when encrypting, the generated code is unique, as different code can and will be generated from identical input/key pairs, thus the generated code will appear random.
However, the generated code is not random. By providing the key used for the encryption, the code can be reversed to recreate the original input.
The typical usage of encryption is for storing or transporting sensitive data in a format unreadable for anyone not having the key to decrypt the data. When storing or transporting the data, the encrypted data is used. To read the data, the key used to encrypt the data must be known. Having this, the encrypted data can be decrypted to rebuild the original plain data.
The mathematical method used to encrypt and decrypted a string is called an algorithm. These have evolved over time, and the oldest are now deprecated, though still in use.
The single algorithm offered here is AES (Advanced Encryption Standard) as it is fast and also considered the best. Also, it is very widely used.
The byte length generated by this algorithm has a minimum of 48 approaching twice the length of the input for large inputs. Some examples are:
|Input Length||Byte Length|
Thus, pay attention to the possible length of text to encrypt.
Note, that the lengths listed above for the AES encryption algorithm are the lengths of the generated byte arrays. As these bytes can take many other values than those for normal ASCII characters - even control characters and other non-printable characters - the byte array must for many practical purposes be converted to something printable.
The simple method would be to convert to the hexadecimal representation of the bytes, for example (for a snippet of four bytes of the data, where character <tab> is the third):
aX<tab>! -> 6158092D
but that consumes twice as many characters, thus an input string of only 100 characters encrypted using AES would have a length of 256 x 2 = 512 characters - not very efficient.
A better method is to use Base64 encoding. If offers the same - a printable and portable string of ASCII characters - but takes only 50% (or less) additional space compared to the byte lengths listed above:
|Input Length||Base64 Length|
Again, pay attention to the possible length of text to encrypt.
For example, the maximum length of a Short Text field in Access is 255, thus it can't hold the encrypted and Base64 encoded value of a text value of 68 characters; to use this field type, the plain text must be kept at only 67 characters or shorter.
To conclude: With an overhead of about 300%, the encrypted values can be treated as normal plain text.
A set of functions is used to encrypt the input and return the encrypted data:
Encrypt EncryptData HashData CngHash RandomData CngRandom CngEncrypt ByteBase64
The top function, Encrypt, the one that creates a Base64 encoded encrypted text value from a passed text using a specific key, contains less than a handful of code lines:
' Encrypt a string using AES and a key. ' Return the encrypted text as a Base64 encoded string. ' ' Example: ' Text = "Careful with that axe, Eugene!" ' Key = "Have a Cigar" ' EncryptedText = Encrypt(Text, Key) ' EncryptedText -> 6uLffExuQmAi/oI3AzCLZTRZfv1XL6kl01z4hJ5y1MWXHgFACj3XhvboF/rNU89znrX1d5btmCbRK9dAjjjlKxTDJMImQr3YGiscMDvn/YtjKmc8nFuR65IU9vEn4a0Rca72k55cZXjKzOGMpbZ/6A== ' ' Note: Length of the encrypted string can be predetermined by the function EncryptedTextLength: ' ' Use Text from example above. ' Length = EncryptedTextLength(Len(Text)) ' Length -> 152 ' ' Original code by Erik A, 2019. ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function Encrypt( _ ByVal Text As String, _ ByVal Key As String) _ As String Dim EncryptedData() As Byte Dim EncryptedText As String If Text = "" Or Key = "" Then ' Nothing to do. Else If EncryptData((Text), (Key), EncryptedData) = True Then ' Success. ' Convert the byte array to a Base64 encoded string. EncryptedText = ByteBase64(EncryptedData) Else ' Missing Text or Key. End If End If Encrypt = EncryptedText End Function
The usage is extremely simple:
Dim Text As String Dim Key As String Dim Value As String Text = "Get your filthy hands off my desert." Key = "Absolutely Curtains" Value = Encrypt(Text, Key) Debug.Print "Plain text:", Text Debug.Print "Encrypted:", Value ' Output: ' Plain text: Get your filthy hands off my desert. ' Encrypted: VrsKJl+J7giIvRGActl9BYl0sZLn3sliMF62vMjRsVev32ALx08EnrXmt57lWR+DvsDsPT3l0jwJvmBTSyeaeQBfw9TsqVIdanDQ2Cnx5QhyWndqSAJKz8DEatWHBH9277O2YI3cVmku/Pzb0R2o8srbc7dt9cBuxPQ4zQ+vLx8=
The few lines of code in the function is possible, because it pulls data from the second level function, EncryptData, also taking a second argument for the key.
The third argument, however, is where the encrypted data will be returned, while the function itself only returns a value, True/False, to tell, if the process was successful or not:
' Encrypt a byte array using AES encryption and a KeyData passed as another byte array. ' Return by reference the encrypted data as a byte array. ' Return True if success. ' ' To be called from function Encrypt. ' ' NOTE: ' Even when passed the same arguments (TextData and KeyData), the returned and ' encrypted data will be unique for every call. ' ' Original code by Erik A, 2019. ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function EncryptData( _ ByRef TextData() As Byte, _ ByRef KeyData() As Byte, _ ByRef EncryptedData() As Byte) _ As Boolean Const SizeLength As Long = 4 Const IVectorLength As Long = 16 Const SecretLength As Long = 16 Dim KeyHash() As Byte Dim InputHash() As Byte Dim Data() As Byte Dim DataLength As Long Dim IVector() As Byte Dim InputHashLength As Long Dim Result As Boolean ' Get SHA1 hash of the data and of the KeyData. InputHash = HashData(TextData, bcSha1) InputHashLength = UBound(InputHash) + 1 KeyHash = HashData(KeyData, bcSha1) ReDim Preserve KeyHash(0 To SecretLength) If StrPtr(InputHash) = 0 Or StrPtr(KeyHash) = 0 Then ' Either no data or no KeyData. Nothing to do. Else DataLength = UBound(TextData) - LBound(TextData) + 1 ' Data size is: Long (4 bytes) + DataLength + SHA1 (20 bytes) ReDim Data(0 To SizeLength + DataLength + InputHashLength - 1) ' Append length (in bytes) to start of array. RtlMoveMemory Data(0), DataLength, SizeLength ' Append data. RtlMoveMemory Data(SizeLength), TextData(LBound(TextData)), DataLength ' Append hash of the data. RtlMoveMemory Data(SizeLength + DataLength), InputHash(0), InputHashLength ' Generate IVector. IVector = RandomData(IVectorLength) ' Encrypt data. EncryptedData = CngEncrypt( _ VarPtr(Data(0)), SizeLength + DataLength + InputHashLength, _ VarPtr(IVector(0)), IVectorLength, _ VarPtr(KeyHash(0)), SecretLength) ' Deallocate copy made to encrypt. Erase Data ' Extend encrypted data to append IVector. ReDim Preserve EncryptedData(LBound(EncryptedData) To UBound(EncryptedData) + IVectorLength) ' Append IVector. RtlMoveMemory EncryptedData(UBound(EncryptedData) - LBound(EncryptedData) + 1 - IVectorLength), IVector(0), IVectorLength Result = True End If EncryptData = Result End Function
The important feature of the function is, that both input and output are byte arrays. However, you may have noticed, that function Encrypt above doesn't pass two byte arrays, but plain text for Text and Key:
EncryptData((Text), (Key), EncryptedData)
That's because you can assign a text variable directly to a byte array; no conversion is needed.
The output is, in function Encrypt, held in the byte array EncryptedData, which then is passed to the function ByteBase64, which converts the byte array to Base64 encoded text. Though quite convoluted, that function is trivial and won't be discussed here.
Function EncryptData also contains relatively few code lines. Essentially, it only prepares the input data and wraps the function, that does "the real work", CngEncrypt, which makes no less than nine calls to the CNG API to create the encrypted data.
The details of these calls will not be discussed here. However, each step is commented in-line, and links to the documentation at Microsoft are included, should you wish to study this further.
The usage of this function is also very straight:
Dim Text As String Dim Key As String Dim EncryptedData() As Byte Dim Success As Boolean Text = "Get your filthy hands off my desert." Key = "Absolutely Curtains" Success = EncryptData((Text), (Key), EncryptedData) If Success Then ' Convert byte array to unicode. Debug.Print StrConv(EncryptedData, vbUnicode) End If ' Partial output if success. ' Ém9dL‡ðeE¤æÂÒ^¾ý¿g-C2jÊO²t‰Éró 5ÈB<.ËD@UÙ½ <snip>
Part 4 of this series will demonstrate some applications for this function.
A set of functions is used to decrypt the input and return the decrypted data:
Decrypt Base64Bytes DecryptData HashData CngHash CngDecrypt CngHash
The top function, Decrypt, the one that reads a Base64 encoded encrypted text value from a passed text and decrypts it using a specific key, contains less than a handful of code lines:
' Decrypt a Base64 encoded string encrypted using AES and a key. ' Return the decrypted and decoded text as a plain string. ' ' Example: ' EncryptedText = "6uLffExuQmAi/oI3AzCLZTRZfv1XL6kl01z4hJ5y1MWXHgFACj3XhvboF/rNU89znrX1d5btmCbRK9dAjjjlKxTDJMImQr3YGiscMDvn/YtjKmc8nFuR65IU9vEn4a0Rca72k55cZXjKzOGMpbZ/6A==" ' Key = "Have a Cigar" ' Text = Decrypt(EncryptedText, Key) ' Text -> Careful with that axe, Eugene! ' ' Original code by Erik A, 2019. ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function Decrypt( _ ByVal EncryptedText As String, _ ByVal Key As String) _ As String Dim EncryptedData() As Byte Dim TextData() As Byte If EncryptedText = "" Or Key = "" Then ' Nothing to do. Else ' Convert the Base64 encoded string to a byte array. EncryptedData = Base64Bytes(EncryptedText) If DecryptData(EncryptedData, (Key), TextData) = True Then ' Success. Else ' Invalid EncryptedData or wrong key. End If End If Decrypt = TextData End Function
The usage is extremely simple:
Dim Text As String Dim Key As String Dim Value As String Text = _ "VrsKJl+J7giIvRGActl9BYl0sZLn3sliMF62vMjRsVev32ALx08EnrXmt57lWR" & _ "+DvsDsPT3l0jwJvmBTSyeaeQBfw9TsqVIdanDQ2Cnx5QhyWndqSAJKz8DEatWH" & _ "BH9277O2YI3cVmku/Pzb0R2o8srbc7dt9cBuxPQ4zQ+vLx8=" Key = "Absolutely Curtains" Value = Decrypt(Text, Key) Debug.Print "Encrypted:", Text Debug.Print "Plain text:", Value ' Output: ' Encrypted: VrsKJl+J7giIvRGActl9BYl0sZLn3sliMF62vMjRsVev32ALx08EnrXmt57lWR+DvsDsPT3l0jwJvmBTSyeaeQBfw9TsqVIdanDQ2Cnx5QhyWndqSAJKz8DEatWHBH9277O2YI3cVmku/Pzb0R2o8srbc7dt9cBuxPQ4zQ+vLx8= ' Plain text: Get your filthy hands off my desert.
As you may have noticed, two enums are used for specifying the encryption algorithm and the random algorithm:
' Allowed BCrypt random algorithms. Public Enum BcRandomAlgorithm [_First] = 1 bcRng = 1 bcFips186DsaRng = 2 [_Last] = 2 End Enum ' Utilised BCrypt encryption algorithms. Public Enum BcEncryptionAlgorithm [_First] = 1 bcAes = 1 [_Last] = 1 End Enum
This is to validate input and make it easy to always supply the rightly spelled and uppercased random and encryption algorithm names to function CngEncrypt.
Functions BcryptRandomAlgorithm and BcryptEncryptionAlgorithm serve this purpose:
' Return the literal random algorithm name determined by ' the passed value of enum BcRandomAlgorithm. ' ' To be called from functions CngRandom and IsBcryptRandomAlgorithm. ' ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function BcryptRandomAlgorithm( _ ByVal BcryptRandomAlgorithmId As BcRandomAlgorithm) _ As String Dim RandomAlgorithmName As String ' Note: RandomAlgorithmName must be in UPPERCASE. Select Case BcryptRandomAlgorithmId Case BcRandomAlgorithm.bcRng RandomAlgorithmName = "RNG" Case BcRandomAlgorithm.bcFips186DsaRng RandomAlgorithmName = "FIPS186DSARNG" End Select BcryptRandomAlgorithm = RandomAlgorithmName End Function
' Return the literal encryption algorithm name determined by ' the passed value of enum BcEncryptionAlgorithm. ' ' To be called from functions CngEncrypt and CngDecrypt. ' ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function BcryptEncryptionAlgorithm( _ ByVal BcryptEncryptionAlgorithmId As BcEncryptionAlgorithm) _ As String Dim EncryptionAlgorithmName As String ' Note: EncryptionAlgorithmName must be in UPPERCASE. Select Case BcryptEncryptionAlgorithmId Case BcEncryptionAlgorithm.bcAes EncryptionAlgorithmName = "AES" End Select BcryptEncryptionAlgorithm = EncryptionAlgorithmName End Function
Likewise, functions, IsBcryptRandomAlgorithm and IsBcryptRandomAlgorithmId, are included to verify/validate either a literal algorithm name or an enum value for the random algorithm name:
' Return True if the passed text value represents a value of ' enum BcRandomAlgorithm. ' Note: To validate, RandomAlgorithm must be in UPPERCASE. ' ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function IsBcryptRandomAlgorithm( _ ByVal RandomAlgorithm As String) _ As Boolean Dim Index As BcRandomAlgorithm Dim Result As Boolean For Index = BcRandomAlgorithm.[_First] To BcRandomAlgorithm.[_Last] If BcryptRandomAlgorithm(Index) = RandomAlgorithm Then Result = True Exit For End If Next IsBcryptRandomAlgorithm = Result End Function
' Return True if the passed value of enum BcRandomAlgorithm ' is valid. ' ' To be called from function CngRandom. ' ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function IsBcryptRandomAlgorithmId( _ ByVal RandomAlgorithmId As BcRandomAlgorithm) _ As Boolean Dim Result As Boolean If BcRandomAlgorithm.[_First] <= RandomAlgorithmId And RandomAlgorithmId <= BcRandomAlgorithm.[_Last] Then Result = True End If IsBcryptRandomAlgorithmId = Result End Function
Similar functions for the encryption algorithm are left out, as only one algorithm (AES) is in use, and therefor is used by default.
Finally, to determine the length of an encrypted value - either as a byte array or a Base64 encoded string - without actually encrypting something, two simple functions, EncryptedByteLength and EncryptedTextLength, will return those values:
' Return the byte length of a string of the length DecryptedTextLength ' encrypted with function EncryptData. ' ' To be called from function FitByteField. ' ' Example: ' Text = "Careful with that axe, Eugene!" ' DecryptedTextLength = Len(Text) ' = 30 ' Length = EncryptedByteLength(DecryptedTextLength) ' Length -> 112 ' ' Example data: ' ' Length plain Length encrypted ' 0 0 ' 1 48 ' 4 64 ' 12 80 ' 20 96 ' 28 112 ' 36 128 ' 44 144 ' 52 160 ' 60 176 ' 68 192 ' 76 208 ' 84 224 ' 92 240 ' 100 256 ' 108 272 ' 116 288 ' 124 304 ' 132 320 ' 140 336 ' 148 352 ' 156 368 ' 164 384 ' 172 400 ' 180 416 ' 188 432 ' 196 448 ' 204 464 ' 212 480 ' 220 496 ' 227 496 ' 227 characters is the largest string to encrypt, if the ' encrypted byte array must fit a Binary field of Access. ' 228 512 ' ' 2022-02-18. Gustav Brock, Cactus Data ApS, CPH. ' Public Function EncryptedByteLength( _ ByVal DecryptedTextLength As Long) _ As Long Dim Length As Long If DecryptedTextLength > 0 Then Length = 48 + ((DecryptedTextLength + 4) \ 8) * 16 End If EncryptedByteLength = Length End Function
' Return the length of a string encrypted and encoded with function Encrypt. ' ' To be called from function FitTextField. ' ' Example: ' Text = "Careful with that axe, Eugene!" ' DecryptedTextLength = Len(Text) ' = 30 ' Length = EncryptedTextLength(DecryptedTextLength) ' Length -> 152 ' ' Example data: ' ' Length plain Length encrypted ' 0 0 ' 1 64 ' 4 88 ' 12 108 ' 20 128 ' 28 152 ' 36 172 ' 44 192 ' 52 216 ' 60 236 ' 67 236 ' 67 characters is the largest string to encrypt, if the ' encrypted string must fit a Short Text field of Access. ' 68 256 ' 76 280 ' 84 300 ' 92 320 ' 100 344 ' 108 364 ' 116 384 ' 124 408 ' 132 428 ' 140 448 ' 148 472 ' 156 492 ' 164 512 ' 172 536 ' 180 556 ' 188 576 ' 196 600 ' 204 620 ' 212 640 ' 220 664 ' 228 684 ' 236 704 ' 244 728 ' 252 748 ' The maximum length of an Access text field is 255 characters. ' 255 748 ' 260 768 ' ' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH. ' Public Function EncryptedTextLength( _ ByVal DecryptedTextLength As Long) _ As Long Dim Length As Long If DecryptedTextLength > 0 Then Length = 64 - Int(-(DecryptedTextLength - 3) / 8) * 20 - Int(-(DecryptedTextLength - 3) / 24) * 4 End If EncryptedTextLength = Length End Function
These functions can come in handy for code that creates or alters table design where fields for encrypted values will be included.
A full set of functions for encryption meeting modern standards has been presented. The functions cover all common needs for encryption 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.