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?
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