JdbcApprovalStore.purgeExpiredApprovals not working

[danielturcu]

When trying to purge expired approvals with the DEFAULT_DELETE_APPROVAL_SQL sql, I received the following error:

[2018-06-20 09:20:53,876] [ERROR] [] [] [] [] [] [o.s.s.o.p.a.JdbcApprovalStore] [JdbcApprovalStore.java:177] [Error purging expired approvals] [org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [delete from oauth_approvals where userId=? and clientId=? and scope=? where expiresAt <= ?]; SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 2; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 2 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:937) at org.springframework.security.oauth2.provider.approval.JdbcApprovalStore.purgeExpiredApprovals(JdbcApprovalStore.java:167)

Version used: 2.2.0.RELEASE

Workaround is to override the default delete sql statement:

@Bean public ApprovalStore approvalStore() { JdbcApprovalStore jdbcApprovalStore = new JdbcApprovalStore(datasource); jdbcApprovalStore.setDeleteApprovalStatment("delete from oauth_approvals"); return jdbcApprovalStore; }

As a side effect the revokeApprovals method will no longer work because it is using the same SQL statement.

[haykart]

Hi, do you have the following table in your database?

create table oauth_approvals (
    userId VARCHAR(255),
    clientId VARCHAR(255),
    scope VARCHAR(255),
    status VARCHAR(10),
    expiresAt TIMESTAMP,
    lastModifiedAt TIMESTAMP
);

[danielturcu]

Hello @haykart

Yes I have the database properly configured.

[danielturcu]

Any updates on this issue?

[nhoughto]

This is because the purge uses the delete SQL, which includes where client_id, user, scope etc but purge only sets the timestamp, as its trying to remove all expired.

Its difficult to workaround, if you want to allow purging and are happy to never use delete() then the following workaround is ok, but if you ever want to call delete() you can't use this as it will delete all the things.

        setDeleteApprovalStatment(String.format("DELETE FROM %s ", "oauth_approvals"));