character offset encryption/decryption

[tranchemontaigne]

I am trying to create a simple character offset encryption function to encrypt specific data fields within a query results output.

Though my method appears to work for encrypting data (provided the field is not null), but unfortunately the decrypt function is not working correctly. When viewing the code through the debugger, the watch window shows Windows ANSI character code being correctly translated into alphabet characters, yet the query displays only character codes (not aplhabet letters).

Here's my code, running in MS Access 2000.


Option Compare Database
Option Explicit

Public Function Encrypt(strString As String) As String
'/////////////////////////////////////////////////////////////////////
'// DESCRIPTION:
'// Function takes a character string and encrypts it by
'// offsetting each character of the string by an integer value.
'//
'// In the event that the character string is equal to "OTH000"
'// the string is not encrypted.
'//
'/////////////////////////////////////////////////////////////////////
'// INPUTS:
'// strString -String expression to encrypt
'//
'/////////////////////////////////////////////////////////////////////
'// OUTPUT:
'// Encrypt -Encrypted string
'//
'/////////////////////////////////////////////////////////////////////

'declare variables
Dim intStrLength As Integer
Dim intOffset As Integer
Dim intCounter As Integer
Dim strLetter As String
Dim intLetterPlain As Integer
Dim intLetterEncrypted As Integer
Dim strLetterEncrypted As String
Dim strEncryptedString As String

'set character offset value
intOffset = 9

'calculate length of string to encrypt
intStrLength = Len(strString)

'test to see if strString is "OTH000"
If strEncryptedString = "OTH000" Then
strString = "OTH000"

'NONE OF THESE 2 TESTS FOR NULL VALUES WORK
'test#1 to see if strString is null
'elseIf strString = "" Then
' intStrLength = 0
'End If

'If intStrLength > 0 Then
' intStrLength = intStrLength
'Else
' intStrLength = 0
'End If

'test#2 to see if strString is null
'elseIf intStrLength < 1 Then
' strEncryptedString = "2"

Else
'encrypt string one letter at a time
'build encrypted string by concatenating one letter at a time
'stop at end of string
For intCounter = 1 To intStrLength
strLetter = Mid(strString, intCounter, 1)
intLetterPlain = Asc(strLetter)
intLetterEncrypted = intLetterPlain + intOffset
strLetterEncrypted = Chr(intLetterEncrypted)
strEncryptedString = strEncryptedString & strLetterEncrypted
Next intCounter
End If

'return encrypted string
Encrypt = strEncryptedString

End Function

Public Function Decrypt(strEncryptedString As String) As String
'/////////////////////////////////////////////////////////////////////
'// DESCRIPTION:
'// Function takes a the chatacter string previously encrypted by
'// the Encrypt() function, and returns the original plain text
'// character string values.
'//
'// In the event that the character string is equal to "OTH000"
'// the string is not decrypted.
'//
'/////////////////////////////////////////////////////////////////////
'// INPUTS:
'// strEncryptedString -Encrypted string expression
'//
'/////////////////////////////////////////////////////////////////////
'// OUTPUT:
'// Decrypt -Decrypted string
'//
'/////////////////////////////////////////////////////////////////////

'declare variables
Dim intStrLength As Integer
Dim intOffset As Integer
Dim intCounter As Integer
Dim strLetter As String
Dim intLetterPlain As Integer
Dim strLetterPlain 'As String
Dim intLetterEncrypted As Integer
Dim strLetterEncrypted As String
Dim strString As String

'set character offset value
intOffset = 9

'calculate length of string to encrypt
intStrLength = Len(strEncryptedString)

'test to see if strString is "OTH000"
If strEncryptedString = "OTH000" Then
strString = "OTH000"

'NONE OF THESE 2 TESTS FOR NULL VALUES WORK
'test#1 to see if strString is null
'elseIf strString = "" Then
' intStrLength = 0
'End If

'If intStrLength > 0 Then
' intStrLength = intStrLength
'Else
' intStrLength = 0
'End If

'test#2 to see if strString is null
'elseIf intStrLength < 1 Then
' strEncryptedString = "2"

Else
'encrypt string one letter at a time
'build encrypted string by concatenating one letter at a time
'stop at end of string
For intCounter = 1 To 2 'intStrLength
strLetterEncrypted = Mid(strEncryptedString, intCounter, 1)
intLetterEncrypted = Asc(strLetterEncrypted)
intLetterPlain = intLetterEncrypted - intOffset
strLetterPlain = Chr(intLetterPlain)
strString = strString & intLetterPlain
Next intCounter
End If

'return encrypted string
Decrypt = strString

End Function




As a secondary question, when I use these functions in a query, I receive "Error#" in the query output whenever the field I am trying to encrypt contains a null value within a record. I can work around this by building an

IIF( [test field] is not null, encrypt([test field]),
null)

expression that tests for null, and only calls the encrypt function if the field value is not null, but ideally I would like to have the function know how to handle a null value natively. I would like to set a default argument value within the function prototype, but do not know how to do this with VBA. Alternatively, is it possible for overload functions in Access VBA, so that the same function name could be used, but the compiler would know wiich version to call based upon the function argument supplied?


Thanks in advance for your help.

 

[PHV]

If you want to handle Null values, you have to play with Variant instead of String, and then use the IsNull function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tranchemontaigne]

PHV! Thanks, You solved the provlem of dealing with null values. I added an IsNull(strString) test to the top of the function and changed the function prototype to receive its argument as a variant.

Now if only I could figure out why MS Access won't execute chr(65) and return the value "A"... The watch window within VBA says that chr(65) becomes "A" when I step through the Decrypt(strEncryptedString) function, but the value of 65 is returned to the query.

 

[PHV]

Have you tried to replace this:
strLetterPlain = Chr(intLetterPlain)
by this ?
strLetterPlain = Chr$(intLetterPlain)
as for some reason you have commented out the typing as String of strLetterPlain.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tranchemontaigne]

I commented out the type of strLetterPlain because the MS Access help files did not declare a data type within their code example. Reading the function description for STR() again, I saw that it receives a LONG for its argument. Infortunately changing the data type to LONG did not get the STR() function to return a character value. I tried again with STR$() and it still did not work.

In an effort to make sure that the argument data type for STR() was correct, I changed it to a Variant. For the sake of clarity, I will re-post the problem function now.

Thanks again for taking a look at this.



Public Function Decrypt(strEncryptedString As Variant) As String
'/////////////////////////////////////////////////////////////////////
'// DESCRIPTION:
'// Function takes a the chatacter string previously encrypted by
'// the Encrypt() function, and returns the original plain text
'// character string values.
'//
'// In the event that the character string is equal to "OTH000"
'// the string is not decrypted.
'//
'/////////////////////////////////////////////////////////////////////
'// INPUTS:
'// strEncryptedString -Encrypted string expression
'//
'/////////////////////////////////////////////////////////////////////
'// OUTPUT:
'// Decrypt -Decrypted string
'//
'/////////////////////////////////////////////////////////////////////

'declare variables
Dim intStrLength As Integer
Dim intOffset As Integer
Dim intCounter As Integer
Dim strLetter As String
Dim intLetterPlain As Variant
Dim strLetterPlain As String
Dim intLetterEncrypted As Integer
Dim strLetterEncrypted As String
Dim strString As String

'set character offset value
intOffset = 9

'test to see if strString is null
If IsNull(strEncryptedString) Then
strString = ""

'test to see if strString is "OTH000"
ElseIf strEncryptedString = "OTH000" Then
strString = "OTH000"

Else
'calculate length of string to encrypt
intStrLength = Len(strEncryptedString)

'encrypt string one letter at a time
'build encrypted string by concatenating one letter at a time
'stop at end of string
For intCounter = 1 To intStrLength
strLetterEncrypted = Mid(strEncryptedString, intCounter, 1)
intLetterEncrypted = Asc(strLetterEncrypted)
intLetterPlain = intLetterEncrypted - intOffset
strLetterPlain = Chr$(intLetterPlain)
strString = strString & intLetterPlain
Next intCounter
End If

'return encrypted string
Decrypt = strString

End Function

 

[PHV]

Change this:
strString = strString & intLetterPlain
By this:
strString = strString & [highlight]str[/highlight]LetterPlain

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tranchemontaigne]

Thanks HUGELY!

I feel a bit foolish for not seeing that one. The function works great now!