{"record":{"id":"d218476276a827c3","repo":"Tencent/WeKnora","slug":"with-clause-ctes-is-not-allowed","errorCode":null,"errorMessage":"WITH clause (CTEs) is not allowed","messagePattern":"WITH clause \\(CTEs\\) is not allowed","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"internal/utils/inject.go","lineNumber":1353,"sourceCode":"\tif len(sql) > v.maxLength {\n\t\treturn fmt.Errorf(\"SQL query too long (max %d characters)\", v.maxLength)\n\t}\n\n\treturn nil\n}\n\n// validateSelectStmt validates a SELECT statement with configured options\nfunc (v *sqlValidator) validateSelectStmt(stmt *pg_query.SelectStmt, result *SQLValidationResult) error {\n\ttablesInQuery := make(map[string]string) // table name -> alias\n\n\t// Check for UNION/INTERSECT/EXCEPT (compound queries)\n\tif stmt.Op != pg_query.SetOperation_SETOP_NONE {\n\t\treturn fmt.Errorf(\"compound queries (UNION/INTERSECT/EXCEPT) are not allowed\")\n\t}\n\n\t// Check for WITH clause (CTEs)\n\tif v.checkCTEs && stmt.WithClause != nil {\n\t\treturn fmt.Errorf(\"WITH clause (CTEs) is not allowed\")\n\t}\n\n\t// Check for INTO clause (SELECT INTO)\n\tif stmt.IntoClause != nil {\n\t\treturn fmt.Errorf(\"SELECT INTO is not allowed\")\n\t}\n\n\t// Check for LOCKING clause (FOR UPDATE, etc.)\n\tif len(stmt.LockingClause) > 0 {\n\t\treturn fmt.Errorf(\"locking clauses (FOR UPDATE, etc.) are not allowed\")\n\t}\n\n\t// Validate FROM clause\n\tfor _, fromItem := range stmt.FromClause {\n\t\tif err := v.validateFromItem(fromItem, tablesInQuery, result); err != nil {\n\t\t\treturn err\n\t\t}\n\t}","sourceCodeStart":1335,"sourceCodeEnd":1371,"githubUrl":"https://github.com/Tencent/WeKnora/blob/988cbb03305e055d8ebb7d46d9ac6cc0803cd074/internal/utils/inject.go#L1335-L1371","documentation":"When the validator is configured with checkCTEs enabled, validateSelectStmt rejects any SELECT carrying a WITH clause (stmt.WithClause != nil). Common Table Expressions are disallowed because they can obscure which tables are actually referenced, defeating the validator's table allowlisting. The query is rejected before the FROM clause is inspected.","triggerScenarios":"Calling the validation API with a query like \"WITH t AS (SELECT ...) SELECT * FROM t\" while the validator has checkCTEs = true (whether by explicit option or default).","commonSituations":"Developers refactor long queries into CTEs for readability and suddenly fail validation; query builders that emit WITH for recursive or reusable subqueries; teams upgrading the library where checkCTEs defaults changed to true.","solutions":["Inline the CTE as a subquery in FROM: SELECT * FROM (SELECT ...) AS t.","Duplicate the underlying query logic without WITH.","Disable the checkCTEs option in the validator if CTEs are safe in your context."],"exampleFix":"// before (rejected when checkCTEs=true)\nq := \"WITH active AS (SELECT id FROM users WHERE active) SELECT * FROM active\"\n\n// after\nq := \"SELECT * FROM (SELECT id FROM users WHERE active) AS active\"","handlingStrategy":"validation","validationCode":"re := regexp.MustCompile(`(?i)^\\s*WITH[\\s(]`)\nif re.MatchString(sql) {\n    return fmt.Errorf(\"CTE (WITH clause) rejected; inline it as a subquery or disable checkCTEs\")\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Prefer subqueries over CTEs in code that goes through this validator.","Know your validator's checkCTEs setting before writing queries.","Add a pre-submission lint rule flagging leading WITH.","When refactoring long queries for readability, re-run validation tests."],"tags":["sql","validation","cte","postgres"],"backgroundTag":"unsupported-sql-construct","analyzedSha":"988cbb03305e055d8ebb7d46d9ac6cc0803cd074","analyzedAt":"2026-09-02T14:41:08.344Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}