Spring Ignore comments when searching for SQL statement delimiter in ScriptUtils

Spring Version: 5.3.4 JDK: 15

The spring utility class org.springframework.jdbc.datasource.init.ScriptUtils falsely interepts single quote literals found within block comments of SQL resource files.

To understand the impact of this bug, I'll illustrate a scenario.

schema.sql

/*
    It's function is as follows..
*/

ALTER TABLE public.User ADD CONSTRAINT
USER_PK PRIMARY KEY(id); 
...

From the resource file above, let's assume that the statement delimiter is that of ScriptUtils.DEFAULT_STATEMENT_SEPARATOR (';') and, accordingly, default comment block start & end separators are used as well ('/ ... /').

Let's also make another assumption: the single quote found in the block comment within the schema.sql resource file is the only single quote in the entire resource file.

In my java utility class, my goal is to execute the statements in my resource file against a given JDBC connection. It would look something as follows:

Resource resource = new ClassPathResource("classpath:schema.sql", Thread.currentThread().getContextClassLoader());
EncodedResource encoded = new EncodedResource(resource, "UTF-8");
ScriptUtils.executeSql (getConnection(), encoded);

Where getConnection() corresponds to my JDBC connection (assume it works accordingly).

Accordingly, I would be met with a fault that looks similar to the following:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement...
ALTER TABLE public.User ADD CONSTRAINT; ; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at end of input Position: 47

Notice how in the error message, it displays a partial SQL statement of what is intended. This is because the .executeSql(...) function call defaulted to its FALLBACK_STATEMENT_SEPARATOR, "\n". How do I know this?

The following is a fraction of code that is currently contained, at the time of writing this, is found within the .executeSql(...) internal call:

if (separator == null) {
    separator = DEFAULT_STATEMENT_SEPARATOR;
}
if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) {
    separator = FALLBACK_STATEMENT_SEPARATOR;
}

So I did a bit of digging and exploring within the internal check to containsSqlScriptDelimiters(...) and found the following:

public static boolean containsSqlScriptDelimiters(String script, String delim) {
        boolean inLiteral = false;
        boolean inEscape = false;

        for (int i = 0; i < script.length(); i++) {
            char c = script.charAt(i);
            if (inEscape) {
                inEscape = false;
                continue;
            }
            // MySQL style escapes
            if (c == '\\') {
                inEscape = true;
                continue;
            }
            if (c == '\'') {
                inLiteral = !inLiteral;
            }
            if (!inLiteral && script.startsWith(delim, i)) {
                return true;
            }
        }

        return false;
    }

The problem here is that it does not distinguish between literal single quotes from within block comments and executable statements. This is an issue because, going back to my resource file: schema.sql, since I declared only a single literal quote within my inital block comment, the parsing fails to acknowledge the separator delimiter ( ';', in this case) within my executable portion (it's waiting for the end literal character!) and falls back to using the FALLBACK_STATEMENT_SEPARATOR.

Here's the workaround I'm currently using:

/*
    It\'s function is as follows..
*/

ALTER TABLE public.User ADD CONSTRAINT
USER_PK PRIMARY KEY(id); 
...

Escape single quotes within comments (for now)!

Comment From: sbrannen

Thank you for bringing this to our attention.

While analyzing the issue, I discovered a separate issue regarding double quotes which will be addressed in #26935 for 5.3.x and backported to 5.2.x.

The issue you have raised here applies not only to block comments but also to single-line comments. In addition, it applies to the ScriptUtils implementations in spring-jdbc and spring-r2dbc.

We will investigate a fix for 5.3.x and consider a backport to 5.2.x.

In the interim, please continue using the workaround you discovered.

Comment From: sbrannen

This has been fixed for 5.3.8 in 569ce840cf720b6534e5ff670fc34eaa151b73cb and backported to 5.2.16 in e4d843e41e61bc93b95d2431075a886aebd552f8.

Feel free to try it out in the upcoming snapshots for 5.3.8 and 5.2.16.