package test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class TestMariaDBSQLSyntaxException
{
    private static String DBNAME = "jdbc:mysql://127.0.0.1:3306/test";
    private static String USER = "root";
    private static String PASSWORD = "*****";

    public void testInsertString()
            throws SQLException
    {
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password", PASSWORD);
        props.setProperty("tcpKeepAlive", "true");
        props.setProperty("connectTimeout", "5000");

        Connection connection = DriverManager.getConnection(DBNAME, props);

        DatabaseMetaData meta = connection.getMetaData();
        ResultSet rs = meta.getTables(null, null, "TEST_SYNTAX_ERROR", null);
        if (rs.next()) {
            // TABLE already exists, delete all rows
            Statement stmt = connection.createStatement();
            stmt.executeQuery("DELETE FROM TEST_SYNTAX_ERROR");
            stmt.close();
        }
        else {
            // Create table TEST_SYNTAX_ERROR
            Statement stmt = connection.createStatement();
            String sql = "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 ))";
            stmt.executeUpdate(sql);
            stmt.close();
        }

        String str = "abc\\\\"; // <-- main culprit causing the issue blackslash at the end
        String json = "{\"data\": \"test\"}";
        String sql = String.format("INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('%s', '%s')", str, json);
        System.out.println(sql);
        Statement stmt = connection.createStatement();
        stmt.executeQuery(sql);
        stmt.close();
        connection.close();
    }

    public static void main(String[] args)
            throws Throwable
    {
        // MariaDB Connector Version: mariadb-connector-j  2.6.0
        //
        // ISSUE:  If you have two columns one is normal string and the other one is a string column to hold json data
        // and if the first string ends with '\', it fails parsing the json column in the insert command.  If you remove the
        // trailing '\' in the first string, the test should pass.  Even escaping the trailing '\' is not working.
        //
        // We are thinking the problem is with the code org.mariadb.jdbc.internal.util.Utils.nativeSql line 497.
        // https://github.com/mariadb-corporation/mariadb-connector-j/blob/master/src/main/java/org/mariadb/jdbc/internal/util/Utils.java
        //
        //
        // method: nativeSql(...)
        //495:  if (lastChar == '\\' && !protocol.noBackslashEscapes()) {
        //496:      sqlBuffer.append(car);
        //497:      lastChar = car;
        //498:      continue;
        //499:  }
        //
        // NOTE: Escaping string's also results in the same error
        new TestMariaDBSQLSyntaxException().testInsertString();
    }
}