Java SQL safely check if column exists

Question

I need to have dynamic SQL which accepts table and column names from users and uses those in queries. Right now I do this with

public Object doSearch(String table, List<String> columns) {
//... some logic
String.format("SELECT %s from %s", String.join(", ", columns), table");
//... some execution and return
}

The source is NOT trusted, so I want to do a whitelist of table and column names, but that list changes. The list of valid tables is strictly the list of tables on my_schema and the list of valid columns is strictly the columns on that particular table.

I've searched around SO and gotten a solution that looks something like:

private boolean validate(String tableName, List<String> columnNames) throws SQLException {
    return tableExist(tableName) && columnNames.stream().allMatch(cn -> columnExistsOnTable(tableName, cn));
}
private boolean tableExist(String tableName) throws SQLException {
    try (ResultSet rs = connection.getMetaData().getTables(null, schema, tableName, null)) {
        while (rs.next()) {
            String tName = rs.getString("TABLE_NAME");
            if (tName != null && tName.equals(tableName)) {
                return true;
            }
        }
    }
    return false;
}

private boolean columnExistsOnTable(String tableName, String columnName) {
    try (ResultSet rs = connection.getMetaData().getColumns(null, schema, tableName, columnName)) {
        while (rs.next()) {
            String tName = rs.getString("COLUMN_NAME");
            if (tName != null && tName.equals(tableName)) {
                return true;
            }
        }
    } catch (SQLException sqle) {
        return false;
    }
    return false;
}

Is this safe and correct?

I think you can refer to [this post](http://stackoverflow.com/questions/985010/easiest-way-to-obtain-database-metadata-in-java). You actually need to get the database definition in order to parse it and find the objects you need. This project might help you to work independently of data sources. — andreim, Apr 20 '17 at 19:31

Matt · Answer 1 · 2017-04-20T19:27:37.470

For each of those methods, you could do this one time in an initialization method and cache the table/column names so you don't have to do a database check every time... something like this:

private Map<String, Set<String>> tableColNames = new HashMap();


    private void initCache(){
       // build the cache


  // get all tables 
   // get all columns
   // add tables and columns to the map 
}


private boolean tableExist(String tableName) throws SQLException {
    return tableColNames.containsKey(tableName);
}

private boolean columnExistsOnTable(String tableName, String columnName) {
   if(tableExist(tableName)){
     return   tableColNames.get(tableName).contains(columnName);
   } else {
     return false;
   }

}
// could make a method for checking a list of Strings too...
// return tableColNames.get(tableName).containsAll(columnName);

https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/ResultSetMetaData.html

Java SQL safely check if column exists

1 Answers1