feat: Configurable backslash \ escaping

- Enables `\` as escape character in String Literals (beside SQL:2016 compliant `'`) - Default is OFF (since its not SQL:2016 compliant) - Activate per Parser Feature - Fixes #1638 - Fixes #1209 - Fixes #1173 - Fixes #1172 - Fixes #832 - Fixes #827 - Fixes #578 BREAKING-CHANGE: Backslash Escaping needs to be activated explicitly or else Backslash won't work as Escape Character.
JSQLParser · wumpz · Jan 20, 2023 · Jan 11, 2023 · Jan 11, 2023 · Jan 11, 2023
commit 9d79d4b87aa8bced517635ab83a94aca2dc3b272
diff --git a/src/main/java/net/sf/jsqlparser/parser/AbstractJSqlParser.java b/src/main/java/net/sf/jsqlparser/parser/AbstractJSqlParser.java
@@ -36,6 +36,10 @@ public P withUnsupportedStatements(boolean allowUnsupportedStatements) {
     public P withTimeOut(int timeOutMillSeconds) {
         return withFeature(Feature.timeOut, timeOutMillSeconds);
     }
+
+    public P withBackslashEscapeCharacter(boolean allowBackslashEscapeCharacter) {
+        return withFeature(Feature.allowBackslashEscapeCharacter, allowBackslashEscapeCharacter);
+    }
 
     public P withFeature(Feature f, boolean enabled) {
         getConfiguration().setValue(f, enabled);

diff --git a/src/main/java/net/sf/jsqlparser/parser/feature/Feature.java b/src/main/java/net/sf/jsqlparser/parser/feature/Feature.java
@@ -759,7 +759,12 @@ public enum Feature {
      */
     allowUnsupportedStatements(false),
 
-    timeOut( 6000)
+    timeOut( 6000),
+
+    /**
+     * allows Backslash '\' as Escape Character
+     */
+    allowBackslashEscapeCharacter(false),
     ;
 
     private Object value;

diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -516,19 +516,45 @@ TOKEN:
 | <#Nd: ["0"-"9","٠"-"٩","۰"-"۹","߀"-"߉","०"-"९","০"-"৯","੦"-"੯","૦"-"૯","୦"-"୯","௦"-"௯","౦"-"౯","೦"-"೯","൦"-"൯","෦"-"෯","๐"-"๙","໐"-"໙","༠"-"༩","၀"-"၉","႐"-"႙","០"-"៩","᠐"-"᠙","᥆"-"᥏","᧐"-"᧙","᪀"-"᪉","᪐"-"᪙","᭐"-"᭙","᮰"-"᮹","᱀"-"᱉","᱐"-"᱙","꘠"-"꘩","꣐"-"꣙","꤀"-"꤉","꧐"-"꧙","꧰"-"꧹","꩐"-"꩙","꯰"-"꯹","０"-"９"]>
 | <#Pc: ["‿"-"⁀","⁔","︳"-"︴","﹍"-"﹏","＿"]>
 
-|   < S_CHAR_LITERAL: (["U","E","N","R","B"]|"RB"|"_utf8")? (("'" ( <ESC> | ~["'", "\\", "\n", "\r"] )* "'") | ("'" ("''" | ~["'"])* "'")) >
-|   < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["\n","\r","\""])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
-    { if ( !configuration.getAsBoolean(Feature.allowSquareBracketQuotation) && matchedToken.image.charAt(0) == '[' ) {
-         matchedToken.image = "[";
-         for (int i=0;i<CCJSqlParserConstants.tokenImage.length;i++) {
-            if (CCJSqlParserConstants.tokenImage[i].equals("\"[\"")) {
-                matchedToken.kind = i;
+| < #SPECIAL_ESC: "\\'" > /* Allowing this will break LIKE ... ESCAPE ...  */
+| < #ESC: "\\" ["n","t","b","r","f","\\","\""] >
+| < S_CHAR_LITERAL: (["U","E","N","R","B"]|"RB"|"_utf8")? (("'" ( <ESC> | <SPECIAL_ESC> | ~["'", "\\", "\n", "\r"] )* "'") | ("'" ("''" | ~["'"])* "'")) >
+    {
+        // <S_CHAR_LITERAL> contains the <QUOTED_BACKSLASH> definition and always the longest match is returned
+        // So when Backslash is explicitly not allowed as an Escape Character and a <S_CHAR_LITERAL> is found
+        // which starts with a Backslash, then we will need to
+        // 1) break off <QUOTED_BACKSLASH> and
+        // 2) continue tokenizing after that <QUOTED_BACKSLASH> in order to allow reading a single quoted backslash character
+        /*if ( !configuration.getAsBoolean(Feature.allowBackslashEscapeCharacter) && matchedToken.image.startsWith("'\\'") ) {
+            matchedToken.image = "'\\'";
+            for (int i=0;i<CCJSqlParserConstants.tokenImage.length;i++) {
+                if ( CCJSqlParserConstants.tokenImage[i].equals("<QUOTED_BACKSLASH>") ) {
+                    matchedToken.kind = i;
+                }
             }
-         }
-         input_stream.backup(image.length() - 1);
-       }
+            input_stream.backup(image.length() - matchedToken.image.length() - 1);
+        } else*/ if ( !configuration.getAsBoolean(Feature.allowBackslashEscapeCharacter) && matchedToken.image.contains("\\'") ) {
+            matchedToken.image = image.substring(0, image.indexOf("\\'")+1) + "'";
+            for (int i=0;i<CCJSqlParserConstants.tokenImage.length;i++) {
+              if ( CCJSqlParserConstants.tokenImage[i].equals("<S_CHAR_LITERAL>") ) {
+                  matchedToken.kind = i;
+              }
+            }
+            input_stream.backup(image.length() - matchedToken.image.length() );
+        }
+    }
+| < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["\n","\r","\""])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
+    {
+        if ( !configuration.getAsBoolean(Feature.allowSquareBracketQuotation) && matchedToken.image.charAt(0) == '[' ) {
+            matchedToken.image = "[";
+            for (int i=0;i<CCJSqlParserConstants.tokenImage.length;i++) {
+                if (CCJSqlParserConstants.tokenImage[i].equals("\"[\"")) {
+                    matchedToken.kind = i;
+                }
+            }
+            input_stream.backup(image.length() - 1);
+        }
     }
-|   < #ESC: "\\" ["n","t","b","r","f","\\","'","\""] >
 }
 
 
@@ -3452,10 +3478,17 @@ Expression LikeExpression(Expression leftExpression) #LikeExpression:
     LikeExpression result = new LikeExpression();
     Expression rightExpression = null;
     Expression escape;
+    Token token;
 }
 {
     [<K_NOT> { result.setNot(true); } ] ( <K_LIKE> | <K_ILIKE> { result.setCaseInsensitive(true); } ) rightExpression=SimpleExpression()
-    [ LOOKAHEAD(2) <K_ESCAPE> escape=Expression() { result.setEscape(escape); }]
+    [ LOOKAHEAD(2) <K_ESCAPE>
+        (
+            LOOKAHEAD(2)  token = <S_CHAR_LITERAL> { result.setEscape( new StringValue( token.image ) ); }
+            |
+            escape=Expression() { result.setEscape(escape); }
+        )
+    ]
     {
         result.setLeftExpression(leftExpression);
         result.setRightExpression(rightExpression);
@@ -4141,7 +4174,7 @@ DateTimeLiteralExpression DateTimeLiteralExpression() : {
     t=<S_CHAR_LITERAL> { expr.setValue(t.image); return expr; }
 }
 
-ArrayConstructor ArrayConstructor(final boolean arrayKeyword) : {
+ArrayConstructor ArrayConstructor(boolean arrayKeyword) : {
     ArrayList<Expression> expList = new ArrayList();
     ArrayConstructor array = new ArrayConstructor(expList, arrayKeyword);
     Expression exp = null;

diff --git a/src/test/java/net/sf/jsqlparser/expression/LikeExpressionTest.java b/src/test/java/net/sf/jsqlparser/expression/LikeExpressionTest.java
@@ -10,8 +10,12 @@
 package net.sf.jsqlparser.expression;
 
 import net.sf.jsqlparser.JSQLParserException;
+import net.sf.jsqlparser.parser.CCJSqlParserUtil;
+import net.sf.jsqlparser.statement.Statements;
 import net.sf.jsqlparser.test.TestUtils;
+import org.junit.jupiter.api.Assertions;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.function.Executable;
 
 /**
  *
@@ -25,4 +29,86 @@ public void testLikeWithEscapeExpressionIssue420() throws JSQLParserException {
 
         TestUtils.assertSqlCanBeParsedAndDeparsed("select * from dual where a LIKE ?1 ESCAPE ?2", true);
     }
+
+    @Test
+    public void testEscapeExpressionIssue1638() throws JSQLParserException {
+        String sqlStr = "select case \n"
+                        + "    when id_portfolio like '%\\_1' escape '\\' then '1'\n"
+                        + "    end";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+
+        Assertions.assertThrows(JSQLParserException.class, new Executable() {
+            @Override
+            public void execute() throws Throwable {
+                CCJSqlParserUtil.parse(
+                        sqlStr
+                        , parser -> parser.withBackslashEscapeCharacter(true)
+                );
+            }
+        });
+    }
+
+    @Test
+    public void testEscapingIssue1209() throws JSQLParserException {
+        String sqlStr="INSERT INTO \"a\".\"b\"(\"c\", \"d\", \"e\") VALUES ('c c\\', 'dd', 'ee\\')";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
+
+    @Test
+    public void testEscapingIssue1173() throws JSQLParserException {
+        String sqlStr="update PARAM_TBL set PARA_DESC = null where PARA_DESC = '\\' and DEFAULT_VALUE = '\\'";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
+
+    @Test
+    public void testEscapingIssue1172() throws JSQLParserException {
+        String sqlStr="SELECT A ALIA1, CASE WHEN B LIKE 'ABC\\_%' ESCAPE '\\' THEN 'DEF' ELSE 'CCCC' END AS OBJ_SUB_TYPE FROM TABLE2";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
+
+    @Test
+    public void testEscapingIssue832() throws JSQLParserException {
+        String sqlStr="SELECT * FROM T1 WHERE (name LIKE ? ESCAPE '\\') AND (description LIKE ? ESCAPE '\\')";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
+
+    @Test
+    public void testEscapingIssue827() throws JSQLParserException {
+        String sqlStr="INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value_1\\', 'my_value_2')";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
+
+    @Test
+    public void testEscapingIssue578() throws JSQLParserException {
+        String sqlStr="SELECT * FROM t1 WHERE UPPER(t1.TIPCOR_A8) like ? ESCAPE '' ORDER BY PERFILB2||TRANSLATE(UPPER(AP1SOL10 || ' ' || AP2SOL10 || ',' || NOMSOL10), '?', 'A') asc";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(false)
+        );
+    }
 }
diff --git a/src/test/java/net/sf/jsqlparser/statement/insert/InsertTest.java b/src/test/java/net/sf/jsqlparser/statement/insert/InsertTest.java
@@ -41,6 +41,7 @@
 import static org.junit.jupiter.api.Assertions.assertThrowsExactly;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 
+import net.sf.jsqlparser.test.TestUtils;
 import org.junit.jupiter.api.Disabled;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.function.Executable;
@@ -309,7 +310,12 @@ public void testModifierPriority3() throws JSQLParserException {
 
     @Test
     public void testIssue223() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("INSERT INTO user VALUES (2001, '\\'Clark\\'', 'Kent')");
+        String sqlStr="INSERT INTO user VALUES (2001, '\\'Clark\\'', 'Kent')";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
     @Test

diff --git a/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java b/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java
@@ -42,6 +42,7 @@
 import static net.sf.jsqlparser.test.TestUtils.*;
 
 import net.sf.jsqlparser.test.MemoryLeakVerifier;
+import net.sf.jsqlparser.test.TestUtils;
 import org.apache.commons.io.IOUtils;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.junit.jupiter.api.Assertions.assertEquals;
@@ -3066,29 +3067,53 @@ public void testIssue162_doubleUserVar() throws JSQLParserException {
         assertSqlCanBeParsedAndDeparsed("SELECT @@SPID AS ID, SYSTEM_USER AS \"Login Name\", USER AS \"User Name\"");
     }
 
-    @Test
-    public void testIssue167_singleQuoteEscape() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("SELECT 'a'");
-        assertSqlCanBeParsedAndDeparsed("SELECT ''''");
-        assertSqlCanBeParsedAndDeparsed("SELECT '\\''");
-        assertSqlCanBeParsedAndDeparsed("SELECT 'ab''ab'");
-        assertSqlCanBeParsedAndDeparsed("SELECT 'ab\\'ab'");
+    @ParameterizedTest
+    @ValueSource(strings = {
+            "SELECT 'a'"
+            , "SELECT ''''"
+            , "SELECT '\\''"
+            , "SELECT 'ab''ab'"
+            , "SELECT 'ab\\'ab'"
+    })
+    public void testIssue167_singleQuoteEscape(String sqlStr) throws JSQLParserException {
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
-    @Test
-    public void testIssue167_singleQuoteEscape2() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("SELECT '\\'''");
-        assertSqlCanBeParsedAndDeparsed("SELECT '\\\\\\''");
+    @ParameterizedTest
+    @ValueSource(strings = {
+            "SELECT '\\'''"
+            , "SELECT '\\\\\\''"
+    })
+    public void testIssue167_singleQuoteEscape2(String sqlStr) throws JSQLParserException {
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
     @Test
     public void testIssue77_singleQuoteEscape2() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("SELECT 'test\\'' FROM dual");
+        String sqlStr ="SELECT 'test\\'' FROM dual";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
     @Test
     public void testIssue223_singleQuoteEscape() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("SELECT '\\'test\\''");
+        String sqlStr = "SELECT '\\'test\\''";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
     @Test

diff --git a/src/test/java/net/sf/jsqlparser/statement/update/UpdateTest.java b/src/test/java/net/sf/jsqlparser/statement/update/UpdateTest.java
@@ -24,6 +24,8 @@
 import static org.junit.jupiter.api.Assertions.assertFalse;
 import static org.junit.jupiter.api.Assertions.assertThrows;
 import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import net.sf.jsqlparser.test.TestUtils;
 import org.junit.jupiter.api.Test;
 
 public class UpdateTest {
@@ -79,7 +81,13 @@ public void testUpdateWithSelect2() throws JSQLParserException {
 
     @Test
     public void testUpdateIssue167_SingleQuotes() throws JSQLParserException {
-        assertSqlCanBeParsedAndDeparsed("UPDATE tablename SET NAME = 'Customer 2', ADDRESS = 'Address \\' ddad2', AUTH_KEY = 'samplekey' WHERE ID = 2");
+        String sqlStr = "UPDATE tablename SET NAME = 'Customer 2', ADDRESS = 'Address \\' ddad2', AUTH_KEY = 'samplekey' WHERE ID = 2";
+
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                sqlStr
+                , true
+                , parser -> parser.withBackslashEscapeCharacter(true)
+        );
     }
 
     @Test