Repository navigation
Escaping a backslash in an insert statement #827
Description
Actual Behavior
I want to escape the backslash character in an insert statement at the end of a string value. To do this I use the following statement:
String insert = "INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value_1\\', 'my_value_2')";
But when I want to parse it, it throws an exception:
CCJSqlParserUtil.parse(insert);
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "my_value_2" <S_IDENTIFIER>
at line 1, column 73.
Was expecting one of:
"&"
")"
","
"::"
"<<"
">>"
"COLLATE"
"^"
"|"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:20951)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:20798)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1537)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:132)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:70)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:41)
... 1 more
It works fine in the following cases:
-
If the backslash is not at the end of the string, for example:
"INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value\\_1', 'my_value_2')"; -
If I only want to insert a single column:
INSERT INTO my_table (my_column_1) VALUES ('my_value_1\\') -
With SELECT, UPDATE, and MERGE instructions.
Specifications
- Version: 2.1
- Platform:
- Subsystem:
Activity
wumpz commentedon Jul 17, 2019
The escaping is quite problematic. I will look into it.
wumpz commentedon Jul 21, 2019
Maybe this is simply a copy and paste bug, but the behaviour for your example SQL is correct. If you use this within your Java file:
String insert = "INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value_1\\', 'my_value_2')";(two backslashes there) Then Java will put into your sql one backslash because of Javas escaping via backslash. Now JSqlParser supports escaping using backslashes as well. Therefore it finds
\'. JSqlParser thinks, you are escaping a quote. The found string literal is now'my_value_1\', '. Now the parse exception JSqlParser delivers makes sense.First you should escape your backslash in Javas sense and then for JSqlParser. IMHO your statement should be:
String insert = "INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value_1\\\\', 'my_value_2')";(four backslashes)
noliver123 commentedon Jul 24, 2019
Thank you for your reply.
Yes this is true but we have noticed that in some cases the parser works inconsistently. We created a table to illustrate this:
a\sdasd\a'sda''sdasd''a\'sdasd\'asd\\'a\\'sdasd\\a\''sdasd\''Our string:
The raw, displayed string we would like to insert, without any Java (or other language)-specific escapes.
Parser response:
Whether the JSQLParser accepted our string in an INSERT or not. The insert string used for (standalone) testing was:
INSERT INTO table(column) VALUES('<ourstring>').*Special cases:
The "special cases" mean that our String was either:
The first two are similar in these cases.
Sometimes the responses were not what we expected (these are in parenthesis after the response).
feat: Configurable backslash `\` escaping