[BUG-SCHEMA-5] Database Name Not Escaped in DDL Queries - Fails on Reserved Keywords

[minguyen9988]

Bug Description

Severity: MEDIUM
Component: ClickHouseAutoCreateTable, ClickHouseAlterTable
Version: 2.9.1

Problem

In DDL operations (CREATE TABLE, ALTER TABLE), the database name is not backtick-escaped. While table names and column names are properly escaped with backticks, the database name is concatenated directly into the SQL string. This causes failures when the database name is a ClickHouse reserved keyword or contains special characters.

Affected Code

File 1: sink-connector/src/main/java/com/altinity/clickhouse/sink/connector/db/operations/ClickHouseAutoCreateTable.java

// Line 126-128: Database name NOT escaped, table name IS escaped
createTableSyntax.append(CREATE_TABLE).append(" ")
    .append(databaseName).append(".")          // NOT escaped!
    .append("\`").append(tableName).append("\`");  // Escaped

File 2: sink-connector/src/main/java/com/altinity/clickhouse/sink/connector/db/operations/ClickHouseAlterTable.java

// Line 73-74: Table name passed without database qualification or escaping
alterTableSyntax.append(ClickHouseDbConstants.ALTER_TABLE)
    .append(" ").append(tableName).append(" ");  // NOT escaped!

Impact

• CREATE TABLE fails if database name is a reserved keyword (e.g., system, default, order)
• ALTER TABLE fails if table name contains special characters or is a reserved keyword
• Silent failure - the error is caught and logged but processing continues with incorrect schema

ClickHouse Reserved Keywords That Trigger This Bug

system, default, order, group, select, insert, table, database, index, key, user, event, status, type, comment

Proposed Fix

Consistently escape all identifiers with backticks:

// CREATE TABLE
createTableSyntax.append(CREATE_TABLE).append(" ")
    .append("\`").append(databaseName).append("\`").append(".")
    .append("\`").append(tableName).append("\`");

// ALTER TABLE  
alterTableSyntax.append(ALTER_TABLE)
    .append(" \`").append(tableName).append("\` ");

Related Issues

• Error: table name with spaces. #1106 (table name with spaces)
• table column name was not found for clickhouse sink connector #480 (table column name not found)