Case-sensitive redirect_uris with case-insensitive DB uniqueness

[nosnilmot]

Before reporting an issue

• I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.

Area

core

Describe the bug

#25001 made redirect URIs completely case-sensitive, so scheme://home is different from Scheme://home

To support both cases for a client now requires two permitted redirect URIs to be added.

Some Keycloak DB schemas (MySQL at least) use case-insensitive comparisons, so attempting to insert two redirect URIs that are identical in everything execpt case results in unique key violation:

mysql> select * from redirect_uris where value like 'scheme%';
+--------------------------------------+---------------+
| CLIENT_ID                            | VALUE         |
+--------------------------------------+---------------+
| 2a4bda63-9429-4ce6-9fd7-e442af2d116f | Scheme://home |
+--------------------------------------+---------------+
1 row in set (0.00 sec)

mysql> insert into redirect_uris values ('2a4bda63-9429-4ce6-9fd7-e442af2d116f', 'scheme://home');
ERROR 1062 (23000): Duplicate entry '2a4bda63-9429-4ce6-9fd7-e442af2d116f-scheme://home' for key 'redirect_uris.PRIMARY'

Attempting to do this using Keycloak UI (incorrectly) reports "Client could not be updated: Client already exists" as a result of the duplicate key exception:

2024-05-10 11:02:59,672 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-47) SQL Error: 1062, SQLState: 23000
2024-05-10 11:02:59,674 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (executor-thread-47) Duplicate entry '2a4bda63-9429-4ce6-9fd7-e442af2d116f-scheme://home' for key 'redirect_uris.PRIMARY'

I see Keycloak 25 will have more changes in this area (#28872), but it does not look like that will help here.

Version

24.0.3

Regression

• The issue is a regression

Expected behavior

able to add multiple redirect URIs that differ only by case

Actual behavior

additional redirect URIs rejected if DB is MySQL (and maybe others)

How to Reproduce?

1. Install Keycloak with MySQL DB
2. Create client with permitted redirect URI (eg. 'Scheme://home')
3. Attempt to add second permitted redirect URI that is same as first but differs only by case (eg. 'scheme://home')

Anything else?

frankly, the requirement that redirect URIs exactly match without allowing for case-insensitivity in at least the scheme seems overly restrictive

[rmartinc]

Hi @nosnilmot!

This is because your database is created with a ci collate. In general if you had needed capitals in any other part of the URI before you would have had the same problem (for example redirect URI http://localhost/sample and http://localhost/Sample, only one can be added in your case). If you have the same collate for all the tables I suppose you also have the same problem for group names (you cannot create a group called Group and a group called group), role names,... I would try to change the collate for that table. For example, if using utf8, changing utf8_general_ci to utf8_bin:

alter table REDIRECT_URIS MODIFY VALUE varchar(255) CHARACTER SET utf8 COLLATE utf8_bin;

Please check what charset / collate you are using before changing anything.

Regards!