How to be sure entered query is a SELECT and not a DDL or DML in JDBC

Question

I have an admin panel where users can run queries. I need to be sure that only SELECT queries can run there. I am using JdbcTemplate's queryForList method jdbcTemplate.queryForList(sql)

Can I be sure that it will only run select and not any DDL or DML command?

I tried running an update query and it returned

Statement.executeQuery() cannot issue statements that do not produce result sets.; nested exception is java.sql.SQLException: Statement.executeQuery() cannot issue statements that do not produce result sets.

It actually give exception just as I wanted but the description did not satisfy me. Is it possible to run a query that produce result set but also add/delete row?

Answer 1

You can't guarantee this. Possibly problematic cases:

• If multiple queries are allowed (allowMultiQueries=true for MySQL), a user might execute something which performs an update and selects something.
• As a variant of this, some DBMSes allow you execute blocks of procedural code which could perform updates and return rows.
• Some DBMSes (e.g. Firebird, PostgreSQL) have a RETURNING clause, which makes a modifying DML statement behave as a result set producing statement, while some other DBMSes support a variant of the SQL standard data change delta table, which allows you to use a modifying DML statement as a table in a select statement (e.g. select * from new table (insert into example (x, y) values (1, 2)))
• A user might call a table-valued procedure or function or use a function in a select which performs data modification
• ... possibly other scenarios ...

I'm not that familiar with MySQL, so I can't say 100% certain if (all of) my scenarios are correct for MySQL, but this pattern is common enough that I suspect some or all of them apply.

In other words, things that produce a result set are not necessarily only reading data.

In addition, some JDBC drivers will actually execute the statement, and then find out it didn't produce a result set and then produce an exception. Depending on whether auto-commit was enabled or not, and how auto-commit is implemented (server-side or driver-side), your statement may already have been executed and maybe even committed!

If you want to prevent a user from modifying your database you need to use one of the following:

• Use a locked down user account which only has select permission to (relevant) tables, and no permissions to any procedures, functions, etc which can modify data or perform DDL
• Use a replication solution, and connect to a read-only replica of the source database