Query parameters with tabs or newlines can't be expressed

[michaelsmithxyz]

Describe the bug

It seems like it's currently not possible to use the client to construct a query with string parameters containing tabs or newlines correctly. Newlines and tabs are a special case of the value format that's used to parse query parameters (as explained on this issue in the main repo). I believe the code in the client which handles escaping backslashes and quotes also needs to escape tab and newline literals.

It doesn't seem like you can work around this by escaping the query parameter values before providing them to the client, because the client then escapes the backslashes you're using to escape tabs and/or newlines, changing the value of the parameter you've provided.

Steps to reproduce

Here's a self-contained example that reproduces the issue:

import {
  createClient,
} from '@clickhouse/client';

const main = async () => {
  const client = createClient({
    host: 'http://localhost:8123',
  });

  await client.command({
    query: `
      create table if not exists test (
        value String
      ) Engine = MergeTree
      ORDER BY value
    `,
  });

  await client.insert({
    table: 'test',
    values: [
      { value: 'with\ttab' },
    ],
    format: 'JSONEachRow',
  });

  await client.insert({
    table: 'test',
    values: [
      { value: 'with\nnewline' },
    ],
    format: 'JSONEachRow',
  });

  try {
    await client.query({
      query: `
      select *
      from test
      where value={arg:String}
    `,
      query_params: {
        arg: 'with\ttab',
      },
    });
  } catch (e) {
    console.error('Tab case failed');
    console.error(e);
  }

  try {
    await client.query({
      query: `
      select *
      from test
      where value={arg:String}
    `,
      query_params: {
        arg: 'with\nnewline',
      },
    });
  } catch (e) {
    console.error('Newline case failed');
    console.error(e);
  }

  // Trying to escape myself
  const result = await client.query({
    query: `
      select *
      from test
      where value={arg:String}
    `,
    query_params: {
      arg: 'with\\ttab',
    },
  });

  // You'd want this to return the row we inserted, but it doesn't
  console.info(await result.json());
};

main().then(() => process.exit(0));

Configuration

Environment

• Client version: 0.3.0 (latest at the time of this writing)

ClickHouse server

• ClickHouse Server version: 23.3.13, but I don't think it matters
• ClickHouse Server non-default settings, if any: N/A
• CREATE TABLE statements for tables involved: See above

[slvrtrn]

Thanks for the report; I will have a look.

[slvrtrn]

@michaelsmithxyz
it should be fixed in 0.3.1.

I added similar code as used in the tests to the example

  // (0.3.1+) It is also possible to bind parameters with special characters.
  const resultSet2 = await client.query({
    query: `
        SELECT
          'foo_\t_bar'  = {tab: String}             AS has_tab,
          'foo_\n_bar'  = {newline: String}         AS has_newline,
          'foo_\r_bar'  = {carriage_return: String} AS has_carriage_return,
          'foo_\\'_bar' = {single_quote: String}    AS has_single_quote,
          'foo_\\_bar'  = {backslash: String}       AS has_backslash`,
    format: 'JSONEachRow',
    query_params: {
      tab: 'foo_\t_bar',
      newline: 'foo_\n_bar',
      carriage_return: 'foo_\r_bar',
      single_quote: "foo_'_bar",
      backslash: 'foo_\\_bar',
    },
  })

  // Should return all 1, as query params will match the strings in the SELECT.
  console.info('Result (special characters):', await resultSet2.json())

which now prints:

Result (special characters): [
  {
    has_tab: 1,
    has_newline: 1,
    has_carriage_return: 1,
    has_single_quote: 1,
    has_backslash: 1
  }
]

[michaelsmithxyz]

@slvrtrn Thanks for knocking this out so quickly!