Exception Invalid Column Type when reading Authorizationcode from Oracle DB #347
Comments
spring-projects-issues
added
the
status: waiting-for-triage
|
I have investigated a little bit further and it seems that there is a problem with filtering on LOBs (CLOB and BLOB) in Oracle. In the "old" Spring Security Hope it helps |
|
Hi, |
|
@GrmpfNarf, as mentioned in this comment:
Take a look at I'm going to close this for now, but if you are still having issues after you have tried out the proposed solution we can re-open and discuss further. |
sjohnr
added
status: invalid
|
Hi, Which means as long you are using LOBs to filter the tokens there will be no Oracle support. I have customized the import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.security.oauth2.core.OAuth2TokenType;
import org.springframework.security.oauth2.core.endpoint.OAuth2ParameterNames;
import org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService;
import org.springframework.security.oauth2.server.authorization.OAuth2Authorization;
import org.springframework.security.oauth2.server.authorization.client.RegisteredClientRepository;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
@Service
public class CustomJdbcOauth2AuthorizationService extends JdbcOAuth2AuthorizationService {
private final JdbcOperations jdbcOperations;
private static final String TABLE_NAME = "oauth2_authorization";
private static final String COLUMN_NAMES = "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";
private static final String UNKNOWN_TOKEN_TYPE_FILTER = "state = ? OR dbms_lob.compare(authorization_code_value,?) = 0 OR " +
"dbms_lob.compare(access_token_value,?) = 0 OR dbms_lob.compare(refresh_token_value,?) = 0";
private static final String STATE_FILTER = "state = ?";
private static final String AUTHORIZATION_CODE_FILTER = "dbms_lob.compare(authorization_code_value,?) = 0";
private static final String ACCESS_TOKEN_FILTER = "dbms_lob.compare(access_token_value,?) = 0";
private static final String REFRESH_TOKEN_FILTER = "dbms_lob.compare(refresh_token_value,?) = 0";
private static final String LOAD_AUTHORIZATION_SQL = "SELECT " + COLUMN_NAMES
+ " FROM " + TABLE_NAME
+ " WHERE ";
public CustomJdbcOauth2AuthorizationService(JdbcOperations jdbcOperations, RegisteredClientRepository registeredClientRepository) {
super(jdbcOperations, registeredClientRepository);
this.jdbcOperations = jdbcOperations;
}
@Override
public OAuth2Authorization findByToken(String token, OAuth2TokenType tokenType) {
Assert.hasText(token, "token cannot be empty");
List<SqlParameterValue> parameters = new ArrayList<>();
if (tokenType == null) {
parameters.add(new SqlParameterValue(Types.VARCHAR, token));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(UNKNOWN_TOKEN_TYPE_FILTER, parameters);
} else if (OAuth2ParameterNames.STATE.equals(tokenType.getValue())) {
parameters.add(new SqlParameterValue(Types.VARCHAR, token.getBytes(StandardCharsets.UTF_8)));
return findBy(STATE_FILTER, parameters);
} else if (OAuth2ParameterNames.CODE.equals(tokenType.getValue())) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(AUTHORIZATION_CODE_FILTER, parameters);
} else if (OAuth2TokenType.ACCESS_TOKEN.equals(tokenType)) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(ACCESS_TOKEN_FILTER, parameters);
} else if (OAuth2TokenType.REFRESH_TOKEN.equals(tokenType)) {
parameters.add(new SqlParameterValue(Types.BLOB, token.getBytes(StandardCharsets.UTF_8)));
return findBy(REFRESH_TOKEN_FILTER, parameters);
}
return null;
}
private OAuth2Authorization findBy(String filter, List<SqlParameterValue> parameters) {
PreparedStatementSetter pss = new LobCreatorArgumentPreparedStatementSetter(getLobHandler().getLobCreator(), parameters.toArray());
List<OAuth2Authorization> result = this.jdbcOperations.query(LOAD_AUTHORIZATION_SQL + filter, pss, getAuthorizationRowMapper());
return !result.isEmpty() ? result.get(0) : null;
}
private static final class LobCreatorArgumentPreparedStatementSetter extends ArgumentPreparedStatementSetter {
private final LobCreator lobCreator;
private LobCreatorArgumentPreparedStatementSetter(LobCreator lobCreator, Object[] args) {
super(args);
this.lobCreator = lobCreator;
}
@Override
protected void doSetValue(PreparedStatement ps, int parameterPosition, Object argValue) throws SQLException {
if (argValue instanceof SqlParameterValue) {
SqlParameterValue paramValue = (SqlParameterValue) argValue;
if (paramValue.getSqlType() == Types.BLOB) {
if (paramValue.getValue() != null) {
Assert.isInstanceOf(byte[].class, paramValue.getValue(),
"Value of blob parameter must be byte[]");
}
byte[] valueBytes = (byte[]) paramValue.getValue();
this.lobCreator.setBlobAsBytes(ps, parameterPosition, valueBytes);
return;
}
}
super.doSetValue(ps, parameterPosition, argValue);
}
}
}In this class are only 2 changes to the original:
The rest of the class is like the original. For this I have some enhancement suggestions:
With this 3 enhancements my customization will only be the filter and maybe another |
ghost
mentioned this issue
|
`import org.springframework.jdbc.core.*; import java.nio.charset.StandardCharsets; @service }` |
|
I copied the source code above from cucosion and apply to SpringBoot Authorization 1.1.2 https://github.com/spring-projects/spring-authorization-server. It works well except the UNKNOWN_TOKEN_TYPE_FILTER is missing oidc_id_token_value as a condition It is surprised that this project, with its out-of-box configuration, is not ready for Oracle. Even worse is there is no any note or document to tell developers the Oracle is not available. Really appreciate the source code from cucosion. Also hope Apache can have a better quality control for projects under its name Sept 29, 2023 |
Hello everbody,
I get an exception when I try to obtain an Accesstoken from an Authorizationcode. In the moment when the
JdbcOAuth2AuthorizationServicetries to read the Authorizration from the table an exception occours.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]; Ungültiger Spaltentyp; nested exception is java.sql.SQLException: Ungültiger Spaltentyp at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542) ~[spring-jdbc-5.3.8.jar:5.3.8] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-5.3.8.jar:5.3.8] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) ~[spring-jdbc-5.3.8.jar:5.3.8] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) ~[spring-jdbc-5.3.8.jar:5.3.8] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:799) ~[spring-jdbc-5.3.8.jar:5.3.8] at org.springframework.security.oauth2.server.authorization.JdbcOAuth2AuthorizationService.findBy(JdbcOAuth2AuthorizationService.java:257) ~[spring-security-oauth2-authorization-server-0.1.2.jar:0.1.2]Translation Ungültiger Spaltentyp = Invalid Column Type
The error occours when Oracle tries to cast the Authorizationcode (as byte[]) into an
oracle.jdbc.internal.OracleBlob.I hope somebody can help me.
Metadata:
Spring Boot Version: 2.5.2
Authorization Server Version: 0.1.2
Oracle Dependency: com.oracle.database.jdbc:ojdbc10:19.11.0.0
The text was updated successfully, but these errors were encountered: