MySQL Bugs: #89283: Confusing read-only flag behaviour in MySQL Connector jdbc driver

Bug #89283	Confusing read-only flag behaviour in MySQL Connector jdbc driver
Submitted:	17 Jan 2018 13:36	Modified:	20 Jan 2018 13:32
Reporter:	Dmitriy Tseyler	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.45	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	read-only

Description:
MySQL Connector/J handles queries in read-only mode in a wrong way. If read-only mode is on only queries starting with 'S' can be executed. It is wrong, because not all read-only queries starts with 'S'. It is wrong because user can execute some stored procedure which leads to data modification using "select some_fun()".  It is wrong from a logical point of view. People who uses read-only flag with old driver can accidentally modify  something because they are rely on the driver's read-only property but it doesn't work as expected.

How to repeat:
Using mysql connector/j:

Connection c = getConnection()
c.createStatement().execute("create table test_table(id int)");
c.setReadOnly(true);
c.createStatement().execute("desc test_table()");

Suggested fix:
It would be better to remove the code which checks first letter in the query and throws exception. If it is not possible, please, add some property to disable this crazy logic.

"desc test_table" instead of "desc test_table()" of course

Hello Dmitriy Tseyler,

Thank you for the bug report testcase.
I could not repeat the issue at our end using with Connector / J  5.1.45, MySQL 5.7.20 version.
Please use ".executeQuery" to run the query.

Thanks,
Chiranjeevi.

Here is the 100% reproducible example:

import java.sql.*;
import java.util.Properties;

public class TestJdbc {
  public static void main(String[] args) throws SQLException {
    Driver driver = DriverManager.getDriver("jdbc:mysql://localhost:33057/guest?user=guest&password=guest");
    Connection connection = driver.connect("jdbc:mysql://localhost:33057/guest?user=guest&password=guest", new Properties());
    DatabaseMetaData data = connection.getMetaData();
    System.out.println(data.getDriverVersion());
    System.out.println(data.getDatabaseProductVersion());
    try {
      connection.createStatement().execute("drop table test_tab");
    }
    catch (Throwable ignore) {
    }
    connection.createStatement().execute("create table test_tab(id int)");
    connection.setReadOnly(true);
    connection.createStatement().execute("desc test_tab");
  }
}

About using executeQuery(). It also works in the wrong way, for example I can execute REPLACE using executeQuery(). And I can execute stored procedure which modifies the data using executeQuery(). 
I've attached screenshot of reproducing.

I think if there is a layer which handles read only in driver it should works correct or shouldn't exists at all.

Screenshot of reproducing

Attachment: mysql-bug-data-89283.png (image/png, text), 337.84 KiB.

Hi Dmitriy,

Thank you again for reporting this and for your interest in Connector/J.

Just to clarify, unless you set the connection property 'readOnlyPropagatesToServer=false' (which I don't recommend), when you set the connection as read-only you don't risk changing any data accidentally thereafter. It is true that you could still do it but that would involve changing the session transaction mode inside the stored procedure for example. But there's no way for us to prevent that from the connector side, you should know your stored procedures.

With regard to allowing to execute a REPLACE statement with Statement.executeQuery() this is a bug and will be fixed.

With regard to preventing the execution of non-DML statements on a read-only connection via Statement.execute() this is also a bug and will be fixed.

Although I understand that this is very important for you, both situations are minor issues and the workarounds to avoid them are actually the right way of doing them, so I'm dropping this report's severity to S3. If you have any objections please let us know.

Thanks, Fillip. I'll really wait for the fixes.