CAS PM password reset works on 6.3.7.4 but not 6.4.6.2 (same config)

[William Jojo]

Our password reset config has worked perfectly on 6.1, 6.2, and 6.3. Moving to 6.4 seems to have some bug.

Please see the log entries below. It seems the JDBC PM service thinks the query is null.

6.3.7.4 debug at the point of using the reset link:

 

2022-04-06 04:49:27,511 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [null]: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED>
2022-04-06 04:49:27,797 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [HikariProxyConnection@933367609 wrapping oracle.jdbc.driver.T4CConnection@1a08d6f1] for JDBC transaction>
2022-04-06 04:49:27,798 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Changing isolation level of JDBC Connection [HikariProxyConnection@933367609 wrapping oracle.jdbc.driver.T4CConnection@1a08d6f1] to 2>
2022-04-06 04:49:27,799 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [HikariProxyConnection@933367609 wrapping oracle.jdbc.driver.T4CConnection@1a08d6f1] to manual commit>
2022-04-06 04:49:27,800 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <Executing prepared SQL query>
2022-04-06 04:49:27,800 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <Executing prepared SQL statement [SELECT cas_question_text question, cas_answer_text answer FROM table(cas.get_question_answers(username_in=>?))]>
2022-04-06 04:49:27,801 TRACE [org.springframework.jdbc.core.StatementCreatorUtils] - <Setting SQL statement parameter value: column index 1, parameter value [w.jojo], value class [java.lang.String], SQL type unknown>
2022-04-06 04:49:27,824 TRACE [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Triggering beforeCommit synchronization>
2022-04-06 04:49:27,824 TRACE [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Triggering beforeCompletion synchronization>
2022-04-06 04:49:27,824 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
2022-04-06 04:49:27,824 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [HikariProxyConnection@933367609 wrapping oracle.jdbc.driver.T4CConnection@1a08d6f1]>
2022-04-06 04:49:27,832 TRACE [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Triggering afterCommit synchronization>
2022-04-06 04:49:27,832 TRACE [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Triggering afterCompletion synchronization>
2022-04-06 04:49:27,832 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [HikariProxyConnection@933367609 wrapping oracle.jdbc.driver.T4CConnection@1a08d6f1] after transaction>

6.4.6.2 debug at the point of using the reset link:

2022-04-05 16:38:52,272 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [null]: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED>
2022-04-05 16:38:52,283 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [HikariProxyConnection@514537969 wrapping oracle.jdbc.driver.T4CConnection@7afc8e3] for JDBC transaction>
2022-04-05 16:38:52,283 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Changing isolation level of JDBC Connection [HikariProxyConnection@514537969 wrapping oracle.jdbc.driver.T4CConnection@7afc8e3] to 2>
2022-04-05 16:38:52,283 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [HikariProxyConnection@514537969 wrapping oracle.jdbc.driver.T4CConnection@7afc8e3] to manual commit>
2022-04-05 16:38:52,284 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction rollback>
2022-04-05 16:38:52,284 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Rolling back JDBC transaction on Connection [HikariProxyConnection@514537969 wrapping oracle.jdbc.driver.T4CConnection@7afc8e3]>
2022-04-05 16:38:52,292 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [HikariProxyConnection@514537969 wrapping oracle.jdbc.driver.T4CConnection@7afc8e3] after transaction>
2022-04-05 16:38:52,294 ERROR [org.apereo.cas.pm.web.flow.actions.VerifyPasswordResetRequestAction] - <Password reset token could not be located or verified>
java.lang.IllegalArgumentException: SQL must not be null
    at org.springframework.util.Assert.notNull(Assert.java:201) ~[spring-core-5.3.18.jar:5.3.18]
    at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.<init>(JdbcTemplate.java:1639) ~[spring-jdbc-5.3.18.jar:5.3.18]

    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) ~[spring-jdbc-5.3.18.jar:5.3.18]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757) ~[spring-jdbc-5.3.18.jar:5.3.18]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:810) ~[spring-jdbc-5.3.18.jar:5.3.18]
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:942) ~[spring-jdbc-5.3.18.jar:5.3.18]
    at org.apereo.cas.pm.jdbc.JdbcPasswordManagementService.lambda$getSecurityQuestions$5(JdbcPasswordManagementService.java:130) ~[cas-server-support-pm-jdbc-6.4.6.2.jar:6.4.6.2]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)

Please advise.

Bill

[William Jojo]

Ugh, a victim of property naming changes, again...

It seems that between 6.3 and 6.4, the properties moved from:

cas.authn.pm.jdbc.sql-Security-Questions

to the group:

cas.authn.pm.jdbc.sql-get-Security-Questions

cas.authn.pm.jdbc.sql-Update-Security-Questions

cas.authn.pm.jdbc.sql-Delete-Security-Questions

There was no mention of the issue with the old property during startup, so I never knew it was a problem until the null value for the SQL query and then digging through the docs and copious debug logs. 

Hopefully, this helps someone else out there...

Bill