[OPENIDM-6657] Describe how to handle case sensitivity for queries Created: 16/Sep/16  Updated: 16/May/18  Resolved: 16/May/18

Status: Closed
Project: OpenIDM
Component/s: documentation
Affects Version/s: OpenIDM 4.0.0, OpenIDM 4.5.0
Fix Version/s: 6.5.0

Type: Improvement Priority: Minor
Reporter: Tom Wood Assignee: Lana Frost
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Target Version/s:
Story Points: 1
Sprint: OpenIDM Sprint 6.5-2
Cases: 15291, 15483
Support Ticket IDs:


Forgotten Username and Reset Functionality requires user to perfectly match their search with the case of their userName, e.g. searching for 'BOB@BOB.COM' would not find a user whose email address was 'bob@bob.com'.


1. Start OpenIDM 4.0.0/4.5.0 with Sample 1
2. Import the sample data from the XML file (curl -u openidm-admin:openidm-admin -H "Content-Type: application/json" -X POST "http://localhost:8080/openidm/recon?_action=recon&mapping=systemXmlfileAccounts_managedUser&waitForCompletion=true")
3. Log in to the Admin UI as openidm-admin and enable Forgotten Username/Password Reset
4. Log out, switch to Self Service UI
5. Attempt to reset password using 'scarter@EXAMPLE.COM' rather than 'scarter@example.com'

When using 'scarter@example.com', an error relating to external/email will be returned (as expected, this has not been configured).

When using 'scarter@EXAMPLE.COM', an 'Unable to find account' error will be returned.

According to RFC 1035, Section 3.1 (re-Domain Names):

Name servers and resolvers must compare labels in a case-insensitive manner (i.e., A=a), assuming ASCII with zero parity.  Non-alphabetic codes must match exactly.

Comment by Jake Feasel [ 23/Sep/16 ]

This isn't really a bug in OpenIDM - it is purely based on the configured database repository collation. See comments here: https://bugster.forgerock.org/jira/browse/OPENIDM-2684?focusedCommentId=44096&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-44096 and the mailing list here: https://lists.forgerock.org/pipermail/openidm/2014-November/003742.html

Comment by Tom Wood [ 26/Sep/16 ]

Agreed - although as we're providing the schema information, we should also be documenting how to handle this (imo).

The link Laurent provides in the above (https://forgerock.org/openidm/doc/bootstrap/integrators-guide/index.html#case-sensitivity) refers only to the case sensitivity of synchronisation mappings, but cannot be applied to how queryFilter responses can be modified.

Happy for this to be a documentation change, rather than anything code based.

Comment by Brendan Miller [ 07/Jun/17 ]

Two workarounds:

1) Modify UI template to either lower-case or upper-case input to match backend expecation

2) Modify Database backend to store data case-insensitively.

Can also document these cc Mike Jang [X]

Comment by Mike Jang [X] (Inactive) [ 07/Jun/17 ]

Brendan Miller Already doc'd case sensitivity in this section: https://ea.forgerock.com/docs/openidm/doc/backstage/integrators-guide/index.html#case-sensitivity

Comment by Brendan Miller [ 05/Mar/18 ]

Tom Wood Is this doc explanation sufficient?

Comment by Tom Wood [ 13/Apr/18 ]

Mike Jang [X] - I assume you're referring to this bit:

Use a case-insensitive option from your datastore. For example, in MySQL, you can change the collation of managedobjectproperties.propvalue to utf8_general_ci.

Is there any way to make this clearer? I missed it on my first read through! If we're going to reference the MySQL specific change, could we also provide information on other DB types (we get a fair few tickets on this) such as the CITEXT extension for Postgres?

Comment by Mike Jang [X] (Inactive) [ 13/Apr/18 ]

Hi Tom Wood, I see how it's easy to miss.

it sounds like we need at least an intro with a link from our Install Guide repo chapter.

I'd want to move the MySQL-specific reference to that chapter, possibly the MySQL section.

Unless we have case-sensitivity rules for other repos easily available, I'd refer to the respective docs for those repos.

if this makes sense, I'll take responsibility for this JIRA, and if you think appropriate, backport as needed.

Comment by Jake Feasel [ 13/Apr/18 ]

I don't think we actually have a proven solution for the citext module for postgres. Probably the best recommendation there is actually to change to an explicit table structure. Getting case insensitivity to work at scale with generic tables in PG is probably not possible.

Comment by Lana Frost [ 16/May/18 ]




Comment by Laurent Bristiel [X] (Inactive) [ 16/May/18 ]

checked OK

Generated at Sun Sep 27 19:27:43 UTC 2020 using Jira 7.13.12#713012-sha1:6e07c38070d5191bbf7353952ed38f111754533a.