MySQL Bugs: #103612: Incorrectly identified WITH...SELECT as unsafe for read-only connections

Bug #103612	Incorrectly identified WITH...SELECT as unsafe for read-only connections
Submitted:	6 May 2021 13:03	Modified:	12 Oct 2021 16:36
Reporter:	Dillon Giacoppo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
`com.mysql.cj.jdbc.ClientPreparedStatement#execute` checks if a statement is read-only with `com.mysql.cj.jdbc.ClientPreparedStatement#checkReadOnlySafeStatement`.

It does this naively by checking the first character of the statment with `com.mysql.cj.ParseInfo#getFirstStmtChar` and assumes that it must equal 'S' (presumably for SELECT).

This logic does not work with common table expressions that may begin as 'WITH ... SELECT'. Since the first character is not 'S' the statement is determined to be unsafe over a read-only connection.

I have confirmed that executing the statement directly on the MySQL server over a read-only session shows no issue.

Workaround is to invoke the query over a writable connection.

The `checkReadOnlySafeStatement` should handle a 'WITH' clause. It is non-trivial to fix by also allowing 'W' as the first char since a WITH clause can be used for mutable statements such as update and delete. Therefore, a deeper check may need to be performed in ParseInfo.

How to repeat:
Using the following SQL:

```
WITH RECURSIVE cte AS (
    SELECT *
    FROM foo
    WHERE foo IN (1)
    UNION
    SELECT foo.*
    FROM foo
    JOIN cte ON (foo.id = cte.id)
)
SELECT *
FROM cte;
```

The exception is thrown:

```
Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:318)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:214)
        at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4217)
```

(Both statement and stack trace abbreviated). I do not have a full copy-paste repro readily available as a lot of the statement preparation has been abstracted away with JOOQ.

Hi Mr. Giacoppo,

Thank you for your bug report.

We agree fully with your analysis and conclusions.

Verified as reported.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.27 changelog: 

"When using client-side prepared statements with read-only connections, Connector/J checks whether a statement should be executed just by looking at the first letter, only executing statements starting with as "S" [for SELECT]. This approach excluded some valid statements (for example, SELECT statements starting with a WITH clause). With this fix, Connector/J performs more accurate checks by looking at the statement keywords and the context, and it is also permissive on statements it is unsure about."

Posted by developer:
 
Corrected the changelog entry to the following: 

"When Statement.executeQuery() was called, Connector/J's check for whether a statement would return results was inadequate, so that sometimes appropriate statements were rejected (for examples, SELECT statements starting with a WITH clause, statements preceded by consecutive comments, and so on) and, at other times, inappropriate statements were executed (for example, DO statements), resulting in various kinds of errors. With this fix, Connector/J performs more accurate checks by looking at the statement keywords and the context, as well as handling properly different corner cases. In this new mechanism, Connector/J takes a permissive approach: statements that might return results are allowed to run."