asked on

Encryption question

How can I add an extra encryption when saving to the table such that someone reading it will not be able to see it. This mean that I will have to decrypt twice since I will now have two levels of encryption.

I will also appreciate suggested code to call the new assisted encryption so that it can easily be adapted to my application without further complication to the db.
Any help will be appreciated.

Below is my current encryption code and several ways I have called it in my db.
' *******************
Public Function PerformEncryption(ByRef strPassword As String, Optional ByVal boo As Boolean = True) As String
' boo = True then Encrypt, boo = False then Decrypt
Dim strCode As String, intCounter As Integer

For intCounter = 1 To Len(strPassword)
If intCounter Mod 2 = 0 Then
If boo = True Then
strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 1)
Else
strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 1)
End If
Else
If boo = True Then
strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) + 2)
Else
strCode = strCode + Chr(Asc(Mid(UCase(strPassword), intCounter, 1)) - 2)
End If
End If
Next intCounter

PerformEncryption = strCode

End Function
' *******************

Several ways I called the above code in my db..

1. dim sEncrypted As String
dim sSQl As String
sEncrypted = PerformEncryption(Me.txtPassword,True)
sSql = "UPDATE tblSecurity SET Password = '" & sEncrypted & "' WHERE UserID = '" & frm.txtUser & "'"
DoCmd.RunSQL sSql

Note the use of "sEncrypted" instead of PerformEncryption(Me.txtPassword,True) '<<<-- I prefer this simplification.

Other usage in my application...

2. ElseIf PerformEncryption(Me.TxtPassword) = rs!Password Then
'................................
'...............................

3. If PerformEncryption(Me.TxtPassword, True) = GetPassword("billcute") Then
'................................
'...............................

4. NewPswrd = PerformEncryption(Me.txtNewPassword, True)
rst.FindFirst "Password = '" & NewPswrd & "'" & " And UserID = '" & Me.txtUser & "'"
'................................
'...............................

wesbird

http://www.frez.co.uk/freecode.htm#sha256 contains VBScript samples that you could put in an Access module.

You'll probably want this too: http://www.freevbcode.com/ShowCode.asp?ID=5248 so that the encrypted data can be stored as base64 for ease of use with quoted SQL

rockiroads

Bill, u need to look at all the places where u get and set the password

You have that function called

EncryptDecrypt

What u could try is this

e.g.

setting a password - we already have sEncrypted which is encrypted to your code
To make it unreadable, u can encrypt again using the other function

(1)
sSql = "UPDATE tblSecurity SET Password = '" & EncryptDecrypt(sEncrypted,TRUE) & "' WHERE UserID = '" & frm.txtUser & "'"

Note, when u create a new user with a password, u need to ensure u do the same there

The other line (4)
NewPswrd = PerformEncryption(Me.txtNewPassword, True)
rst.FindFirst "Password = '" & EncryptDecrypt(NewPswrd,True) & "'" & " And UserID = '" & Me.txtUser & "'"

Now when u want to read it, u perform your encryption to unencrypt. Well we do this again

'this line is checking user entered password against the one in the DB

(2)
ElseIf PerformEncryption(Me.TxtPassword) = EncryptDecrypt(rs!Password, False)

(3)
Regarding GetPassword, does it fetch the password from the DB? I think so,
if so, try this

If PerformEncryption(Me.TxtPassword, True) = EncryptDecrypt(GetPassword("billcute"), False)

puppydogbuddy

Bill,
It looks like Rocki's got you covered. The only thing I can add is to check out the following link for free encryption software to use to protect shared files and directories with or without a password encryption routine.

http://www.cypherix.co.uk/cryptainerle/index.htm?adv=enc_ing

billcute

ASKER

rockiroads,
Thank you for your inputs here...there are still some minor details I am trying to sort out before I can test the suggestion. By the time I sit down to understand the logic, the examples will make it easier to better understand.

For Example, here are some other info that needed some attention, I will appreciate help with these and you may just respond with a single line only where

...... (' <<<--- Help area) are indicated.

a.
Dim Pswrd As String
Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)
'Check to see if the default "password" is correct
Pswrd = PerformEncryption(Me.txtChkPassword, True) ' <<----- See this line as it relates

If Pswrd = Me.Password Then
'Check the new password

If IsNull(Me.txtNewPassword) Or Me.txtNewPassword = "" Then
PopUpMsgBox "Please enter a new password. ", 2, " Edit Account", vbExclamation
Me.txtNewPassword.SetFocus
' **********
b.
Dim strAnswer As Variant
' .......................................
' .......................................
strAnswer = InputBox(.Fields!Question)
If strAnswer = "" Then
fBackdoor = "Reset"
MsgBox "No data Input, please enter the requested data to continue"
Exit Function
End If
If StrComp(strAnswer, PerformEncryption(!Answer, False)) <> 0 Then ' <<<--- Help area
MsgBox "The value entered did not match the data in the database"
' **********
c.
Dim dtBirthdate As Date
On Error GoTo EH_CBD
dtBirthdate = CDate(strInput)

On Error GoTo EH_CBDGeneral
With rsBirthdate
If Nz(!DOB) = "" Then
CheckBirthDate = "Fail"
Exit Function
End If
If dtBirthdate = CDate(PerformEncryption(!DOB, False)) Then ' <<<--- Help area
CheckBirthDate = "Pass"
Else
MsgBox "The value entered did not match the data in the database"
CheckBirthDate = "Reset"
' ***********
d.
Dim db As DAO.Database, rst As DAO.Recordset
Dim dtmModified As Date
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblPasswords_Old", dbOpenDynaset)
rst.FindFirst "UserID = '" & strUser & "'"
If rst.NoMatch Then
rst.AddNew
rst("UserID") = strUser
Else
rst.Edit
End If
rst("Password") = PerformEncryption(NewPassword, True) ' <<<--- Help area
dtmModified = Now()
' **********

e. Dim strPassword As String
strPassword = fRandomPword(8, True)

'Next we encrypt password
sEncrypted = PerformEncryption(sPassword, True)
Updateusertable Me.txtUser, strPassword 'pass in unencrypted pswd as that func does it ..' <<<--- Help area

f.
Dim strPassword As String

strPassword = ""
Do While strPassword = ""
strPassword = InputBox("New Password :")
If strPassword <> "" Then
Me.txtDummyPWDField = strPassword
If Not ValidatePassword(Me.txtDummyPWDField) Then strPassword = ""
End If
Loop

Call SavePassword("billcute", PerformEncryption(strPassword, True), Now()) ' <<<--- Help area

Note: ValidatePassword is a function for checking 8 character lenght password...

g.

The following are msgboxes... ...' <<<--- Help area

i. sPassword = PerformEncryption(Nz(TxtPassword.Value))
Msgbox "DIAGNOSTIC - THE PASSWORD ENTERED AFTER PASSING THRU PEFORMENCRYPTION IS" & vbCrLf & sPassword

ii. If Me.txtUser = "billcute" Then

Msgbox "DIAGNOSTIC" & vbCrLf & "GetPassword(billcute) = " & GetPassword("billcute") & vbCrLf & "PerformEnc = " & PerformEncryption(TxtPassword.Value)

iii. Msgbox "DIAGNOSTIC" & vbCrLf & "Password in DB is " & rs!Password

billcute

ASKER

rockiroads,
I tried to configure all my Login Form codes using first set of samples you provided....I couldnt login at all...because I am sure that I am still doing something wrong.

If you could please atake a minute of your time to provide a single line answer to the "help area" code request - this would present a clearer picture of your code logic.

Regards
Bill

billcute

ASKER

rockiroads,
Any luck with my code request above?

Regards
Bill

billcute

ASKER

rockiroads,
I will appreciate your help in closing this post as soon as possible.

Regards
Bill

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

rockiroads,
If I got you correctly... then the following answer from my previous comment should be as follows....

PLEASE CORRECT ME WHERE I AM WRONG......

(a). Pswrd = PerformEncryption(Me.txtChkPassword, True)

becomes...
Pswrd = PerformEncryption(Me.txtChkPassword, True)

(b). If StrComp(strAnswer, PerformEncryption(!Answer, False)) <> 0 Then

becomes...
If StrComp(strAnswer, EncryptDecrypt(PerformEncryption(!Answer, False))) <> 0 Then

(c). If dtBirthdate = CDate(PerformEncryption(!DOB, False)) Then

becomes...
If dtBirthdate = CDate(EncryptDecrypt(PerformEncryption(!DOB, False))) Then

(d). rst("Password") = PerformEncryption(NewPassword, True)

becomes....
rst("Password") = EncryptDecrypt(PerformEncryption(NewPassword, True))

(e). There are no changes to this request....

Updateusertable Me.txtUser, strPassword ' <<---'pass in unencrypted
' pswd as that func does it ..'

(f). Call SavePassword("billcute", PerformEncryption(strPassword, True), Now())

if double encryption is activated for "billcute" then it will become..
Call SavePassword("billcute", EncryptDecrypt(PerformEncryption(strPassword, True)), Now())

(g). The following are msgboxes... ...'
' .................................
Dim sPassword As String
Dim sPassword2
sPassword = PerformEncryption(Nz(TxtPassword.Value))
sPassword2 = EncryptDecrypt(PerformEncryption(Nz(TxtPassword.Value)))

i. Msgbox "DIAGNOSTIC - THE PASSWORD ENTERED AFTER PASSING THRU PEFORMENCRYPTION IS" & vbCrLf & sPassword2 ' <<<<---- New msgbox

ii. If Me.txtUser = "billcute" Then

Msgbox "DIAGNOSTIC" & vbCrLf & "GetPassword(billcute) = " & GetPassword("billcute") & vbCrLf & "PerformEnc = " & sPassword2 ' <<<<---- New msgbox

iii. Msgbox "DIAGNOSTIC" & vbCrLf & "Password in DB is " & rs!Password

(h).
Other MISCELLANEOUS......
' ____________________________________________

(1).
CurrentDb.Execute "UPDATE tblUsers SET DOB = '" & PerformEncryption(Me.txtDOB, True) & "', Answer = '" & PerformEncryption(Me.txtAnswer, True) & "' WHERE NameID = " & Me.txtNameID

becomes......

CurrentDb.Execute "UPDATE tblUsers SET DOB = '" & EncryptDecrypt(PerformEncryption(Me.txtDOB, True)) & "', Answer = '" & EncryptDecrypt(PerformEncryption(Me.txtAnswer, True)) & "' WHERE NameID = " & Me.txtNameID

(2).
Call SavePassword(strUser, PerformEncryption(NewPassword, True), dtmModified)

changed to...

Call SavePassword(strUser, EncryptDecrypt(PerformEncryption(NewPassword, True)), dtmModified)

(3).
If dtBirthdate = CDate(PerformEncryption(!DOB, False)) Then

changed to...

If dtBirthdate = CDate(EncryptDecrypt(PerformEncryption(!DOB, False))) Then

(4).
If StrComp(strAnswer, PerformEncryption(!Answer, False)) <> 0 Then

changed to....

If StrComp(strAnswer, EncryptDecrypt(PerformEncryption(!Answer, False))) <> 0 Then

(5).
rst("Password") = (PerformEncryption(NewPassword, True))

becomes......
rst("Password") = EncryptDecrypt(PerformEncryption(NewPassword, True))
' ************

Regards
Bill

rockiroads

Wow, thats some post

What can I say

basically here is the order

when u save something to the DB

e.g. take Answer, DOB or Password

you must first encrypt it using PerformEncryption
then you encrypt it again using EncryptDecrypt
the resulting value is what u save

To fetch a Answer, DOB or Password

you must first decrypt using EncryptDecrypt
then you decrypt using PerformEncryption

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

rockiroads,
All your suggested code works without a doubt. However, based on the sql
you suggested earlier on today 06/18/2006 05:53AM EDT, I created an On Click Event then passed the sql to it with a view to resetting all users password to the default "wizard".

In the process I received a Run-time error ‘3075’:

"Wrong number of arguments used with function in query expression ‘EncryptDecrypt(Password, False)."

On.....

Public Sub UpdateDBPasswords()
DoCmd.RunSQL "UPDATE tblSecurity SET PASSWORD = EncryptDecrypt(Password, False)"
DoCmd.RunSQL "UPDATE tblPasswords_Old p, tblSecurity s SET p.PASSWORD = s.password where p.userid = s.userid"
Call SavePassword(strUser, EncryptDecrypt(PerformEncryption(NewPassword, True)), dtmModified)
End Sub
' *******
Here is my OIn Click Event0.

Private Sub btnResetPassword_Click()
Call UpdateDBPasswords
End Sub
________________________

What did I do wrong here?

Regards
Bill

rockiroads

ok, sorry
I changed the EncryptDecrypt but forgot to change the SQL

note, it just takes one parameter now

try this on the first sql

DoCmd.RunSQL "UPDATE tblSecurity SET PASSWORD = EncryptDecrypt(Password)"

billcute

ASKER

rockiroads,
Thanks...one more thing and I will rest my case.
1
(a). We need to also update tblPasswords.old.changedate to now when password is reset.
(b). Is there any way to automatically set "billcute" password to (Abc123) when the two SQL RUNS using the code below.
(Abc123) '<--- that is, write this password for billcute using the call below in the DB Properties.

Call SavePassword("billcute", EncryptDecrypt(PerformEncryption(Password, True)), dtmModified)
' *************

Public Sub UpdateDBPasswords()
DoCmd.RunSQL "UPDATE tblSecurity SET PASSWORD = EncryptDecrypt(Password)"
DoCmd.RunSQL "UPDATE tblPasswords_Old p, tblSecurity s SET p.PASSWORD = s.password where p.userid = s.userid"
Call SavePassword(strUser, EncryptDecrypt(PerformEncryption("(Abc123)", True)), dtmModified)
'....please note the bracket as part of the password.
End Sub

(2). When developer splits a db into FE and BE..does the db properties reside in the BE or FE where it has to be updated each time there is a table link up.?

Regards
Bill

billcute

ASKER

rockiroads,

I further amended the code to include PWChange field reset. So if you could assist with my last request above, IK will be most grateful.

Public Sub UpdateDBPasswords()
DoCmd.RunSQL "UPDATE tblSecurity SET PASSWORD = EncryptDecrypt(Password), PWChange=True" '<<---Added
DoCmd.RunSQL "UPDATE tblPasswords_Old p, tblSecurity s SET p.PASSWORD = s.password where p.userid = s.userid"
Call SavePassword(strUser, EncryptDecrypt(PerformEncryption("(Abc123)", True)), dtmModified)
'....please note the bracket as part of the password.
End Sub
' *******

Regards
Bill

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

rockiroads,
If db properties exist ONLY in each Front end...if one is reset in order to gain access into the backend of the db then..it seems to me that all other FEs will have different password for "billcute" for example..hummmmm. Does this not present future problem?

Thank you for all your assistance on this post.

Regards
Bill

billcute

ASKER

rockiroads,
Your suggested code and assistance were specific to my request, therefore, I will award rockiroads full points to be spread over his suggestions.

I am using this medium to thank other experts for their contributions on this post as well.

Regards
Bill

rockiroads

Yes, using DB properties may cause problems. I don't know of any other alternatives on offer with regards to using this property.