Escape literal used in the update query does not work fails with parsing exception.

[OracleCloud99]

trafficstars

Handle handle = Jdbi.create(ds).open();
        String updateQuery = "UPDATE TABLE1 SET COLUMN1=0," +
                "  COLUMN2=1 WHERE COULMN4= 'xyz' "+
                " AND PARENT_ID='123'" +
                " AND (COLUMN3 LIKE 'work' OR COLUMN3 like 'type' ESCAPE '\\'  )";

        int s = handle.createUpdate(updateQuery)               
                .execute();

Throws

Caused by: java.lang.IllegalArgumentException: MismatchedTokenException(-1!=39)
	at org.jdbi.v3.core.internal.lexer.DefineStatementLexer.reportError(DefineStatementLexer.java:30)
	at org.antlr.runtime.Lexer.nextToken(Lexer.java:99)
	at org.jdbi.v3.core.statement.DefinedAttributeTemplateEngine.render(DefinedAttributeTemplateEngine.java:64)
	... 6 more
Caused by: MismatchedTokenException(-1!=39)
	at org.antlr.runtime.Lexer.match(Lexer.java:206)
	at org.jdbi.v3.core.internal.lexer.DefineStatementLexer.mQUOTE(DefineStatementLexer.java:55)
	at org.jdbi.v3.core.internal.lexer.DefineStatementLexer.mQUOTED_TEXT(DefineStatementLexer.java:327)
	at org.jdbi.v3.core.internal.lexer.DefineStatementLexer.mTokens(DefineStatementLexer.java:1092)
	at org.antlr.runtime.Lexer.nextToken(Lexer.java:85)
	... 7 more

[stevenschlansker]

I'm not sure I understand the intent of this expression. You write AND (... ESCAPE '\\' ) which sure looks like an unterminated single-quoted expression, since you have escaped what would be the closing quote otherwise.

Is this valid SQL if you just put it in at the SQL prompt? What does it look like there? Why must you use ' as an escape character?

[qualidafial]

The error you are seeing is correct. The \\ in your Java code resolves to a single \ character in the actual String, e.g.:

UPDATE TABLE1 SET COLUMN1=0,
COLUMN2=1
WHERE COULMN4= 'xyz'
AND PARENT_ID='123'
AND (COLUMN3 LIKE 'work' OR COLUMN3 like 'type' ESCAPE '\'  )

The parser treats '\' as a start of string ' followed by an escaped single quote \'. So the parser error is actually correct.

You want to double-escape the backslash to get the desired effect:

String updateQuery = "UPDATE TABLE1 SET COLUMN1=0," +
        "  COLUMN2=1 WHERE COULMN4= 'xyz' "+
        " AND PARENT_ID='123'" +
        " AND (COLUMN3 LIKE 'work' OR COLUMN3 like 'type' ESCAPE '\\\\'  )";

Thus the final String that gets executed as SQL will be:

UPDATE TABLE1 SET COLUMN1=0,
COLUMN2=1
WHERE COULMN4= 'xyz'
AND PARENT_ID='123'
AND (COLUMN3 LIKE 'work' OR COLUMN3 like 'type' ESCAPE '\\'  )

[OracleCloud99]

@qualidafial Thanks for your response. I did try adding double escape made no difference to the JDBI parsing. When the query has only one like condition JDBI recognizes the ESCAPE '' literal, only when multiple likes are present i see this issue.

[qualidafial]

So the error you're seeing is from Jdbi's SQL template engine. The default template engine substitutes <foo>-style markers in the SQL string with defined attributes, e.g. via .define("foo", 7).

If you don't need that token substitution, you could possibly work around this by configuring a no-op template engine so Jdbi didn't try to parse the '\' thing:

TemplateEngine noOp = (template, ctx) -> template;

Ways to apply the template engine:

// globally
jdbi.getConfig(SqlStatements.class).setTemplateEngine(noOp);

// on a single handle
handle.getConfig(SqlStatements.class).setTemplateEngine(noOp);

// inline on a single SQL statement
handle.createUpdate(sql)
      .configure(SqlStatements.class,
                 config -> config.setTemplateEngine((template, ctx) -> template))
      .execute();

[qualidafial]

Hmm.. that probably won't buy you much since the named parameter lexer will still recognize the '\' as an unterminated string.

[qualidafial]

I'm afraid I don't have any quick fixes. Is it possible to use a different escape character?

[OracleCloud99]

@qualidafial hmm No I cannot change the escape character, its a standard across the product. Can this bug be addressed any sooner ? Thanks for your help!

[qualidafial]

What about a q string? e.g. q'[\\]'?

[qualidafial]

The next option I can think of is to define Oracle-specific template engine and SQL parser, to explicitly allow TEMPLATE '\' through without an error. That's a bit more work and might not be perfect on the first try.

[Gaibhne]

This issue is much worse: any string ending in a \ will trigger it. Even a simple "INSERT INTO folders (path) VALUES ('c:\\')" will cause it, making it, as far as I can tell, impossible to execute raw SQL if any string in it ends on a \.

[stevenschlansker]

Why don't you bind your c:\ path into a bound parameter? This issue does not affect you unless you template your values into the SQL string, which is not secure and generally an anti-pattern.

[Gaibhne]

Our particular use case is importing SQL files, which may contain any valid SQL, so this issue does affect us.

[zn8ae]

Following this issue. Is there any solution to the multiple Likes & Escapes condition? Or is it only because the '/' ?

[mishra2611]

we can use the @Define annotation to handle special characters. This will help jdbi treat the string as constant