Escaping a backslash in an insert statement

[noliver123]

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:

[wumpz]

The escaping is quite problematic. I will look into it.

[wumpz]

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]

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:

Our string	Parser response	Response in special cases*
a\sd	accepted	accepted
asd\	rejected	accepted (should reject)
a'sd	rejected	rejected
a''sd	accepted	accepted
asd''	accepted	accepted
a\'sd	accepted (should reject)	accepted (should reject)
asd\'	rejected	accepted (should reject)
asd\\'	rejected	rejected
a\\'sd	accepted	accepted
asd\\	accepted	accepted
a\''sd	accepted	accepted
asd\''	accepted	accepted

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 only value inserted
• the last value inserted
• not the last, but was not followed by any other String in the value list.

The first two are similar in these cases.

Sometimes the responses were not what we expected (these are in parenthesis after the response).