MySQL’s allowMultiQueries flag with JDBC and jOOQ – Java, SQL and jOOQ.

MySQL’s JDBC connector has a safety function known as allowMultiQueries, which defaults to false. When turned off, it prevents utilizing a helpful, however probably harmful function in MySQL by way of JDBC:

attempt (Assertion s = connection.createStatement()) {
    attempt {
        s.execute("create desk t (i int);");

        // This does not work, by default:
        s.executeUpdate("""
            insert into t values (1);
            insert into t values (2);
        """);
    }
    lastly {
        s.execute("drop desk t");
    }
}

By default, the above produces a syntax error:

Exception in thread "predominant" java.sql.SQLSyntaxErrorException: You will have an error in your SQL syntax; verify the handbook that corresponds to your MySQL server model for the correct syntax to make use of close to '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.predominant(JDBC.java:34)

We will’t chain statements like that, until we activate allowMultiQueries=true within the JDBC connection URL:

jdbc:mysql://localhost/take a look at?allowMultiQueries=true

And now, immediately, the assertion batch completes usually, and two data are inserted into the desk.

Why this function?

The safety function helps forestall some SQL injection vulnerabilities. It’s now a lot more durable to append extra statements in case you’ve gotten the dangerous thought of concatenating values to your string, resembling:

// Horrible thought:
s.executeUpdate("insert into t values (" + worth + ")");

As a result of, what if worth contained the string "1); drop desk t;"? It could be syntactically appropriate, so it will execute “as anticipated.” That wouldn’t be very good.

Don’t get a false sense of safety now. Turning off this functionality won’t forestall all SQL injection vulnerabilities. Simply make this explicit one more durable. There are nonetheless numerous methods this explicit lack of utilizing a bind variable can result in an attacker studying your information, e.g. via a time-based assault.

The chance of SQL injection must be taken severely. The perfect factor is to at all times write static SQL with bind variables (e.g. PreparedStatement, saved procedures, or jOOQ), or a SQL builder like jOOQ for dynamic SQL.

Utilizing allowMultiQueries in jOOQ

When utilizing jOOQ, the above scenario could be very unlikely to occur. The default utilization of jOOQ is utilizing:

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

Solely in uncommon instances would you employ plain SQL templating to work round a particular lack of performance in jOOQ, and in that case, the templating language will make it easier to keep away from concatenating strings and working into SQL injection vulnerabilities.

For those who’re of the cautious kind, you possibly can add an annotation processor to your construct that stops utilizing the plain SQL API in jOOQ (any utilization received’t compile by default, until you explicitly choose in).

So, the MySQL flag isn’t actually helpful in your jOOQ utilization. In reality, it’s even an issue, as a result of jOOQ internally depends on producing assertion batches because the above. Listed below are some options that don’t work accurately if you flip off allowMultiQueries=false (most of those additionally apply to MariaDB, btw):

GROUP_CONCAT

Everytime you use GROUP_CONCAT in jOOQ on MySQL, jOOQ assumes you haven’t already modified MySQL’s default worth for @@group_concat_max_length. The default worth is extraordinarily low, specifically 1024. And never solely does that worth forestall the string aggregation of bigger information units, it simply fails silently, which produces mistaken values!

When emulating JSON_ARRAYAGG() in MySQL utilizing GROUP_CONCAT, there’s often a detectable syntax error within the ensuing JSON array, however this isn’t the case if you simply wish to produce some string values, e.g. a comma separated listing. (See this earlier weblog why we don’t use the native JSON_ARRAYAGG() help but).

So, what jOOQ does each time you employ GROUP_CONCAT explicitly (or jOOQ makes use of it internally for some emulation), jOOQ will prepend and append the next statements:

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

-- Precise assertion right here:
SELECT group_concat(A SEPARATOR ',') FROM T;

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

For those who already fastened the system or session variable your self, you possibly can flip off this function by altering the Settings.renderGroupConcatMaxLenSessionVariable flag.

CREATE OR REPLACE FUNCTION

Many SQL dialects have a CREATE OR REPLACE syntax for saved procedures, features, triggers, and different saved objects that don’t comprise information. It’s very helpful syntax sugar for penning this, as a substitute:

-- A lot easier
CREATE OR REPLACE FUNCTION f ...

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

However once more, for those who flip off allowMultiQueries=false, then this emulation in jOOQ received’t work and also you get a syntax error once more. There’s nothing jOOQ can do right here for you. You’d need to manually run the 2 statements, as a substitute of utilizing the comfort syntax.

FOR UPDATE WAIT n

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

SELECT *
FROM t
FOR UPDATE WAIT n;

MySQL 8.0.26 doesn’t help this function but, however since jOOQ 3.15 and #11543, we’re emulating the above syntax utilizing this:

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

One other factor that wouldn’t work for those who had allowMultiQueries=false

Nameless blocks

Many procedural languages help nameless blocks of procedural code, i.e. procedural code that’s not saved in a process. It makes excellent sense. In spite of everything, we don’t need to retailer all of our SQL in views both, so why do we’ve to retailer our PL/SQL, T-SQL, PL/pgSQL, and many others. in a saved process? This may be very helpful particularly if you wish to generate these blocks dynamically, utilizing jOOQ to run some logic on the server moderately than the consumer, lowering spherical journeys.

In Oracle, you possibly can write:

BEGIN
  INSERT INTO t VALUES (1);

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

jOOQ has began supporting such nameless blocks since 3.12. Have a look at the handbook web page concerning the IF assertion. You possibly can write:

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

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

ctx.start(
  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 interprets to and executes the right procedural nameless blocks in these dialects that help them, however MySQL 8.0.26 sadly doesn’t, but, so what will we do? We generate an “nameless” process, name it, and drop it once more:

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 imply, why not. However once more, this depends on allowMultiQueries=true, in any other case, the JDBC driver will reject this assertion.

For more information concerning the procedural language API in jOOQ, please consult with: https://weblog.jooq.org/vendor-agnostic-dynamic-procedural-logic-with-jooq/

Conclusion

MySQL’s JDBC driver has a pleasant safety function that’s supposed to stop some instances of SQL injection, particularly when customers use the JDBC connection for handbook SQL execution. There’s at all times that one poor soul on the crew that doens’t learn about SQL injection but, and thus will get it mistaken, opening pandora’s field. For these usages, allowMultiQueries=false is an affordable default.

When utilizing jOOQ as jOOQ was supposed for use, SQL injection is a lot much less doubtless. This excludes plain SQL templating usages, in case of which this text doesn’t apply, although. Alternatively, jOOQ internally depends on allowMultiQueries=true to allow just a few emulations that require a number of statements to be executed in a single spherical journey.

A future jOOQ model will permit for configuring the execution mannequin for multi queries, such that the above may be executed as a number of spherical journeys. See #9645 for extra particulars.

Till then, if you wish to get probably the most out of jOOQ with MySQL, make sure to activate allowMultiQueries=true in your jOOQ connection, probably preserving it turned off elsewhere.