@@ -171,7 +171,7 @@ function otherIsReadOnly(sql: string, parsed: Array<{ category: string }>, diale
171171// Hard-deny patterns — blocked regardless of permissions
172172const HARD_DENY_TYPES = new Set ( [ "DROP DATABASE" , "DROP SCHEMA" , "TRUNCATE" , "TRUNCATE TABLE" ] )
173173
174- const HARD_DENY_PATTERN = / ^ \s * ( D R O P \s + ( D A T A B A S E | S C H E M A ) \b | T R U N C A T E ( \s + T A B L E ) ? \b ) / i
174+ const HARD_DENY_PATTERN = / ^ \s * ( D R O P \s + ( ( E X T E R N A L | P L U G G A B L E ) \s + ) ? ( D A T A B A S E | S C H E M A | C A T A L O G | N A M E S P A C E ) \b | T R U N C A T E ( \s + T A B L E ) ? \b ) / i
175175
176176/**
177177 * Fail closed when the parser is unavailable or rejects the input. Lexical
@@ -192,16 +192,19 @@ function classifyFallback(sql: string, dialect?: string): { queryType: "read" |
192192 // comment), so the words alone decide; the TRUNCATE() function does not count.
193193 const lexical = tokenizeSql ( sql , dialect )
194194 if ( ! lexical . complete ) {
195- if ( mentionsHardDeny ( sql ) ) blocked = true
195+ // Escapes decoded too: dynamic SQL may run a literal such as '\x44ROP SCHEMA s'.
196+ if ( mentionsHardDenyAnywhere ( sql ) ) blocked = true
196197 } else {
197198 // Read with certainty: the statement words in code (literals, identifiers and comments masked,
198199 // so DROP/**/SCHEMA reads as DROP SCHEMA) decide too, since T-SQL runs `SELECT 1 DROP DATABASE x`
199200 // 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
201+ const names = namesKept ( lexical . tokens )
202+ // Snowflake's ->> chains statements (elsewhere a JSON operator), and Snowflake Scripting's ASYNC (,
203+ // := ( and DEFAULT ( hold one (elsewhere DEFAULT(col) is a function).
204+ const chained =
205+ ( / ^ s n o w f l a k e $ / i. test ( dialect ?? "" ) || ! isModeledDialect ( dialect ) ) &&
206+ / ( - > > | \b A S Y N C \s * \( | : = \s * \( | \b D E F A U L T \s * \( ) \s * T R U N C A T E \b (? ! \s * \( ) / i. test ( names )
207+ if ( CODE_HARD_DENY . test ( names ) || handlerTruncate ( names ) || chained ) blocked = true
205208 }
206209 return { queryType : "write" , blocked }
207210}
@@ -217,26 +220,113 @@ function batchedStatement(masked: string, dialect?: string): boolean {
217220// or clauses (USE, FETCH, OPEN, CLOSE, ENABLE, DISABLE, RENAME, THROW, ADD, MOVE, SEND, RECEIVE, END)
218221// count only in their statement forms.
219222const 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|" +
223+ "\\bTRUNCATE\\b(?!\\s*\\()|\\b( INSERT|UPDATE|DELETE|MERGE|DROP|ALTER|CREATE|EXEC|EXECUTE|GRANT|REVOKE|DENY|BACKUP|RESTORE|DBCC|KILL|SHUTDOWN|BULK|WAITFOR|DECLARE|SET|INTO|" +
221224 "OPENROWSET|OPENQUERY|OPENDATASOURCE|OPENXML|CHECKPOINT|RECONFIGURE|RAISERROR|GOTO|BEGIN|COMMIT|ROLLBACK|SAVE|DEALLOCATE|" +
222225 "READTEXT|UPDATETEXT|WRITETEXT|SETUSER|REVERT|PRINT)\\b" +
223- "|\\b(ENABLE|DISABLE)\\s+TRIGGER\\b|\\bRENAME\\s+(OBJECT|DATABASE)\\b|\\bTHROW\\s*($|;| [\\d@]) " +
226+ "|\\b(ENABLE|DISABLE)\\s+TRIGGER\\b|\\bRENAME\\s+(OBJECT|DATABASE)\\b|\\bTHROW\\s+ [\\d@]" +
224227 // USE except OPTION (USE HINT/PLAN) and USE INDEX; FETCH except OFFSET paging (FETCH NEXT 10 ROWS ONLY).
225228 "|\\bUSE\\b(?!\\s+(HINT|PLAN|INDEX|KEY)\\b)|\\bFETCH\\b(?!\\s+(FIRST|NEXT)\\s+([\\w@().+-]+\\s+)?(PERCENT\\s+)?ROWS?\\b)" +
226229 "|\\b(OPEN|CLOSE)\\s+(GLOBAL\\s+)?[\\w@#]+\\s*(;|\\b(SELECT|WITH)\\b)|\\bADD\\s+(SENSITIVITY|SIGNATURE|COUNTER)\\b" +
227230 "|\\bMOVE\\s+CONVERSATION\\b|\\bSEND\\s+ON\\b|\\bRECEIVE\\s+(TOP\\b|\\*|\\w+\\s+FROM\\b)|\\bEND\\s+CONVERSATION\\b" ,
228231 "i" ,
229232)
230233
231- const CODE_HARD_DENY = / \b D R O P \s + ( D A T A B A S E | S C H E M A ) \b | \b T R U N C A T E \b (? ! \s * \( ) / i
232- const DYNAMIC_SQL = / \b E X E C ( U T E ) ? \b | ( ^ | ; ) \s * ( P R E P A R E | D O ) \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 = / \b N E X T \s + V A L U E \s + F O R \b | \. \s * N E X T V A L \b / i
234+ /** A literal's text as an engine may run it, one string per reading: numeric escapes (\xHH, \uHHHH,
235+ * \UHHHHHHHH, octal \ooo, U&'...' \HHHH and \+HHHHHH) decoded, and a single-character escape read as a
236+ * space when it stands for whitespace (\n \r \t \f, and \v in BigQuery) and as itself otherwise;
237+ * and SQL Server's reading, where a backslash before a line break joins the two lines. */
238+ function escapeReadings ( sql : string ) : string [ ] {
239+ const char = ( hex : string , base = 16 ) => String . fromCodePoint ( Math . min ( parseInt ( hex , base ) , 0x10ffff ) )
240+ let numeric = sql
241+ if ( / U & / i. test ( numeric ) ) numeric = numeric . replace ( / \\ \+ ( [ 0 - 9 a - f A - F ] { 6 } ) | \\ ( [ 0 - 9 a - f A - F ] { 4 } ) / g, ( _ , six , four ) => char ( six ?? four ) )
242+ const decode = ( whitespace : RegExp ) =>
243+ numeric . replace (
244+ / \\ (?: x ( [ 0 - 9 a - f A - F ] { 1 , 2 } ) | u ( [ 0 - 9 a - f A - F ] { 4 } ) | U ( [ 0 - 9 a - f A - F ] { 8 } ) | ( [ 0 - 7 ] { 1 , 3 } ) | ( [ \s \S ] ) ) / g,
245+ ( _ , x , u , U , octal , single ) =>
246+ single !== undefined ? ( whitespace . test ( single ) ? " " : single ) : octal ? char ( octal , 8 ) : char ( x ?? u ?? U ) ,
247+ )
248+ return [ sql , sql . replace ( / \\ \r ? \n / g, "" ) , decode ( / ^ [ n r t f ] $ / ) , decode ( / ^ [ n r t f v ] $ / ) ]
249+ }
250+
251+ // TRUNCATE as a statement: TRUNCATE TABLE, or TRUNCATE starting a statement or a block (BEGIN [NOT
252+ // ATOMIC], DO, LOOP, REPEAT, a handler's condition). After THEN or ELSE it must be a name and the end
253+ // of the statement, since CASE ... THEN truncate ... is a column. A column or alias named truncate is not.
254+ const CODE_HARD_DENY = new RegExp (
255+ "\\bDROP\\s+((EXTERNAL|PLUGGABLE)\\s+)?(DATABASE|SCHEMA|CATALOG|NAMESPACE)\\b|\\bTRUNCATE\\s+TABLE\\b" +
256+ // DuckDB accepts TRUNCATE after EXPLAIN (options) and PREPARE p AS ([^()] keeps the scan linear).
257+ "|(^|;|\\bPREPARE\\s+\\w+\\s+AS\\b|\\bEXPLAIN\\s*\\([^()]*\\))\\s*TRUNCATE\\b(?!\\s*\\()" +
258+ // After a block or handler word (or EXPLAIN ANALYZE), a target must follow: these words are also
259+ // column names, so `SELECT atomic truncate FROM t` is an alias.
260+ "|\\b(BEGIN|NOT\\s+ATOMIC|DO|LOOP|REPEAT|SQLEXCEPTION|SQLWARNING|FOUND|ANALYZE|ANALYSE)\\s+TRUNCATE\\s+" +
261+ "(?!(FROM|AS|WHERE|ORDER|GROUP|HAVING|UNION|LIMIT|WINDOW|INTO|JOIN|ON|AND|OR|END)\\b)[\\w\"`$\\u0080-\\uffff]" +
262+ // After THEN/ELSE: [IF EXISTS] a (dotted, quoted or IDENTIFIER(...)) name, then the statement's end.
263+ "|\\b(THEN|ELSE)\\s+TRUNCATE\\s+(IF\\s+EXISTS\\s+)?(?!END\\b)" +
264+ "(IDENTIFIER\\s*\\([^()]*\\)|[^\\s;,().]+(\\s*\\.{1,2}\\s*[^\\s;,().]+)*)(\\s+(NOWAIT|WAIT\\s+\\d+))?\\s*(;|$)" +
265+ // Anywhere else, TRUNCATE followed by a target (a routine or task body, BEGIN ATOMIC, ALTER TABLE
266+ // ... TRUNCATE PARTITION). A column named truncate is followed by a clause word, an operator, `,`
267+ // or `)`, or qualified (t.truncate). A name truncate followed by any other word is refused, e.g. an
268+ // alias without AS (`SELECT truncate x`, `FROM truncate t`).
269+ // The t.truncate check runs only after TRUNCATE matched, over the whitespace just before it (comments
270+ // are masked to spaces), so each run of whitespace is scanned once.
271+ "|\\bTRUNCATE(?<!\\.\\s*TRUNCATE)\\s+(?!(FROM|AS|WHERE|ORDER|GROUP|HAVING|UNION|EXCEPT|INTERSECT|MINUS|LIMIT|OFFSET|FETCH|" +
272+ "QUALIFY|WINDOW|INTO|JOIN|INNER|LEFT|RIGHT|FULL|CROSS|OUTER|ON|USING|AND|OR|NOT|IS|IN|LIKE|ILIKE|RLIKE|REGEXP|SIMILAR|" +
273+ "BETWEEN|AT|COLLATE|ESCAPE|ASC|DESC|NULLS|OVER|FILTER|WITHIN|THEN|ELSE|WHEN|END|DIV|MOD|XOR|OVERLAPS|" +
274+ "ISNULL|NOTNULL|GLOB|MATCH|SOUNDS|ROWS|RANGE|GROUPS|WITH|START|CONNECT|SETTINGS|OPTION|FOR|OPERATOR|ASCENDING|DESCENDING)\\b)[\\w\"`$\\u0080-\\uffff]" ,
275+ "i" ,
276+ )
236277
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
278+ /** A handler's statement follows its conditions (a code, SQLSTATE '...', a name): a TRUNCATE after
279+ * HANDLER FOR within the same statement counts. One pass over the statements. */
280+ function handlerTruncate ( masked : string ) : boolean {
281+ return masked . split ( ";" ) . some ( ( part ) => {
282+ const handler = part . search ( / \b H A N D L E R \s + F O R \b / i)
283+ return handler >= 0 && / \b T R U N C A T E \b (? ! \s * \( ) / i. test ( part . slice ( handler ) )
284+ } )
285+ }
286+
287+ /** Code with literals and comments masked, and each quoted identifier as a placeholder name, so a
288+ * statement keeps its target (TRUNCATE `t`) without the identifier's text reading as code. */
289+ function namesKept ( tokens : ReturnType < typeof tokenizeSql > [ "tokens" ] ) : string {
290+ return tokens
291+ . map ( ( { kind, text } ) =>
292+ kind === "code" ? text : kind === "identifier" ? ` ${ "_" . repeat ( Math . max ( text . length - 2 , 1 ) ) } ` : text . replace ( / [ ^ \r \n ] / g, " " ) ,
293+ )
294+ . join ( "" )
295+ }
296+
297+ /** Whether a write's quoted text holds a hard-denied statement. A write can run its literals as SQL:
298+ * dynamic SQL (EXEC('...'), sp_executesql N'...', EXECUTE IMMEDIATE, PREPARE ... FROM, dblink_exec),
299+ * or a function, procedure or trigger body created and called in the same call. Each literal is read
300+ * word by word with its escapes decoded, as uncertain SQL is, so nothing inside it (nested quotes,
301+ * comments) can separate the words. A statement built by concatenation is not seen, and a custom
302+ * UESCAPE character is not decoded, so it blocks. */
303+ function quotedHardDeny ( tokens : ReturnType < typeof tokenizeSql > [ "tokens" ] , dialect ?: string ) : boolean {
304+ // UESCAPE counts with a U&'...' literal: U& in code right before a literal token.
305+ const unicode = tokens . some ( ( { kind, text } , i ) => kind === "code" && / \b U & $ / i. test ( text ) && tokens [ i + 1 ] ?. kind === "literal" )
306+ if ( unicode && / \b U E S C A P E \b / i. test ( maskSql ( tokens ) ) ) return true
307+ // "..." is a string in Spark, Databricks and Hive, under SQL Server's QUOTED_IDENTIFIER OFF and in
308+ // MySQL without ANSI_QUOTES; other identifiers never are.
309+ const quotedStrings =
310+ / ^ ( s q l s e r v e r | t s q l | m s s q l | f a b r i c | m y s q l | m a r i a d b | s p a r k | d a t a b r i c k s | h i v e ) $ / i. test ( dialect ?? "" ) || ! isModeledDialect ( dialect )
311+ return tokens . some (
312+ ( { kind, text } ) =>
313+ ( kind === "literal" || ( kind === "identifier" && quotedStrings && text [ 0 ] === '"' ) ) &&
314+ ( ( / \b U E S C A P E \b / i. test ( text ) && / \b U & ' / i. test ( text ) ) || mentionsHardDenyAnywhere ( text ) ) ,
315+ )
316+ }
317+
318+ // Sequence advances outside PostgreSQL's nextval(): NEXT VALUE FOR s (SQL Server, DB2), and in Oracle,
319+ // Snowflake and MariaDB (Oracle mode) s.NEXTVAL, quoted or not (elsewhere s.nextval is a column).
320+ function advancesSequence ( tokens : ReturnType < typeof tokenizeSql > [ "tokens" ] , masked : string , dialect ?: string ) : boolean {
321+ if ( / \b N E X T \s + V A L U E \s + F O R \b / i. test ( masked ) ) return true
322+ if ( ! / ^ ( o r a c l e | s n o w f l a k e | m y s q l | m a r i a d b ) $ / i. test ( dialect ?? "" ) && isModeledDialect ( dialect ) ) return false
323+ // Comments masked to spaces and quoted names unwrapped, so s./* c */"NEXTVAL" reads as s.NEXTVAL.
324+ return / \. \s * N E X T V A L \b / i. test ( maskSql ( tokens , true ) )
325+ }
326+
327+ /** The word DROP with DATABASE, SCHEMA, CATALOG or NAMESPACE anywhere after it, or TRUNCATE not
328+ * directly followed by `(` (the TRUNCATE() function; a comment before the `(` does not count, since
329+ * where a comment ends is part of what is uncertain). Only words count: quotes and comments are ignored, since how they read
240330 * is what is uncertain, so no comment or quote placement can separate the words. One forward pass. */
241331function mentionsHardDeny ( sql : string ) : boolean {
242332 let drop = false
@@ -250,7 +340,7 @@ function mentionsHardDeny(sql: string): boolean {
250340 while ( j < sql . length && / \w / . test ( sql [ j ] ) ) j ++
251341 const word = sql . slice ( i , j ) . toUpperCase ( )
252342 if ( word === "DROP" ) drop = true
253- else if ( drop && ( word === "DATABASE" || word === "SCHEMA" ) ) return true
343+ else if ( drop && ( word === "DATABASE" || word === "SCHEMA" || word === "CATALOG" || word === "NAMESPACE" ) ) return true
254344 else if ( word === "TRUNCATE" && ! / ^ [ \t \r \n ] * \( / . test ( sql . slice ( j , j + 64 ) ) ) return true
255345 i = j
256346 }
@@ -349,7 +439,21 @@ export function classifyAndCheck(sql: string, dialect?: string): {
349439 blocked : boolean
350440 parseError ?: string
351441} {
352- return classifySql ( sql , dialect , 0 )
442+ const result = classifySql ( sql , dialect , 0 )
443+ if ( result . queryType === "write" && ! result . blocked ) {
444+ // SQL read with certainty: a write may still run the SQL in its literals. Otherwise the word scan
445+ // of the whole text already covered them.
446+ const lexical = tokenizeSql ( sql , dialect )
447+ if ( lexical . complete && quotedHardDeny ( lexical . tokens , dialect ) ) return { ...result , blocked : true }
448+ }
449+ return result
450+ }
451+
452+ /** Whether DROP DATABASE/SCHEMA/CATALOG/NAMESPACE or TRUNCATE appears anywhere in the text, read word by word
453+ * with quotes and comments ignored and escapes decoded (for text another layer renders, such as dbt's
454+ * Jinja). */
455+ export function mentionsHardDenyAnywhere ( sql : string ) : boolean {
456+ return escapeReadings ( sql ) . some ( mentionsHardDeny )
353457}
354458
355459function classifySql ( sql : string , dialect : string | undefined , depth : number ) : {
@@ -385,7 +489,14 @@ function classifySql(sql: string, dialect: string | undefined, depth: number): {
385489 ! functions . write &&
386490 ! blocked &&
387491 ! batchedStatement ( masked , dialect ) &&
388- ! SEQUENCE_ADVANCE . test ( masked ) &&
492+ // Snowflake's ->> runs the statements it chains, DML included.
493+ ! ( / ^ s n o w f l a k e $ / i. test ( dialect ?? "" ) && masked . includes ( "->>" ) ) &&
494+ // Spark/Hive SELECT TRANSFORM(...) USING 'cmd' runs a command on the cluster; any USING not
495+ // followed by "(" counts (comments and the script masked), while JOIN ... USING (col) is a join.
496+ ! ( ( / ^ ( s p a r k | d a t a b r i c k s | h i v e ) $ / i. test ( dialect ?? "" ) || ! isModeledDialect ( dialect ) ) &&
497+ / \b T R A N S F O R M \s * \( / i. test ( masked ) &&
498+ / \b U S I N G \b (? ! \s * \( ) / i. test ( masked ) ) &&
499+ ! advancesSequence ( lexical . tokens , masked , dialect ) &&
389500 otherIsReadOnly ( sql , result . statements , dialect )
390501 ? "read"
391502 : "write" ,
0 commit comments