It is not possible to use the `?` symbol in Clickhouse SQL queries

[ilya-zlobintsev]

Describe the bug Some Clickhouse functions such as sequenceMatch use question marks in their syntax. Cube.js will always treat the question marks as parameters, and there doesn't seem to be a way to escape them.

To Reproduce Steps to reproduce the behavior:

1. Create a Cube that uses the sequenceMatch function with ? in the syntax
2. Try to use it
3. The generated SQL is incorrect

Expected behavior With the example Cube schema, I would expect the following SQL to be generated:

SELECT
      count(CASE WHEN (`sequence_match_example`.matched = 1) THEN `sequence_match_example`."subject_reference_id" END) `sequence_match_example__count`
    FROM
      (SELECT
          subject_reference_id,
          sequenceMatch('(?1)(?t>172800)(?2)')(effectivePeriod_start, code_coding_code = '93018', code_coding_code = '92920') as matched
        FROM ehr.Event
        GROUP BY subject_reference_id) AS `sequence_match_example`  LIMIT 10000;

Actual behaviour Instead, the generated SQL is this:

SELECT
      count(CASE WHEN (`sequence_match_example`.matched = 1) THEN `sequence_match_example`."subject_reference_id" END) `sequence_match_example__count`
    FROM
      (SELECT
          subject_reference_id,
          sequenceMatch('('93018'1)('92920't>172800)('93018'2)')(effectivePeriod_start, code_coding_code = ''93018'',
            code_coding_code = ''92920'') as matched
        FROM ehr.Event
        GROUP BY subject_reference_id) AS `sequence_match_example`  WHERE ('92920' = ?) AND (? = ?) LIMIT 10000

Minimally reproducible Cube Schema

cube(`SequenceMatchExample`, {
  sql: `SELECT
          subject_reference_id,
          sequenceMatch('(?1)(?t>172800)(?2)')(effectivePeriod_start, code_coding_code = '${FILTER_PARAMS.SequenceMatchExample.firstEventCode.filter((x) => `${x}`)}',
            code_coding_code = '${FILTER_PARAMS.SequenceMatchExample.secondEventCode.filter((x) => `${x}`)}') as matched
        FROM ehr.Event
        GROUP BY subject_reference_id`,

  measures: {
    count: {
      type: `count`,
      sql: `${CUBE}."subject_reference_id"`,
      filters: [{ sql: `${CUBE}.matched = 1` }],
    },
  },

  dimensions: {

    firstEventCode: {
      sql: `${FILTER_PARAMS.SequenceMatchExample.firstEventCode.filter((x) => x)}`,
      type: `string`,
      shown: true,
    },

    secondEventCode: {
      sql: `${FILTER_PARAMS.SequenceMatchExample.secondEventCode.filter((x) => x)}`,
      type: `string`,
      shown: true,
    },

  },

  dataSource: `default`,
});

Query JSON:

{
  "measures": [
    "SequenceMatchExample.count"
  ],
  "filters": [
    {
      "member": "SequenceMatchExample.firstEventCode",
      "operator": "equals",
      "values": [
        "93018"
      ]
    },
    {
      "member": "SequenceMatchExample.secondEventCode",
      "operator": "equals",
      "values": [
        "92920"
      ]
    }
  ]
}

Version: 0.31.32

Additional context This happens because the Cube Clickhouse driver uses the sqlstring library for escaping SQL queries, which is designed for MySQL and not Clickhouse. The library does not support escaping the question mark.

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[gojumprope]

Same issue here, but I guess it happens for all the drivers if we have ? symbol in the sql statement when we do filter

Example: it will replace ? symbol to the filter

    sql:`
    SELECT 'https://abc.com?id=123' link
`,