| 1 | // Package dbquery is the one read-only query runner `carlos db query` uses: |
| 2 | // on a member's machine (the CLI, db.go) and inside the db-read worker on a |
| 3 | // box (internal/dbreads, spec 2026-10-22 §8.3). It moved out of db.go so the |
| 4 | // guards, the open flags and the output format cannot drift between the two. |
| 5 | package dbquery |
| 6 | |
| 7 | import ( |
| 8 | "context" |
| 9 | "database/sql" |
| 10 | "errors" |
| 11 | "fmt" |
| 12 | "io" |
| 13 | "strings" |
| 14 | "time" |
| 15 | |
| 16 | "modernc.org/sqlite" |
| 17 | sqlitelib "modernc.org/sqlite/lib" |
| 18 | ) |
| 19 | |
| 20 | // Options are the only difference between the two callers. Immutable adds |
| 21 | // immutable=1 to the open: the worker's copy is a file nothing else ever |
| 22 | // writes (spec §8.3). |
| 23 | type Options struct { |
| 24 | Immutable bool |
| 25 | } |
| 26 | |
| 27 | // ErrOneStatement and ErrPragmaChange are query's two refusals before SQLite |
| 28 | // sees anything. Their text is the CLI's, unchanged. |
| 29 | var ( |
| 30 | ErrOneStatement = errors.New("carlos db query runs one statement at a time. Run each statement on its own") |
| 31 | ErrPragmaChange = errors.New("this pragma changes how the copy is read, so carlos db query does not run it. Reading a pragma's value is fine") |
| 32 | ) |
| 33 | |
| 34 | // dsn is the open string. temp_store(2): sorts and temp b-trees stay in |
| 35 | // memory, so a large query cannot spill plaintext pages to a file outside the |
| 36 | // private directory. |
| 37 | func dsn(path string, opt Options) string { |
| 38 | mode := "mode=ro" |
| 39 | if opt.Immutable { |
| 40 | mode = "mode=ro&immutable=1" |
| 41 | } |
| 42 | return "file:" + path + "?" + mode + "&_pragma=query_only(1)&_pragma=busy_timeout(5000)&_pragma=temp_store(2)" |
| 43 | } |
| 44 | |
| 45 | // RunContext opens path read-only, both ways (mode=ro and query_only, db |
| 46 | // spec §4), runs sqlText, and writes a header row then one tab-separated |
| 47 | // line per row. A write fails with SQLite's own "attempt to write a readonly |
| 48 | // database", returned as it is: the error is the truth. It is db.go's |
| 49 | // runQueryContext with the open string from dsn. |
| 50 | func RunContext(ctx context.Context, w io.Writer, path, sqlText string, opt Options) error { |
| 51 | if err := CheckSQL(sqlText); err != nil { |
| 52 | return err |
| 53 | } |
| 54 | db, err := sql.Open("sqlite", dsn(path, opt)) |
| 55 | if err != nil { |
| 56 | return err |
| 57 | } |
| 58 | defer db.Close() |
| 59 | conn, err := db.Conn(ctx) |
| 60 | if err != nil { |
| 61 | return err |
| 62 | } |
| 63 | defer conn.Close() |
| 64 | // mode=ro covers only main, and query_only is a pragma the statement |
| 65 | // list itself can switch off. ATTACH opens with the connection's |
| 66 | // read-write flags, so it is the way around both: a second name for the |
| 67 | // copy that writes silently, or a new file anywhere the user can write. |
| 68 | // VACUUM INTO attaches internally. No attached databases at all closes |
| 69 | // every one of those. |
| 70 | if _, err := sqlite.Limit(conn, sqlitelib.SQLITE_LIMIT_ATTACHED, 0); err != nil { |
| 71 | return err |
| 72 | } |
| 73 | rows, err := conn.QueryContext(ctx, sqlText) |
| 74 | if err != nil { |
| 75 | return err |
| 76 | } |
| 77 | defer rows.Close() |
| 78 | cols, err := rows.Columns() |
| 79 | if err != nil { |
| 80 | return err |
| 81 | } |
| 82 | if len(cols) > 0 { |
| 83 | if _, err := fmt.Fprintln(w, strings.Join(cols, "\t")); err != nil { |
| 84 | return err |
| 85 | } |
| 86 | } |
| 87 | vals := make([]any, len(cols)) |
| 88 | ptrs := make([]any, len(cols)) |
| 89 | for i := range vals { |
| 90 | ptrs[i] = &vals[i] |
| 91 | } |
| 92 | line := make([]string, len(cols)) |
| 93 | for rows.Next() { |
| 94 | if err := rows.Scan(ptrs...); err != nil { |
| 95 | return err |
| 96 | } |
| 97 | for i, v := range vals { |
| 98 | line[i] = Cell(v) |
| 99 | } |
| 100 | // A reader that went away (EPIPE, once SIGPIPE is caught) ends the |
| 101 | // query here rather than after every remaining row. |
| 102 | if _, err := fmt.Fprintln(w, strings.Join(line, "\t")); err != nil { |
| 103 | return err |
| 104 | } |
| 105 | } |
| 106 | return rows.Err() |
| 107 | } |
| 108 | |
| 109 | // Cell renders one value. NULL is spelled out so it cannot be mistaken for |
| 110 | // an empty string; tabs and newlines inside a value are escaped so a row |
| 111 | // stays one line. |
| 112 | func Cell(v any) string { |
| 113 | var s string |
| 114 | switch x := v.(type) { |
| 115 | case nil: |
| 116 | return "NULL" |
| 117 | case []byte: |
| 118 | s = string(x) |
| 119 | case time.Time: |
| 120 | s = x.UTC().Format(time.RFC3339Nano) |
| 121 | default: |
| 122 | s = fmt.Sprint(x) |
| 123 | } |
| 124 | return strings.NewReplacer("\\", `\\`, "\t", `\t`, "\n", `\n`, "\r", `\r`).Replace(s) |
| 125 | } |
| 126 | |
| 127 | // dbReadPragmas are the pragmas that may take an argument here: each reads |
| 128 | // the schema or checks the file, and none changes the connection. Any |
| 129 | // other pragma may be read bare but not given a value. |
| 130 | var dbReadPragmas = map[string]bool{ |
| 131 | "table_info": true, "table_xinfo": true, "table_list": true, |
| 132 | "index_info": true, "index_xinfo": true, "index_list": true, |
| 133 | "foreign_key_list": true, "foreign_key_check": true, |
| 134 | "integrity_check": true, "quick_check": true, |
| 135 | } |
| 136 | |
| 137 | // CheckSQL is the guard modernc.org/sqlite v1.55.0 leaves to its |
| 138 | // caller: it exposes no authorizer and no sqlite3_stmt_readonly, and it runs |
| 139 | // every statement in a list. mode=ro, query_only and the attach limit hold |
| 140 | // for any ONE statement, so the rule is one statement, and no pragma that |
| 141 | // changes the connection (query_only, temp_store, temp_store_directory, |
| 142 | // writable_schema and the rest), since a pragma is the one statement that |
| 143 | // can loosen what follows it. |
| 144 | // |
| 145 | // The pragma rule reads the statement's tokens, not its raw prefix: SQLite |
| 146 | // skips byte-order marks, comments and whitespace anywhere, and applies a |
| 147 | // pragma at prepare even under EXPLAIN. So when the statement is a PRAGMA |
| 148 | // (first token, or after EXPLAIN [QUERY PLAN]), the pragma it names may be |
| 149 | // given a value only if it is a read-only one, and only in the (argument) |
| 150 | // form. Elsewhere "pragma" is an identifier and a read may use it. |
| 151 | func CheckSQL(q string) error { |
| 152 | _, rest := splitFirstStatement(q) |
| 153 | if len(sqlTokens(rest)) > 0 { |
| 154 | return ErrOneStatement |
| 155 | } |
| 156 | toks := sqlTokens(q) |
| 157 | // A pragma is a statement, so it can only stand in statement position: |
| 158 | // first, or after EXPLAIN [QUERY PLAN]. Anywhere else "pragma" is an |
| 159 | // ordinary identifier (a column, an alias). One statement is enforced |
| 160 | // above, so there is no later statement position to check. |
| 161 | i := 0 |
| 162 | if i < len(toks) && toks[i].kind == sqlWord && toks[i].text == "explain" { |
| 163 | i++ |
| 164 | if i+1 < len(toks) && toks[i].kind == sqlWord && toks[i].text == "query" && |
| 165 | toks[i+1].kind == sqlWord && toks[i+1].text == "plan" { |
| 166 | i += 2 |
| 167 | } |
| 168 | } |
| 169 | if i < len(toks) && toks[i].kind == sqlWord && toks[i].text == "pragma" { |
| 170 | // PRAGMA [schema .] name [= value | (value)] |
| 171 | j := i + 1 |
| 172 | name := "" |
| 173 | if j < len(toks) && toks[j].kind != sqlPunct { |
| 174 | name = toks[j].text |
| 175 | j++ |
| 176 | } |
| 177 | if j+1 < len(toks) && toks[j].kind == sqlPunct && toks[j].text == "." && toks[j+1].kind != sqlPunct { |
| 178 | name = toks[j+1].text |
| 179 | j += 2 |
| 180 | } |
| 181 | if j >= len(toks) || toks[j].kind != sqlPunct || (toks[j].text != "=" && toks[j].text != "(") { |
| 182 | return nil // a bare read of the pragma's value |
| 183 | } |
| 184 | if toks[j].text == "(" && dbReadPragmas[name] { |
| 185 | return nil |
| 186 | } |
| 187 | return ErrPragmaChange |
| 188 | } |
| 189 | return nil |
| 190 | } |
| 191 | |
| 192 | type sqlTokenKind int |
| 193 | |
| 194 | const ( |
| 195 | sqlWord sqlTokenKind = iota // keyword or bare identifier, lower case |
| 196 | sqlQuoted // string literal or quoted identifier, unquoted, lower case |
| 197 | sqlPunct // any other single byte |
| 198 | ) |
| 199 | |
| 200 | type sqlToken struct { |
| 201 | kind sqlTokenKind |
| 202 | text string |
| 203 | } |
| 204 | |
| 205 | const utf8BOM = "\uFEFF" |
| 206 | |
| 207 | // sqlTokens splits q the way SQLite's tokenizer does, for the guard's |
| 208 | // purposes: comments and whitespace (space, tab, newline, carriage return, |
| 209 | // form feed, vertical tab, and a byte-order mark anywhere) separate tokens |
| 210 | // and vanish; string literals and quoted identifiers ('...', "...", `...`, |
| 211 | // [...]) are one token each; runs of identifier bytes are words; anything |
| 212 | // else is one punctuation byte. |
| 213 | func sqlTokens(q string) []sqlToken { |
| 214 | var out []sqlToken |
| 215 | for i := 0; i < len(q); { |
| 216 | c := q[i] |
| 217 | switch { |
| 218 | case strings.HasPrefix(q[i:], utf8BOM): |
| 219 | i += len(utf8BOM) |
| 220 | case c == ' ' || c == '\t' || c == '\n' || c == '\r' || c == '\f' || c == '\v': |
| 221 | i++ |
| 222 | case c == '-' && i+1 < len(q) && q[i+1] == '-': |
| 223 | if j := strings.IndexByte(q[i:], '\n'); j >= 0 { |
| 224 | i += j + 1 |
| 225 | } else { |
| 226 | i = len(q) |
| 227 | } |
| 228 | case c == '/' && i+1 < len(q) && q[i+1] == '*': |
| 229 | if j := strings.Index(q[i+2:], "*/"); j >= 0 { |
| 230 | i += j + 4 |
| 231 | } else { |
| 232 | i = len(q) |
| 233 | } |
| 234 | case c == '\'' || c == '"' || c == '`': |
| 235 | end := min(skipQuoted(q, i, c), len(q)) |
| 236 | body := strings.ReplaceAll(q[i+1:end], string([]byte{c, c}), string(c)) |
| 237 | out = append(out, sqlToken{sqlQuoted, strings.ToLower(body)}) |
| 238 | i = end + 1 |
| 239 | case c == '[': |
| 240 | j := strings.IndexByte(q[i:], ']') |
| 241 | if j < 0 { |
| 242 | j = len(q) - i |
| 243 | } |
| 244 | out = append(out, sqlToken{sqlQuoted, strings.ToLower(q[i+1 : i+j])}) |
| 245 | i += j + 1 |
| 246 | case isSQLIdentByte(c): |
| 247 | j := i |
| 248 | for j < len(q) && isSQLIdentByte(q[j]) && !strings.HasPrefix(q[j:], utf8BOM) { |
| 249 | j++ |
| 250 | } |
| 251 | out = append(out, sqlToken{sqlWord, strings.ToLower(q[i:j])}) |
| 252 | i = j |
| 253 | default: |
| 254 | out = append(out, sqlToken{sqlPunct, string(c)}) |
| 255 | i++ |
| 256 | } |
| 257 | } |
| 258 | return out |
| 259 | } |
| 260 | |
| 261 | // isSQLIdentByte reports a byte that can continue an unquoted identifier: |
| 262 | // ASCII letters, digits, _ and $, and any byte of a multi-byte UTF-8 |
| 263 | // character (the byte-order mark is checked for before this). |
| 264 | func isSQLIdentByte(c byte) bool { |
| 265 | return c == '_' || c == '$' || c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9' || c >= 0x80 |
| 266 | } |
| 267 | |
| 268 | // splitFirstStatement returns the text up to and including the first |
| 269 | // top-level semicolon, and everything after it. Semicolons inside string |
| 270 | // literals, quoted identifiers and comments do not count. |
| 271 | func splitFirstStatement(q string) (first, rest string) { |
| 272 | for i := 0; i < len(q); i++ { |
| 273 | switch c := q[i]; c { |
| 274 | case '\'', '"', '`': |
| 275 | i = skipQuoted(q, i, c) |
| 276 | case '[': |
| 277 | if j := strings.IndexByte(q[i:], ']'); j >= 0 { |
| 278 | i += j |
| 279 | } else { |
| 280 | i = len(q) |
| 281 | } |
| 282 | case '-': |
| 283 | if i+1 < len(q) && q[i+1] == '-' { |
| 284 | if j := strings.IndexByte(q[i:], '\n'); j >= 0 { |
| 285 | i += j |
| 286 | } else { |
| 287 | i = len(q) |
| 288 | } |
| 289 | } |
| 290 | case '/': |
| 291 | if i+1 < len(q) && q[i+1] == '*' { |
| 292 | if j := strings.Index(q[i+2:], "*/"); j >= 0 { |
| 293 | i += j + 3 |
| 294 | } else { |
| 295 | i = len(q) |
| 296 | } |
| 297 | } |
| 298 | case ';': |
| 299 | return q[:i+1], q[i+1:] |
| 300 | } |
| 301 | } |
| 302 | return q, "" |
| 303 | } |
| 304 | |
| 305 | // skipQuoted returns the index of the quote closing the one at i, where a |
| 306 | // doubled quote is an escaped one, or len(q) when it never closes. |
| 307 | func skipQuoted(q string, i int, quote byte) int { |
| 308 | for j := i + 1; j < len(q); j++ { |
| 309 | if q[j] == quote { |
| 310 | if j+1 < len(q) && q[j+1] == quote { |
| 311 | j++ |
| 312 | continue |
| 313 | } |
| 314 | return j |
| 315 | } |
| 316 | } |
| 317 | return len(q) |
| 318 | } |
| 319 | |
amadan