How to limit dynamic queries to only accept select statements?

Question

Currently I allow admin users to query the database by providing a dynamic query with this method:

public DataSet executeRawQuery(final String q) {
    JdbcDataSet dataSet = jdbcTpl.execute(q, new DataSetBuildingCallback(settingsProvider));
    return dataSet;
}

However, I want to allow them to use only select statements and block table drop, delete, etc. statements. How can I do it?

The SQL statements can be quite complex so I wouldn't like having to check the string for keywords. Is there a Spring's built-in functionality that could help me with this?

Answer 1

As I commented, there's no need for checking the input string, just let the database itself handle the security and use a database connection that will only allow select statements.

To do that, first create a database user and only grant select:

CREATE USER username IDENTIFIED BY apassword;
GRANT CONNECT TO username;
GRANT SELECT on schema.table TO username; 

and then use the username/password created above when getting the JDBC connection. If other parts of the application need insert/delete/update then they can use another connection.