Case insensitive username not working when Multi-attribute Login enabled

[Tiffany-silva]

Describe the issue: Upon enabling Multi-attribute login, when the username is provided in uppercase during login, it results in a user not found error. Meaning, although the user store is configured as case insensitive, it checks as case sensitive. How to reproduce:

1. Enable Multi-Attribute Login with telephone/email/username (either two).
2. Try out the login or Recovery flow with uppercase characters.
3. Results in user not found (carbon logs).

Expected behavior: Upon providing username in uppercase, the user should be able to login when caseinsensitive configuration is added when Multi-attribute login is enabled.

Environment information

• Product Version: IS 5.10.0,
• Database: [PostgreSQL]
• Userstore: [JDBC]

**Work around*** Configure the following query for GetUserLisForPropertyWithIDSQL user store property, GetUserLisForPropertyWithIDSQL = "SELECT DISTINCT UM_USER.UM_USER_ID FROM UM_USER, UM_USER_ATTRIBUTE WHERE UM_USER_ATTRIBUTE.UM_USER_ID = UM_USER.UM_ID AND UM_USER_ATTRIBUTE.UM_ATTR_NAME =? AND LOWER(UM_USER_ATTRIBUTE.UM_ATTR_VALUE) LIKE LOWER(?) AND UM_USER_ATTRIBUTE.UM_PROFILE_ID=? AND UM_USER_ATTRIBUTE.UM_TENANT_ID=? AND UM_USER.UM_TENANT_ID=?"

[mifrazmurthaja]

This should also be fixed for GetUserListForClaimValueWithIDSQL.

Steps to reproduce:

1. Create a user with lower case email address

   curl --location 'https://localhost:9443/api/identity/user/v1.0/me' \
   --header 'Content-Type: application/json' \
   --header 'Authorization: Basic YWRtaW46YWRtaW4=' \
   --data-raw '{
       "user": {
           "username": "xcv124",
           "realm": "US2",
           "password": "pa$$w0rd",
           "claims": [
               {
                   "uri": "http://wso2.org/claims/emailaddress",
                   "value": "user1@mail.com"
               }
           ]
       },
       "properties": []
   }'

2. Create a user with an upper case email address.

   curl --location 'https://localhost:9443/api/identity/user/v1.0/me' \
   --header 'Content-Type: application/json' \
   --header 'Authorization: Basic YWRtaW46YWRtaW4=' \
   --data-raw '{
       "user": {
           "username": "xcv124",
           "realm": "US2",
           "password": "pa$$w0rd",
           "claims": [
               {
                   "uri": "http://wso2.org/claims/emailaddress",
                   "value": "User1@mail.com"
               }
           ]
       },
       "properties": []
   }'

3. Both the users will be created. However, if the CaseInsensitiveUsername is enabled, the 2nd user creation should be failed.

Workaround:

GetUserListForClaimValueWithIDSQL = "SELECT DISTINCT UM_USER.UM_USER_ID FROM UM_USER, UM_USER_ATTRIBUTE WHERE UM_USER_ATTRIBUTE.UM_USER_ID = UM_USER.UM_ID AND UM_USER_ATTRIBUTE.UM_ATTR_NAME =? AND LOWER(UM_USER_ATTRIBUTE.UM_ATTR_VALUE)=LOWER(?) AND UM_USER_ATTRIBUTE.UM_PROFILE_ID=? AND UM_USER_ATTRIBUTE.UM_TENANT_ID=? AND UM_USER.UM_TENANT_ID=?"

[hwupathum]

This issue is not reproducible in IS 5.11.0 and IS 7.0.0 for username, but happens for emailaddress

Describe the issue: Upon enabling Multi-attribute login, when the username is provided in uppercase during login, it results in a user not found error. Meaning, although the user store is configured as case insensitive, it checks as case sensitive. How to reproduce:

1. Enable Multi-Attribute Login with telephone/email/username (either two).
2. Try out the login or Recovery flow with uppercase characters.
3. Results in user not found (carbon logs).

Expected behavior: Upon providing username in uppercase, the user should be able to login when caseinsensitive configuration is added when Multi-attribute login is enabled.

Environment information

• Product Version: IS 5.10.0,
• Database: [PostgreSQL]
• Userstore: [JDBC]

**Work around*** Configure the following query for GetUserLisForPropertyWithIDSQL user store property, GetUserLisForPropertyWithIDSQL = "SELECT DISTINCT UM_USER.UM_USER_ID FROM UM_USER, UM_USER_ATTRIBUTE WHERE UM_USER_ATTRIBUTE.UM_USER_ID = UM_USER.UM_ID AND UM_USER_ATTRIBUTE.UM_ATTR_NAME =? AND LOWER(UM_USER_ATTRIBUTE.UM_ATTR_VALUE) LIKE LOWER(?) AND UM_USER_ATTRIBUTE.UM_PROFILE_ID=? AND UM_USER_ATTRIBUTE.UM_TENANT_ID=? AND UM_USER.UM_TENANT_ID=?"