`ScriptUtils` fails to correctly parse string literal ending with backslash with H2 database

[vdkhvb]

It looks like inserting a row which has a string with a backslash at the end causes Spring's splitting of SQL files into statements to behave erratically.

Minimal example.

Setup : Spring Boot test suite with schema.sql and data.sql given below :

schema.sql

    CREATE SCHEMA IF NOT EXISTS TEST;
    
    CREATE TABLE TEST.ONE (
        IDENTIFIER BIGINT AUTO_INCREMENT PRIMARY KEY,
        NAME VARCHAR(500)
    );
    
    CREATE TABLE TEST.TWO (
        IDENTIFIER BIGINT AUTO_INCREMENT PRIMARY KEY,
        NAME VARCHAR(500)
    );

data.sql

    INSERT INTO TEST.ONE(IDENTIFIER,NAME) VALUES
        (1,'\1\3\');
    
    INSERT INTO TEST.TWO(IDENTIFIER,NAME) VALUES
        (1,'test;test');

This fails at my end because Spring splits the second inserted row around the semicolon inside 'test;test'.

The cause is Spring's ScriptUtils class in its splitSQLScript method. It's a method that splits the data.sql script into queries with the semicolon as the default delimiter. Whilst scanning the script, it keeps track of whether or not it's within a statement surrounded by single or double quotes. When it encounters a backslash, it enters a sort of escape mode. The character after this backslash as well as the backslash itself are left as-is, but if the character after the backslash is a single or double quote it's effectively treated as if that quote were to be escaped.

Since I'm using the H2 driver I could fix it via unicode formatting (I wasn't able to make this work with just U&'\1\005c') :

    U&'\1|005c' UESCAPE '|'

[sdeleuze]

Could you please let us know what database are you using and check if it accepts such SQL file when executed without going via Spring? What we could like to check here is if '\1\3\' is parsed as a valid string.

[sbrannen]

Following up on what @sdeleuze said, based on my understanding of the H2 grammar for strings, the proper way to write that first string is '\\1\\3\\'.

Can you verify if that works for you?

[vdkhvb]

Hey, thanks for the feedback.

Adding double backslashes doesn't work, they're inserted as-is (I can add '\1\\' to the DB and it will print out that exact string when fetching the row). H2 does not treat the backslash as an escape char, so I can add '\1\sometext' just fine.

I debugged what happens and added a description of the cause of the issue in my original post. It seems like the method in Spring (which is used to split the data.sql SQL statements) handles backslashes in a way I find weird : it doesn't escape anything preceded by a backslash, but if it finds a single/double quote right after a backslash it stays in the inSingleQuote or inDoubleQuote mode which causes a sort of virtual escaping of those quotes. This causes issues with the splitting when a delimiter (semicolon) is used inside a string in some other insert later down the line. I'm assuming it is not intended behaviour.

[sbrannen]

Hi @vdkhvb,

Thanks for trying out the double backslashes and reporting back with your findings.

Regarding your solution, I assume you meant U&'\1\3|005c' UESCAPE '|' to end up with \1\3\ in the database.

I wasn't able to make this work with just U&'\1\005c'

That was almost correct. You can actually achieve your goal without changing the unicode escape character (UESCAPE '|') by using double backslashes everywhere you want a single backslash in the result.

INSERT INTO TEST.ONE(IDENTIFIER, NAME) VALUES (1, U&'\\1\\3\005c'); -- Unicode 005c is a backslash "\"

At this time, I would consider that the best workaround for this issue.

Please try that out and let us know if that works for you.

---

As a side note, I noticed that you originally asked about this on Stack Overflow.

For future reference, it's often good to provide a link to such Stack Overflow questions (or other external discussions) in order to provide a cross reference and additional context.

[sbrannen]

it doesn't escape anything preceded by a backslash, but if it finds a single/double quote right after a backslash it stays in the inSingleQuote or inDoubleQuote mode which causes a sort of virtual escaping of those quotes. This causes issues with the splitting when a delimiter (semicolon) is used inside a string in some other insert later down the line. I'm assuming it is not intended behaviour.

The behavior is actually intentional and was originally added for \x style escaping support in MySQL.

A number of databases support backslash as an escape character; however, as you've mentioned, H2 database does not (unless you use the Unicode string literal feature).

Interestingly enough, you are the first person to report this issue. So I suppose this doesn't arise very often.

However, having said that, the current behavior can definitely be improved.

The crux of the issue is that the "MySQL style escapes" support is hard coded in both implementations of ScriptUtils (for JDBC as well as for R2DBC).

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/init/ScriptUtils.java

Lines 469 to 473 in 3431b23

	// MySQL style escapes
	if (c == '\\') {
	inEscape = true;
	continue;
	}

spring-framework/spring-r2dbc/src/main/java/org/springframework/r2dbc/connection/init/ScriptUtils.java

Lines 380 to 384 in 3431b23

	// MySQL style escapes
	if (c == '\\') {
	inEscape = true;
	continue;
	}

Thus, there is currently no way to disable this feature, which is in stark contrast to other features such as statement separators, comment prefixes, etc.

As a proof of concept, I commented out that inEscape code, and your original SQL functions as-is (which was to be expected).

So I think the only real way to address this is to make the check for "MySQL style escapes" an optional feature -- controlled by a new boolean flag which defaults to true for backward compatibility.

The problem with adding "yet another configuration property" for SQL script parsing is that the method signature of methods like ScriptUtils.splitSqlScript(EncodedResource, String, String, String[], String, String, List<String>) grow out of control over time. To address that, we have considered introducing a Parameter Object (design pattern) such as SqlScriptConfig, ScriptParserConfig, or similar.

Since we have collectively determined that a viable workaround exists with the current code base, I am assigning this to Spring Framework 6.1.x for the time being in order to investigate how best to introduce the additional configuration flag for script parsing.