Implementing Login 5.3 from Isladogs

[JMongi]

Does this particular function need updated to the encrypted version also? Meaning i need to pass the decrypted RC4_Key to GetRC4Key?

Public Function GetRC4Key()

    GetRC4Key = RC4_Key

End Function

or this section of code:

            'CR v5.2 05/01/2020 - corrected date code to fix error
            CurrentDb.Execute "UPDATE tblUsers SET PWD = RC4(strPassword,'RC4_Key'), PWDDate = #" & Format(Date, "mm/dd/yyyy") & "#" & _
                " WHERE UserName = '" & GetUserName() & "';"

Since it's in single quotes, I'm not sure. It's the RC4 function call so I assumed it need to the decrypted key but I'm just dotting all my i's and t's.

 

[isladogs]

Hi. Sorry but I didn't see post #21 as its on a new page and didn't get a separate notification email.
Just about to log off as its late here in the UK.
You may have already worked out the answers for yourself. Please let me know

If not, I may create a version 5.5 to demonstrate all changes needed to encrypt/encode the RC4 cipher key
However, it may be a few days before I have time to do this

 

[JMongi]

@isladogs - Sorry, this was sitting as a draft post. It compiles ok. I haven't done extensive testing. It seems to work ok.

 

[isladogs]

OK I'll leave it for now

 

[JMongi]

@isladogs - So, everything seems to be working as far as I can tell. I did have a question about the need to hit enter and then activate the log-in button. What benefit does that serve? Is it programatic? security? I'm trying to step through the process myself but wanted to get your thoughts.

 

[isladogs]

Its largely personal preference.
You can remove that step if you prefer but to avoid losing functionality, you would need to ensure all the code in cmdLogin_Click event is still run.
That includes the password check, new password setup and the DoLogin procedure

 

[JMongi]

Thanks! That is what my code review seemed to indicate. Glad to get a second opinion.

 

[isladogs]

You're welcome. Hope you find it useful.

 

[JMongi]

@isladogs - Well, I managed to break it somehow and I'm not sure why it isn't working. It appears the first issue is the SQL command to update the users password. Here is your code:

Private Sub txtConPWD_AfterUpdate()

On Error GoTo Err_Handler

    If Trim(Me.TxtNewPWD & "") <> Trim(Me.TxtConPWD & "") Then
        Me.CmdLogin.Enabled = False
         '3 strikes and you're out
        Attempts = Attempts + 1
        
        Select Case Attempts
        Case 1
            FormattedMsgBox "Passwords do not match.       " & _
                "@Please try again        @", vbInformation + vbOKOnly, "Invalid Password Confirmation"
        
        Case 2
                FormattedMsgBox "Passwords still do not match           " & _
                "@You have ONE more attempt left      @", vbExclamation + vbOKOnly, "Password Error"
        
        Case 3
            FormattedMsgBox "Password confirmation failed THREE times. " & _
                "@The application will now close           @", vbCritical + vbOKOnly, "Access Denied"
            Application.Quit
            Exit Sub
        End Select
    
        Screen.PreviousControl.SetFocus
        'Me.TxtConPWD.SetFocus
        Exit Sub
    Else
        Me.CmdLogin.Enabled = True
        'passwords match so we can update the users record with the new password
        strPassword = Me.TxtConPWD
        
     'CR v5.2 05/01/2020 - corrected date code to fix error
        CurrentDb.Execute "UPDATE tblUsers SET PWD = '" & RC4(strPassword, RC4_Key) & "', PWDDate = #" & Format(Date, "mm/dd/yyyy") & "#" & _
            " WHERE UserName = '" & GetUserName() & "';"
                            
        'update old password on form to prevent error
        Me.TxtOldPWD = Me.TxtConPWD
        
        FormattedMsgBox "Your password has been updated" & _
            "@Click Login to continue       @", vbInformation + vbOKOnly, "Password Updated"
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in txtConPWD_AfterUpdateprocedure : " & vbCrLf & Err.Description
    Resume Exit_Handler

End Sub

And here is my code:

Private Sub txtConPWD_AfterUpdate()
'Confirms password in this box matches the new password box
Dim strPassword As String

On Error GoTo Err_Handler

If Trim(Me.txtNewPWD & "") <> Trim(Me.txtConPWD & "") Then
    Attempts = Attempts + 1 'Increments on failed match, max of 3 attempts
    
    Select Case Attempts
    Case 1
        FormattedMsgBox "Passwords do not match.       " & _
            "@Please try again        @", vbInformation + vbOKOnly, "Invalid Password Confirmation"
    
    Case 2
            FormattedMsgBox "Passwords still do not match           " & _
            "@You have ONE more attempt left      @", vbExclamation + vbOKOnly, "Password Error"
    
    Case 3
        FormattedMsgBox "Password confirmation failed THREE times. " & _
            "@The application will now close           @", vbCritical + vbOKOnly, "Access Denied"
        Application.Quit
        Exit Sub
    End Select
 
    Me.txtNewPWD.SetFocus
    Exit Sub
Else
    'passwords match so we can update the users record with the new password
    'CR v5.2 05/01/2020 - corrected date code to fix error
    Debug.Print Me.txtConPWD
    strPassword = Me.txtConPWD
    Debug.Print strPassword
    Debug.Print RC4(strPassword, ucp(RC4_Key))
    CurrentDb.Execute "UPDATE tblUsers SET PWD = '" & RC4(strPassword, ucp(RC4_Key)) & "', PWDDate = #" & Format(Date, "mm/dd/yyyy") & "#" & _
            " WHERE UserName = '" & GetUserName() & "';"

    FormattedMsgBox "Your password has been updated" & _
        "@Reenter your login information to proceed.       @", vbInformation + vbOKOnly, "Password Updated"
    'Set Form LoginForm to Original and clear values from hidden textboxes
    Me.cmdLogin.Enabled = True
    Me.txtOldPWD.Visible = True
    Me.txtConPWD = ""
    Me.txtNewPWD = ""
    Me.txtOldPWD = ""
    Me.txtOldPWD.SetFocus
    Me.txtConPWD.Visible = False
    Me.txtNewPWD.Visible = False
    Me.lblOld.Caption = "Password:"
    
End If
    
Exit Sub
    
    
Exit_Handler:
'Some Code to do after error here.
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in txtConPWD_AfterUpdateprocedure : " & vbCrLf & Err.Description
    Resume Exit_Handler

End Sub

All the debug.prints are showing the CORRECT information. So it is the SQL that is failing to update the tblUsers correctly. I don't get an error, it just doesn't work. I thought maybe I messed something up so I copied and pasted the original SQL statement from your sample DB (that works) into mine and it did not fix it.

 

[JMongi]

A more general question for anyone else watching this thread, what would cause an SQL update statement to fail to write to the appropriate records without throwing an error? Since it appears that the data to be updated is correct, it leads me to the the "Where" clause. If Where evaluates to nothing than no record(s) would be updated, right?

 

[JMongi]

Possible solution in the works....testing....
Edit: YES!!!!!!!!!!
So, it was me that screwed it up, but not like I thought. I reworked some of the logic to fit my application needs. That seems to be working fine though it needs more robust case testing. But, what caught me out was related to using someone else's code. I started defining variables because I thought that they were being defined in line with the code and I prefer them defined at the top of the procedure. That was an incorrect assumption on my part. They were actually global variables defined in public functions and my local definitions messed everything up. I assume that one of those local definitions of username was causing the WHERE clause on the SQL statement to evaluate to 0 records to update.

 

[JMongi]

@isladogs - I have a lot of irons in the fire today so I'm shortcutting by asking directly. I think I goofed on my code using your RC4 password login with session logging. I don't think I have a session logout. What is the code (or where in your sample db) on application quit to properly session log out? Is there a function call for it. I will look at the code in a few minutes if I don't hear from you.

 

[isladogs]

I'll leave it to you to investigate .... at least for now.
I get questions like this every week following changes made by users and it can take a huge amount of my time
As an example, there is a similar thread currently at another forum where the developer has changed many things and broken it!
In case it helps, see How To Set User Rights From Access - From Scratch (accessforums.net)

 

[JMongi]

No worries, just trying to manage my time, not trying to make you my tech support
I realized it testing it on another computer when it was locking my username out!

 

[JMongi]

Well, this became a tomorrow problem. Cheers!

 

[IDK_YOU]

thanks my brother. but i want to know. how to change the password? @isladogs

 

[theDBguy]

thanks my brother. but i want to know. how to change the password? @isladogs

Hi. Welcome to AWF!

Try manually deleting the current password from the users table and then log in.

 

[IDK_YOU]

Hi. Welcome to AWF!

Try manually deleting the current password from the users table and then log in.

what field must deleted?

 

[theDBguy]

View attachment 99805

what field must deleted?

The one that says "PWD."

 

[isladogs]

thanks my brother. but i want to know. how to change the password? @isladogs

Do you want to change user passwords or the encryption cipher?
You need to understand how the code works before attempting to make changes.

I recommend you read my Web article

Password Login

This utility has been provided to manage user passwords and also keep a user session log

www.isladogs.co.uk