Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-785

jdbc insert query throws SQLSyntaxErrorException (unknown escape sequence) with 2.6.0 j-connector

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.6.1
    • Component/s: JDBC compatibility
    • Labels:
      None
    • Environment:
      - mac OS 10.13.6 (openjdk version "11.0.6" 2020-01-14 LTS)
      - centOS 7 with java 11

      Description

      We recently upgraded to MariaDB Connector/J (version 2.6.0) from version 1.5.x and found that our insert statement is breaking with "SQLSyntaxErrorException: unknown escape sequence" exception. The same insert works fine if we use mysql client

      For example,

      CREATE TABLE TEST_SYNTAX_ERROR(
           id INTEGER unsigned NOT NULL AUTO_INCREMENT, 
           str_value MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL, 
           json_value  MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL
          PRIMARY KEY ( id )
      )
      

      and if try to insert a string for "str_value" with trailing '\' character followed by some json string for "json_value", we see the following exception.

      INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('abc\\', '{"data": "test"}')
       
       
      Exception in thread "main" java.sql.SQLSyntaxErrorException: (conn=519) unknown escape sequence {"data": "test"}
      	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
      	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
      	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:273)
      	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:362)
      	at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:610)
      	at test.TestMariaDBSQLSyntaxException.testInsertString(TestMariaDBSQLSyntaxException.java:53)
      	at test.TestMariaDBSQLSyntaxException.main(TestMariaDBSQLSyntaxException.java:71)
      Caused by: java.sql.SQLException: unknown escape sequence {"data": "test"}
      	at org.mariadb.jdbc.internal.util.Utils.resolveEscapes(Utils.java:465)
      	at org.mariadb.jdbc.internal.util.Utils.nativeSql(Utils.java:560)
      	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
      	... 3 more
      

      The main problem is with trailing '\' character in the first string and if the insert command got some column which can hold json string. If we remove the trailing '\' characters in the first string, the query passes.

      Please find here attached the test case with the above issue. Our project is blocked by issue.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              diego dupin Diego Dupin
              Reporter:
              zcabir Cabir Zounaidou
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: