3

My user wants to input random sql to access databases for which a db link exists in Oracle db. However, this gives an exception with Hibernate. Below is a function which is supposed to check whether the query is valid by running the query. Any recommendation is welcome.

The exception is: 

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query 'Select ID,NAME from YYY@XXX_DB_LINK'

Here is the code:

public String testSQL(String sqlQuery) {
    if(!checkAllowedSQL(sqlQuery)) {
        logger.debug("Forbidden SQL Query detected: " + sqlQuery);
        return "Forbidden SQL Query detected '" + sqlQuery + "'";
    }


try {
    Query query = entityManager.createNativeQuery(sqlQuery);
        List<Object[]> list = query.getResultList();
    } catch(Exception e) {
        return e.toString() +  " '" + sqlQuery + "'";
    }

    return null;
}
Matteo Baldi
  • 5,613
  • 10
  • 39
  • 51
Adder
  • 5,708
  • 1
  • 28
  • 56
  • 2
    nice question but I suppose is same as this one http://stackoverflow.com/questions/22017108/hibernate-with-oracle-dblink-implementation – Acewin Dec 06 '16 at 19:29

1 Answers1

4

You should create a SYNONYM for your linked table YYY@XXX_DB_LINK, then your user can submit a native query like Select ID,NAME from YYYwithout worrying about table real location.

Matteo Baldi
  • 5,613
  • 10
  • 39
  • 51