Column names are not quoted, causing SQL syntax errors

[rivol]

What happened:
my database has column names that include colons, e.g verification:id. the Query Builder mode in Grafana does not quote them, resulting in invalid SQL ala:
SELECT verification:id FROM "default"."verifications" LIMIT 1000
and syntax errors:

error querying the database: code: 62, message: Syntax error: failed at position 24 ('FROM'): FROM "default"."verifications" LIMIT 1000. Expected one of: token, Dot, OR, AND, IS NOT DISTINCT FROM, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV

What you expected to happen:
the column names should be quoted. switching to SQL Editor mode and manually quoting the column name fixes the issue:
SELECT "verification:id" FROM "default"."verifications" LIMIT 1000

How to reproduce it (as minimally and precisely as possible):

1. create table with a problematic column name in ClickHouse:

CREATE TABLE "default"."test" (
    "my:name" String
) 
ENGINE MergeTree()
ORDER BY tuple()

2. go to Explore mode in Grafana
3. try and query the table, including the my:name column. the error occurs.

Environment:

• Grafana version: Grafana v11.3.0 (d9455ff7db) (running via Docker)
• Plugin version: 4.0.2
• OS Grafana is installed on: Docker on MacOS

[SpencerTorres]

Interesting. I suppose we could add : to the list of characters we check to enable quoting column names. Thanks for the report

[mbUSC]

@SpencerTorres I ran into this issue as well (see #1217).

It think it would make sense to quote the column names by default.
Are there any downsides to doing that?

[mbUSC]

@SpencerTorres

Looks like this is the offending code:
https://github.com/grafana/clickhouse-datasource/blob/00710672ed9e97eb59070b7e4131fa26ea1bea31/src/data/sqlGenerator.ts#L539C1-L555C1

const getColumnIdentifier = (col: SelectedColumn): string => {
  let colName = col.name;


  // allow for functions like count()
  if (colName.includes('(') || colName.includes(')') || colName.includes('"') || colName.includes('"') || colName.includes(' as ')) {
    colName = col.name
  } else if (colName.includes(' ')) {
    colName = escapeIdentifier(col.name);
  }


  if (col.alias && (col.alias !== col.name && escapeIdentifier(col.alias) !== colName)) {
    return `${colName} as "${col.alias}"`
  }


  return colName;
}

We should be able to logically separate out the function from the column name, since they are two different things