Handle SCIM filter predicate case-insensitive matching correctly by elhichaouiYassine · Pull Request #51530 · keycloak/keycloak

elhichaouiYassine · 2026-08-06T23:18:31Z

Logs:

email:

2026-08-06 23:28:45,295 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [85dcdb5e-9ca1-4809-8a89-12e033c819f4]                                             
2026-08-06 23:28:45,295 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (2:VARCHAR) <- [local]                                                                            
2026-08-06 23:28:45,295 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (3:VARCHAR) <- [local]                                                                            
2026-08-06 23:28:45,295 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (4:VARCHAR) <- [0]                                                                                
2026-08-06 23:28:45,295 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (5:VARCHAR) <- [53319e98-ac4c-4daa-a23c-0e2faef1d5d2]                                             
2026-08-06 23:28:45,297 DEBUG [org.hibernate.SQL] (executor-thread-8) select distinct ue1_0.ID,ue1_0.CREATED_TIMESTAMP,ue1_0.EMAIL,ue1_0.EMAIL_CONSTRAINT,ue1_0.EMAIL_VERIFIED,ue1_0.ENABLED,ue1_0.FEDERATION_LINK,ue1_0.FIRST_NAME,ue1_0.LAST_MODIFIED_TIMESTAMP,ue1_0.LAST_NAME,ue1_0.NOT_BEFORE,ue1_0.REALM_ID,ue1_0.SERVICE_ACCOUNT_CLIENT_LINK,ue1_0.USERNAME from USER_ENTITY ue1_0 where ue1_0.EMAIL=? and ue1_0.SERVICE_ACCOUNT_CLIENT_LINK is null and ue1_0.REALM_ID=? order by ue1_0.USERNAME fetch first ? rows only                                                                            
2026-08-06 23:28:45,297 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [jdoe@example.com]                                                                 
2026-08-06 23:28:45,297 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (2:VARCHAR) <- [89636bc9-6465-4dd4-b4bf-d5d91c465c7d]                                             
2026-08-06 23:28:45,297 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (3:INTEGER) <- [100]                                                                              
2026-08-06 23:28:45,298 DEBUG [org.hibernate.SQL] (executor-thread-8) select urme1_0.ROLE_ID from USER_ROLE_MAPPING urme1_0 where urme1_0.USER_ID=?                                                 
2026-08-06 23:28:45,298 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:28:45,298 DEBUG [org.hibernate.SQL] (executor-thread-8) select ugme1_0.GROUP_ID from USER_GROUP_MEMBERSHIP ugme1_0 where ugme1_0.USER_ID=?                                            
2026-08-06 23:28:45,298 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:28:45,299 DEBUG [org.hibernate.SQL] (executor-thread-8) select a1_0.USER_ID,a1_0.ID,a1_0.LONG_VALUE,a1_0.LONG_VALUE_HASH,a1_0.LONG_VALUE_HASH_LOWER_CASE,a1_0.NAME,a1_0.VALUE from USER_ATTRIBUTE a1_0 where a1_0.USER_ID=?                                                                                                                                                               
2026-08-06 23:28:45,299 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:28:45,301 DEBUG [org.hibernate.SQL] (executor-thread-8) select count(distinct ue1_0.ID) from USER_ENTITY ue1_0 where ue1_0.EMAIL=? and ue1_0.SERVICE_ACCOUNT_CLIENT_LINK is null and ue1_0.REALM_ID=?

username:

2026-08-06 23:30:51,194 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [85dcdb5e-9ca1-4809-8a89-12e033c819f4]                                             
2026-08-06 23:30:51,195 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (2:VARCHAR) <- [local]                                                                            
2026-08-06 23:30:51,195 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (3:VARCHAR) <- [local]                                                                            
2026-08-06 23:30:51,195 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (4:VARCHAR) <- [0]                                                                                
2026-08-06 23:30:51,195 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (5:VARCHAR) <- [475d6e35-6339-4106-ae8c-24aead0e3eb5]                                             
2026-08-06 23:30:51,196 DEBUG [org.hibernate.SQL] (executor-thread-8) select distinct ue1_0.ID,ue1_0.CREATED_TIMESTAMP,ue1_0.EMAIL,ue1_0.EMAIL_CONSTRAINT,ue1_0.EMAIL_VERIFIED,ue1_0.ENABLED,ue1_0.FEDERATION_LINK,ue1_0.FIRST_NAME,ue1_0.LAST_MODIFIED_TIMESTAMP,ue1_0.LAST_NAME,ue1_0.NOT_BEFORE,ue1_0.REALM_ID,ue1_0.SERVICE_ACCOUNT_CLIENT_LINK,ue1_0.USERNAME from USER_ENTITY ue1_0 where ue1_0.USERNAME=? and ue1_0.SERVICE_ACCOUNT_CLIENT_LINK is null and ue1_0.REALM_ID=? order by ue1_0.USERNAME fetch first ? rows only                                                                         
2026-08-06 23:30:51,196 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:NVARCHAR) <- [jdoe]                                                                            
2026-08-06 23:30:51,196 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (2:VARCHAR) <- [89636bc9-6465-4dd4-b4bf-d5d91c465c7d]                                             
2026-08-06 23:30:51,196 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (3:INTEGER) <- [100]                                                                              
2026-08-06 23:30:51,197 DEBUG [org.hibernate.SQL] (executor-thread-8) select urme1_0.ROLE_ID from USER_ROLE_MAPPING urme1_0 where urme1_0.USER_ID=?                                                 
2026-08-06 23:30:51,197 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:30:51,197 DEBUG [org.hibernate.SQL] (executor-thread-8) select ugme1_0.GROUP_ID from USER_GROUP_MEMBERSHIP ugme1_0 where ugme1_0.USER_ID=?                                            
2026-08-06 23:30:51,197 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:30:51,198 DEBUG [org.hibernate.SQL] (executor-thread-8) select a1_0.USER_ID,a1_0.ID,a1_0.LONG_VALUE,a1_0.LONG_VALUE_HASH,a1_0.LONG_VALUE_HASH_LOWER_CASE,a1_0.NAME,a1_0.VALUE from USER_ATTRIBUTE a1_0 where a1_0.USER_ID=?                                                                                                                                                               
2026-08-06 23:30:51,198 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-8) binding parameter (1:VARCHAR) <- [304fa8f0-3756-4da9-9ea8-de838c25e238]                                             
2026-08-06 23:30:51,200 DEBUG [org.hibernate.SQL] (executor-thread-8) select count(distinct ue1_0.ID) from USER_ENTITY ue1_0 where ue1_0.USERNAME=? and ue1_0.SERVICE_ACCOUNT_CLIENT_LINK is null and ue1_0.REALM_ID=?

group name:

2026-08-06 23:39:14,474 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (1:VARCHAR) <- [85dcdb5e-9ca1-4809-8a89-12e033c819f4]                                            
2026-08-06 23:39:14,474 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (2:VARCHAR) <- [local]                                                                           
2026-08-06 23:39:14,474 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (3:VARCHAR) <- [local]                                                                           
2026-08-06 23:39:14,474 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (4:VARCHAR) <- [0]                                                                               
2026-08-06 23:39:14,474 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (5:VARCHAR) <- [mL1ZvrBCmNwEvuuFNEEGL9g7]                                                        
2026-08-06 23:39:17,676 DEBUG [org.hibernate.SQL] (executor-thread-12) select pcse1_0.CLIENT_ID,pcse1_0.CLIENT_STORAGE_PROVIDER,pcse1_0.EXTERNAL_CLIENT_ID,pcse1_0.OFFLINE_FLAG,pcse1_0.USER_SESSION_ID,pcse1_0.DATA,pcse1_0.REALM_ID,pcse1_0.TIMESTAMP,pcse1_0.VERSION from OFFLINE_CLIENT_SESSION pcse1_0 where (pcse1_0.CLIENT_ID,pcse1_0.CLIENT_STORAGE_PROVIDER,pcse1_0.EXTERNAL_CLIENT_ID,pcse1_0.OFFLINE_FLAG,pcse1_0.USER_SESSION_ID) in ((?,?,?,?,?))                                                                                                                                              
2026-08-06 23:39:17,676 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (1:VARCHAR) <- [85dcdb5e-9ca1-4809-8a89-12e033c819f4]                                            
2026-08-06 23:39:17,676 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (2:VARCHAR) <- [local]                                                                           
2026-08-06 23:39:17,676 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (3:VARCHAR) <- [local]                                                                           
2026-08-06 23:39:17,676 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (4:VARCHAR) <- [0]                                                                               
2026-08-06 23:39:17,676 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (5:VARCHAR) <- [7b83ae2a-7318-4d36-9430-ce5b2ad03dc3]                                            
2026-08-06 23:39:17,678 DEBUG [org.hibernate.SQL] (executor-thread-12) select distinct ge1_0.ID,ge1_0.CREATED_TIMESTAMP,ge1_0.DESCRIPTION,ge1_0.LAST_MODIFIED_TIMESTAMP,ge1_0.NAME,ge1_0.ORG_ID,ge1_0.PARENT_GROUP,ge1_0.REALM_ID,ge1_0.TYPE from KEYCLOAK_GROUP ge1_0 where lower(ge1_0.NAME)=? and ge1_0.REALM_ID=? and ge1_0.TYPE=? and ge1_0.PARENT_GROUP=? order by ge1_0.NAME fetch first ? rows only                                                                                                                                                                                                 
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (1:VARCHAR) <- [y]                                                                               
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (2:VARCHAR) <- [89636bc9-6465-4dd4-b4bf-d5d91c465c7d]                                            
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (3:INTEGER) <- [0]                                                                               
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (4:VARCHAR) <- [ ]                                                                               
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (5:INTEGER) <- [100]                                                                             
2026-08-06 23:39:17,678 DEBUG [org.hibernate.SQL] (executor-thread-12) select grme1_0.ROLE_ID from GROUP_ROLE_MAPPING grme1_0 where grme1_0.GROUP_ID=?                                              
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (1:VARCHAR) <- [87ce3a59-78f0-4c00-91e7-5e65e3f4d549]                                            
2026-08-06 23:39:17,678 DEBUG [org.hibernate.SQL] (executor-thread-12) select a1_0.GROUP_ID,a1_0.ID,a1_0.NAME,a1_0.VALUE from GROUP_ATTRIBUTE a1_0 where a1_0.GROUP_ID=?                            
2026-08-06 23:39:17,678 TRACE [org.hibernate.orm.jdbc.bind] (executor-thread-12) binding parameter (1:VARCHAR) <- [87ce3a59-78f0-4c00-91e7-5e65e3f4d549]                                            
2026-08-06 23:39:17,679 DEBUG [org.hibernate.SQL] (executor-thread-12) select count(distinct ge1_0.ID) from KEYCLOAK_GROUP ge1_0 where lower(ge1_0.NAME)=? and ge1_0.REALM_ID=? and ge1_0.TYPE=? and ge1_0.PARENT_GROUP=?

Copilot

Pull request overview

Adds storage-aware case-insensitive SCIM filtering while preserving database index usage.

Changes:

Marks usernames and emails as lowercase-stored.
Normalizes filter values without wrapping lowercase-stored columns in LOWER().
Adds case-insensitive user search coverage.

Reviewed changes

Copilot reviewed 4 out of 4 changed files in this pull request and generated 1 comment.

File	Description
`scim/core/.../Attribute.java`	Adds lowercase-storage metadata.
`scim/model/.../ScimJPAPredicateProvider.java`	Applies storage-aware predicate normalization.
`scim/model/.../UserCoreModelSchema.java`	Marks usernames and emails lowercase-stored.
`scim/tests/.../UserTest.java`	Tests case-insensitive email and username searches.

sguilhen · 2026-08-07T16:07:10Z

+        assertEquals(expected.getEmail().toLowerCase(), actual.getEmail().toLowerCase());
+
+        // userName is case-insensitive per RFC 7643
+        result = client.users().search("userName eq \"" + expected.getUserName().toUpperCase() + "\"").getResources();


Not applicable - verified manually against Postgres with EXPLAIN ANALYZE.

Copilot

Pull request overview

Copilot reviewed 3 out of 3 changed files in this pull request and generated no new comments.

Suppressed comments (1)

scim/model/src/main/java/org/keycloak/scim/model/filter/ScimJPAPredicateProvider.java:147

Please add integration coverage for mixed-case userName and emails.value filters. The existing FilterTest only uses lowercase values, so the regression this branch changes and the new storedLowerCase path are not verified.

        if (value != null && (attrInfo.isStoredLowerCase() || !attrInfo.isCaseExact())) {
            value = value.toString().toLowerCase();
            if (!attrInfo.isStoredLowerCase()) {
                expression = cb.lower((Expression<String>) expression);
            }

sguilhen

LGTM - solid fix for both the index bypass and the email case-sensitivity bug.

Verified against Postgres with a test realm:

Query pattern	Index usable?	Result
`username = ?` (with fix)	Yes - `(realm_id, username)` unique index	Correct
`LOWER(username) = ?` (old)	No - bypasses index, forces seq scan	Correct but slow
`email = lower(?)` (with fix)	Yes - `idx_user_email`	Correct
`email = 'UPPER@CASE'` (old)	N/A	Bug: 0 rows

End-to-end SCIM filter calls with mixed-case input (userName eq "TESTUSER1", emails eq "UPPER@CASE", emails.value eq "UPPER@CASE") all return the expected results.

Two minor observations:

Naming inconsistency: the setter is setStoredLowercase() (lowercase 'c') while the field is storedLowerCase, the getter is isStoredLowerCase(), and the builder method is storedLowerCase(). Should be setStoredLowerCase() to match.
Test coverage for the email fix: bug (b) is a functional correctness change - emails eq "UPPERCASE@EMAIL" previously returned 0 results. The existing FilterTest.testFilterByEmail only uses lowercase email values, so the new behavior isn't covered. A test filtering with a mixed-case email value would lock this in.

Closes keycloak#51401 Signed-off-by: elhichaouiYassine <etu_elhichaoui.yassine@emsi-edu.ma>

Copilot

Pull request overview

Copilot reviewed 4 out of 4 changed files in this pull request and generated no new comments.

sguilhen · 2026-08-08T01:25:58Z

Thanks for the fix, @elhichaouiYassine .

@pedroigor can you run the CI for this one?

ahus1 · 2026-08-08T13:17:28Z

CI is now running

Copilot AI balanced review requested due to automatic review settings August 6, 2026 23:18

elhichaouiYassine requested a review from a team as a code owner August 6, 2026 23:18

keycloak-github-bot Bot added the team/core-iam label Aug 6, 2026

elhichaouiYassine force-pushed the issue/51401 branch from 04ea69b to da3c5c7 Compare August 6, 2026 23:24

Copilot started reviewing on behalf of elhichaouiYassine August 6, 2026 23:32 View session

Copilot AI reviewed Aug 6, 2026

View reviewed changes

Copilot AI review requested due to automatic review settings August 7, 2026 11:21

elhichaouiYassine force-pushed the issue/51401 branch from da3c5c7 to 7f088c8 Compare August 7, 2026 11:21

Copilot started reviewing on behalf of elhichaouiYassine August 7, 2026 11:22 View session

Copilot AI reviewed Aug 7, 2026

View reviewed changes

sguilhen approved these changes Aug 7, 2026

View reviewed changes

Handle SCIM filter predicate case-insensitive matching correctly

0924d70

Closes keycloak#51401 Signed-off-by: elhichaouiYassine <etu_elhichaoui.yassine@emsi-edu.ma>

Copilot AI review requested due to automatic review settings August 7, 2026 16:30

elhichaouiYassine force-pushed the issue/51401 branch from 7f088c8 to 0924d70 Compare August 7, 2026 16:30

Copilot started reviewing on behalf of elhichaouiYassine August 7, 2026 16:30 View session

Copilot AI reviewed Aug 7, 2026

View reviewed changes

sguilhen approved these changes Aug 8, 2026

View reviewed changes

Navigation Menu

Uh oh!

Handle SCIM filter predicate case-insensitive matching correctly - #51530

Handle SCIM filter predicate case-insensitive matching correctly#51530
elhichaouiYassine wants to merge 1 commit into
keycloak:mainfrom
elhichaouiYassine:issue/51401

elhichaouiYassine commented Aug 6, 2026

Uh oh!

Copilot AI left a comment

Uh oh!

sguilhen Aug 7, 2026

Uh oh!

Copilot AI left a comment

Uh oh!

sguilhen left a comment

Uh oh!

Copilot AI left a comment

Uh oh!

sguilhen commented Aug 8, 2026

Uh oh!

ahus1 commented Aug 8, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Uh oh!

Conversation

elhichaouiYassine commented Aug 6, 2026

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

sguilhen Aug 7, 2026

Choose a reason for hiding this comment

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

sguilhen left a comment

Choose a reason for hiding this comment

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Uh oh!

sguilhen commented Aug 8, 2026

Uh oh!

ahus1 commented Aug 8, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants