Examples with MySQL org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL used on opensource projects

Example 11 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project carbon-identity-framework by wso2.

the class WorkflowRequestDAO method getRequestsOfUserFilteredByTime.

/**
 * Get requests of a user created/updated in given time period
 *
 * @param userName     User to get requests of, empty String to retrieve requests of all users
 * @param beginTime    lower limit of date range to filter
 * @param endTime      upper limit of date range to filter
 * @param timeCategory filter by created time or last updated time ?
 * @param tenantId     tenant id of currently logged in user
 * @return
 * @throws InternalWorkflowException
 */
public org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] getRequestsOfUserFilteredByTime(String userName, Timestamp beginTime, Timestamp endTime, String timeCategory, int tenantId, String status) throws InternalWorkflowException {
    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    String query = "";
    ResultSet resultSet = null;
    try {
        String driverName = connection.getMetaData().getDriverName();
        if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_MYSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_MYSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_MYSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_MYSQL;
                }
            }
        } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_DB2SQl;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_DB2SQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_DB2SQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_DB2SQL;
                }
            }
        } else if (driverName.contains("MS SQL")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_MSSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_MSSQL;
                }
            }
        } else if (driverName.contains("Microsoft") || driverName.contains("microsoft")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_MSSQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_MSSQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_MSSQL;
                }
            }
        } else if (driverName.contains("PostgreSQL")) {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_POSTGRESQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_POSTGRESQL;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_POSTGRESQL;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_POSTGRESQL;
                }
            }
        } else if (driverName.contains("Informix")) {
            // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_INFORMIX;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_INFORMIX;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_INFORMIX;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_INFORMIX;
                }
            }
        } else {
            if (UPDATED_AT_FILTER.equals(timeCategory)) {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_ORACLE;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_UPDATED_TIME_AND_STATUS_ORACLE;
                }
            } else {
                if (status.equals(ALL_TASKS_FILTER) || status.equals("")) {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_ORACLE;
                } else {
                    query = SQLConstants.GET_REQUESTS_OF_USER_FILTER_FROM_CREATED_TIME_AND_STATUS_ORACLE;
                }
            }
        }
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, userName);
        prepStmt.setTimestamp(2, beginTime);
        prepStmt.setTimestamp(3, endTime);
        prepStmt.setInt(4, tenantId);
        if (!status.equals(ALL_TASKS_FILTER) && !status.equals("")) {
            prepStmt.setString(5, status);
        }
        resultSet = prepStmt.executeQuery();
        ArrayList<org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest> requestDTOs = new ArrayList<>();
        while (resultSet.next()) {
            org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest requestDTO = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest();
            requestDTO.setRequestId(resultSet.getString(SQLConstants.REQUEST_UUID_COLUMN));
            requestDTO.setEventType(resultSet.getString(SQLConstants.REQUEST_OPERATION_TYPE_COLUMN));
            requestDTO.setCreatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_CREATED_AT_COLUMN).toString());
            requestDTO.setUpdatedAt(resultSet.getTimestamp(SQLConstants.REQUEST_UPDATED_AT_COLUMN).toString());
            requestDTO.setStatus(resultSet.getString(SQLConstants.REQUEST_STATUS_COLUMN));
            requestDTO.setRequestParams((deserializeWorkflowRequest(resultSet.getBytes(SQLConstants.REQUEST_COLUMN))).getRequestParameterAsString());
            requestDTO.setCreatedBy(resultSet.getString(SQLConstants.CREATED_BY_COLUMN));
            requestDTOs.add(requestDTO);
        }
        org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[] requestArray = new org.wso2.carbon.identity.workflow.mgt.bean.WorkflowRequest[requestDTOs.size()];
        for (int i = 0; i < requestDTOs.size(); i++) {
            requestArray[i] = requestDTOs.get(i);
        }
        return requestArray;
    } catch (SQLException e) {
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } catch (ClassNotFoundException | IOException e) {
        throw new InternalWorkflowException("Error when deserializing a workflow request.", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
}

Example 12 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project carbon-identity-framework by wso2.

the class ApplicationDAOImpl method getApplicationBasicInfo.

@Override
public ApplicationBasicInfo[] getApplicationBasicInfo(int offset, int limit) throws IdentityApplicationManagementException {
    validateAttributesForPagination(offset, limit);
    int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId();
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement getAppNamesStmt = null;
    ResultSet appNameResultSet = null;
    String sqlQuery;
    ArrayList<ApplicationBasicInfo> appInfo = new ArrayList<ApplicationBasicInfo>();
    try {
        String databaseProductName = connection.getMetaData().getDatabaseProductName();
        if (databaseProductName.contains("MySQL") || databaseProductName.contains("MariaDB") || databaseProductName.contains("H2")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_MYSQL;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            populateListAppNamesQueryValues(tenantID, offset, limit, getAppNamesStmt);
        } else if (databaseProductName.contains("Oracle")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_ORACLE;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            populateListAppNamesQueryValues(tenantID, offset + limit, offset, getAppNamesStmt);
        } else if (databaseProductName.contains("Microsoft")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_MSSQL;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            populateListAppNamesQueryValues(tenantID, offset, limit, getAppNamesStmt);
        } else if (databaseProductName.contains("PostgreSQL")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_POSTGRESQL;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            populateListAppNamesQueryValues(tenantID, limit, offset, getAppNamesStmt);
        } else if (databaseProductName.contains("DB2")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_DB2SQL;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            populateListAppNamesQueryValues(tenantID, offset + 1, offset + limit, getAppNamesStmt);
        } else if (databaseProductName.contains("INFORMIX")) {
            sqlQuery = LOAD_APP_NAMES_BY_TENANT_INFORMIX;
            getAppNamesStmt = connection.prepareStatement(sqlQuery);
            getAppNamesStmt.setInt(1, offset);
            getAppNamesStmt.setInt(2, limit);
            getAppNamesStmt.setInt(3, tenantID);
            getAppNamesStmt.setString(4, LOCAL_SP);
        } else {
            log.error("Error while loading applications from DB: Database driver could not be identified or " + "not supported.");
            throw new IdentityApplicationManagementException("Error while loading applications from DB: " + "Database driver could not be identified or not supported.");
        }
        appNameResultSet = getAppNamesStmt.executeQuery();
        while (appNameResultSet.next()) {
            appInfo.add(buildApplicationBasicInfo(appNameResultSet));
        }
    } catch (SQLException e) {
        throw new IdentityApplicationManagementException("Error while loading applications from DB: " + e.getMessage(), e);
    } finally {
        IdentityApplicationManagementUtil.closeStatement(getAppNamesStmt);
        IdentityApplicationManagementUtil.closeResultSet(appNameResultSet);
        IdentityApplicationManagementUtil.closeConnection(connection);
    }
    return appInfo.toArray(new ApplicationBasicInfo[0]);
}

Example 13 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class AccessTokenDAOImpl method getLatestAccessTokenByState.

private AccessTokenDO getLatestAccessTokenByState(Connection connection, String consumerKey, AuthenticatedUser authzUser, String userStoreDomain, String scope, boolean active) throws IdentityOAuth2Exception, SQLException {
    if (log.isDebugEnabled()) {
        log.debug("Retrieving latest " + (active ? " active" : " non active") + " access token for user: " + authzUser.getLoggableUserId() + " client: " + consumerKey + " scope: " + scope);
    }
    String tenantDomain = authzUser.getTenantDomain();
    int tenantId = OAuth2Util.getTenantId(tenantDomain);
    boolean isUsernameCaseSensitive = IdentityUtil.isUserStoreCaseSensitive(authzUser.getUserStoreDomain(), tenantId);
    String tenantAwareUsernameWithNoUserDomain = authzUser.getUserName();
    String userDomain = OAuth2Util.getUserStoreDomain(authzUser);
    String authenticatedIDP = OAuth2Util.getAuthenticatedIDP(authzUser);
    PreparedStatement prepStmt = null;
    ResultSet resultSet = null;
    try {
        String sql;
        String driverName = connection.getMetaData().getDriverName();
        if (active) {
            if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
                if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MYSQL;
                } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_DB2SQL;
                } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
                } else if (driverName.contains("PostgreSQL")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_POSTGRESQL;
                } else if (driverName.contains("Informix")) {
                    // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_INFORMIX;
                } else {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_ORACLE;
                }
            } else {
                if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MYSQL;
                } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_DB2SQL;
                } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
                } else if (driverName.contains("PostgreSQL")) {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_POSTGRESQL;
                } else if (driverName.contains("Informix")) {
                    // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_INFORMIX;
                } else {
                    sql = SQLQueries.RETRIEVE_LATEST_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_ORACLE;
                }
            }
        } else {
            if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
                if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MYSQL;
                } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_DB2SQL;
                } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_MSSQL;
                } else if (driverName.contains("PostgreSQL")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_POSTGRESQL;
                } else if (driverName.contains("Informix")) {
                    // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_INFORMIX;
                } else {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_IDP_NAME_ORACLE;
                }
            } else {
                if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MYSQL;
                } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_DB2SQL;
                } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_MSSQL;
                } else if (driverName.contains("PostgreSQL")) {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_POSTGRESQL;
                } else if (driverName.contains("Informix")) {
                    // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_INFORMIX;
                } else {
                    sql = SQLQueries.RETRIEVE_LATEST_NON_ACTIVE_ACCESS_TOKEN_BY_CLIENT_ID_USER_SCOPE_ORACLE;
                }
            }
        }
        sql = OAuth2Util.getTokenPartitionedSqlByUserStore(sql, userDomain);
        if (!isUsernameCaseSensitive) {
            sql = sql.replace(AUTHZ_USER, LOWER_AUTHZ_USER);
        }
        String hashedScope = OAuth2Util.hashScopes(scope);
        if (hashedScope == null) {
            sql = sql.replace("TOKEN_SCOPE_HASH=?", "TOKEN_SCOPE_HASH IS NULL");
        }
        prepStmt = connection.prepareStatement(sql);
        prepStmt.setString(1, getPersistenceProcessor().getProcessedClientId(consumerKey));
        if (isUsernameCaseSensitive) {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain);
        } else {
            prepStmt.setString(2, tenantAwareUsernameWithNoUserDomain.toLowerCase());
        }
        prepStmt.setInt(3, tenantId);
        prepStmt.setString(4, userDomain);
        if (hashedScope != null) {
            prepStmt.setString(5, hashedScope);
        }
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            prepStmt.setString(6, authenticatedIDP);
        }
        resultSet = prepStmt.executeQuery();
        AccessTokenDO accessTokenDO = null;
        if (resultSet.next()) {
            String accessToken = getPersistenceProcessor().getPreprocessedAccessTokenIdentifier(resultSet.getString(1));
            String refreshToken = null;
            if (resultSet.getString(2) != null) {
                refreshToken = getPersistenceProcessor().getPreprocessedRefreshToken(resultSet.getString(2));
            }
            long issuedTime = resultSet.getTimestamp(3, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime();
            long refreshTokenIssuedTime = resultSet.getTimestamp(4, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime();
            long validityPeriodInMillis = resultSet.getLong(5);
            long refreshTokenValidityPeriodInMillis = resultSet.getLong(6);
            String userType = resultSet.getString(7);
            String tokenId = resultSet.getString(8);
            String subjectIdentifier = resultSet.getString(9);
            // data loss at dividing the validity period but can be neglected
            AuthenticatedUser user = OAuth2Util.createAuthenticatedUser(tenantAwareUsernameWithNoUserDomain, userDomain, tenantDomain, authenticatedIDP);
            ServiceProvider serviceProvider;
            try {
                serviceProvider = OAuth2ServiceComponentHolder.getApplicationMgtService().getServiceProviderByClientId(consumerKey, OAuthConstants.Scope.OAUTH2, tenantDomain);
            } catch (IdentityApplicationManagementException e) {
                throw new IdentityOAuth2Exception("Error occurred while retrieving OAuth2 application data for " + "client id " + consumerKey, e);
            }
            user.setAuthenticatedSubjectIdentifier(subjectIdentifier, serviceProvider);
            accessTokenDO = new AccessTokenDO(consumerKey, user, OAuth2Util.buildScopeArray(scope), new Timestamp(issuedTime), new Timestamp(refreshTokenIssuedTime), validityPeriodInMillis, refreshTokenValidityPeriodInMillis, userType);
            accessTokenDO.setAccessToken(accessToken);
            accessTokenDO.setRefreshToken(refreshToken);
            accessTokenDO.setTokenId(tokenId);
        }
        return accessTokenDO;
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        String errorMsg = "Error occurred while trying to retrieve latest 'ACTIVE' " + "access token for Client ID : " + consumerKey + ", User ID : " + authzUser + " and  Scope : " + scope;
        if (!active) {
            errorMsg = errorMsg.replace("ACTIVE", "NON ACTIVE");
        }
        throw new IdentityOAuth2Exception(errorMsg, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(null, resultSet, prepStmt);
    }
}

Example 14 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class OAuthScopeDAOImpl method getPreparedStatementForGetScopesWithPagination.

/**
 * Get SQL statement for get OAuth2 scope with pagination.
 *
 * @param offset   Offset.
 * @param limit    Limit.
 * @param tenantID Tenet ID.
 * @param conn     Database connection.
 * @return
 * @throws SQLException
 */
private NamedPreparedStatement getPreparedStatementForGetScopesWithPagination(Integer offset, Integer limit, int tenantID, Connection conn) throws SQLException {
    String query;
    String driverName = conn.getMetaData().getDriverName();
    if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_MYSQL;
    } else if (conn.getMetaData().getDatabaseProductName().contains("DB2")) {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_DB2SQL;
    } else if (driverName.contains("MS SQL")) {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_MSSQL;
    } else if (driverName.contains("Microsoft") || driverName.contains("microsoft")) {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_MSSQL;
    } else if (driverName.contains("PostgreSQL")) {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_POSTGRESQL;
    } else if (driverName.contains("Informix")) {
        // Driver name = "IBM Informix JDBC Driver for IBM Informix Dynamic Server"
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_INFORMIX;
    } else {
        query = SQLQueries.RETRIEVE_SCOPES_WITH_PAGINATION_ORACLE;
    }
    NamedPreparedStatement namedPreparedStatement = new NamedPreparedStatement(conn, query);
    namedPreparedStatement.setString(Oauth2ScopeConstants.SQLPlaceholders.SCOPE_TYPE, Oauth2ScopeConstants.SCOPE_TYPE_OAUTH2);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.TENANT_ID, tenantID);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.OFFSET, offset);
    namedPreparedStatement.setInt(Oauth2ScopeConstants.SQLPlaceholders.LIMIT, limit);
    return namedPreparedStatement;
}

Example 15 with MySQL

use of org.wso2.carbon.idp.mgt.util.IdPManagementConstants.MySQL in project identity-inbound-auth-oauth by wso2-extensions.

the class TokenManagementDAOImpl method validateRefreshToken.

@Override
public RefreshTokenValidationDataDO validateRefreshToken(String consumerKey, String refreshToken) throws IdentityOAuth2Exception {
    if (log.isDebugEnabled()) {
        if (IdentityUtil.isTokenLoggable(IdentityConstants.IdentityTokens.REFRESH_TOKEN)) {
            log.debug("Validating refresh token(hashed): " + DigestUtils.sha256Hex(refreshToken) + " client: " + consumerKey);
        } else {
            log.debug("Validating refresh token for client: " + consumerKey);
        }
    }
    RefreshTokenValidationDataDO validationDataDO = new RefreshTokenValidationDataDO();
    Connection connection = IdentityDatabaseUtil.getDBConnection(false);
    PreparedStatement prepStmt = null;
    ResultSet resultSet = null;
    String sql;
    try {
        String driverName = connection.getMetaData().getDriverName();
        if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_DB2SQL;
            } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_POSTGRESQL;
            } else if (driverName.contains("INFORMIX")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_IDP_NAME_ORACLE;
            }
        } else {
            if (driverName.contains("MySQL") || driverName.contains("MariaDB") || driverName.contains("H2")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_MYSQL;
            } else if (connection.getMetaData().getDatabaseProductName().contains("DB2")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_DB2SQL;
            } else if (driverName.contains("MS SQL") || driverName.contains("Microsoft")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_MSSQL;
            } else if (driverName.contains("PostgreSQL")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_POSTGRESQL;
            } else if (driverName.contains("INFORMIX")) {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_INFORMIX;
            } else {
                sql = SQLQueries.RETRIEVE_ACCESS_TOKEN_VALIDATION_DATA_ORACLE;
            }
        }
        sql = OAuth2Util.getTokenPartitionedSqlByToken(sql, refreshToken);
        if (refreshToken == null) {
            sql = sql.replace("REFRESH_TOKEN = ?", "REFRESH_TOKEN IS NULL");
        }
        prepStmt = connection.prepareStatement(sql);
        prepStmt.setString(1, getPersistenceProcessor().getProcessedClientId(consumerKey));
        if (refreshToken != null) {
            prepStmt.setString(2, getHashingPersistenceProcessor().getProcessedRefreshToken(refreshToken));
        }
        resultSet = prepStmt.executeQuery();
        int iterateId = 0;
        List<String> scopes = new ArrayList<>();
        while (resultSet.next()) {
            if (iterateId == 0) {
                if (isHashDisabled) {
                    validationDataDO.setAccessToken(getPersistenceProcessor().getPreprocessedAccessTokenIdentifier(resultSet.getString(1)));
                } else {
                    validationDataDO.setAccessToken(resultSet.getString(1));
                }
                String userName = resultSet.getString(2);
                int tenantId = resultSet.getInt(3);
                String userDomain = resultSet.getString(4);
                String tenantDomain = OAuth2Util.getTenantDomain(tenantId);
                validationDataDO.setScope(OAuth2Util.buildScopeArray(resultSet.getString(5)));
                validationDataDO.setRefreshTokenState(resultSet.getString(6));
                validationDataDO.setIssuedTime(resultSet.getTimestamp(7, Calendar.getInstance(TimeZone.getTimeZone(UTC))));
                validationDataDO.setValidityPeriodInMillis(resultSet.getLong(8));
                validationDataDO.setTokenId(resultSet.getString(9));
                validationDataDO.setGrantType(resultSet.getString(10));
                String subjectIdentifier = resultSet.getString(11);
                validationDataDO.setTokenBindingReference(resultSet.getString(12));
                validationDataDO.setAccessTokenIssuedTime(resultSet.getTimestamp(13, Calendar.getInstance(TimeZone.getTimeZone(UTC))));
                validationDataDO.setAccessTokenValidityInMillis(resultSet.getLong(14));
                String authenticatedIDP = null;
                if (OAuth2ServiceComponentHolder.isIDPIdColumnEnabled()) {
                    authenticatedIDP = resultSet.getString(15);
                }
                AuthenticatedUser user = OAuth2Util.createAuthenticatedUser(userName, userDomain, tenantDomain, authenticatedIDP);
                user.setAuthenticatedSubjectIdentifier(subjectIdentifier);
                validationDataDO.setAuthorizedUser(user);
            } else {
                scopes.add(resultSet.getString(5));
            }
            iterateId++;
        }
        if (scopes.size() > 0 && validationDataDO != null) {
            validationDataDO.setScope((String[]) ArrayUtils.addAll(validationDataDO.getScope(), scopes.toArray(new String[scopes.size()])));
        }
    } catch (SQLException e) {
        throw new IdentityOAuth2Exception("Error when validating a refresh token", e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, resultSet, prepStmt);
    }
    return validationDataDO;
}