Bug 38983

Summary: Foreign key constraints are case insensitive so can lead to erroneous data input via the API
Product: Koha Reporter: Martin Renvoize (ashimema) <martin.renvoize>
Component: REST APIAssignee: Bugs List <koha-bugs>
Status: CONFIRMED --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: nick, tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Martin Renvoize (ashimema) 2025-01-28 10:16:51 UTC
This one is fun...

You can input randomly cases branchcodes via the API for patrons without receiving an error.  This appears to be because sql's foreign key constraints aren't being triggered.  This is fine in the database, but as soon as we start returning the data via API javascript is case sensitive in the UI and can crash

To replicate, try adding a library with the code `CAPITALS` and then add a user via the API with a library_id of `Capitals`.  If you then search for that user, the patron search table will fail to load and you'll get errors in the console.
Comment 1 Tomás Cohen Arazi (tcohen) 2025-01-28 11:26:56 UTC
I think the only workaround is to force upper case at the API level, which is what the UI does?


```yaml
attribute:
    type: string
    pattern: "^[A-Z]+$"
```
Comment 2 Martin Renvoize (ashimema) 2025-02-19 14:17:19 UTC
That seems like a reasonable approach to me :)