https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33905
--- Comment #10 from Tomás Cohen Arazi (tcohen) <[email protected]> --- Created attachment 183664 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=183664&action=edit Bug 33905: Add database triggers to prevent cardnumber/userid interchange This patch adds database triggers to prevent users from having cardnumbers that match other users' userids and vice versa. This prevents potential security issues and user confusion where a patron might accidentally or maliciously use another patron's cardnumber as their userid. The solution implementation includes: * Database triggers: - trg_borrowers_cardnumber_userid_insert: Prevents INSERT operations that would create conflicts - trg_borrowers_cardnumber_userid_update: Prevents UPDATE operations that would create conflicts * Exception-style error messages: - Koha::Exceptions::Patron::CardnumberMatchesUserID: When cardnumber matches another user's userid - Koha::Exceptions::Patron::UserIDMatchesCardnumber: When userid matches another user's cardnumber * New trigger_exists() utility function in C4::Installer: - Uses standard information_schema.triggers table for portability - Returns 1 if trigger exists, 0 if it doesn't - Includes proper POD documentation - Available for other atomicupdates to use * Database structure updates: - atomicupdate for existing installations - kohastructure.sql updates for new installations It uses database triggers instead of CHECK constraints because CHECK constraints with subqueries are not portable between MySQL/MariaDB and PostgreSQL. The exception messages follow Koha's exception naming patterns, making them easy to catch and handle in application code. TODO: * Exception handling is not implemented in Koha::Patron->store() * Exceptions are not defined yet * There's already Koha::Patron->has_valid_userid which should be adjusted to not look for duplicates and its tests should be adapted. * Some existing tests should fail and will need tweaks * I haven't checked if the TRIGGER definitions fail if existing inconsistencies are found. We should probably add a check in the atomicupdate before an attempt to change the DB. Test plan: 1. Apply the patches 2. Update the DB structure: $ ktd --shell k$ updatedatabase => SUCCESS: It works :-D 3. Run: k$ prove t/db_dependent/Patrons_cardnumber_userid_constraint.t 4. Verify that attempts to create conflicting cardnumber/userid combinations throw the appropriate exceptions: - Koha::Exceptions::Patron::CardnumberMatchesUserID - Koha::Exceptions::Patron::UserIDMatchesCardnumber 5. Verify that valid operations still work normally 6. Test fresh installations to ensure triggers are created: k$ reset_all => SUCCESS: Triggers are created 7. Sign off :-D -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
