Hibernate NativeQuery setParameter doesn't work when getting a list of tables

Question

I am trying to get an ArrayList of tables from a MySQL database. From the articles I have read, the following code should work, yet it does not.

private ArrayList<String> makeTableArray(String dbName){
    ArrayList<String> stringArr = new ArrayList<>();
    try(Session session = ServerConnect.getSessionFactory().openSession()){
        String query = "show tables from :dbName";
        NativeQuery nativeQuery = session.createNativeQuery(query);
        nativeQuery.setParameter("dbName", dbName);
        List list = nativeQuery.list();

        for(Object database: list){
            stringArr.add(String.valueOf(database));
        }
    }
    return stringArr;
}

The error I get using the database sakila is:

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sakila'' at line 1

I can get this working if I concatenate the query with the dbName such as "show tables from " + dbName;, but if I setParameter, it doesn't work.

Answer 1

You can't use parameters for database objects such as table names. Your SQL database's prepared statement API does not support it, and if it did, it would be opening major vulnerabilities. Here is one possible workaround, using a switch statement:

ArrayList<String> stringArr = new ArrayList<>();
try (Session session = ServerConnect.getSessionFactory().openSession()) {
    String query = "";
    switch (dbName) {
        case "sakila":
            query = "SHOW TABLES FROM sakila";
            break;

        case "foo":
            query = "SHOW TABLES FROM foo";
            break;

        // other database names go here
    }

    NativeQuery nativeQuery = session.createNativeQuery(query);
    nativeQuery.setParameter("dbName", dbName);
    List list = nativeQuery.list();

    for(Object database: list){
        stringArr.add(String.valueOf(database));
    }
}

The idea here is to just maintain a list of known databases which you expect to come from the outside. You then simply map an incoming database name to a show tables statement.