Bug 31739 - Password reset from staff fails if previous expired reset-entry exists
Summary: Password reset from staff fails if previous expired reset-entry exists
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Björn Nylén
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-10-12 10:16 UTC by Björn Nylén
Modified: 2023-06-08 22:32 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00, 22.05.08, 21.11.15


Attachments
Bug 31739: Remove need to pass update param to Koha::Patron::Password::Recovery::SendPasswordRecoveryEmail (5.33 KB, patch)
2022-10-12 14:37 UTC, Björn Nylén
Details | Diff | Splinter Review
Bug 31739: Password recovery from staff fails if previous expired reset-entry exists. (5.37 KB, patch)
2022-10-18 22:20 UTC, David Nind
Details | Diff | Splinter Review
Bug 31739: Password recovery from staff fails if previous expired reset-entry exists. (5.43 KB, patch)
2022-10-21 14:18 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Björn Nylén 2022-10-12 10:16:42 UTC
Bug 30611 enabled the password reset to be initiated from the patron details page. However if there is a previous *expired* reset-entry in the db then Koha will try to insert a new entry and fail with a "duplicate key"-error.

To reproduce:
Initiate a password reset, don't complete.
Wait until expired (or modify expiry in db)
Initiate a new reset from Staff -- FAIL
Comment 1 Björn Nylén 2022-10-12 12:49:47 UTC
Will upload a patch shortly.
Comment 2 Björn Nylén 2022-10-12 14:37:38 UTC
Created attachment 141732 [details] [review]
Bug 31739:  Remove need to pass update param to Koha::Patron::Password::Recovery::SendPasswordRecoveryEmail

Bug 31739: Password recovery from staff fails if previous expired reset-entry exists.
    
SendPasswordRecoveryEmail relies on the calling script to tell if there is an existing valid recovery already. If there's an expired recovery-entry the members/notices.pl script will try to create a new entry resulting in a duplicate key error.
    
This patch fixes the bug by removing the need for the calling script to do the check since SendPasswordRecoveryEmail does the same thing anyway. SendPasswordRecoveryEmail will now use DBIx ->update_or_create instead of looking at the $update param to determine if it should update an existing entry or create a new.
    
The update param is removed from all calling scripts and test are updated.
    
To test:
1. Generate a password recovery mail for a patron
2. Let it expire.
3. Generate a new password recovery from staff to the same patron - Fail!
4: Apply patch
5. Generate a new password recovery from staff to the same patron - Success!
6. Opac password recovery flow should also work.
7. Tests pass.
    
Sponsored-by: Lund University Library
Comment 3 Björn Nylén 2022-10-12 14:42:15 UTC
Setting to Needs signoff as well.

Did the patch by changing the behaviour of SendPasswordRecoveryEmail. Could probably have made it easier but since the bug was related to the somewhat convoluted logic I chose to simplify the code instead.
Comment 4 David Nind 2022-10-18 22:20:25 UTC
Created attachment 142110 [details] [review]
Bug 31739: Password recovery from staff fails if previous expired reset-entry exists.

SendPasswordRecoveryEmail relies on the calling script to tell if there is an
existing valid recovery already. If there's an expired recovery-entry the
members/notices.pl script will try to create a new entry resulting in a duplicate
key error.

This patch fixes the bug by removing the need for the calling script to do the check as
since SendPasswordRecoveryEmail does the same thing anyway.
SendPasswordRecoveryEmail will now use DBIx ->update_or_create instead of looking at
the $update param to determine if it should update an existing entry or create a new.

The update param is removed from all calling scripts and test are updated.

To test:
1. Generate a password recovery mail for a patron
2. Let it expire.
3. Generate a new password recovery from staff to the same patron - Fail!
4: Apply patch
5. Generate a new password recovery from staff to the same patron - Success!
6. Opac password recovery flow should also work.
7. Tests pass.

Sponsored-by: Lund University Library

Signed-off-by: David Nind <david@davidnind.com>
Comment 5 David Nind 2022-10-18 22:22:21 UTC
Comment on attachment 141732 [details] [review]
Bug 31739:  Remove need to pass update param to Koha::Patron::Password::Recovery::SendPasswordRecoveryEmail

I obsoleted the first patch - for whatever reason, when I signed it off there were two patches.
Comment 6 David Nind 2022-10-18 22:24:51 UTC
Testing notes (using koha-testing-docker):

1. For a patron category (for example, Patron (PT)), set 'Password reset in OPAC' to 'Allowed' (Administration > Patrons and circulation > Patron categories).

2. Enable system to send emails (for testing, using Gmail) and restart everything (flush_memcached, restart_all):
   - You need a valid SMTP configuration in koha-conf.xml.
   - Generate an 'app password' for your Gmail account.
   - Edit koha-conf.xml and add this configuration (YOUR APP PASSWORD = the app password):
     <smtp_server>
       <host>smtp.gmail.com</host>
       <port>587</port>
       <timeout>5</timeout>
       <ssl_mode>STARTTLS</ssl_mode>
       <user_name>YOUR GMAIL ADDRESS</user_name>
       <password>YOUR APP PASSWORD</password>
       <debug>1</debug>
     </smtp_server>

3. Update the details for a patron:
   - add your Gmail address to a patron (for example: YOUR GMAIL ADDRESS for Mary Burton, borrower number 49)
   - update the OPAC/Staff interface login username (for example: mary)

4. Initiate a password reset from the OPAC:
   . From the OPAC home page, click on 'Forgot your password' below the log in box
   . Enter the username for the login and press 'Submit'

5. Update the expiry date for the password reset in the database so that it is expired:
   - List password recovery requests:
     . koha-mysql kohadev
     . select * from borrower_password_recovery;
   - Update the expiry date:
     . update borrower_password_recovery set valid_until = "2022-10-18 00:00:00" where borrowernumber=49;

6. Go to the patron's details page in the staff interface and select More > Send password reset:
   ==> You get an error trace: 
       DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Duplicate entry '49' for key 'PRIMARY' at /kohadevbox/koha/Koha/Patron/Password/Recovery.pm line 132  at /usr/share/perl5/DBIx/Class/Exception.pm line 77

7. Apply the patch and restart everything (flush_memcached, restart_all).

8. Send password reset from the staff interface again - it should now work (email received and 'Koha password reset' notice under notices).

9. Re-test the forgotten password recovery process - steps 4, 5 and 8. Everything should work as expected:
   . Note that for step 4 you may get an error message "The process of password recovery has already been started for this account ("mary")...". Click on the link 'Get new password recovery link'.

10. Tests should pass: prove t/db_dependent/Passwordrecovery.t
Comment 7 Kyle M Hall 2022-10-21 14:18:51 UTC
Created attachment 142399 [details] [review]
Bug 31739: Password recovery from staff fails if previous expired reset-entry exists.

SendPasswordRecoveryEmail relies on the calling script to tell if there is an
existing valid recovery already. If there's an expired recovery-entry the
members/notices.pl script will try to create a new entry resulting in a duplicate
key error.

This patch fixes the bug by removing the need for the calling script to do the check as
since SendPasswordRecoveryEmail does the same thing anyway.
SendPasswordRecoveryEmail will now use DBIx ->update_or_create instead of looking at
the $update param to determine if it should update an existing entry or create a new.

The update param is removed from all calling scripts and test are updated.

To test:
1. Generate a password recovery mail for a patron
2. Let it expire.
3. Generate a new password recovery from staff to the same patron - Fail!
4: Apply patch
5. Generate a new password recovery from staff to the same patron - Success!
6. Opac password recovery flow should also work.
7. Tests pass.

Sponsored-by: Lund University Library

Signed-off-by: David Nind <david@davidnind.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 8 Tomás Cohen Arazi 2022-10-24 17:41:50 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 9 Lucas Gass 2022-12-05 22:39:52 UTC
backported to 22.05.x for upcoming 22.05.08
Comment 10 Arthur Suzuki 2022-12-16 15:42:24 UTC
applied to 21.11 for 21.11.15