Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

18.7 KB
Raw Blame
import { QUERY_USER_LIMITS } from "../../../db/clickhouse/queryLimits.js";

export const MAX_CUSTOM_QUERY_LENGTH = 20_000;

const blockedKeywords = [
  "ALTER",
  "ATTACH",
  "BACKUP",
  "CREATE",
  "DELETE",
  "DESCRIBE",
  "DETACH",
  "DROP",
  "EXCHANGE",
  "EXPLAIN",
  "FORMAT",
  "GRANT",
  "INFILE",
  "INSERT",
  "INTO",
  "KILL",
  "OPTIMIZE",
  "OUTFILE",
  "RENAME",
  "RESTORE",
  "REVOKE",
  "SET",
  "SETTINGS",
  "SHOW",
  "SYSTEM",
  "TRUNCATE",
  "USE",
  "WATCH",
] as const;

const blockedFunctions = [
  "arrowFlight",
  "azureBlobStorage",
  "azureBlobStorageCluster",
  "cluster",
  "clusterAllReplicas",
  "currentProfiles",
  "currentRoles",
  "currentUser",
  "defaultProfiles",
  "defaultRoles",
  "enabledProfiles",
  "enabledRoles",
  "cosn",
  "deltaLake",
  "dictionary",
  "executable",
  "file",
  "filesystemAvailable",
  "filesystemCapacity",
  "filesystemUnreserved",
  "format",
  "FQDN",
  "fuzzJSON",
  "fuzzQuery",
  "gcs",
  "generateRandom",
  "getClientHTTPHeader",
  "getMacro",
  "getOSKernelVersion",
  "getServerPort",
  "getSetting",
  "getSettingOrDefault",
  "hdfs",
  "hdfsCluster",
  "hostName",
  "hostname",
  "hudi",
  "iceberg",
  "icebergCluster",
  "input",
  "jdbc",
  "kafka",
  "loop",
  "meilisearch",
  "merge",
  "mergeTreeIndex",
  "mergeTreeProjection",
  "mongodb",
  "mysql",
  "nats",
  "numbers",
  "odbc",
  "paimon",
  "postgresql",
  "prometheus",
  "rabbitmq",
  "redis",
  "remote",
  "remoteSecure",
  "s3",
  "s3Cluster",
  "showCertificate",
  "sleep",
  "sleepEachRow",
  "sqlite",
  "tcpPort",
  "timeSeriesData",
  "timeSeriesMetrics",
  "timeSeriesTags",
  "url",
  "urlCluster",
  "values",
  "view",
  "viewExplain",
  "viewIfPermitted",
  "ytsaurus",
] as const;

// External-storage table functions come in many suffixed variants
// (icebergS3, icebergAzure, deltaLakeLocal, hudiCluster, azureBlobStorage…).
// Match the family prefix so a new variant doesn't slip past the exact list.
const blockedFunctionPrefixes = [
  "iceberg",
  "deltaLake",
  "hudi",
  "hive",
  "azure",
  "s3",
  "hdfs",
  "gcs",
  "oss",
  "cosn",
  "timeSeries",
  "fuzz",
  "numbers",
  "zeros",
  "generate",
] as const;

const unsupportedSyntaxError =
  "Quoted identifiers, nested /* comments, # and // comments, $$ strings, and non-ASCII characters outside string literals are not allowed in custom analytics queries";

// Rejects syntax the literal stripper below can't model. ClickHouse treats
// double quotes, backticks and “…” as identifier quoting, `#` / `#!` / `//` as
// line comments, `$tag$…$tag$` and ‘…’ as string literals, nests `/* */`
// comments, and skips Unicode whitespace the JS regexes below don't all match.
// If any of these reached the stripper it would disagree with ClickHouse about
// where a literal or comment starts and ends, and text it classified as
// "inside a string" would execute as SQL. None of them are needed for
// analytics SQL, so they're rejected outright outside a single-quoted literal
// (non-ASCII text stays allowed inside literals and comments).
export function hasUnsupportedSyntax(query: string) {
  let index = 0;
  let state: "normal" | "single" | "line-comment" | "block-comment" = "normal";

  while (index < query.length) {
    const char = query[index];
    const next = query[index + 1];

    if (state === "normal") {
      if (
        char === '"' ||
        char === "`" ||
        char === "#" ||
        char === "$" ||
        (char === "/" && next === "/") ||
        char.charCodeAt(0) > 0x7f
      ) {
        return true;
      }
      if (char === "'") {
        state = "single";
      } else if (char === "-" && next === "-") {
        state = "line-comment";
        index++;
      } else if (char === "/" && next === "*") {
        state = "block-comment";
        index++;
      }
    } else if (state === "single") {
      if (char === "\\" && next !== undefined) {
        index++;
      } else if (char === "'" && next === "'") {
        index++;
      } else if (char === "'") {
        state = "normal";
      }
    } else if (state === "line-comment") {
      if (char === "\n") {
        state = "normal";
      }
    } else if (state === "block-comment") {
      // ClickHouse nests block comments, so `/* /* */` is still open after the
      // first `*/`. Mirror its lexer order: an opener is checked before a closer.
      if (char === "/" && next === "*") {
        return true;
      }
      if (char === "*" && next === "/") {
        state = "normal";
        index++;
      }
    }

    index++;
  }

  return false;
}

export function stripSqlLiteralsAndComments(query: string) {
  let result = "";
  let index = 0;
  let state: "normal" | "single" | "double" | "backtick" | "line-comment" | "block-comment" = "normal";
  let commentDepth = 0;

  while (index < query.length) {
    const char = query[index];
    const next = query[index + 1];

    if (state === "normal") {
      if (char === "'") {
        state = "single";
        result += " ";
      } else if (char === '"') {
        state = "double";
        result += " ";
      } else if (char === "`") {
        state = "backtick";
        result += " ";
      } else if (char === "-" && next === "-") {
        state = "line-comment";
        result += "  ";
        index++;
      } else if (char === "/" && next === "*") {
        state = "block-comment";
        commentDepth = 1;
        result += "  ";
        index++;
      } else {
        result += char;
      }
    } else if (state === "single") {
      if (char === "\\" && next !== undefined) {
        result += "  ";
        index++;
      } else if (char === "'" && next === "'") {
        result += "  ";
        index++;
      } else if (char === "'") {
        state = "normal";
        result += " ";
      } else {
        result += char === "\n" ? "\n" : " ";
      }
    } else if (state === "double") {
      if (char === "\\" && next !== undefined) {
        result += "  ";
        index++;
      } else if (char === '"') {
        state = "normal";
        result += " ";
      } else {
        result += char === "\n" ? "\n" : " ";
      }
    } else if (state === "backtick") {
      if (char === "`") {
        state = "normal";
      }
      result += " ";
    } else if (state === "line-comment") {
      if (char === "\n") {
        state = "normal";
        result += "\n";
      } else {
        result += " ";
      }
    } else if (state === "block-comment") {
      // Block comments nest in ClickHouse; the comment ends at the matching */.
      if (char === "/" && next === "*") {
        commentDepth++;
        result += "  ";
        index++;
      } else if (char === "*" && next === "/") {
        commentDepth--;
        if (commentDepth === 0) {
          state = "normal";
        }
        result += "  ";
        index++;
      } else {
        result += char === "\n" ? "\n" : " ";
      }
    }

    index++;
  }

  return result;
}

export function normalizeCustomQuery(query: string) {
  return query.trim().replace(/;+$/g, "").trim();
}

export function getCteNames(query: string) {
  const cteNames = new Set<string>();
  const ctePattern = /(?:\bWITH|,)\s*([a-zA-Z_][a-zA-Z0-9_]*)\s+AS\s*\(/gi;
  let match: RegExpExecArray | null;

  while ((match = ctePattern.exec(query)) !== null) {
    cteNames.add(match[1].toLowerCase());
  }

  return cteNames;
}

// Identifiers that end a FROM clause's comma-separated table list. Once one of these
// appears at the top paren level, later commas belong to another clause (GROUP BY,
// ORDER BY, a UNIONed SELECT, …) rather than the table list.
const fromClauseTerminators = new Set([
  "where",
  "prewhere",
  "group",
  "having",
  "order",
  "limit",
  "settings",
  "union",
  "intersect",
  "except",
  "window",
  "qualify",
  "format",
  "into",
]);

function isIdentifierStart(char: string) {
  return /[A-Za-z_]/.test(char);
}

function isIdentifierChar(char: string) {
  return /[A-Za-z0-9_.]/.test(char);
}

// ClickHouse joins `db . table` (whitespace, or a stripped comment, around the
// dot) into one compound identifier. Read it the same way so the qualified
// name is what gets checked, not just its first part — which could otherwise
// match a declared CTE.
function readIdentifier(query: string, start: number): [string, number] {
  let identifier = "";
  let end = start;
  for (;;) {
    const partStart = end;
    while (end < query.length && isIdentifierChar(query[end])) {
      end++;
    }
    identifier += query.slice(partStart, end);
    const dot = skipWhitespace(query, end);
    const next = skipWhitespace(query, dot + 1);
    if (query[dot] !== "." || next >= query.length || !isIdentifierStart(query[next])) {
      return [identifier, end];
    }
    identifier += ".";
    end = next;
  }
}

function skipWhitespace(query: string, index: number): number {
  while (index < query.length && /\s/.test(query[index])) {
    index++;
  }
  return index;
}

// Collect every directly-named table reference in the query. Covers the table after
// each FROM/JOIN and every comma-separated entry in a FROM list (`FROM a, b`), which
// a FROM/JOIN-keyword-only scan would miss. Subquery references (`FROM ( SELECT … )`)
// are skipped here — their inner FROM/JOIN clauses are reached by this same scan.
export function collectTableReferences(query: string): string[] {
  const references: string[] = [];
  const length = query.length;

  // Record the table reference that follows a FROM / JOIN / comma when it is a plain
  // identifier; subqueries and anything else are left to the surrounding scan.
  const readReference = (index: number) => {
    const start = skipWhitespace(query, index);
    if (start < length && isIdentifierStart(query[start])) {
      references.push(readIdentifier(query, start)[0]);
    }
  };

  // Match ARRAY JOIN ahead of a bare JOIN so its inner JOIN isn't rescanned.
  const keywordPattern = /\b(ARRAY\s+JOIN|FROM|JOIN)\b/gi;
  let match: RegExpExecArray | null;
  while ((match = keywordPattern.exec(query)) !== null) {
    const afterKeyword = match.index + match[0].length;
    const keyword = match[1].toLowerCase();

    // ARRAY JOIN takes an array expression (a column or function like
    // mapKeys(...)), not a table reference — skip it.
    if (keyword.includes("array")) {
      continue;
    }

    // A JOIN introduces exactly one table reference.
    if (keyword === "join") {
      readReference(afterKeyword);
      continue;
    }

    // A FROM introduces a comma-separated table list. Read the first reference, then
    // walk the clause tracking paren depth and pick up every top-level comma entry.
    readReference(afterKeyword);

    let depth = 0;
    let index = afterKeyword;
    while (index < length) {
      const char = query[index];
      if (char === "(") {
        depth++;
        index++;
      } else if (char === ")") {
        if (depth === 0) {
          break; // a closing paren that ends an enclosing subquery — clause is done
        }
        depth--;
        index++;
      } else if (char === "," && depth === 0) {
        readReference(index + 1);
        index++;
      } else if (depth === 0 && isIdentifierStart(char)) {
        const [word, end] = readIdentifier(query, index);
        if (fromClauseTerminators.has(word.toLowerCase())) {
          break;
        }
        index = end; // an alias or join keyword — skip past it
      } else {
        index++;
      }
    }
  }

  return references;
}

export function collectInTableReferences(query: string): string[] {
  const references: string[] = [];
  const inPattern = /\b(?:GLOBAL\s+)?(?:NOT\s+)?IN\b/gi;
  let match: RegExpExecArray | null;

  while ((match = inPattern.exec(query)) !== null) {
    const start = skipWhitespace(query, match.index + match[0].length);
    if (start < query.length && isIdentifierStart(query[start])) {
      const [identifier, end] = readIdentifier(query, start);
      // `expr IN function(...)` (e.g. tuple('US','GB')) is a value expression, not
      // the `expr IN table` shorthand — a following "(" marks it as a function call.
      if (query[skipWhitespace(query, end)] === "(") {
        continue;
      }
      references.push(identifier);
    }
  }

  return references;
}

export function validateScopedQuery(query: string): string | null {
  const normalizedQuery = normalizeCustomQuery(query);

  // ClickHouse treats double quotes and backticks as identifier quoting, not
  // string literals. Allowing them lets a quoted table name disappear before the
  // validator scans FROM/JOIN targets.
  if (hasUnsupportedSyntax(normalizedQuery)) {
    return unsupportedSyntaxError;
  }

  const queryWithoutLiterals = stripSqlLiteralsAndComments(normalizedQuery);
  const compactQuery = queryWithoutLiterals.trim();
  const cteNames = getCteNames(compactQuery);

  if (!/^(SELECT|WITH)\b/i.test(compactQuery)) {
    return "Only SELECT queries are allowed";
  }

  if (compactQuery.includes(";")) {
    return "Only one SQL statement is allowed";
  }

  for (const keyword of blockedKeywords) {
    if (new RegExp(`\\b${keyword}\\b`, "i").test(compactQuery)) {
      return `${keyword} is not allowed in custom analytics queries`;
    }
  }

  for (const fn of blockedFunctions) {
    if (new RegExp(`\\b${fn}\\s*\\(`, "i").test(compactQuery)) {
      return `${fn}() is not allowed in custom analytics queries`;
    }
  }

  for (const prefix of blockedFunctionPrefixes) {
    const match = new RegExp(`\\b(${prefix}[A-Za-z0-9_]*)\\s*\\(`, "i").exec(compactQuery);
    if (match) {
      return `${match[1]}() is not allowed in custom analytics queries`;
    }
  }

  // Dictionary accessors (dictGet, dictGetString, dictHas, dictGetHierarchy, …)
  // can read external dictionary data that isn't scoped to the site.
  if (/\bdict[A-Za-z]*\s*\(/i.test(compactQuery)) {
    return "Dictionary functions are not allowed in custom analytics queries";
  }

  // Block any database-qualified reference to system / information_schema,
  // regardless of how it's reached (FROM, JOIN, scalar subquery, function arg).
  // readonly=2 still permits SELECTing from these, so this is the real guard.
  if (/\b(system|information_schema|INFORMATION_SCHEMA)\s*\./i.test(compactQuery)) {
    return "Queries can only read from scoped_events";
  }

  if (/\bWITH\s+scoped_events\s+AS\b/i.test(compactQuery) || /\bAS\s+scoped_events\b/i.test(compactQuery)) {
    return "scoped_events is reserved and cannot be redefined";
  }

  // getCteNames also matches `, name AS (` outside a WITH clause (e.g. a second
  // named WINDOW), so a name it reports may not be a CTE ClickHouse knows about.
  // That's harmless for made-up names, but `events` is the real table: treating
  // it as a CTE would let `FROM events` through, so it can never be one.
  if (cteNames.has("events")) {
    return "events is reserved and cannot be used as a CTE name";
  }

  // Every data-source reference must be scoped_events or a declared CTE. collectTableReferences
  // walks the full FROM list, so comma-separated targets (`FROM scoped_events, other`) are
  // validated too — a FROM/JOIN-keyword-only scan captured only the first table and let the
  // rest through. collectInTableReferences covers ClickHouse's `expr IN table_name`
  // shorthand, which is equivalent to `expr IN (SELECT * FROM table_name)`.
  for (const reference of [...collectTableReferences(compactQuery), ...collectInTableReferences(compactQuery)]) {
    const normalizedTableName = reference.toLowerCase();
    if (normalizedTableName !== "scoped_events" && !cteNames.has(normalizedTableName)) {
      return "Queries can only read from scoped_events";
    }
  }

  if (!/\bscoped_events\b/i.test(compactQuery)) {
    return "Query must read from scoped_events";
  }

  return null;
}

// ClickHouse error codes whose message text is safe and useful to show to the
// query author (syntax position, unknown column, type mismatch, limits hit).
// Anything else — storage/IO/network errors that mention hosts, paths, or the
// internal user — is collapsed to a generic message with just the code.
const userFacingClickhouseErrorCodes = new Set([
  6, // CANNOT_PARSE_TEXT
  10, // NOT_FOUND_COLUMN_IN_BLOCK
  16, // NO_SUCH_COLUMN_IN_TABLE
  36, // BAD_ARGUMENTS
  42, // NUMBER_OF_ARGUMENTS_DOESNT_MATCH
  43, // ILLEGAL_TYPE_OF_ARGUMENT
  44, // ILLEGAL_COLUMN
  46, // UNKNOWN_FUNCTION
  47, // UNKNOWN_IDENTIFIER
  48, // NOT_IMPLEMENTED
  49, // LOGICAL_ERROR (surfaces as a type-inference failure on user SQL)
  53, // TYPE_MISMATCH
  62, // SYNTAX_ERROR
  69, // ARGUMENT_OUT_OF_BOUND
  70, // CANNOT_CONVERT_TYPE
  117, // INCORRECT_DATA
  158, // TOO_MANY_ROWS
  164, // READONLY
  179, // MULTIPLE_EXPRESSIONS_FOR_ALIAS
  182, // ILLEGAL_PREWHERE
  184, // ILLEGAL_AGGREGATION
  215, // NOT_AN_AGGREGATE
  306, // TOO_DEEP_RECURSION
  386, // NO_COMMON_TYPE
  452, // SETTING_CONSTRAINT_VIOLATION
  497, // ACCESS_DENIED (handled separately, listed for clarity)
]);

export function sanitizeClickhouseError(error: unknown): string {
  const raw = error instanceof Error && error.message ? error.message : "";
  if (!raw) {
    return "Failed to run query";
  }
  // The SDK removes "Code: ..." from message and puts it on error.code.
  // Also accept unparsed ClickHouse errors returned while reading a response.
  const errorCode = error instanceof Error && "code" in error ? error.code : undefined;
  const code = Number(
    (typeof errorCode === "string" || typeof errorCode === "number") && /^\d+$/.test(String(errorCode))
      ? errorCode
      : /^Code: (\d+)\./.exec(raw)?.[1]
  );
  if (code === 497 || /Not enough privileges/i.test(raw)) {
    return "Query references data outside scoped_events";
  }
  if (code === 159 || code === 160) {
    return `Query exceeded the ${QUERY_USER_LIMITS.maxExecutionTimeSeconds}-second time limit. Try a shorter date range or simplify the query.`;
  }
  if (code === 241) {
    return "Query exceeded the memory limit. Try a shorter date range or simplify the query.";
  }
  if (code === 202) {
    return "Too many queries are running at once. Wait a moment and try again.";
  }
  if (code === 394) {
    return "Query was cancelled. Try running it again.";
  }
  if (raw === "Timeout error." || errorCode === "ETIMEDOUT") {
    return "Timed out waiting for the database. Try again or use a shorter date range.";
  }
  if (
    typeof errorCode === "string" &&
    ["ECONNREFUSED", "ECONNRESET", "ENOTFOUND", "EAI_AGAIN", "EPIPE"].includes(errorCode)
  ) {
    return "Could not connect to the database. Please try again shortly.";
  }
  if (!Number.isFinite(code) || !userFacingClickhouseErrorCodes.has(code)) {
    return Number.isFinite(code) ? `Query failed (ClickHouse error ${code})` : "Failed to run query";
  }
  return raw
    .replace(/\s*\(version [^()]*(?:\([^()]*\)[^()]*)*\)/g, "")
    .replace(/\s*\([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\)/gi, "")
    .replace(/\s*\(from [^)]*\)/g, "")
    .replace(/\s*Stack trace:[\s\S]*$/, "")
    .trim();
}