In Oracle database 12C for Authorization Code flow. select * from oauth2_authorization where authorization_code_value = 'xxx' is giving error

[Basit-Mahmood]

Describe the bug
I am using oarcle database 12C. In original oauth2_authorization table. attributes column is of type varchar. I changed it to clob. The authorization saved in database fine. But when tried to find the oauth2_authorization by token. The following query makes

SELECT id, registered_client_id, principal_name, authorization_grant_type, attributes, state, authorization_code_value, authorization_code_issued_at, authorization_code_expires_at,authorization_code_metadata,access_token_value,access_token_issued_at,access_token_expires_at,access_token_metadata,access_token_type,access_token_scopes,oidc_id_token_value,oidc_id_token_issued_at,oidc_id_token_expires_at,oidc_id_token_metadata,refresh_token_value,refresh_token_issued_at,refresh_token_expires_at,refresh_token_metadata FROM oauth2_authorization WHERE authorization_code_value = 'RydSPq-E6nR8OxYI-OeLBec8FugHBPtrw_kuzUDIx-cIn98Y2qimveRy_h_KfOByBmADSjCXKAN-q8PPprpkwFtUsP7iTOV28vzhd9mF22KtEGCZYaEaBsuFX0CpYmXD';

The above query is giving the following error

ORA-00932: inconsistent datatypes: expected - got BLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

But if instead of = use like then the query works fine

SELECT id, registered_client_id, principal_name, authorization_grant_type, attributes, state, authorization_code_value, authorization_code_issued_at, authorization_code_expires_at,authorization_code_metadata,access_token_value,access_token_issued_at,access_token_expires_at,access_token_metadata,access_token_type,access_token_scopes,oidc_id_token_value,oidc_id_token_issued_at,oidc_id_token_expires_at,oidc_id_token_metadata,refresh_token_value,refresh_token_issued_at,refresh_token_expires_at,refresh_token_metadata FROM oauth2_authorization WHERE authorization_code_value like 'RydSPq-E6nR8OxYI-OeLBec8FugHBPtrw_kuzUDIx-cIn98Y2qimveRy_h_KfOByBmADSjCXKAN-q8PPprpkwFtUsP7iTOV28vzhd9mF22KtEGCZYaEaBsuFX0CpYmXD';
Similarly If I change the datatype of authorization_code_value to clob. Then I get the same error

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

With authorization_code_value like 'xxx' both blob and clob are working. But with authorization_code_value = 'xxx'. Both blob and clob giving error.

You can not use = in where clause for oracle with blob or clob data type.

To Reproduce

1. Use the following script on oracle database 12C for oauth2_authorization . Note the attributes, authorization_code_value , access_token_value, access_token_metadata and access_token_scopes are clob.

CREATE TABLE oauth2_authorization (
id varchar2(100) NOT NULL,
registered_client_id varchar2(100) NOT NULL,
principal_name varchar2(200) NOT NULL,
authorization_grant_type varchar2(100) NOT NULL,
attributes clob DEFAULT NULL,
state varchar2(500) DEFAULT NULL,
authorization_code_value clob DEFAULT NULL,
authorization_code_issued_at timestamp DEFAULT NULL,
authorization_code_expires_at timestamp DEFAULT NULL,
authorization_code_metadata varchar2(4000) DEFAULT NULL,
access_token_value clob DEFAULT NULL,
access_token_issued_at timestamp DEFAULT NULL,
access_token_expires_at timestamp DEFAULT NULL,
access_token_metadata clob DEFAULT NULL,
access_token_type varchar2(100) DEFAULT NULL,
access_token_scopes clob DEFAULT NULL,
oidc_id_token_value blob DEFAULT NULL,
oidc_id_token_issued_at timestamp DEFAULT NULL,
oidc_id_token_expires_at timestamp DEFAULT NULL,
oidc_id_token_metadata varchar2(4000) DEFAULT NULL,
refresh_token_value blob DEFAULT NULL,
refresh_token_issued_at timestamp DEFAULT NULL,
refresh_token_expires_at timestamp DEFAULT NULL,
refresh_token_metadata varchar2(4000) DEFAULT NULL,
PRIMARY KEY (id)
);

2. Run the authorization code grant flow. Authorization will save in database fine. But when try to get the authorization using filter WHERE authorization_code_value = 'xxxx'. The error will generate.

Expected behavior
The query should run fine on oracle for blob or clob data type.

Sample

I think the sample application can be check on oracle by connecting it to oracle database 12c and other versions of oracle.

Also no need of code. Just create the table oauth2_authorization in oracle database using blob or cob data type for authorization_code_value and execute the following query in any client. Like Sqldeveloper . Even with the original scripts present in spring code the error is coming (org/springframework/security/oauth2/server/authorization/oauth2-authorization-schema.sql).

select * from oauth2_authorization where authorization_code_value = 'ssss';

The error will coccur

[Basit-Mahmood]

I tried it to change the data type of authorization_code_value varchar2(4000) DEFAULT NULL,. Originally it was blob, I changed it to clob. But with both blob and clob at database level error is coming. I changed it to varcahr2 to see if it works. But at database level it works. But now in the code. I am getting the following exception org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT id, registered_client_id, principal_name, authorization_grant_type, attributes, state, authorization_code_value, authorization_code_issued_at, authorization_code_expires_at,authorization_code_metadata,access_token_value,access_token_issued_at,access_token_expires_at,access_token_metadata,access_token_type,access_token_scopes,oidc_id_token_value,oidc_id_token_issued_at,oidc_id_token_expires_at,oidc_id_token_metadata,refresh_token_value,refresh_token_issued_at,refresh_token_expires_at,refresh_token_metadata FROM oauth2_authorization WHERE authorization_code_value = ?]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type Because in the code. It is considering the blob at line parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));. Now I think I need to override this method. But problem is findBy() method is private. If you suggest any thing like changing the query or other solution for now. It would be good. Thanks

[Basit-Mahmood]

Also I noted that all the constants in JdbcOAuth2AuthorizationService are private. I override the findByToken() method and I need to declare all the constants and findBy() method which is private in this class. Instead of it if there is a way to just modify the private static final String AUTHORIZATION_CODE_FILTER = "authorization_code_value = ?"; then I can simply change this constant to protected static final String AUTHORIZATION_CODE_FILTER = "authorization_code_value like ?"; and my problem could be solve. I mean to say there should be method in this class that can returns these constants and then anyone can override those methods if he wants to change the value. Like protected String getAuthorizationCodeFilter(){return AUTHORIZATION_CODE_FILTER}. Now anyone can override this method and change the value. Just a suggestion. So please make customization of constants and methods possible in JdbcOAuth2AuthorizationService. Thanks

[sjohnr]

Hi @Basit-Mahmood, as mentioned in this comment:

It is very difficult to provide an implementation that works out of the box for all databases. This implementation strives to use standard sql datatypes and is a simplified JDBC implementation. However, it is designed to be customizable so user's can provide customizations for database vendors that deviate from the standard sql types.

Your comments show that you've made some good progress customizing for yourself. However, if you're still having trouble, take a look at JdbcOAuth2AuthorizationServiceTests.tableDefinitionWhenCustomThenAbleToOverride(), which provides a test on how to override the table definition altogether. There are tests in that class that demonstrate how to customize further.

You may also be interested in implementing this using Spring Data. See this gist if you're interested in trying that approach: JpaOAuth2AuthorizationService

I'm going to close this for now, but if you are still having issues after you have tried out the proposed solutions we can re-open and discuss further.

[Basit-Mahmood]

@sjohnr Hi, Thanks for your response. I have done it. I changed all the blob columns to clob in the database script. In-fact I also changed one varchar column to clob. I extend the JdbcOAuth2AuthorizationService. Override the findByToken() method. Adjust the blob parameters. Similarly customize the OAuth2AuthorizationParametersMapper and OAuth2AuthorizationRowMapper. Changed all the blob handling and one varchar type to clob. Use the following configuration

`@Bean
public OAuth2AuthorizationService authorizationService(JdbcTemplate jdbcTemplate, RegisteredClientRepository registeredClientRepository) {
	
	LOGGER.debug("in authorizationService");
	
	RowMapper<OAuth2Authorization> oauth2AuthorizationRowMapper = new CustomOAuth2AuthorizationRowMapper(registeredClientRepository);	
	
	JdbcOAuth2AuthorizationService jdbcOAuth2AuthorizationService = new CustomJdbcOAuth2AuthorizationService(jdbcTemplate, registeredClientRepository);
	jdbcOAuth2AuthorizationService.setAuthorizationParametersMapper(new CustomOAuth2AuthorizationParametersMapper());
	jdbcOAuth2AuthorizationService.setAuthorizationRowMapper(oauth2AuthorizationRowMapper);
	return jdbcOAuth2AuthorizationService;
}`

and bingo. I didn't get a chance to check the jpa implementation (JpaOAuth2AuthorizationService). Otherwise I definitely gave tha JPA implementation status too. But I will check it too.

Just one request. All the queries and table variables are private in JdbcOAuth2AuthorizationService. Although I extend it but I have to declare all the same variables again in my subclass. So if these variables also make available to subclass then it will simplify the subclass more. It is still simple but it will look more simpler :) .

Thanks for the response. Thanks for the help :)

[sjohnr]

@Basit-Mahmood no problem, glad it helped! Thanks for the feedback.

[maradanasai]

Hi @Basit-Mahmood I'm also facing this issue. Can you please help with sharing your fix?

[basit-mahmood-strabl]

@maradanasai I don't think so this is the right place to discuss this ... Anyways after changing the columns data type to blob or clob. You will have to extend the JdbcOAuth2AuthorizationService class. In this class variables are final. So you will have to duplicate the code. Change the value of the variables ACCESS_TOKEN_FILTER and REFRESH_TOKEN_FILTER. Just change = to like

If I start explaining each and every step it will become very long post. There is another way. Instead of using JDBC use JPA. Check my Github repository . Spring Authorization Server Spring Boot 3 . In this repository I have configured JPA.

If JDBC is the requirement. Then I think we can discuss it on my Github or on Stackoverflow or wherever you like.

Thanks & Regards
Basit Mahmood Ahmed

[maradanasai]

Hi @basit-mahmood-strabl Thanks for prompt response. Able to figure out the issue and resolved it.

I think it would be nice if they make findBy method protected so that we can override and can update the filters.

[Basit-Mahmood]

@maradanasai Glad to hear you have resolved the issue. Thanks :)