| 1 | package main |
| 2 | |
| 3 | // The CLI half of `carlos db` (spec 2026-09-02 §4): the read-only query, the |
| 4 | // pull's landing rules, and the wire shape the session's replica client |
| 5 | // sends. The whole path through a real console is db_e2e_test.go. |
| 6 | |
| 7 | import ( |
| 8 | "bytes" |
| 9 | "context" |
| 10 | "crypto/sha256" |
| 11 | "database/sql" |
| 12 | "errors" |
| 13 | "net/http" |
| 14 | "net/http/httptest" |
| 15 | "os" |
| 16 | "os/exec" |
| 17 | "path/filepath" |
| 18 | "runtime" |
| 19 | "slices" |
| 20 | "strings" |
| 21 | "testing" |
| 22 | |
| 23 | "github.com/carlosframework/platform/internal/dbdoor" |
| 24 | ) |
| 25 | |
| 26 | // fixtureDB writes a small database the way a restore leaves one: WAL mode, |
| 27 | // one table, a few rows, checkpointed into the main file. |
| 28 | func fixtureDB(t *testing.T) string { |
| 29 | t.Helper() |
| 30 | path := filepath.Join(t.TempDir(), "copy.db") |
| 31 | db, err := sql.Open("sqlite", path) |
| 32 | if err != nil { |
| 33 | t.Fatal(err) |
| 34 | } |
| 35 | if _, err := db.Exec(`PRAGMA journal_mode=WAL; |
| 36 | CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, note TEXT); |
| 37 | INSERT INTO users (name, note) VALUES ('ada', NULL), ('grace', 'x'); |
| 38 | PRAGMA wal_checkpoint(TRUNCATE);`); err != nil { |
| 39 | t.Fatal(err) |
| 40 | } |
| 41 | if err := db.Close(); err != nil { |
| 42 | t.Fatal(err) |
| 43 | } |
| 44 | return path |
| 45 | } |
| 46 | |
| 47 | func sha(t *testing.T, path string) [32]byte { |
| 48 | t.Helper() |
| 49 | b, err := os.ReadFile(path) |
| 50 | if err != nil { |
| 51 | t.Fatal(err) |
| 52 | } |
| 53 | return sha256.Sum256(b) |
| 54 | } |
| 55 | |
| 56 | // A copy is harmless to write to. Read-only exists so a caller cannot |
| 57 | // BELIEVE it changed production: an agent that sees "1 row affected" |
| 58 | // concludes it fixed the bug. The error is the truth, in SQLite's words. |
| 59 | func TestQueryRefusesAWriteWithSQLitesOwnError(t *testing.T) { |
| 60 | path := fixtureDB(t) |
| 61 | for _, stmt := range []string{ |
| 62 | "UPDATE users SET name='x'", |
| 63 | "DELETE FROM users", |
| 64 | "INSERT INTO users (name) VALUES ('mallory')", |
| 65 | "CREATE TABLE t2 (x)", |
| 66 | } { |
| 67 | err := runQuery(&bytes.Buffer{}, path, stmt) |
| 68 | if err == nil || !strings.Contains(err.Error(), "attempt to write a readonly database") { |
| 69 | t.Errorf("%s: error = %v, want SQLite's readonly refusal", stmt, err) |
| 70 | } |
| 71 | } |
| 72 | } |
| 73 | |
| 74 | // query_only is set as well as mode=ro, and a statement list cannot talk |
| 75 | // its way past either: switching query_only off first still leaves the |
| 76 | // write refused, and the data unchanged. |
| 77 | func TestQueryCannotTurnOffItsOwnGuard(t *testing.T) { |
| 78 | path := fixtureDB(t) |
| 79 | for _, stmt := range []string{"PRAGMA query_only=0; UPDATE users SET name='x'", "PRAGMA query_only=0"} { |
| 80 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil { |
| 81 | t.Fatalf("%q: succeeded, want a refusal", stmt) |
| 82 | } |
| 83 | } |
| 84 | var out bytes.Buffer |
| 85 | if err := runQuery(&out, path, "SELECT name FROM users ORDER BY id"); err != nil || out.String() != "name\nada\ngrace\n" { |
| 86 | t.Fatalf("read after = %v %q, want the rows unchanged", err, out.String()) |
| 87 | } |
| 88 | } |
| 89 | |
| 90 | func TestQueryLeavesTheFileByteIdentical(t *testing.T) { |
| 91 | path := fixtureDB(t) |
| 92 | before := sha(t, path) |
| 93 | _ = runQuery(&bytes.Buffer{}, path, "UPDATE users SET name='x'") |
| 94 | if err := runQuery(&bytes.Buffer{}, path, "SELECT * FROM users"); err != nil { |
| 95 | t.Fatal(err) |
| 96 | } |
| 97 | if sha(t, path) != before { |
| 98 | t.Fatal("the database file changed under a read-only query") |
| 99 | } |
| 100 | } |
| 101 | |
| 102 | // Output is for an agent as much as a person: a header row, then one line |
| 103 | // per row, tab-separated, NULL spelled out. |
| 104 | func TestQueryPrintsAHeaderAndTabSeparatedRows(t *testing.T) { |
| 105 | path := fixtureDB(t) |
| 106 | var out bytes.Buffer |
| 107 | if err := runQuery(&out, path, "SELECT id, name, note FROM users ORDER BY id"); err != nil { |
| 108 | t.Fatal(err) |
| 109 | } |
| 110 | want := "id\tname\tnote\n1\tada\tNULL\n2\tgrace\tx\n" |
| 111 | if out.String() != want { |
| 112 | t.Fatalf("output = %q, want %q", out.String(), want) |
| 113 | } |
| 114 | } |
| 115 | |
| 116 | func TestQueryReportsABadStatement(t *testing.T) { |
| 117 | if err := runQuery(&bytes.Buffer{}, fixtureDB(t), "SELEKT 1"); err == nil { |
| 118 | t.Fatal("a syntax error succeeded") |
| 119 | } |
| 120 | } |
| 121 | |
| 122 | // --out must not exist, and what lands is 0600 whatever the umask. |
| 123 | func TestPullRefusesAnExistingOutAndLands0600(t *testing.T) { |
| 124 | src := fixtureDB(t) |
| 125 | dir := t.TempDir() |
| 126 | existing := filepath.Join(dir, "taken.db") |
| 127 | if err := os.WriteFile(existing, []byte("mine"), 0o644); err != nil { |
| 128 | t.Fatal(err) |
| 129 | } |
| 130 | if err := copyOut(context.Background(), src, existing); err == nil { |
| 131 | t.Fatal("copyOut over an existing file succeeded") |
| 132 | } |
| 133 | if b, _ := os.ReadFile(existing); string(b) != "mine" { |
| 134 | t.Fatal("copyOut touched the existing file") |
| 135 | } |
| 136 | out := filepath.Join(dir, "new.db") |
| 137 | if err := copyOut(context.Background(), src, out); err != nil { |
| 138 | t.Fatal(err) |
| 139 | } |
| 140 | fi, err := os.Stat(out) |
| 141 | if err != nil { |
| 142 | t.Fatal(err) |
| 143 | } |
| 144 | if perm := fi.Mode().Perm(); perm != 0o600 { |
| 145 | t.Fatalf("pulled copy is %04o, want 0600", perm) |
| 146 | } |
| 147 | if sha(t, out) != sha(t, src) { |
| 148 | t.Fatal("pulled copy differs from the restored one") |
| 149 | } |
| 150 | } |
| 151 | |
| 152 | // os.Rename fails across devices, and the temp directory and --out are |
| 153 | // routinely on different filesystems. A copy leaves its source in place; a |
| 154 | // rename would not. |
| 155 | func TestPullCopiesAcrossFilesystems(t *testing.T) { |
| 156 | src := fixtureDB(t) |
| 157 | out := filepath.Join(t.TempDir(), "elsewhere", "pulled.db") |
| 158 | if err := os.MkdirAll(filepath.Dir(out), 0o755); err != nil { |
| 159 | t.Fatal(err) |
| 160 | } |
| 161 | if err := copyOut(context.Background(), src, out); err != nil { |
| 162 | t.Fatal(err) |
| 163 | } |
| 164 | if _, err := os.Stat(src); err != nil { |
| 165 | t.Fatalf("the source is gone after copyOut (%v): that was a rename, which fails across filesystems", err) |
| 166 | } |
| 167 | } |
| 168 | |
| 169 | // A failed copy leaves nothing at --out. |
| 170 | func TestPullLeavesNoPartialFile(t *testing.T) { |
| 171 | out := filepath.Join(t.TempDir(), "pulled.db") |
| 172 | if err := copyOut(context.Background(), filepath.Join(t.TempDir(), "missing.db"), out); err == nil { |
| 173 | t.Fatal("copyOut of a missing source succeeded") |
| 174 | } |
| 175 | if _, err := os.Stat(out); !os.IsNotExist(err) { |
| 176 | t.Fatalf("a failed copy left %s behind (%v)", out, err) |
| 177 | } |
| 178 | } |
| 179 | |
| 180 | // The session's replica client sends the coordinate under the app's db |
| 181 | // path, carries the account segment and its ?exact=sqid switch verbatim, |
| 182 | // and authenticates as the logged-in person. |
| 183 | func TestAPISessionDBClientWireShape(t *testing.T) { |
| 184 | var gotURI, gotAuth, gotRange string |
| 185 | ts := httptest.NewServer(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { |
| 186 | gotURI, gotAuth, gotRange = r.URL.RequestURI(), r.Header.Get("Authorization"), r.Header.Get("Range") |
| 187 | w.WriteHeader(http.StatusPartialContent) |
| 188 | })) |
| 189 | defer ts.Close() |
| 190 | c := newTestSession(ts.URL).dbClient(apiAcct{Seg: "bab", Exact: true}, "titogo", "a.go.tito.io") |
| 191 | rc, err := c.OpenLTXFile(context.Background(), 3, 16, 32, 0, 0) |
| 192 | if err != nil { |
| 193 | t.Fatal(err) |
| 194 | } |
| 195 | rc.Close() |
| 196 | if want := "/api/cli/apps/bab/titogo/db/a.go.tito.io" + dbdoor.GetTail(3, 16, 32) + "?exact=sqid"; gotURI != want { |
| 197 | t.Errorf("uri = %s, want %s", gotURI, want) |
| 198 | } |
| 199 | if gotAuth != "Bearer cli-token" || gotRange != "bytes=0-" { |
| 200 | t.Errorf("auth %q range %q", gotAuth, gotRange) |
| 201 | } |
| 202 | } |
| 203 | |
| 204 | // Both guards are on, not one: mode=ro alone would pass every write test |
| 205 | // above, so query_only is pinned on its own. |
| 206 | func TestQueryRunsWithQueryOnlySet(t *testing.T) { |
| 207 | var out bytes.Buffer |
| 208 | if err := runQuery(&out, fixtureDB(t), "PRAGMA query_only"); err != nil || out.String() != "query_only\n1\n" { |
| 209 | t.Fatalf("PRAGMA query_only = %v %q, want 1", err, out.String()) |
| 210 | } |
| 211 | } |
| 212 | |
| 213 | // --instance may be left out only when the app has exactly one. Several is |
| 214 | // a refusal that names them all; guessing would hand over the wrong data |
| 215 | // while saying it was the right data (spec §7). |
| 216 | func TestResolveDBInstance(t *testing.T) { |
| 217 | answer := "" |
| 218 | ts := httptest.NewServer(http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { |
| 219 | if r.URL.Path != "/api/cli/apps/bab/titogo/instances" { |
| 220 | t.Errorf("path = %s", r.URL.Path) |
| 221 | } |
| 222 | w.Write([]byte(answer)) |
| 223 | })) |
| 224 | defer ts.Close() |
| 225 | sess := newTestSession(ts.URL) |
| 226 | ctx := context.Background() |
| 227 | acct := apiAcct{Seg: "bab"} |
| 228 | |
| 229 | if h, err := resolveDBInstance(ctx, sess, acct, "titogo", "named.go.tito.io"); err != nil || h != "named.go.tito.io" { |
| 230 | t.Fatalf("named = %q %v", h, err) |
| 231 | } |
| 232 | answer = `{"instances":[{"host":"only.go.tito.io"}]}` |
| 233 | if h, err := resolveDBInstance(ctx, sess, acct, "titogo", ""); err != nil || h != "only.go.tito.io" { |
| 234 | t.Fatalf("one instance = %q %v", h, err) |
| 235 | } |
| 236 | answer = `{"instances":[{"host":"b.go.tito.io"},{"host":"a.go.tito.io"}]}` |
| 237 | _, err := resolveDBInstance(ctx, sess, acct, "titogo", "") |
| 238 | if err == nil || !strings.Contains(err.Error(), "--instance") || !strings.Contains(err.Error(), "a.go.tito.io, b.go.tito.io") { |
| 239 | t.Fatalf("two instances = %v, want a refusal naming both", err) |
| 240 | } |
| 241 | answer = `{"instances":[{"host":"a.go.tito.io"}],"truncated":true}` |
| 242 | if _, err := resolveDBInstance(ctx, sess, acct, "titogo", ""); err == nil { |
| 243 | t.Fatal("a truncated list of one was taken as the only instance") |
| 244 | } |
| 245 | answer = `{"instances":[]}` |
| 246 | if _, err := resolveDBInstance(ctx, sess, acct, "titogo", ""); err == nil { |
| 247 | t.Fatal("no instances resolved to something") |
| 248 | } |
| 249 | } |
| 250 | |
| 251 | // Every way a restore can fail is named, and none of them reads as a |
| 252 | // transient "no replica found" (spec §5 R4, §7). |
| 253 | func TestDBRestoreErrorsNameTheirCause(t *testing.T) { |
| 254 | cases := []struct { |
| 255 | err error |
| 256 | want string |
| 257 | }{ |
| 258 | {&dbdoor.DoorError{Status: 409, Message: "a.go.tito.io keeps its data in region uk."}, "region uk"}, |
| 259 | {&dbdoor.DoorError{Status: 404}, "no instance"}, |
| 260 | // A proxy's HTML page during a console roll carries no sentence; |
| 261 | // the status still has to reach the person. |
| 262 | {&dbdoor.DoorError{Status: 502}, "502"}, |
| 263 | {dbdoor.ErrNothingReplicated, `replicate: "app"`}, |
| 264 | {dbdoor.ErrCheckFailed, "integrity check"}, |
| 265 | {os.ErrNotExist, "changed while it was being read"}, |
| 266 | } |
| 267 | for _, c := range cases { |
| 268 | if got := dbRestoreError("a.go.tito.io", c.err).Error(); !strings.Contains(got, c.want) { |
| 269 | t.Errorf("%v -> %q, want it to mention %q", c.err, got, c.want) |
| 270 | } |
| 271 | } |
| 272 | } |
| 273 | |
| 274 | // mode=ro covers only main, and query_only is a pragma a statement list can |
| 275 | // switch off. ATTACH opens with the connection's read-write flags, so it is |
| 276 | // the door around both: it could write the copy silently through a second |
| 277 | // name, or create and fill a file anywhere the user can write. VACUUM INTO |
| 278 | // attaches internally. All of them must fail, and leave no file behind. |
| 279 | func TestQueryCannotAttachOrWriteFilesElsewhere(t *testing.T) { |
| 280 | path := fixtureDB(t) |
| 281 | elsewhere := t.TempDir() |
| 282 | stray := filepath.Join(elsewhere, "stray.db") |
| 283 | for _, stmt := range []string{ |
| 284 | "PRAGMA query_only=0; ATTACH (SELECT file FROM pragma_database_list WHERE name='main') AS rw; UPDATE rw.users SET name='x'", |
| 285 | "ATTACH '" + stray + "' AS e", |
| 286 | "PRAGMA query_only=0; ATTACH '" + stray + "' AS e; CREATE TABLE e.t (x); INSERT INTO e.t VALUES (1)", |
| 287 | "VACUUM INTO '" + stray + "'", |
| 288 | "PRAGMA query_only=0; VACUUM INTO '" + stray + "'", |
| 289 | } { |
| 290 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil { |
| 291 | t.Errorf("%s: succeeded, want a refusal", stmt) |
| 292 | } |
| 293 | if _, err := os.Stat(stray); !os.IsNotExist(err) { |
| 294 | t.Fatalf("%s: left a file at %s", stmt, stray) |
| 295 | } |
| 296 | } |
| 297 | var out bytes.Buffer |
| 298 | if err := runQuery(&out, path, "SELECT name FROM users ORDER BY id"); err != nil || out.String() != "name\nada\ngrace\n" { |
| 299 | t.Fatalf("rows after = %v %q, want them unchanged", err, out.String()) |
| 300 | } |
| 301 | } |
| 302 | |
| 303 | // errWriter fails every write, the way a closed pipe does. |
| 304 | type errWriter struct{ n int } |
| 305 | |
| 306 | func (w *errWriter) Write([]byte) (int, error) { w.n++; return 0, errors.New("broken pipe") } |
| 307 | |
| 308 | // A reader that went away is an error the query loop stops on, not one it |
| 309 | // prints a thousand more rows into. |
| 310 | func TestQueryStopsWhenTheReaderGoesAway(t *testing.T) { |
| 311 | w := &errWriter{} |
| 312 | if err := runQuery(w, fixtureDB(t), "SELECT * FROM users"); err == nil { |
| 313 | t.Fatal("a failing writer was not reported") |
| 314 | } |
| 315 | if w.n != 1 { |
| 316 | t.Fatalf("kept writing after the first failure: %d writes", w.n) |
| 317 | } |
| 318 | } |
| 319 | |
| 320 | // `carlos db query ... | head` closes stdout early. Without SIGPIPE caught, |
| 321 | // Go kills the process on the next write and every deferred cleanup is |
| 322 | // skipped, leaving a plaintext copy of production in the temp directory. |
| 323 | // The helper below runs dbSignalContext's handling in a child whose stdout |
| 324 | // is a pipe nobody reads; it must get EPIPE back, clean up, and exit 0. |
| 325 | func TestQueryCleansUpWhenStdoutIsClosed(t *testing.T) { |
| 326 | if os.Getenv("CARLOS_DB_PIPE_HELPER") != "" { |
| 327 | ctx, stop := dbSignalContext() |
| 328 | defer stop() |
| 329 | dir := os.Getenv("CARLOS_DB_PIPE_HELPER") |
| 330 | for i := 0; i < 1<<20 && ctx.Err() == nil; i++ { |
| 331 | if _, err := os.Stdout.Write([]byte("row\n")); err != nil { |
| 332 | break |
| 333 | } |
| 334 | } |
| 335 | os.RemoveAll(dir) |
| 336 | os.Exit(0) |
| 337 | } |
| 338 | if runtime.GOOS == "windows" { |
| 339 | t.Skip("no SIGPIPE on windows") |
| 340 | } |
| 341 | dir := t.TempDir() |
| 342 | private := filepath.Join(dir, "carlos-db-x") |
| 343 | if err := os.Mkdir(private, 0o700); err != nil { |
| 344 | t.Fatal(err) |
| 345 | } |
| 346 | r, w, err := os.Pipe() |
| 347 | if err != nil { |
| 348 | t.Fatal(err) |
| 349 | } |
| 350 | r.Close() // the reader has gone, like head after its lines |
| 351 | cmd := exec.Command(os.Args[0], "-test.run=^TestQueryCleansUpWhenStdoutIsClosed$") |
| 352 | cmd.Env = append(os.Environ(), "CARLOS_DB_PIPE_HELPER="+private) |
| 353 | cmd.Stdout = w |
| 354 | err = cmd.Run() |
| 355 | w.Close() |
| 356 | if err != nil { |
| 357 | t.Fatalf("helper died (%v): the write killed it before cleanup could run", err) |
| 358 | } |
| 359 | if _, err := os.Stat(private); !os.IsNotExist(err) { |
| 360 | t.Fatalf("private directory survived a closed stdout (%v)", err) |
| 361 | } |
| 362 | } |
| 363 | |
| 364 | // dirEntries lists a directory's names, sorted. |
| 365 | func dirEntries(t *testing.T, dir string) []string { |
| 366 | t.Helper() |
| 367 | ents, err := os.ReadDir(dir) |
| 368 | if err != nil { |
| 369 | t.Fatal(err) |
| 370 | } |
| 371 | var out []string |
| 372 | for _, e := range ents { |
| 373 | out = append(out, e.Name()) |
| 374 | } |
| 375 | return out |
| 376 | } |
| 377 | |
| 378 | // The review's reproduction, verbatim. mode=ro covers main only, the |
| 379 | // attach limit does not cover the built-in temp schema, and the driver runs |
| 380 | // every statement in a list, so switching query_only off first let a temp |
| 381 | // copy of the data be written and changed with no error. It must fail, and |
| 382 | // leave no file anywhere: not beside the copy, not in the temp directory. |
| 383 | func TestQueryRefusesTheTempTableBypass(t *testing.T) { |
| 384 | tmp := t.TempDir() |
| 385 | t.Setenv("TMPDIR", tmp) |
| 386 | path := fixtureDB(t) |
| 387 | before := dirEntries(t, filepath.Dir(path)) |
| 388 | err := runQuery(&bytes.Buffer{}, path, |
| 389 | "PRAGMA query_only=0; CREATE TEMP TABLE stolen AS SELECT * FROM main.users; UPDATE stolen SET name='x'") |
| 390 | if err == nil { |
| 391 | t.Fatal("the temp-table bypass succeeded") |
| 392 | } |
| 393 | if got := dirEntries(t, tmp); len(got) != 0 { |
| 394 | t.Fatalf("temp directory holds %v after the refusal", got) |
| 395 | } |
| 396 | after := dirEntries(t, filepath.Dir(path)) |
| 397 | for _, name := range after { |
| 398 | if !slices.Contains(before, name) && name != "copy.db-wal" && name != "copy.db-shm" { |
| 399 | t.Fatalf("a new file %s appeared beside the copy", name) |
| 400 | } |
| 401 | } |
| 402 | } |
| 403 | |
| 404 | // One statement per query. The guards that matter (mode=ro, query_only) |
| 405 | // hold for a single statement; a list is how one statement turns them off |
| 406 | // for the next. |
| 407 | func TestQueryRunsOneStatementOnly(t *testing.T) { |
| 408 | path := fixtureDB(t) |
| 409 | for _, stmt := range []string{ |
| 410 | "SELECT 1; SELECT 2", |
| 411 | "PRAGMA query_only=0; UPDATE users SET name='x'", |
| 412 | "SELECT 1;\n-- trailing comment\nDELETE FROM users", |
| 413 | "SELECT 1; /* c */ SELECT 2", |
| 414 | } { |
| 415 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil || !strings.Contains(err.Error(), "one statement") { |
| 416 | t.Errorf("%q: error = %v, want the one-statement refusal", stmt, err) |
| 417 | } |
| 418 | } |
| 419 | for _, stmt := range []string{ |
| 420 | "SELECT name FROM users;", |
| 421 | "SELECT name FROM users; -- trailing comment", |
| 422 | "SELECT ';' AS semi, \"a;b\" FROM (SELECT 1 AS \"a;b\")", |
| 423 | "SELECT name FROM users /* ; */", |
| 424 | "WITH x AS (SELECT 1) SELECT * FROM x", |
| 425 | } { |
| 426 | if err := runQuery(&bytes.Buffer{}, path, stmt); err != nil { |
| 427 | t.Errorf("%q: error = %v, want it to run", stmt, err) |
| 428 | } |
| 429 | } |
| 430 | } |
| 431 | |
| 432 | // A pragma that takes an argument can change how this connection reads or |
| 433 | // where it spills (query_only, temp_store, temp_store_directory, |
| 434 | // writable_schema...). Only the read-only introspection pragmas may take |
| 435 | // one; bare reads of any pragma are fine. |
| 436 | func TestQueryRefusesPragmaChanges(t *testing.T) { |
| 437 | path := fixtureDB(t) |
| 438 | for _, stmt := range []string{ |
| 439 | "PRAGMA query_only=0", |
| 440 | "PRAGMA query_only = OFF", |
| 441 | "pragma temp_store=FILE", |
| 442 | "PRAGMA temp_store_directory='/tmp/x'", |
| 443 | "PRAGMA main.writable_schema=1", |
| 444 | "PRAGMA journal_mode(DELETE)", |
| 445 | " /* lead */ PRAGMA user_version=9", |
| 446 | } { |
| 447 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil || !strings.Contains(err.Error(), "pragma") { |
| 448 | t.Errorf("%q: error = %v, want the pragma refusal", stmt, err) |
| 449 | } |
| 450 | } |
| 451 | for _, stmt := range []string{ |
| 452 | "PRAGMA table_info(users)", |
| 453 | "PRAGMA main.table_info('users')", |
| 454 | "PRAGMA query_only", |
| 455 | "PRAGMA integrity_check", |
| 456 | "SELECT * FROM pragma_table_info('users')", |
| 457 | } { |
| 458 | if err := runQuery(&bytes.Buffer{}, path, stmt); err != nil { |
| 459 | t.Errorf("%q: error = %v, want it to run", stmt, err) |
| 460 | } |
| 461 | } |
| 462 | } |
| 463 | |
| 464 | // Sorts and temp b-trees stay in memory, so a big query cannot spill |
| 465 | // plaintext pages to a file outside the private directory. |
| 466 | func TestQueryKeepsTempStorageInMemory(t *testing.T) { |
| 467 | var out bytes.Buffer |
| 468 | if err := runQuery(&out, fixtureDB(t), "PRAGMA temp_store"); err != nil || out.String() != "temp_store\n2\n" { |
| 469 | t.Fatalf("PRAGMA temp_store = %v %q, want 2 (memory)", err, out.String()) |
| 470 | } |
| 471 | } |
| 472 | |
| 473 | // SQLite applies a pragma when it prepares the statement, EXPLAIN or not, |
| 474 | // and skips a leading byte-order mark and every kind of SQL whitespace. The |
| 475 | // pragma check must see through all of them. |
| 476 | func TestQueryRefusesDisguisedPragmaChanges(t *testing.T) { |
| 477 | path := fixtureDB(t) |
| 478 | for _, stmt := range []string{ |
| 479 | "EXPLAIN PRAGMA query_only=0", |
| 480 | "explain query plan PRAGMA query_only=0", |
| 481 | "EXPLAIN\fQUERY\tPLAN\nPRAGMA temp_store=FILE", |
| 482 | "\uFEFFPRAGMA query_only=0", |
| 483 | "\uFEFF EXPLAIN /* x */ PRAGMA writable_schema=1", |
| 484 | "\fPRAGMA\fquery_only\f=\f0", |
| 485 | } { |
| 486 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil || !strings.Contains(err.Error(), "pragma") { |
| 487 | t.Errorf("%q: error = %v, want the pragma refusal", stmt, err) |
| 488 | } |
| 489 | } |
| 490 | for _, stmt := range []string{"EXPLAIN SELECT * FROM users", "EXPLAIN QUERY PLAN SELECT * FROM users", "\uFEFFSELECT 1", "EXPLAIN PRAGMA table_info(users)"} { |
| 491 | if err := runQuery(&bytes.Buffer{}, path, stmt); err != nil { |
| 492 | t.Errorf("%q: error = %v, want it to run", stmt, err) |
| 493 | } |
| 494 | } |
| 495 | } |
| 496 | |
| 497 | // A pull interrupted before or during the copy publishes nothing at --out, |
| 498 | // and so cannot go on to report success. |
| 499 | func TestPullPublishesNothingOnceCancelled(t *testing.T) { |
| 500 | src := fixtureDB(t) |
| 501 | out := filepath.Join(t.TempDir(), "pulled.db") |
| 502 | ctx, cancel := context.WithCancel(context.Background()) |
| 503 | cancel() |
| 504 | if err := copyOut(ctx, src, out); !errors.Is(err, context.Canceled) { |
| 505 | t.Fatalf("copyOut after cancel = %v, want context.Canceled", err) |
| 506 | } |
| 507 | if _, err := os.Stat(out); !os.IsNotExist(err) { |
| 508 | t.Fatalf("a cancelled pull left %s (%v)", out, err) |
| 509 | } |
| 510 | } |
| 511 | |
| 512 | // The pragma guard is an invariant over the whole statement, not a prefix |
| 513 | // check: SQLite skips byte-order marks, comments and every kind of |
| 514 | // whitespace anywhere, and applies a pragma at prepare even under EXPLAIN. |
| 515 | // This generates the disguises from parts and asserts every one is refused. |
| 516 | func TestQueryRefusesEveryDisguiseOfAPragmaChange(t *testing.T) { |
| 517 | bom := "\uFEFF" |
| 518 | gaps := []string{"", " ", "\t", "\n", "\f", "\r\n", bom, " " + bom, bom + bom, "/*x*/", "/*x*/" + bom, bom + "/**/ ", "-- c\n", " /* a */ /* b */ "} |
| 519 | leads := []string{"", "EXPLAIN", "explain query plan", "EXPLAIN QUERY PLAN"} |
| 520 | changes := []struct{ name, set string }{ |
| 521 | {"query_only", "=0"}, {"query_only", "(0)"}, {"temp_store", "=1"}, {"temp_store", "=FILE"}, |
| 522 | {"temp_store_directory", "='/tmp/x'"}, {"writable_schema", "=1"}, {"main.writable_schema", "=ON"}, |
| 523 | {`"query_only"`, "=0"}, {"[query_only]", "=0"}, {"`temp_store`", "=1"}, {"main . query_only", "=0"}, |
| 524 | } |
| 525 | path := fixtureDB(t) |
| 526 | n := 0 |
| 527 | for _, lead := range leads { |
| 528 | for _, g1 := range gaps { |
| 529 | for _, g2 := range gaps { |
| 530 | for _, c := range changes { |
| 531 | sep := g2 |
| 532 | if sep == "" { |
| 533 | sep = " " // two words need something between them |
| 534 | } |
| 535 | stmt := g1 + lead + sep + "PRAGMA" + sep + c.name + g1 + c.set |
| 536 | if err := checkQuerySQL(stmt); err == nil || !strings.Contains(err.Error(), "pragma") { |
| 537 | t.Errorf("%q: error = %v, want the pragma refusal", stmt, err) |
| 538 | } |
| 539 | n++ |
| 540 | } |
| 541 | } |
| 542 | } |
| 543 | } |
| 544 | if n < 1000 { |
| 545 | t.Fatalf("generated only %d cases", n) |
| 546 | } |
| 547 | // And end to end, a handful through the real query path. |
| 548 | for _, stmt := range []string{"/*x*/" + bom + "PRAGMA query_only=0", " " + bom + "PRAGMA query_only=0", bom + bom + "PRAGMA temp_store=1", "EXPLAIN " + bom + "PRAGMA query_only=0"} { |
| 549 | if err := runQuery(&bytes.Buffer{}, path, stmt); err == nil || !strings.Contains(err.Error(), "pragma") { |
| 550 | t.Errorf("%q through runQuery: error = %v, want the pragma refusal", stmt, err) |
| 551 | } |
| 552 | } |
| 553 | // What must still run. |
| 554 | for _, stmt := range []string{ |
| 555 | "PRAGMA table_info(users)", bom + " PRAGMA table_info ( 'users' )", "PRAGMA query_only", "EXPLAIN PRAGMA query_only", |
| 556 | "SELECT 'PRAGMA query_only=0' AS s", `SELECT 1 AS "pragma"`, "SELECT 1 -- PRAGMA query_only=0", "SELECT 1 /* PRAGMA temp_store=1 */", |
| 557 | } { |
| 558 | if err := runQuery(&bytes.Buffer{}, path, stmt); err != nil { |
| 559 | t.Errorf("%q: error = %v, want it to run", stmt, err) |
| 560 | } |
| 561 | } |
| 562 | } |
| 563 | |
| 564 | // "pragma" is only a statement in statement position. As a column name or |
| 565 | // alias it is an ordinary identifier, and a read that uses it must run. |
| 566 | func TestQueryAllowsPragmaAsAnIdentifier(t *testing.T) { |
| 567 | for _, q := range []string{ |
| 568 | "SELECT pragma FROM (SELECT 1 AS pragma)", |
| 569 | "SELECT pragma = 1 FROM (SELECT 1 AS pragma)", |
| 570 | "SELECT x FROM (SELECT 1 AS x) WHERE x IN (SELECT 1 AS pragma)", |
| 571 | "EXPLAIN SELECT pragma = 1 FROM (SELECT 1 AS pragma)", |
| 572 | } { |
| 573 | if err := checkQuerySQL(q); err != nil { |
| 574 | t.Errorf("checkQuerySQL(%q) = %v, want it allowed", q, err) |
| 575 | } |
| 576 | } |
| 577 | } |
| 578 | |
amadan