{"record":{"id":"712a4410f16ac18c","repo":"siyuan-note/siyuan","slug":"sql-statement-is-not-a-read-only-query","errorCode":null,"errorMessage":"SQL statement is not a read-only query","messagePattern":"SQL statement is not a read-only query","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"kernel/sql/stmt_validate.go","lineNumber":191,"sourceCode":"}\n\n// CheckReadonlyStatementInBox 在指定笔记本对应的数据库连接上检查 SQL 是否只读。\nfunc CheckReadonlyStatementInBox(stmt, boxID string) error {\n\ttargetDB := db\n\tif boxDB := GetEncryptedDB(boxID); nil != boxDB {\n\t\ttargetDB = boxDB\n\t} else if IsEncryptedBoxFn != nil && IsEncryptedBoxFn(boxID) {\n\t\treturn errors.New(\"encrypted box db not opened for box \" + boxID)\n\t}\n\treturn checkReadonlyStatement(stmt, targetDB)\n}\n\nfunc checkReadonlyStatement(stmt string, targetDB *sql.DB) error {\n\tif strings.TrimSpace(stmt) == \"\" {\n\t\treturn errors.New(\"SQL statement is empty\")\n\t}\n\tif !isReadonlyQueryStatement(stmt) {\n\t\treturn errors.New(\"SQL statement is not a read-only query\")\n\t}\n\tif nil == targetDB {\n\t\treturn errors.New(\"database is nil\")\n\t}\n\tctx := context.Background()\n\tconn, err := targetDB.Conn(ctx)\n\tif err != nil {\n\t\treturn err\n\t}\n\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 {","sourceCodeStart":173,"sourceCodeEnd":209,"githubUrl":"https://github.com/siyuan-note/siyuan/blob/251596fc0de2f9528c00c224252fd073a99973f4/kernel/sql/stmt_validate.go#L173-L209","documentation":"Returned by checkReadonlyStatement in kernel/sql/stmt_validate.go when isReadonlyQueryStatement(stmt) is false. This is the first-line keyword filter: after stripping leading whitespace, line comments (--) and block comments (/* */), the statement must begin with SELECT or WITH. Any other leading keyword (INSERT, UPDATE, DELETE, ATTACH, DETACH, PRAGMA, BEGIN, EXPLAIN, CREATE, etc.) is rejected here, before SQLite ever sees it, so that constructs SQLite would otherwise mark readonly (ATTACH, DETACH, transaction control) cannot bypass the guard.","triggerScenarios":"Calling sql.CheckReadonlyStatement / CheckReadonlyStatementInBox / CheckAssetContentReadonlyStatement with a statement whose first keyword is not SELECT or WITH. These are invoked from /api/query and /api/search HTTP endpoints, the MCP sql tool, the `siyuan sql` CLI command, and SQL asset-content query paths.","commonSituations":"A plugin or user submits a PRAGMA or EXPLAIN QUERY PLAN through the readonly SQL API expecting it to pass; sending an INSERT/UPDATE/DELETE to a query-only endpoint; pasting a multi-line statement that starts with a comment followed by a non-SELECT keyword where the comment is stripped but the keyword still fails.","solutions":["Rewrite the statement so the first executable keyword is SELECT or WITH (e.g. use `WITH ... SELECT ...` for CTEs).","If you actually need to mutate data, use the dedicated write API/endpoint instead of the readonly SQL query API.","If you need PRAGMA/EXPLAIN diagnostics, run them through the kernel's own tooling, not the user-facing readonly SQL surface."],"exampleFix":"// before\nstmt := \"PRAGMA database_list\"\nerr := sql.CheckReadonlyStatement(stmt)\n\n// after (readonly query surface only accepts SELECT/WITH)\nstmt := \"SELECT * FROM pragma_database_list\"\nerr := sql.CheckReadonlyStatement(stmt)","handlingStrategy":"validation","validationCode":"// Validate the statement starts with SELECT or WITH before calling the readonly API.\nfunc isLikelyReadonlyQuery(stmt string) bool {\n    s := strings.TrimSpace(stmt)\n    for s != \"\" {\n        switch {\n        case strings.HasPrefix(s, \"--\"):\n            if i := strings.IndexByte(s, '\\n'); i >= 0 {\n                s = strings.TrimSpace(s[i+1:])\n                continue\n            }\n            return false\n        case strings.HasPrefix(s, \"/*\"):\n            if i := strings.Index(s[2:], \"*/\"); i >= 0 {\n                s = strings.TrimSpace(s[i+4:])\n                continue\n            }\n            return false\n        }\n        break\n    }\n    end := strings.IndexFunc(s, func(r rune) bool { return !unicode.IsLetter(r) })\n    if end < 0 { end = len(s) }\n    switch strings.ToUpper(s[:end]) {\n    case \"SELECT\", \"WITH\":\n        return true\n    }\n    return false\n}\n\nif !isLikelyReadonlyQuery(stmt) {\n    return errors.New(\"refusing to send non-SELECT/WITH statement to readonly SQL API\")\n}\nerr := sql.CheckReadonlyStatement(stmt)","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Only ever submit SELECT or WITH queries through /api/query, /api/search, the MCP sql tool, or the CLI sql command.","Route all data mutation through dedicated write APIs; never try to overload the readonly SQL surface.","Add a unit test that asserts your statement string starts with SELECT or WITH before sending it."],"tags":["sql","sqlite","readonly","validation","security"],"backgroundTag":null,"analyzedSha":"251596fc0de2f9528c00c224252fd073a99973f4","analyzedAt":"2026-08-12T21:18:37.123Z","schemaVersion":2},"datasetVersion":"2026-08-12T23:17:12.415Z"}