How do I encrypt/decrypt data on SQL DB using MS ACCESS - part 2
In this post:
https://www.experts-exchange.com/questions/22549030/How-do-I-encrypt-decrypt-data-on-SQL-DB-using-MS-ACCESS.html?sfQueryTermInfo=1+encrypt+mde
capricorn1 suggested looking at code in a previous post:
<see if the answer in this thread helps
https://www.experts-exchange.com/questions/20927981/encrypt-decrypt-code.html?>
Which I did. I grabbed the code and have been using it successfully. But now, I have users who have different regional settings, specifically Czech and Vietnamese.
When I change my settings to Czech, for example, and encrypt an IP address, then turn around immediately, while still in Czech, and decrypt it, the value I get back is not what I started with.
1. What am I missing?
2. What encryption code could I use in Access 2007 that will encrypt/decrypt successfully regardless of what the regional settings are?
https://www.experts-exchange.com/questions/22549030/How-do-I-encrypt-decrypt-data-on-SQL-DB-using-MS-ACCESS.html?sfQueryTermInfo=1+encrypt+mde
capricorn1 suggested looking at code in a previous post:
<see if the answer in this thread helps
https://www.experts-exchange.com/questions/20927981/encrypt-decrypt-code.html?>
Which I did. I grabbed the code and have been using it successfully. But now, I have users who have different regional settings, specifically Czech and Vietnamese.
When I change my settings to Czech, for example, and encrypt an IP address, then turn around immediately, while still in Czech, and decrypt it, the value I get back is not what I started with.
1. What am I missing?
2. What encryption code could I use in Access 2007 that will encrypt/decrypt successfully regardless of what the regional settings are?
Here is the module (basCrypto) I use to do the encryption and decryption. Use the EncryptData() function to encrypt and and the DecryptData() function to decrypt.
Attribute VB_Name = "basCrypto"
Option Compare Database
Option Explicit
' clsPIN declarations
Public EncryptClass As New clsEncrypt
Public PINNumber As New clsPIN
Public Const conPIN = "X*0\2o{Qm%`:<~u|"
Public strPIN As String
Public strEncryptionPIN As String
Public strEncrypted As String
' requires clsEncrypt and clsPIN
Public Function EncryptData(strIn As String)
' set up error handling
On Error GoTo ErrorHandler
' initialize the PIN
PINNumber.InitialisePIN conPIN, 128
strPIN = PINNumber.CreatePIN
PINNumber.ClosePIN
' encrypt the data
EncryptClass.Password strPIN
strEncryptionPIN = EncryptClass.EncryptString(strPIN, True)
EncryptData = EncryptClass.EncryptString(strIn, True)
ExitHere:
' exit the sub
Exit Function
ErrorHandler:
' set the function to F*A*I*L*E*D
EncryptData = "F*A*I*L*E*D"
' display the error and exit
MsgBox "Error " & Err.Number & " raised by " & Err.Source & ": " & _
HandleQuotes(Err.Description), vbCritical, "EncryptData"
' goto the ExitHere label
Resume ExitHere
End Function
' requires clsEncrypt
Public Function DecryptData(strIn As String, strEncryptedPIN As String)
' set up error handling
On Error GoTo ErrorHandler
' set the global string to the local string's value
strEncryptionPIN = strEncryptedPIN
' decrypt the data
DecryptData = Trim$(EncryptClass.DecryptString(strIn, True))
ExitHere:
' exit the sub
Exit Function
ErrorHandler:
' set the function to F*A*I*L*E*D
DecryptData = "F*A*I*L*E*D"
' display the error and exit
MsgBox "Error " & Err.Number & " raised by " & Err.Source & ": " & _
HandleQuotes(Err.Description), vbCritical, "DecryptData"
' goto the ExitHere label
Resume ExitHere
End Function
Public Function HandleQuotes(ByVal varValue As Variant, _
Optional strDelimiter As String = "'") As Variant
' if the input value is not null,
If Not IsNull(varValue) Then
' replace the delimiter with double single quotes
HandleQuotes = Replace(varValue, strDelimiter, "''")
End If
End Function
Here is another class (clsPIN) you will need.
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "clsPIN"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit
Const Max = 256
Dim PINTxt As String, PINLen As Integer, PINTxtLen As Integer
Dim PINCount As Integer
Dim PINArray() As String
Public Sub InitialisePIN(PINString As String, PINLength As Integer)
PINTxt = PINString
PINTxtLen = Len(PINString)
PINLen = PINLength
Randomize Timer
ReDim PINArray(100)
PINCount = 0
End Sub
Public Function CreatePIN() As String
Dim i As Integer, PIN As String, n As Integer
Retry:
PIN = ""
For i = 1 To PINLen
n = Rnd * PINTxtLen + 1
If n < 1 Then
n = 1
ElseIf n > PINTxtLen Then
n = PINTxtLen
End If
PIN = PIN & Mid$(PINTxt, n, 1)
Next i
PINCount = PINCount + 1
If PINCount > UBound(PINArray) Then
ReDim Preserve PINArray(PINCount + 50)
End If
PINArray(PINCount) = PIN
For i = 1 To PINCount - 1
If PIN = PINArray(i) Then Exit For
Next i
If i < PINCount Then
PINCount = PINCount - 1
GoTo Retry
End If
CreatePIN = PIN
End Function
Public Sub ClosePIN()
Erase PINArray
End Sub
Here are the public declarations for the class. You can set conPIN to any 16 characters. You will have to store the encrypted data (strEncrypted = EncryptData(strToBeEncrypt
' clsPIN declarations
Public EncryptClass As New clsEncrypt
Public PINNumber As New clsPIN
Public Const conPIN = "&hQ0#cI~|`<;"
Public strPIN As String
Public strEncryptionPIN As String
Public strEncrypted As StringASKER
I have implemented your example.
Using the conPIN value from your example, and with my settings set to "English (United States)", I tried this:
debug.Print encryptdata("123.456.789.0
107BAC80ADD23BB5B078CB9D9B
debug.Print decryptdata("107BAC80ADD23
123.456.789.012
Works perfectly
Then I changed my Regional setting to Czech and tried it:
debug.Print encryptdata("123.456.789.0
2E345D965A1E53B60B50757A44
debug.Print decryptdata("2E345D965A1E5
123.456.W?¤cµA.?
As you can see, when my machine was set to Czech, the results were no good.
What causes that, and how can I overcome it?
Using the conPIN value from your example, and with my settings set to "English (United States)", I tried this:
debug.Print encryptdata("123.456.789.0
107BAC80ADD23BB5B078CB9D9B
debug.Print decryptdata("107BAC80ADD23
123.456.789.012
Works perfectly
Then I changed my Regional setting to Czech and tried it:
debug.Print encryptdata("123.456.789.0
2E345D965A1E53B60B50757A44
debug.Print decryptdata("2E345D965A1E5
123.456.W?¤cµA.?
As you can see, when my machine was set to Czech, the results were no good.
What causes that, and how can I overcome it?
ASKER
An additional problem...
I need to take the encrypted value of the IP address for "English (United States)", which is "107BAC80ADD23BB5B078CB9D9
To test this, I got out of Access, then got back in and tried this:
debug.Print decryptdata("107BAC80ADD23
¡ AÃí=Í!oßÜß3@t
As you can see, I got something totally unexpected. What is going on?
I need to take the encrypted value of the IP address for "English (United States)", which is "107BAC80ADD23BB5B078CB9D9
To test this, I got out of Access, then got back in and tried this:
debug.Print decryptdata("107BAC80ADD23
¡ AÃí=Í!oßÜß3@t
As you can see, I got something totally unexpected. What is going on?
ASKER
Okay - I just realized you said this:
<You will have to store the encrypted data (strEncrypted = EncryptData(strToBeEncrypt
So I need to encrypt the PIN also...
Let me test that.
<You will have to store the encrypted data (strEncrypted = EncryptData(strToBeEncrypt
So I need to encrypt the PIN also...
Let me test that.
You have to use the strEncryptionPIN that was set in the EncryptData function as the PIN.
ASKER
I have posted a test accdb. Could you please look at it and tell what I am doing wrong.
Open it. Leave the supplied values or enter your own in the orange fields. Click Encrypt, then click Decrypt.
Set-Up-Encrypted-Connection-Valu.txt
Open it. Leave the supplied values or enter your own in the orange fields. Click Encrypt, then click Decrypt.
Set-Up-Encrypted-Connection-Valu.txt
I found you have to instantiate a new class for each encryption and decryption or only the last one decrypts successfully. The attached test accdb encrypts and decrypts all three fields successfully.
Set-Up-Encrypted-Connection-Valu.txt
Set-Up-Encrypted-Connection-Valu.txt
Please let me know if this works with your other regional settings.
ASKER
It still does not work with Czech.
Rats! I am sorry if doesn't work. I recommend you cancel this question and start over so that other experts can give you more ideas.
ASKER
I wonder if I should set my regional setting to Czech abd theb reboot?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was it! I had to select Czech on the Regional Options Tab AND select Czech on the Advanced tab. Doing the latter forced me to reboot.
I do not understand yet why this is necessary. This is going to be a painful process whenever I need to set up new encrypted values.
Czech-Regional-Settings.bmp
I do not understand yet why this is necessary. This is going to be a painful process whenever I need to set up new encrypted values.
Czech-Regional-Settings.bmp
ASKER
I liked your encryption/decryption code better than what I had, and while you did not specifically give me the answer to "why doesn't it work with Czech regional setting", you did lead me to the answer.
ASKER
<I found you have to instantiate a new class for each encryption and decryption or only the last one decrypts successfully. The attached test accdb encrypts and decrypts all three fields successfully.>
Your latest example was set to handle exactly 3 encryption/decryptions, which is what I had in my example.
But in my real world, I have to decrypt an unpredictable number of times. Can you set the instantiation so it is more generic?
I cannot figure out how to do that.
Your latest example was set to handle exactly 3 encryption/decryptions, which is what I had in my example.
But in my real world, I have to decrypt an unpredictable number of times. Can you set the instantiation so it is more generic?
I cannot figure out how to do that.
ASKER
I also need to store the 3 encrypted values and their PINs in an external file, then read them in at a later time, so I cannot depend on a public value still being available at that time.
I will experiment with my file and see what I can do.
Open in new window