Skip to content

Commit dac4caa

Browse files
hkaltaiclaude
andcommitted
fix: classify SQL as each warehouse reads it, and keep the hard block on every reachable path
- SQL Server/Fabric: no E'' strings; statements without a separator count only in their statement forms - BigQuery triple-quoted and backslash-escaped strings, Snowflake // comments and $$ quotes, Spark comment line continuation, MySQL -- before ASCII whitespace, non-ASCII dollar tags - uncertain comments and backslash runs make SQL uncertain; uncertain SQL is checked word by word - dynamic SQL literals naming DROP DATABASE/SCHEMA or TRUNCATE are blocked; sequence advances are writes - an omitted warehouse is classified under each dialect execution can reach, with dbt following its cached adapter - data_diff and the write ask check the whole SQL, not a preview Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent c9a9cf9 commit dac4caa

13 files changed

Lines changed: 447 additions & 91 deletions

File tree

‎packages/opencode/src/altimate/native/connections/dbt-adapter-type.ts‎

Lines changed: 45 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -4,25 +4,35 @@ import path from "path"
44

55
let cached: { key: string; type?: string } | undefined
66

7-
/** Whether the working directory has a dbt project, and the adapter type its compiled manifest
8-
* records (`metadata.adapter_type`, undefined before the project is compiled or when the manifest
9-
* cannot be read). Runs no subprocess (no Python discovery) and starts nothing; the manifest is
10-
* parsed again only when its path, size or modification time changes. */
11-
export async function dbtProjectAdapterType(): Promise<{ project: boolean; type?: string }> {
12-
let projectRoot: string | null = null
13-
try {
14-
const config = await import("../../../../../dbt-tools/src/config")
7+
/** The project root the dbt adapter was created for (null: none), set once register.ts has resolved
8+
* its adapter. That result is kept for the process, so the dialects read here follow it, not a
9+
* saved config changed since. */
10+
export const dbtRoute: { root?: string | null } = {}
11+
12+
/** Whether the working directory has a dbt project; the adapter type its compiled manifest records
13+
* (`metadata.adapter_type`, undefined before the project is compiled or when the manifest cannot be
14+
* read); and the types of every target of the project's profile, any of which dbt may connect with.
15+
* Runs no subprocess (no Python discovery) and starts nothing; the manifest is parsed again only
16+
* when its path, size or modification time changes. */
17+
export async function dbtProjectAdapterType(): Promise<{ project: boolean; type?: string; targetTypes: string[] }> {
18+
let projectRoot = dbtRoute.root
19+
if (projectRoot === undefined) {
1520
try {
16-
const saved = config.path()
17-
if (existsSync(saved)) projectRoot = (JSON.parse(readFileSync(saved, "utf8")) as { projectRoot?: string }).projectRoot ?? null
21+
const config = await import("../../../../../dbt-tools/src/config")
22+
// The project dbt's routing uses: a readable saved config's root, else discovery from the
23+
// working directory (a malformed config falls through to discovery, as dbt's config reader does).
24+
let saved: { projectRoot?: string } | null | undefined
25+
try {
26+
if (existsSync(config.path())) saved = JSON.parse(readFileSync(config.path(), "utf8"))
27+
} catch {}
28+
projectRoot = saved === undefined ? config.findProjectRoot() : (saved?.projectRoot ?? null)
1829
} catch {
19-
// A malformed saved config falls through to discovery, as dbt's own config reader does.
30+
projectRoot = null
2031
}
21-
projectRoot ??= config.findProjectRoot()
22-
} catch {
23-
projectRoot = null
2432
}
25-
if (!projectRoot) return { project: false }
33+
// A root without dbt_project.yml is rejected by the routing, which then uses the registry default.
34+
if (!projectRoot || !existsSync(path.join(projectRoot, "dbt_project.yml"))) return { project: false, targetTypes: [] }
35+
const targetTypes = await profileTargetTypes(projectRoot)
2636
try {
2737
const { resolveDbtTargetPath } = await import("../../validators/validator-utils")
2838
const manifest = path.join(await resolveDbtTargetPath(projectRoot), "manifest.json")
@@ -33,11 +43,27 @@ export async function dbtProjectAdapterType(): Promise<{ project: boolean; type?
3343
const type = String(raw?.metadata?.adapter_type ?? "").toLowerCase() || undefined
3444
cached = { key, type }
3545
}
36-
return { project: true, ...(cached.type && { type: cached.type }) }
46+
return { project: true, targetTypes, ...(cached.type && { type: cached.type }) }
47+
} catch {
48+
// A dbt project whose manifest is missing or unreadable: dbt can still take the call.
49+
return { project: true, targetTypes }
50+
}
51+
}
52+
53+
/** The adapter types of the targets in the profile dbt_project.yml names (none when unreadable). */
54+
async function profileTargetTypes(projectRoot: string): Promise<string[]> {
55+
try {
56+
const yml = readFileSync(path.join(projectRoot, "dbt_project.yml"), "utf8")
57+
const value = /^profile:[ \t]*(.*)$/m.exec(yml)?.[1]?.replace(/\s+#.*$/, "").trim()
58+
if (!value) return []
59+
const { parseDbtProfiles } = await import("./dbt-profiles")
60+
const connections = await parseDbtProfiles(undefined, projectRoot)
61+
// A templated name ({{ env_var(...) }}) is not resolved here: every profile's targets count.
62+
const profile = value.includes("{{") ? undefined : value.replace(/^["']|["']$/g, "")
63+
const targets = profile === undefined ? connections : connections.filter((c) => c.name.startsWith(`${profile}_`))
64+
return [...new Set(targets.map((c) => c.type.toLowerCase()))]
3765
} catch {
38-
// A dbt project whose manifest is missing or unreadable: dbt can still take the call, with an
39-
// adapter type nobody can establish here.
40-
return { project: true }
66+
return []
4167
}
4268
}
4369
// altimate_change end

‎packages/opencode/src/altimate/native/connections/register.ts‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@ import { defaultInspectionSchema, resolveInspectionTable, resolveInspectionTarge
1313
import { discoverContainers } from "./docker-discovery"
1414
import { parseDbtProfiles } from "./dbt-profiles"
1515
import { runDataDiff } from "./data-diff"
16-
import { dbtProjectAdapterType } from "./dbt-adapter-type"
16+
import { dbtProjectAdapterType, dbtRoute } from "./dbt-adapter-type"
1717
import type {
1818
SqlExecuteParams,
1919
SqlExecuteResult,
@@ -83,9 +83,10 @@ async function ensureDbtAdapter(): Promise<any | null> {
8383
// Likewise the cache: a superseded attempt returns its result to the callers that
8484
// awaited it but must not publish it, or it would overwrite what the newer attempt
8585
// stored. Every write goes through here.
86-
const publish = (value: any | null): any | null => {
86+
const publish = (value: any | null, root?: string): any | null => {
8787
if (dbtAdapterInflight === mine) {
8888
dbtAdapter = value
89+
dbtRoute.root = value ? (root ?? null) : null
8990
dbtAdapterInternals.writes += 1
9091
}
9192
return value
@@ -113,7 +114,7 @@ async function ensureDbtAdapter(): Promise<any | null> {
113114

114115
// Create the adapter
115116
const { create } = await import("../../../../../dbt-tools/src/adapter")
116-
return publish(await create(dbtConfig))
117+
return publish(await create(dbtConfig), dbtConfig.projectRoot)
117118
} catch {
118119
// dbt-tools not available or config invalid — fall back to native
119120
return publish(null)
@@ -263,6 +264,7 @@ async function tryExecuteViaDbt(
263264
/** Reset dbt adapter (for testing). */
264265
export function resetDbtAdapter(): void {
265266
dbtAdapter = undefined
267+
dbtRoute.root = undefined
266268
// altimate_change — drop any in-flight creation too, or a test that resets mid-flight
267269
// would still receive the previous adapter.
268270
dbtAdapterInflight = undefined

‎packages/opencode/src/altimate/tool-cli/permissions.ts‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,10 @@ export function denied(permission: string, retry = "Re-run"): Error {
3636
export function askFor(allowWrite: boolean, retry = "Re-run"): Tool.Context["ask"] {
3737
return ({ permission, patterns, metadata }) => {
3838
const dialect = dialectOf(metadata)
39-
if (patterns?.some((pattern) => isBlockedStatement(pattern, dialect))) {
39+
// sql_execute has already checked its whole SQL; its pattern is a 200-character preview, which a cut
40+
// inside a literal would make read differently.
41+
const checked = (metadata as { hardDenyChecked?: unknown } | undefined)?.hardDenyChecked === true
42+
if (!checked && patterns?.some((pattern) => isBlockedStatement(pattern, dialect))) {
4043
return Effect.die(
4144
new Error(`Permission denied: ${permission}. DROP DATABASE, DROP SCHEMA and TRUNCATE are blocked for safety. This cannot be overridden.`),
4245
)

‎packages/opencode/src/altimate/tools/data-diff.ts‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ import z from "zod"
22
import { Tool } from "../../tool/tool"
33
import { Dispatcher } from "../native"
44
import * as Registry from "../native/connections/registry"
5+
import { classifyAndCheck } from "./sql-classify"
56

67
export const DataDiffTool = Tool.define("data_diff", {
78
description: [
@@ -98,8 +99,19 @@ export const DataDiffTool = Tool.define("data_diff", {
9899
{ patterns: [args.source.slice(0, 120)], warehouse: sourceWarehouse },
99100
{ patterns: [args.target.slice(0, 120)], warehouse: targetWarehouse },
100101
]
102+
// The hard deny is checked on the whole SQL here; the ask's patterns are 120-character previews,
103+
// which a cut inside a literal would make read differently.
104+
const dialectOf = (warehouse?: string) => Registry.canonicalType(warehouse ? Registry.getConfig(warehouse)?.type : undefined) ?? undefined
105+
for (const [sql, warehouse] of [[args.source, sourceWarehouse], [args.target, targetWarehouse]] as const)
106+
if (classifyAndCheck(sql, dialectOf(warehouse)).blocked)
107+
throw new Error("DROP DATABASE, DROP SCHEMA and TRUNCATE are blocked for safety. This cannot be overridden.")
101108
for (const side of sides)
102-
await ctx.ask({ permission: "sql_execute_read", patterns: side.patterns, always: ["*"], metadata: { warehouse: side.warehouse } })
109+
await ctx.ask({
110+
permission: "sql_execute_read",
111+
patterns: side.patterns,
112+
always: ["*"],
113+
metadata: { warehouse: side.warehouse, hardDenyChecked: true },
114+
})
103115

104116
try {
105117
const result = await Dispatcher.call("data.diff", {

‎packages/opencode/src/altimate/tools/read-only-sql.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@ import { hasSideEffectFunction } from "./sql-function-policy"
99
* that writes or runs SQL (dynamic query() included). Undefined otherwise. */
1010
function readOnlyBody(sql: string, dialect?: string): { masked: string; body: string } | undefined {
1111
const { queryType, blocked } = classifyAndCheck(sql, dialect)
12+
// Settled already; the function scan below is costly on deeply nested input.
13+
if (blocked || queryType !== "read") return undefined
1214
const { tokens, complete } = tokenizeSql(sql, dialect)
1315
const masked = maskSql(tokens)
1416
// All calls use real code '(' tokens.

‎packages/opencode/src/altimate/tools/sql-classify.ts‎

Lines changed: 59 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
// Parser failures are never reads; lexical checks retain hard-deny protection.
66

77
import { hasMongoWriteStage, normalizeMqlCommand } from "@altimateai/drivers"
8-
import { maskSql, statementsOf, tokenizeSql } from "./sql-tokenize"
8+
import { isModeledDialect, maskSql, statementsOf, tokenizeSql } from "./sql-tokenize"
99
import { sqlFunctionCalls, sqlLiteral } from "./sql-functions"
1010
import { hasSideEffectFunction, isReadOnlyNestedFunction } from "./sql-function-policy"
1111

@@ -190,13 +190,54 @@ function classifyFallback(sql: string, dialect?: string): { queryType: "read" |
190190
// When quoting or comments cannot be read with certainty, no reading of the literals or comments
191191
// can be trusted to hide a hard deny (a misread literal can make a real statement look like a
192192
// comment), so the words alone decide; the TRUNCATE() function does not count.
193-
if (!tokenizeSql(sql, dialect).complete && mentionsHardDeny(sql)) blocked = true
193+
const lexical = tokenizeSql(sql, dialect)
194+
if (!lexical.complete) {
195+
if (mentionsHardDeny(sql)) blocked = true
196+
} else {
197+
// Read with certainty: the statement words in code (literals, identifiers and comments masked,
198+
// so DROP/**/SCHEMA reads as DROP SCHEMA) decide too, since T-SQL runs `SELECT 1 DROP DATABASE x`
199+
// as two statements with no ; and a parser may read a literal differently from the engine.
200+
const masked = maskSql(lexical.tokens)
201+
if (CODE_HARD_DENY.test(masked)) blocked = true
202+
// Dynamic SQL (EXEC('...'), EXECUTE IMMEDIATE, PREPARE ... FROM, DO $$...$$) runs a string, so
203+
// the words in its literals count too. A statement built by concatenation is not seen.
204+
else if (DYNAMIC_SQL.test(masked) && mentionsHardDeny(sql)) blocked = true
205+
}
194206
return { queryType: "write", blocked }
195207
}
196208

197-
/** The word DROP with DATABASE or SCHEMA anywhere after it, or TRUNCATE not followed by `(`. Only
198-
* words count: quotes and comments are ignored, since how they read is what is uncertain, so no
199-
* comment or quote placement can separate the words. One forward pass. */
209+
/** Where statements need no separator (T-SQL) or the dialect is not one the lexer models, a read
210+
* must hold no statement keyword in its code (literals, identifiers and comments masked): the
211+
* engine would run `SELECT ... DELETE FROM t` as two statements however a parser reads it. */
212+
function batchedStatement(masked: string, dialect?: string): boolean {
213+
return (/^(sqlserver|tsql|mssql|fabric)$/i.test(dialect ?? "") || !isModeledDialect(dialect)) && BATCH_STATEMENT.test(masked)
214+
}
215+
216+
// T-SQL statements that can follow a read in the same batch. Words that are also common column names
217+
// or clauses (USE, FETCH, OPEN, CLOSE, ENABLE, DISABLE, RENAME, THROW, ADD, MOVE, SEND, RECEIVE, END)
218+
// count only in their statement forms.
219+
const BATCH_STATEMENT = new RegExp(
220+
"\\b(INSERT|UPDATE|DELETE|MERGE|DROP|ALTER|CREATE|TRUNCATE|EXEC|EXECUTE|GRANT|REVOKE|DENY|BACKUP|RESTORE|DBCC|KILL|SHUTDOWN|BULK|WAITFOR|DECLARE|SET|INTO|" +
221+
"OPENROWSET|OPENQUERY|OPENDATASOURCE|OPENXML|CHECKPOINT|RECONFIGURE|RAISERROR|GOTO|BEGIN|COMMIT|ROLLBACK|SAVE|DEALLOCATE|" +
222+
"READTEXT|UPDATETEXT|WRITETEXT|SETUSER|REVERT|PRINT)\\b" +
223+
"|\\b(ENABLE|DISABLE)\\s+TRIGGER\\b|\\bRENAME\\s+(OBJECT|DATABASE)\\b|\\bTHROW\\s*($|;|[\\d@])" +
224+
// USE except OPTION (USE HINT/PLAN) and USE INDEX; FETCH except OFFSET paging (FETCH NEXT 10 ROWS ONLY).
225+
"|\\bUSE\\b(?!\\s+(HINT|PLAN|INDEX|KEY)\\b)|\\bFETCH\\b(?!\\s+(FIRST|NEXT)\\s+([\\w@().+-]+\\s+)?(PERCENT\\s+)?ROWS?\\b)" +
226+
"|\\b(OPEN|CLOSE)\\s+(GLOBAL\\s+)?[\\w@#]+\\s*(;|\\b(SELECT|WITH)\\b)|\\bADD\\s+(SENSITIVITY|SIGNATURE|COUNTER)\\b" +
227+
"|\\bMOVE\\s+CONVERSATION\\b|\\bSEND\\s+ON\\b|\\bRECEIVE\\s+(TOP\\b|\\*|\\w+\\s+FROM\\b)|\\bEND\\s+CONVERSATION\\b",
228+
"i",
229+
)
230+
231+
const CODE_HARD_DENY = /\bDROP\s+(DATABASE|SCHEMA)\b|\bTRUNCATE\b(?!\s*\()/i
232+
const DYNAMIC_SQL = /\bEXEC(UTE)?\b|(^|;)\s*(PREPARE|DO)\b/i
233+
// Sequence advances outside PostgreSQL's nextval(): NEXT VALUE FOR s (SQL Server, DB2), s.NEXTVAL
234+
// (Oracle, Snowflake).
235+
const SEQUENCE_ADVANCE = /\bNEXT\s+VALUE\s+FOR\b|\.\s*NEXTVAL\b/i
236+
237+
/** The word DROP with DATABASE or SCHEMA anywhere after it, or TRUNCATE not directly followed by `(`
238+
* (the TRUNCATE() function; a comment before the `(` does not count, since where a comment ends is
239+
* part of what is uncertain). Only words count: quotes and comments are ignored, since how they read
240+
* is what is uncertain, so no comment or quote placement can separate the words. One forward pass. */
200241
function mentionsHardDeny(sql: string): boolean {
201242
let drop = false
202243
let i = 0
@@ -210,18 +251,7 @@ function mentionsHardDeny(sql: string): boolean {
210251
const word = sql.slice(i, j).toUpperCase()
211252
if (word === "DROP") drop = true
212253
else if (drop && (word === "DATABASE" || word === "SCHEMA")) return true
213-
else if (word === "TRUNCATE") {
214-
// The TRUNCATE() function: "(" next, past whitespace and closed block comments.
215-
let k = j
216-
for (;;) {
217-
while (k < sql.length && /\s/.test(sql[k])) k++
218-
if (!sql.startsWith("/*", k)) break
219-
const end = sql.indexOf("*/", k + 2)
220-
if (end < 0) break
221-
k = end + 2
222-
}
223-
if (sql[k] !== "(") return true
224-
}
254+
else if (word === "TRUNCATE" && !/^[ \t\r\n]*\(/.test(sql.slice(j, j + 64))) return true
225255
i = j
226256
}
227257
return false
@@ -295,7 +325,7 @@ function functionSafety(sql: string, dialect: string | undefined, depth: number,
295325
Object.hasOwn(SQL_ARGUMENTS, call.name) && call.qualifier === undefined && !hasSideEffectFunction(call.name)
296326
if (depth > 0 && !nestedExecutor && !isReadOnlyNestedFunction(call.name, call.qualifier)) write = true
297327
// SQL Server's XML .query() takes XQuery, not executable SQL.
298-
if (call.name === "query" && /^(sqlserver|tsql|mssql)$/i.test(dialect ?? "")) continue
328+
if (call.name === "query" && /^(sqlserver|tsql|mssql|fabric)$/i.test(dialect ?? "")) continue
299329
const argument = Object.hasOwn(SQL_ARGUMENTS, call.name) ? SQL_ARGUMENTS[call.name] : undefined
300330
if (!argument) continue
301331
const inner = sqlLiteral(call.args[argument.index], dialect)
@@ -328,7 +358,8 @@ function classifySql(sql: string, dialect: string | undefined, depth: number): {
328358
parseError?: string
329359
} {
330360
if (!sql || typeof sql !== "string") return { queryType: "read", blocked: false }
331-
if (!tokenizeSql(sql, dialect).complete) {
361+
const lexical = tokenizeSql(sql, dialect)
362+
if (!lexical.complete) {
332363
return { ...classifyFallback(sql, dialect), parseError: "SQL contains unterminated or unsupported quoted text or comments" }
333364
}
334365
if (!getStatementTypes) return { ...classifyFallback(sql, dialect), parseError: "SQL parser is unavailable" }
@@ -341,6 +372,7 @@ function classifySql(sql: string, dialect: string | undefined, depth: number): {
341372
// A generic parser can treat MySQL arithmetic (--2) as a comment and omit
342373
// trailing statements. Keep dialect-aware lexical hard denies authoritative.
343374
const functions = functionSafety(sql, dialect, depth)
375+
const masked = maskSql(lexical.tokens)
344376
const blocked =
345377
functions.blocked ||
346378
classifyFallback(sql, dialect).blocked ||
@@ -349,7 +381,14 @@ function classifySql(sql: string, dialect: string | undefined, depth: number): {
349381
)
350382

351383
return {
352-
queryType: !functions.write && !blocked && otherIsReadOnly(sql, result.statements, dialect) ? "read" : "write",
384+
queryType:
385+
!functions.write &&
386+
!blocked &&
387+
!batchedStatement(masked, dialect) &&
388+
!SEQUENCE_ADVANCE.test(masked) &&
389+
otherIsReadOnly(sql, result.statements, dialect)
390+
? "read"
391+
: "write",
353392
blocked,
354393
}
355394
} catch (error) {

0 commit comments

Comments
 (0)