Description

WUWEI111222333

opened on Jun 26, 2023

Issue body actions

if sql contains a Escape character like ''' ，the parse will occur error
the sql is :
INSERT INTO recycle_record (id,order_no,site_id,ic_no,ic_card_id,provider_id,car_no,trace_source_code,trade_time,in_time,out_time,weight_gross,weight_tare,weight_net,weight_deduct,weight_settlement,product_code,product_name,product_type,deduct,price,total,rater,rater_id,reviewer,reviewer_id,reviewer_time,pay_status,pay_method,pay_time,deleted,data_source,equipment_code,equipment_id,create_time,modify_time,payer_id,payer_name,remark,project_id,data_type,creator_id,creator_name,bno,receiving_name,receiving_account,pay_amount,data_sync,batch_no,pay_review_id,pay_review_name,pay_msg,pay_review_time,adjustment_status,ic_name,ic_card_type,reviewer_mode,inventory_channel,third_data_push_status,quality_order_no) VALUES (262846,'RK20230625000316',30013,'LYZH13589001',270005,180036,'',NULL,'2023-06-25 00:00:00.000',NULL,NULL,210.00,0.00,210.00,NULL,210.00,'A00','纸张',1,NULL,0.800,168.00,NULL,NULL,'',NULL,'2023-06-25 11:26:00',3,4,'2023-06-25 23:24:45',0,3,NULL,NULL,'2023-06-25 11:25:38','2023-06-25 23:24:44',30525,'傅益玲',''',NULL,2,NULL,NULL,'83653692186728700711687663398101','','',0.00,2,NULL,NULL,NULL,NULL,NULL,0,'董建军',1,2,2,0,NULL);

Activity

1. manticore-projects commented on Jun 26, 2023

   

   manticore-projects

   on Jun 26, 2023

   Contributor

   More actions

   Greetings,

   if you want to use single quote ' in strings, then you will need to quote/escape it like '''' or '\''.
   Then your statement will parse fine and your can try it online here.
2. WUWEI111222333 commented on Jun 26, 2023

   

   WUWEI111222333

   on Jun 26, 2023

   Author

   More actions

   thanks for your answer, but i have already quote/escape it with '''.
   can you help me ,why the sql will occur error:

   INSERT INTO recycle_record (a,b,c,d,e,f) VALUES (''',NULL,2,NULL,NULL,'83653692186728700711687663398101');
   
3. manticore-projects commented on Jun 26, 2023

   

   manticore-projects

   on Jun 26, 2023

   Contributor

   More actions

   hanks for your answer, but i have already quote/escape it with '''.

   No, you have not: it would be '''' not ''' because one ' opens the string, one ' escapes, one ' for the character and one ' to close the string.

   

   If you want use back-slash \ for escaping, you will need to activate it.

   http://jsqlformatter.manticore-projects.com/jsqlformatter/demo.html?args=-c%20MoUQMiDCAqAEDkj4CgDczmgjBAdRaQA
4. WUWEI111222333 commented on Jun 26, 2023

   

   WUWEI111222333

   on Jun 26, 2023

   Author

   More actions

   it still can not work:
   

   and i try it with java:
   

   the code is :

   String sql = "INSERT INTO recycle_record (a,b,c,d,e,f) VALUES ('\'',NULL,2,NULL,NULL,'83653692186728700711687663398101');";
           System.out.println(sql);
   
           CCJSqlParser ccjSqlParser = new CCJSqlParser(sql);
           FeatureConfiguration featureConfiguration = new FeatureConfiguration();
           featureConfiguration.setValue(Feature.allowBackslashEscapeCharacter,true);
           ccjSqlParser.withConfiguration(featureConfiguration);
           CCJSqlParserUtil.parseStatements(ccjSqlParser);
5. manticore-projects commented on Jun 26, 2023

   

   manticore-projects

   on Jun 26, 2023

   Contributor

   More actions

   https://manticore-projects.com/JSQLParser/usage.html#define-the-parser-features

   // Allow Back-slash escaping
   sqlStr="SELECT ('\\'Clark\\'', 'Kent')";
   Statement stmt2 = CCJSqlParserUtil.parse(
           sqlStr
           , parser -> parser
               .withBackslashEscapeCharacter(true)
   );
6. WUWEI111222333 commented on Jun 26, 2023

   

   WUWEI111222333

   on Jun 26, 2023

   Author

   More actions

   your sql is ok.but i wonder why my sql is wrong:

   //        String sql = "INSERT INTO recycle_record (a,b,c,d,e,f) VALUES ('\\'',NULL,2,NULL,NULL,'83653692186728700711687663398101');";
           String sql = "INSERT INTO recycle_record (a,f) VALUES ('\\'','83653692186728700711687663398101');";
   //        String sql = "SELECT ('\\'Clark\\'', 'Kent')";
   
           Statement parse = CCJSqlParserUtil.parse(sql, ccjSqlParser -> ccjSqlParser.withBackslashEscapeCharacter(true));
           System.out.println(parse);
7. manticore-projects commented on Jun 26, 2023

   

   manticore-projects

   on Jun 26, 2023

   Contributor

   More actions

   Yours looks ok to me too and it should work.
   I will analyze this in detail tomorrow.
8. WUWEI111222333 commented on Jun 26, 2023

   

   WUWEI111222333

   on Jun 26, 2023

   Author

   More actions

   thanks so much !
9. manticore-projects commented on Jun 27, 2023

   

   manticore-projects

   on Jun 27, 2023

   Contributor

   More actions

   Only the specific '\\'' (without any character after the ') does not work:

   -- works
   INSERT INTO recycle_record (a,f) VALUES ('\'anything', 'abc');
   -- fails
   INSERT INTO recycle_record (a,f) VALUES ('\'', 'abc');

   The explanation is simple: the '' after the slash \ is read as SQL standard compliant escape. The parser fails to understand, that we mean \' + ' instead of \ + ''.
   I will look for a way working around this, but its complicated. Good catch though!
10. manticore-projects

    added a commit that references this issue on Jun 27, 2023

    fix: Backslash escaped single quote `'\''`

    This issue will close once commit a297543 is merged into the 'master' branch.

    a297543
11. WUWEI111222333 commented on Jun 27, 2023

    

    WUWEI111222333

    on Jun 27, 2023

    Author

    More actions

    good work, i am looking for a new version.
12. manticore-projects

    closed this as completedin #1813on Jun 27, 2023
13. manticore-projects

    added a commit that references this issue on Jun 27, 2023

    Backslash Quotation of Single-Quote `'\\''` (#1813)

    This issue will close once commit c79dfff is merged into the 'master' branch.

    c79dfff

Issue actions