Java jdbc - how to execute a statement strictly read only

Question

My server app uses prepared statements in almost all cases, to prevent sql injection. Nevertheless a possibility is needed providing special users executing raw SELECT queries.

How can I more or less securely make sure the query does not modify the database? Is it possible to execute a query read only, or is there any other 'secure' way making sure noone tries any sql injection? (Using sqlite3, so I cannot use any privileges)

Thanks a lot!

Answer 1

JDBC supports read-only connections by calling Connection.setReadOnly(true). However the javadoc says:

Puts this connection in read-only mode as a hint to the driver to enable database optimizations.

Some JDBC drivers will enforce the read-only request, others will use it for optimizations only, or simply ignore it. I don't know how sqlite3 implements it. You'll have to test that.

Otherwise, you could do a "simple" parse of the SQL statement, to ensure that it's a single valid SELECT statement.

Answer 2

I'm not aware of a general JBDC configuration which specifies readonly. But Sqlite does have special database open modes and this can be leveraged in your connection to your sqlite database. Eg.

Properties config = new Properties();
config.setProperty("open_mode", "1");  //1 == readonly
Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db", config);

Credit: https://stackoverflow.com/a/18092761/62344

FWIW All supported open modes can be seen here.