JDBC attribute SQL query

[Parker Grimes]

I recently set up a JDBC attributeRepository and my SQL query expects the username passed from CAS to be lowercase. Does anyone know how to either override the "WHERE {0}" or make sure the username being passed is lowercase?

The problem we have is people entering their username in mixed case like "UserName" instead of "username" and "UserName" is being passed to the "SELECT * FROM user_table WHERE {0}"

From the logs:

DEBUG [org.apereo.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDao] - <Generated query builder 'sql=[username = ?] args=[UserName]' from query Map {username=[UserName]}.>

[Kink]

Hi Parker,

What about changing the collation of your DB/TABLE/COLUMN to a case insensitive (CI) one?

MYSQL seems to do that easily:

ALTER TABLE user_table MODIFY COLUMN username VARCHAR(…) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI

MSSQL would be something like:

ALTER TABLE dbo.user_table

ALTER COLUMN username VARCHAR(...) COLLATE Latin1_General_100_CI_AI_SC_UTF8

In both case, just select the collation that fits your needs, a CI one for Case Insensitive.

If using another DB, you should have the same feature available...

Pierre

[Jason Everling]

you could also just use a few lines of javascript on your login page to first convert the username input to lowercase before posting, easier then messing with your db if it is used for some other applications

---

From: cas-...@apereo.org <cas-...@apereo.org> on behalf of Kink <igcsecr...@gmail.com>
Sent: Friday, July 10, 2020 2:16:43 AM
To: CAS Community <cas-...@apereo.org>
Subject: [cas-user] Re: JDBC attribute SQL query

 

--
- Website: https://apereo.github.io/cas
- Gitter Chatroom: https://gitter.im/apereo/cas
- List Guidelines: https://goo.gl/1VRrw7
- Contributions: https://goo.gl/mh7qDG
---
You received this message because you are subscribed to the Google Groups "CAS Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/1134582a-46a3-4908-b245-834e52a6d303o%40apereo.org.

[Richard Frovarp]

cas.authn.attribute-repository.jdbc[0].case-canonicalization=NONE|LOWER|UPPER

It's been a while since we haven't used canonicalization, so I can't remember the details (like CAS 3.4). However, it used to be that CAS would return back the username attribute case matching whatever the user typed in. So if the end application wasn't doing anything, it could get different casings of the same user. I will admit to writing code that then failed because it wasn't expecting a different case. I always typed my username in all lower case, so it wasn't expect by me. You can also use canonicalization on the authentication bit as well.

[Parker Grimes]

Thanks for the feedback. We use Oracle and as far as I can tell, you would have to alter the Oracle session to change the queries to be case insensitive, that sounds like a messy path to go down.

I have tried the cas.authn.attributeRepository.jdbc[0].caseCanonicalization=LOWER and it still passes the username as typed to the SQL query.

I've settled on some simple javascript to lowercase the username when the user submits the form in combination with a usernameAttributeProvider service definition to set canonicalizationMode to LOWER. The latter doesn't affect the SQL query, but I also have had the username passed to registered services in mixed case as well that has caused me issues.

The javascript isn't ideal, but it should prevent most of my problems. I wish there was a global setting to always force the principal ID to be lowercase and have that apply to SQL queries also. (I do have cas.authn.accept.principalTransformation.caseConversion=LOWERCASE)