Spring Boot data.sql initialization of H2 database has issue with backslash followed by single quote

In some Spring Boot app I initialize my in-memory H2 DB with some inserts (in the data.sql file). One of the tables in the DB is filled with rows where each row has a string column, and every string in this column ends with a backslash e.g. '\1\'.

The backslashes are not treated as an escape character hence inserting '\1' inserts this string literally (no need for double backslashes). However, the backslash before the single quote seems to escape that single quote, as inserting '\1\' causes a syntax-related exception. If I insert '\1\\' then that string is put in the DB literally (with the two backslashes at the end).

In the H2 docs relating to the string data type I can't find any information on this. I tried prefixing my strings with U& to use unicode chars but this throws an error as well. The only fix I found so far is to add a space after the last backslash and do an update after insertion (using the TRIM function) to remove said spaces.

Is this behaviour explained anywhere, or is there a better way to circumvent the issue?

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,'\');

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

I have the following in my application.properties :

server.port=8083
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;INIT=CREATE SCHEMA IF NOT EXISTS TEST
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.data=classpath:data-test.sql
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.javax.persistence.schema-generation.drop-source=script
spring.jpa.properties.javax.persistence.schema-generation.drop-script-source=drop-tables.sql
spring.jpa.properties.hibernate.default_schema=TEST
spring.cache.jcache.config=classpath:ehcache.xml
spring.h2.console.enabled=true
spring.application.admin.jmx-name=org.springframework.boot:type=Admin,name=SpringApplication${random.int(1000000)}

• Expected behaviour : script runs fine
• Actual behaviour : script fails
  to run, because the semicolon in the second inserted tuple is
  interpreted as the end of the insert statement. If you add a space
  after the backslash in the first inserted tuple, the script does run.
  If you reverse the order of the inserts, it also runs.

H2 driver version 1.4.200

The issue was caused by 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 modus. 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.

One way to circumvent this is to use unicode :

U&'|005c' UESCAPE '|'

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

The related Spring issue report can be found at <https://github.com/spring-projects/spring-framework/issues/30098>.