MySQL’s allowMultiQueries flag with JDBC and jOOQ

MySQL’s JDBC connector has a security feature called allowMultiQueries, which defaults to false. When turned off, it prevents using a useful, but potentially dangerous feature in MySQL via JDBC:

try (Statement s = connection.createStatement()) {
    try {
        s.execute("create table t (i int);");

        // This doesn't work, by default:
        s.executeUpdate("""
            insert into t values (1);
            insert into t values (2);
        """);
    }
    finally {
        s.execute("drop table t");
    }
}

By default, the above produces a syntax error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into t values (2)' at line 2
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1333)
	at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2106)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1243)
	at org.jooq.testscripts.JDBC.main(JDBC.java:34)

We can’t chain statements like that, unless we turn on allowMultiQueries=true in the JDBC connection URL:

jdbc:mysql://localhost/test?allowMultiQueries=true

And now, suddenly, the statement batch completes normally, and two records are inserted into the table.

Why this feature?

The security feature helps prevent some SQL injection vulnerabilities. It is now much harder to append additional statements in case you have the bad idea of concatenating values to your string, such as:

// Terrible idea:
s.executeUpdate("insert into t values (" + value + ")");

Because, what if value contained the string "1); drop table t;"? It would be syntactically correct, so it would execute “as expected.” That wouldn’t be very nice.

Don’t get a false sense of security now. Turning off this capability will not prevent all SQL injection vulnerabilities. Just make this particular one harder. There are still various ways this particular lack of using a bind variable can lead to an attacker reading your data, e.g. through a time-based attack.

The risk of SQL injection needs to be taken seriously. The best thing is to always write static SQL with bind variables (e.g. PreparedStatement, stored procedures, or jOOQ), or a SQL builder like jOOQ for dynamic SQL.

Using allowMultiQueries in jOOQ

When using jOOQ, the above situation is very unlikely to happen. The default usage of jOOQ is using:

  • The code generator to generate database meta data
  • The DSL to generate SQL

Only in rare cases would you use plain SQL templating to work around a specific lack of functionality in jOOQ, and in that case, the templating language will help you avoid concatenating strings and running into SQL injection vulnerabilities.

If you’re of the careful sort, you can add an annotation processor to your build that prevents using the plain SQL API in jOOQ (any usage won’t compile by default, unless you explicitly opt in).

So, the MySQL flag isn’t really useful for your jOOQ usage. In fact, it’s even a problem, because jOOQ internally relies on generating statement batches as the above. Here are some features that don’t work correctly when you turn off allowMultiQueries=false (most of these also apply to MariaDB, btw):

GROUP_CONCAT

Whenever you use GROUP_CONCAT in jOOQ on MySQL, jOOQ assumes you haven’t already changed MySQL’s default value for @@group_concat_max_length. The default value is extremely low, namely 1024. And not only does that value prevent the string aggregation of larger data sets, it just fails silently, which produces wrong values!

When emulating JSON_ARRAYAGG() in MySQL using GROUP_CONCAT, there’s usually a detectable syntax error in the resulting JSON array, but this isn’t the case when you just want to produce some string values, e.g. a comma separated list. (See this previous blog why we don’t use the native JSON_ARRAYAGG() support yet).

So, what jOOQ does every time you use GROUP_CONCAT explicitly (or jOOQ uses it internally for some emulation), jOOQ will prepend and append the following statements:

-- These are prepended
SET @t = @@group_concat_max_len;
SET @@group_concat_max_len = 4294967295;

-- Actual statement here:
SELECT group_concat(A SEPARATOR ',') FROM T;

-- These are appended
SET @@group_concat_max_len = @t;

If you already fixed the system or session variable yourself, you can turn off this feature by changing the Settings.renderGroupConcatMaxLenSessionVariable flag.

CREATE OR REPLACE FUNCTION

Many SQL dialects have a CREATE OR REPLACE syntax for stored procedures, functions, triggers, and other stored objects that do not contain data. It’s very useful syntax sugar for writing this, instead:

-- Much simpler
CREATE OR REPLACE FUNCTION f ...

-- Than this
DROP FUNCTION IF EXISTS f;
CREATE FUNCTION f ...

But again, if you turn off allowMultiQueries=false, then this emulation in jOOQ won’t work and you get a syntax error again. There’s nothing jOOQ can do here for you. You’d have to manually run the two statements, instead of using the convenience syntax.

FOR UPDATE WAIT n

Many dialects have a FOR UPDATE WAIT n syntax that allows for specifying a WAIT timeout for pessimistic locks, e.g.

SELECT *
FROM t
FOR UPDATE WAIT n;

MySQL 8.0.26 doesn’t support this feature yet, but since jOOQ 3.15 and #11543, we’re emulating the above syntax using this:

SET @t = @@innodb_lock_wait_timeout;
SET @@innodb_lock_wait_timeout = 2;
SELECT *
FROM t
FOR UPDATE;
SET @@innodb_lock_wait_timeout = @t;

Another thing that wouldn’t work if you had allowMultiQueries=false

Anonymous blocks

Many procedural languages support anonymous blocks of procedural code, i.e. procedural code that is not stored in a procedure. It makes perfect sense. After all, we don’t have to store all of our SQL in views either, so why do we have to store our PL/SQL, T-SQL, PL/pgSQL, etc. in a stored procedure? This can be very useful especially when you want to generate these blocks dynamically, using jOOQ to run some logic on the server rather than the client, reducing round trips.

In Oracle, you can write:

BEGIN
  INSERT INTO t VALUES (1);

  IF TRUE THEN
    INSERT INTO t VALUES (2);
  END IF;
END;

jOOQ has started supporting such anonymous blocks since 3.12. Look at the manual page about the IF statement. You can write:

// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType;

// Then write:
Variable<Integer> i = var("i", INTEGER);

ctx.begin(
  declare(i).set(1),

  if_(i.eq(0)).then(
    insertInto(A).columns(A.COL).values(1)
  ).elsif(i.eq(1)).then(
    insertInto(B).columns(B.COL).values(2)
  ).else_(
    insertInto(C).columns(C.COL).values(3)
  )
).execute();

This translates to and executes the correct procedural anonymous blocks in those dialects that support them, but MySQL 8.0.26 unfortunately doesn’t, yet, so what do we do? We generate an “anonymous” procedure, call it, and drop it again:

CREATE PROCEDURE block_1629705082441_2328258() 
BEGIN
DECLARE i INT; 
  SET i = 1; 

  IF i = 0 THEN
    INSERT INTO a (col) VALUES (1);
  ELSEIF i = 1 THEN
    INSERT INTO b (col) VALUES (2);
  ELSE
    INSERT INTO c (col) VALUES (3);
  END IF; 
END; 
CALL block_1629705082441_2328258(); 
DROP PROCEDURE block_1629705082441_2328258;

I mean, why not. But again, this relies on allowMultiQueries=true, otherwise, the JDBC driver will reject this statement.

For more info about the procedural language API in jOOQ, please refer to: https://blog.jooq.org/vendor-agnostic-dynamic-procedural-logic-with-jooq/

Conclusion

MySQL’s JDBC driver has a nice security feature that is intended to prevent some cases of SQL injection, especially when users use the JDBC connection for manual SQL execution. There’s always that one poor soul on the team that doens’t know about SQL injection yet, and thus gets it wrong, opening pandora’s box. For those usages, allowMultiQueries=false is a reasonable default.

When using jOOQ as jOOQ was intended to be used, SQL injection is much less likely. This excludes plain SQL templating usages, in case of which this article doesn’t apply, though. On the other hand, jOOQ internally relies on allowMultiQueries=true to enable a few emulations that require multiple statements to be executed in a single round trip.

A future jOOQ version will allow for configuring the execution model for multi queries, such that the above can be executed as multiple round trips. See #9645 for more details.

Until then, if you want to get the most out of jOOQ with MySQL, be sure to turn on allowMultiQueries=true on your jOOQ connection, possibly keeping it turned off elsewhere.

How I Incorrectly Fetched JDBC ResultSets. Again.

You know JDBC, right? It’s that really easy, concise API that we love to use to work with virtually any database, relational or not. It has essentially three types that you need to care about: All the other types some sort of utilities. Now, with the above three, we can do really nice and lean Java/SQL coding as follows:

try (Connection c = datasource.getConnection();
     Statement s = c.createStatement();
     ResultSet r = s.executeQuery("SELECT 'hello'")
) {
    while (r.next())
        System.out.println(r.getString(1));
}

Output:
hello
OK? Super easy.

Unless…

Unless you want to write generic JDBC code, because you don’t know what the SQL string is. It could be a SELECT statement. It could be and UPDATE. It could be DDL. It could be a statement batch (several statements). It could call triggers and stored procedures, which again produce nice things like warnings, exceptions, update counts, and additional result sets. You know, the sort of thing that might come flying in to a generic utility method like jOOQ’s ResultQuery.fetchMany(). (Don’t think this couldn’t happen to you as well. SQL Server triggers are really mean things!) For this, let’s consider the correct way to execute the following simple statement batch that works wonderfully in SQL Server:

raiserror('e0', 0, 2, 3);
create table t(i int);
raiserror('e1', 5, 2, 3);
insert into t values (1);
raiserror('e2', 10, 2, 3);
insert into t values (2);
raiserror('e3', 15, 2, 3);
select * from t;
drop table t;
raiserror('e4', 16, 2, 3);

The result is: And obviously For your convenience, I have pre-formatted the above String into a Java String variable, which is already the first problem, because Java STILL doesn’t have multi-line strings (gaah):

String sql =
    "\n raiserror('e0', 0, 2, 3);"
  + "\n create table t(i int);"
  + "\n raiserror('e1', 5, 2, 3);"
  + "\n insert into t values (1);"
  + "\n raiserror('e2', 10, 2, 3);"
  + "\n insert into t values (2);"
  + "\n raiserror('e3', 15, 2, 3);"
  + "\n select * from t;"
  + "\n drop table t;"
  + "\n raiserror('e4', 16, 2, 3);";

Now see, we might be inclined to just copy paste some JDBC snippet off some website (e.g. this blog, and take its first snippet) and execute it as such:

try (
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(sql)
) {
    while (r.next())
        System.out.println(r.getString(1));
}

Yeah. What’ll happen if we do this? Rats:
com.microsoft.sqlserver.jdbc.SQLServerException: e3
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:857)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:757)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2689)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:659)
	at SQLServer.main(SQLServer.java:80)
e3? What on earth? So what happened with my statement batch? Did it execute? Only until the middle? Or did I get to the end as well? OK, quite obviously, we have to do this more carefully. We cannot use Statement.executeQuery() here, because we don’t know whether we’ll get a result set. In fact, we got an exception, but not the first one. Let’s try something else. Let’s try this:

try (Statement s = c.createStatement()) {
    System.out.println(s.execute(sql));
}

That just yields:
false
Okaaay, did anything execute in the database at all? No more exceptions… Let me have a look at the SQL Server Profiler… Nope, the entire batch got executed. (Could’ve just removed the DROP TABLE statement and checked the contents of table T in SQL Server Management Studio, of course). Huh, quite a different result, depending on what method we’re calling. Does that scare you? Does your ORM get this right? (jOOQ didn’t but this is now fixed). OK, let’s read the Javadoc on Statement.execute() It says:
Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string. The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
Huh, OK. Statement.getResultSet() and getUpdateCount() must be used, and then getMoreResults() The getMoreResults() method also has this interesting bit of information:
There are no more results when the following is true: // stmt is a Statement object ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))
Interesting. -1. I guess we can be very happy that at least it’s not returning null or a punch in your face. So, let’s try this again:
  • We first have to call execute()
  • If it’s true, we fetch getResultSet()
  • If it’s false, we check getUpdateCount()
  • If that was -1, we can stop
Or, in code:

fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
    boolean result = (i == 0)
        ? s.execute(sql)
        : s.getMoreResults();

    if (result)
        try (ResultSet rs = s.getResultSet()) {
            System.out.println("Result      :");

            while (rs.next())
                System.out.println("  " + rs.getString(1));
        }
    else if ((updateCount = s.getUpdateCount()) != -1)
        System.out.println("Update Count: " + updateCount);
    else
        break fetchLoop;
}

Beautiful! Some remarks:
  • Note how the loop stops after 256 iterations. Never trust these infinite streaming APIs, there’s always a bug somewhere, trust me
  • The boolean value return from Statement.execute() and Statement.getMoreResults() is the same. We can assign it to a variable inside the loop and call execute only on the first iteration
  • If true, fetch the result set
  • If false, check the update count
  • If that was -1, stop
Run time!
Update Count: 0
Update Count: 1
Update Count: 1
com.microsoft.sqlserver.jdbc.SQLServerException: e3
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1270)
	at SQLServer.main(SQLServer.java:83)
Crap. But did it execute completely? Yes it did, but we didn’t get that sweet result set after e3, because of that exception. But at least, we now got 3 update counts. But wait a second, why didn’t we get e0, e1, and e2? AHA, they’re warnings, not exceptions. Funky SQL Server decided that everything below some severity level is a warning. Whatever. Anyway, let’s fetch those warnings as well!

fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
    boolean result = (i == 0)
        ? s.execute(sql)
        : s.getMoreResults();

    // Warnings here
    SQLWarning w = s.getWarnings();
    for (int j = 0; j < 255 && w != null; j++) {
        System.out.println("Warning     : " + w.getMessage());
        w = w.getNextWarning();
    }

    // Don't forget this
    s.clearWarnings();

    if (result)
        try (ResultSet rs = s.getResultSet()) {
            System.out.println("Result      :");

            while (rs.next())
                System.out.println("  " + rs.getString(1));
        }
    else if ((updateCount = s.getUpdateCount()) != -1)
        System.out.println("Update Count: " + updateCount);
    else
        break fetchLoop;
}

Great, so now we get all the warnings e0, e1, e2, and the exception e3, along with the update counts:
Warning     : e0
Update Count: 0
Warning     : e1
Update Count: 1
Warning     : e2
Update Count: 1
com.microsoft.sqlserver.jdbc.SQLServerException: e3
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1270)
	at SQLServer.main(SQLServer.java:82)
That’s more like our batch. But we’re still aborting after e3. How can we get the result set? Easy! Just ignore the exception, right? :) And while we’re at it, let’s use ResultSetMetaData to read the unknown result set type.

fetchLoop:
for (int i = 0, updateCount = 0; i < 256; i++) {
    try {
        boolean result = (i == 0)
            ? s.execute(sql)
            : s.getMoreResults();

        SQLWarning w = s.getWarnings();
        for (int j = 0; j < 255 && w != null; j++) {
            System.out.println("Warning     : " + w.getMessage());
            w = w.getNextWarning();
        }

        s.clearWarnings();

        if (result)
            try (ResultSet rs = s.getResultSet()) {
                System.out.println("Result      :");
                ResultSetMetaData m = rs.getMetaData();

                while (rs.next())
                    for (int c = 1; c <= m.getColumnCount(); c++)
                        System.out.println(
                            "  " + m.getColumnName(c) +
                            ": " + rs.getInt(c));
            }
        else if ((updateCount = s.getUpdateCount()) != -1)
            System.out.println("Update Count: " + updateCount);
        else
            break fetchLoop;
        }
    catch (SQLException e) {
        System.out.println("Exception   : " + e.getMessage());
    }
}

There, that’s more like it:
Warning     : e0
Update Count: 0
Warning     : e1
Update Count: 1
Warning     : e2
Update Count: 1
Exception   : e3
Result      :
  i: 1
  i: 2
Update Count: 0
Exception   : e4
Now we’ve executed the entire batch in a super generic way with JDBC

Gah, I want this to be easier

Of course you do, which is why there is jOOQ. jOOQ has the really nice fetchMany() methods, which can execute random SQL strings to get a mixture of:
  • Update counts
  • Result sets
  • Exceptions / Warnings (jOOQ 3.10+ only)
For example, we can write:

// Use this new setting to indicate that we don't want to throw
//  exceptions, but collect them, as we've seen above
DSLContext ctx = DSL.using(c, 
  new Settings().withThrowExceptions(THROW_NONE));

// And then, simply:
System.out.println(ctx.fetchMany(sql));

The result is of the form:
Warning: SQL [...]; e0
Update count: 0
Warning: SQL [...]; e1
Update count: 1
Warning: SQL [...]; e2
Update count: 1
Exception: SQL [...]; e3
Result set:
+----+
|   i|
+----+
|   1|
|   2|
+----+
Update count: 0
Exception: SQL [...]; e4
Excellent!

What we didn’t cover

Oh, tons of things, but I need material for future blog posts, too, right?
  • We only discussed SQL Server so far
  • We didn’t discuss the fact that SQLException.getNextException() doesn’t work here
  • We didn’t discuss how we can combine this with OUT parameters (eegh, at what moment do we fetch those)
  • We didn’t discuss the fact that some JDBC drivers don’t implement this correctly (looking at you, Oracle)
  • We didn’t go into the depths of how JDBC drivers don’t implement ResultSetMetaData correctly
  • We didn’t cover the performance overhead of fetching warnings, e.g. in MySQL
  • … and much more
So, are you still writing JDBC code yourself? :)