{"record":{"id":"78130773fe6f2e33","repo":"siyuan-note/siyuan","slug":"sql-statement-is-not-read-only","errorCode":null,"errorMessage":"SQL statement is not read-only","messagePattern":"SQL statement is not read-only","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"kernel/sql/stmt_validate.go","lineNumber":219,"sourceCode":"\tdefer conn.Close()\n\n\treturn conn.Raw(func(dc any) error {\n\t\tsqliteConn, ok := dc.(*sqlite3.SQLiteConn)\n\t\tif !ok {\n\t\t\treturn fmt.Errorf(\"SQL driver connection type is unexpected: %T\", dc)\n\t\t}\n\t\tds, err := sqliteConn.Prepare(stmt)\n\t\tif err != nil {\n\t\t\treturn err\n\t\t}\n\t\tdefer ds.Close()\n\n\t\tsst, ok := ds.(*sqlite3.SQLiteStmt)\n\t\tif !ok {\n\t\t\treturn fmt.Errorf(\"SQL driver statement type is unexpected: %T\", ds)\n\t\t}\n\t\tif !sst.Readonly() {\n\t\t\treturn errors.New(\"SQL statement is not read-only\")\n\t\t}\n\t\treturn nil\n\t})\n}\n\n// isReadonlyQueryStatement 仅允许查询语句进入 SQLite prepare，提前拒绝会被 sqlite3_stmt_readonly\n// 视为只读的 ATTACH、DETACH 和事务控制语句。WITH 中的写操作仍由 sqlite3_stmt_readonly 拒绝。\nfunc isReadonlyQueryStatement(stmt string) bool {\n\tstmt = strings.TrimSpace(stmt)\n\tfor \"\" != stmt {\n\t\tswitch {\n\t\tcase strings.HasPrefix(stmt, \"--\"):\n\t\t\tif lineEnd := strings.IndexByte(stmt, '\\n'); 0 <= lineEnd {\n\t\t\t\tstmt = strings.TrimSpace(stmt[lineEnd+1:])\n\t\t\t\tcontinue\n\t\t\t}\n\t\t\treturn false\n\t\tcase strings.HasPrefix(stmt, \"/*\"):","sourceCodeStart":201,"sourceCodeEnd":237,"githubUrl":"https://github.com/siyuan-note/siyuan/blob/251596fc0de2f9528c00c224252fd073a99973f4/kernel/sql/stmt_validate.go#L201-L237","documentation":"Returned by checkReadonlyStatement inside conn.Raw after the statement is prepared and sst.Readonly() (the SQLite sqlite3_stmt_readonly C API) reports false. The first-line keyword filter (isReadonlyQueryStatement) already passed — the statement starts with SELECT or WITH — but SQLite itself judges that executing it could modify the database. The classic case is a WITH clause whose body contains INSERT/UPDATE/DELETE.","triggerScenarios":"Submitting a statement that begins with WITH (or SELECT) but whose prepared form is non-readonly according to sqlite3_stmt_readonly. The most common shape is a CTE-written write: `WITH x AS (...) INSERT INTO ...` or a WITH ... UPDATE/DELETE. Also reachable through /api/query, /api/search, the MCP sql tool, the CLI sql command, and asset-content query helpers.","commonSituations":"A plugin author tries to smuggle a write past the keyword filter by prefixing `WITH`; using data-modifying CTEs; relying on the fact that the keyword check only inspects the first token.","solutions":["Remove any DML (INSERT/UPDATE/DELETE) from inside the WITH/SELECT so the statement is genuinely read-only.","Move the write operation to a proper write API endpoint; the readonly SQL surface will never accept it.","Run `EXPLAIN` locally in a sqlite3 shell to confirm sqlite3_stmt_readonly returns true before submitting."],"exampleFix":"// before\nstmt := \"WITH moved AS (DELETE FROM blocks RETURNING *) SELECT * FROM moved\"\nerr := sql.CheckReadonlyStatement(stmt) // -> \"SQL statement is not read-only\"\n\n// after: keep the query side read-only; perform writes through the write API\nstmt := \"SELECT id, content FROM blocks WHERE box = ?\"\nerr := sql.CheckReadonlyStatement(stmt)","handlingStrategy":"try-catch","validationCode":null,"typeGuard":null,"tryCatchPattern":"err := sql.CheckReadonlyStatement(stmt)\nif err != nil {\n    if strings.Contains(err.Error(), \"not read-only\") {\n        // statement prepared but SQLite judged it mutative (e.g. WITH containing DML)\n        return fmt.Errorf(\"%q is not a readonly query even though it starts with SELECT/WITH; rewrite without DML\", stmt)\n    }\n    return err\n}","preventionTips":["Never embed INSERT/UPDATE/DELETE inside a WITH clause intended for the readonly API.","Test novel CTEs against sqlite3 locally and confirm sqlite3_stmt_readonly returns true.","Treat any 'not read-only' error as authoritative — do not retry with minor variations."],"tags":["sql","sqlite","readonly","cte","security"],"backgroundTag":null,"analyzedSha":"251596fc0de2f9528c00c224252fd073a99973f4","analyzedAt":"2026-08-12T21:18:37.123Z","schemaVersion":2},"datasetVersion":"2026-08-13T04:17:16.726Z"}