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!

I would suggest creating a database account with only rights to perform selects .... — Stephen C, Oct 19 '15 at 13:00
@StephenC I was thinking the same.@SalkinD Create user with read only rights on the specific tables and use that in your application for JDBC connection — Kumar Abhinav, Oct 19 '15 at 13:06
@StephenC, sqlite3 does not provide any "database accounts", and has no GRANT or REVOKE commands. I'm looking for something like statement.setReadOnly or similar — SalkinD, Oct 19 '15 at 13:16

score 7 · Accepted Answer · answered Oct 19 '15 at 13:21

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.

score 3 · Answer 2 · edited May 23 '17 at 11:45

3

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.

edited May 23 '17 at 11:45

Community

1
1

answered Oct 19 '15 at 13:06

andyroberts

3,458
2
37
40

thanks, also though of that. Nevertheless in this case I have to close my active connection for that, and to reopen it again after this action. I'm looking for a solution that does not need close/open/close/open – SalkinD Oct 19 '15 at 13:13
Read/Write access is at the driver level, not the app level. One trick is to maintain a pool of connections, and re-open as necessary for the purpose. It's more complicated than One Connection To Rule Them All, especially for single-client style applications, but it means you can purpose the connection for the application at hand. It can also make the application more resilient to certain kinds of DB exceptions. – Oct 19 '15 at 13:32

score 2 · Answer 3 · edited May 23 '17 at 12:33

If you use some sort of factory class to create or return connections to the database, you can individually set connections to be read-only:

public Connection getReadOnlyConnection() {
    // Alternatively this could come from a connection pool:
    final Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db");
    conn.setReadOnly(true);
    return conn;
}

If you're using a connection pool, then you may also want to provide a method for getting writeable connections too:

public Connection getWriteableConnection() {
     final Connection conn = getPooledConnection(); // I'm assuming this method exists!
     conn.setReadOnly(false);
     return conn;
}

You could also provide just a single getConnection(boolean readOnly) method and simply pass the parameter through to the setReadOnly(boolean) call. I prefer the separate methods personally, as it makes your intent much clearer.

Alternatively, some databases like Oracle provide a read only mode that can be enabled. SQLite doesn't provide one, but you can emulate it by simply setting the actual database files (including directories) to read only on the filesystem itself.

Another way of doing it is as follows (credit goes to deadlock for the below code):

public Connection getReadOnlyConnection() {
    SQLiteConfig config = new SQLiteConfig();
    config.setReadOnly(true);
    Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db",
            config.toProperties());
 }

Java jdbc - how to execute a statement strictly read only

3 Answers3