iron-shell 14.0.3

//! SQL built by string interpolation, run by a CLI that executes many statements.
//!
//! `safety::sql_literal` and `safety::sql_identifier` exist and are careful:
//! literals get their quotes doubled and a NUL refused; identifiers are refused
//! outright unless they are plainly `[A-Za-z_][A-Za-z0-9_]*`, because an
//! identifier cannot be passed as a quoted value. Several `db_sqlite_*` builtins
//! do not call either, and interpolate caller-controlled strings straight into a
//! statement handed to the `sqlite3` CLI — which runs every statement in the
//! string, separated by `;`.
//!
//! When this was written, `db_sqlite_export_csv` and `db_sqlite_create` were not named in
//! `safety::classified_effect` at all, so `effect_of` returns `Pure` and
//! `guard_dispatch` lets them through without a decision or an audit line. A
//! "read-only export" that can drop a table is the same shape as the `rm` bug
//! this repo keeps citing: policy that reads as coverage while governing
//! nothing.
//!
//! These tests demonstrate rather than argue. Each one writes a real database,
//! runs the builtin the way an agent would, and then checks the database for the
//! effect — the value the builtin returned is not evidence, because the
//! injected statement's whole point is that it happens off to the side.
//!
//! They are `#[ignore]`d only if `sqlite3` is missing, which is checked at
//! runtime rather than assumed.

use iron_shell::env::Env;
use iron_shell::value::Value;
use std::path::PathBuf;
use std::sync::Mutex;

static ENV: Mutex<()> = Mutex::new(());

fn have_sqlite3() -> bool {
    std::process::Command::new("sqlite3")
        .arg("--version")
        .output()
        .map(|o| o.status.success())
        .unwrap_or(false)
}

fn call(name: &str, args: Vec<Value>) -> Result<Value, String> {
    let mut env = Env::new();
    iron_shell::builtins::call(name, args, &mut env).map_err(|e| e.to_string())
}

fn s(v: &str) -> Value {
    Value::Str(v.to_string())
}

/// A fresh database with one table `victim` holding one row, plus a workspace
/// that agent mode will accept.
fn fixture(tag: &str) -> PathBuf {
    let ws = std::env::temp_dir().join("ae_sqli");
    std::fs::create_dir_all(&ws).unwrap();
    std::env::set_var("IRONSHELL_MODE", "agent");
    std::env::set_var("IRONSHELL_WORKSPACE", &ws);
    std::env::remove_var("IRONSHELL_POLICY");
    iron_shell::safety::set_principal(None);

    let db = ws.join(format!("{tag}.sqlite"));
    let _ = std::fs::remove_file(&db);
    let out = std::process::Command::new("sqlite3")
        .arg(&db)
        .arg("CREATE TABLE victim(id INTEGER); INSERT INTO victim VALUES (1);")
        .output()
        .expect("seed the database");
    assert!(out.status.success(), "fixture setup failed");
    db
}

/// Does `victim` still exist in `db`?
fn victim_survives(db: &PathBuf) -> bool {
    let out = std::process::Command::new("sqlite3")
        .arg(db)
        .arg("SELECT name FROM sqlite_master WHERE type='table' AND name='victim';")
        .output()
        .expect("query sqlite_master");
    String::from_utf8_lossy(&out.stdout).contains("victim")
}

/// `db_sqlite_create` is **not dispatched** — it is part of the unregistered
/// set (§5 item 3 of docs/HANDOFF.md). `db_sqlite_export_csv` was listed with
/// it, but its body was dispatched all along as `db_sqlite_to_csv`.
///
/// An injection test against an unreachable name passes because nothing ran,
/// which is a blind check. The first draft of this file did exactly that and
/// reported two clean results it had not earned; only `db_sqlite_insert` was
/// reachable, and it was the one that failed.
fn dispatched(name: &str) -> bool {
    iron_shell::builtins::is_dispatched(name)
}

#[test]
fn the_unreachable_cases_are_still_unreachable() {
    // If it becomes registered, its injection surface ships with it —
    // `db_sqlite_create` still interpolates the column *type* as written. This
    // failing is the reminder to validate before exposing.
    // `db_sqlite_export_csv` was listed here, but its body was reachable all
    // along as `db_sqlite_to_csv`; that is the name the export test now calls.
    assert!(
        !dispatched("db_sqlite_create"),
        "`db_sqlite_create` is now reachable. Before shipping it, check that every \
         identifier it interpolates goes through `safety::sql_identifier`; \
         it still passes the column type through unchecked, which is noted at \
         the call site."
    );
}

#[test]
fn the_injectable_builtin_is_actually_reachable() {
    // The counterweight to the test above: this file would be worthless if the
    // one case it proves were unreachable too.
    assert!(
        dispatched("db_sqlite_insert"),
        "db_sqlite_insert must be dispatched for its injection test to mean anything"
    );
    assert!(dispatched("db_sqlite_update"));
    assert!(dispatched("db_sqlite_drop_table"));
    assert!(dispatched("db_sqlite_to_csv"));
}

#[test]
fn export_csv_does_not_execute_a_second_statement() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    // Dispatched as `db_sqlite_to_csv` (asserted in
    // `the_injectable_builtin_is_actually_reachable`), so this is a live path.
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("export");
    assert!(
        victim_survives(&db),
        "fixture must start with the table present"
    );

    // `table_or_query` is interpolated into `SELECT * FROM {}` whenever it does
    // not begin with SELECT. The sqlite3 CLI runs every statement in the string.
    let injected = "victim; DROP TABLE victim; --";
    let _ = call(
        "db_sqlite_to_csv",
        vec![s(&db.to_string_lossy()), s(injected)],
    );

    assert!(
        victim_survives(&db),
        "`db_sqlite_to_csv` executed an injected `DROP TABLE`. The second \
         argument is interpolated into `SELECT * FROM {{}}` without \
         `safety::sql_identifier`, and the sqlite3 CLI runs every `;`-separated \
         statement it is given. The builtin is also absent from \
         `safety::classified_effect`, so `effect_of` is `Pure` and \
         `guard_dispatch` neither gated nor audited the call — an export that \
         drops tables, with no approval prompt in agent mode."
    );
}

#[test]
fn create_does_not_execute_a_second_statement() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("create");
    assert!(victim_survives(&db));

    // The table name goes into `CREATE TABLE IF NOT EXISTS {} (...)` unchecked.
    let mut cols = std::collections::BTreeMap::new();
    cols.insert("id".to_string(), s("INTEGER"));
    let injected = "t1(x); DROP TABLE victim; --";
    let _ = call(
        "db_sqlite_create",
        vec![s(&db.to_string_lossy()), s(injected), Value::Record(cols)],
    );

    assert!(
        victim_survives(&db),
        "`db_sqlite_create` executed an injected `DROP TABLE`: the table name is \
         interpolated without `safety::sql_identifier`"
    );
}

#[test]
fn insert_does_not_execute_a_second_statement_through_a_column_name() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("insert");
    assert!(victim_survives(&db));

    // Values are escaped (quotes doubled inline). Column names are not — and they
    // come from a caller-supplied record, so they are just as controlled.
    let mut rec = std::collections::BTreeMap::new();
    rec.insert(
        "id) VALUES (1); DROP TABLE victim; --".to_string(),
        Value::Int(1),
    );
    let _ = call(
        "db_sqlite_insert",
        vec![s(&db.to_string_lossy()), s("victim"), Value::Record(rec)],
    );

    assert!(
        victim_survives(&db),
        "`db_sqlite_insert` executed an injected `DROP TABLE` through a column \
         name. The record's keys are interpolated into `INSERT INTO t ({{}})` \
         without `safety::sql_identifier`, even though its *values* are escaped — \
         so the escaping present makes the gap easier to miss, not smaller."
    );
}

#[test]
fn a_value_containing_a_quote_still_round_trips() {
    // The counterweight. Refusing injection must not mean refusing apostrophes:
    // if this breaks, the fix has been applied to literals rather than to
    // identifiers, which is the wrong half.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("quote");

    let out = std::process::Command::new("sqlite3")
        .arg(&db)
        .arg("CREATE TABLE names(n TEXT);")
        .output()
        .expect("create");
    assert!(out.status.success());

    let mut rec = std::collections::BTreeMap::new();
    rec.insert("n".to_string(), s("O'Brien"));
    call(
        "db_sqlite_insert",
        vec![s(&db.to_string_lossy()), s("names"), Value::Record(rec)],
    )
    .expect("a legitimate insert with an apostrophe must succeed");

    let read = std::process::Command::new("sqlite3")
        .arg(&db)
        .arg("SELECT n FROM names;")
        .output()
        .expect("read back");
    assert!(
        String::from_utf8_lossy(&read.stdout).contains("O'Brien"),
        "an ordinary apostrophe must survive the round trip"
    );
}

#[test]
fn update_does_not_execute_a_second_statement_through_a_column_name() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("update");
    assert!(victim_survives(&db));
    assert!(dispatched("db_sqlite_update"));

    // `db_sqlite_update` shares the shape: values escaped, keys not. Its WHERE
    // clause is SQL by contract, so the key is the part that should not be.
    let mut rec = std::collections::BTreeMap::new();
    rec.insert("id = 1; DROP TABLE victim; --".to_string(), Value::Int(2));
    let _ = call(
        "db_sqlite_update",
        vec![
            s(&db.to_string_lossy()),
            s("victim"),
            Value::Record(rec),
            s("1=1"),
        ],
    );

    assert!(
        victim_survives(&db),
        "`db_sqlite_update` executed an injected `DROP TABLE` through a column name"
    );
}

#[test]
fn a_nul_in_a_value_is_refused_rather_than_silently_truncating() {
    // The hand-rolled `replace('\'', "''")` these builtins used agreed with
    // `safety::sql_literal` about quotes and inherited none of its other care.
    // sqlite3 reads its SQL as a C string, so a NUL truncates the statement and
    // the row lands short — a wrong value written silently.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("nul");
    let out = std::process::Command::new("sqlite3")
        .arg(&db)
        .arg("CREATE TABLE names(n TEXT);")
        .output()
        .expect("create");
    assert!(out.status.success());

    let mut rec = std::collections::BTreeMap::new();
    rec.insert("n".to_string(), s("before\u{0}after"));
    let err = call(
        "db_sqlite_insert",
        vec![s(&db.to_string_lossy()), s("names"), Value::Record(rec)],
    )
    .expect_err("a NUL in a value must be refused, not truncated");
    assert!(
        err.contains("NUL"),
        "the refusal should say what was wrong with it: {err}"
    );
}

#[test]
fn a_json_file_cannot_inject_sql_through_its_object_keys() {
    // The nastiest of the three, because the payload need not be an argument.
    // `db_json_to_sqlite` built both CREATE TABLE and INSERT from the *file's*
    // object keys, unvalidated — so importing an untrusted JSON file was enough.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("json");
    assert!(victim_survives(&db));
    assert!(
        dispatched("db_json_to_sqlite"),
        "this test is pointless if the builtin is unreachable — the lesson from \
         the two vacuous cases above"
    );

    let ws = std::env::temp_dir().join("ae_sqli");
    let json = ws.join("payload.json");
    std::fs::write(&json, r#"[{"x TEXT); DROP TABLE victim; --": "1"}]"#).unwrap();

    let _ = call(
        "db_json_to_sqlite",
        vec![
            s(&db.to_string_lossy()),
            s(&json.to_string_lossy()),
            s("imported"),
        ],
    );

    assert!(
        victim_survives(&db),
        "`db_json_to_sqlite` executed a `DROP TABLE` carried in a JSON object \
         key. Column names came from the file and were interpolated without \
         `safety::sql_identifier`, so importing an untrusted file was enough."
    );
    let _ = std::fs::remove_file(&json);
}

#[test]
fn a_legitimate_json_import_still_works() {
    // The counterweight. Validation must not mean refusing ordinary imports.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("jsonok");
    let ws = std::env::temp_dir().join("ae_sqli");
    let json = ws.join("ok.json");
    std::fs::write(&json, r#"[{"name": "O'Brien", "age": 42}]"#).unwrap();

    call(
        "db_json_to_sqlite",
        vec![
            s(&db.to_string_lossy()),
            s(&json.to_string_lossy()),
            s("people"),
        ],
    )
    .expect("an ordinary JSON import must still succeed");

    let read = std::process::Command::new("sqlite3")
        .arg(&db)
        .arg("SELECT name, age FROM people;")
        .output()
        .expect("read back");
    let got = String::from_utf8_lossy(&read.stdout);
    assert!(
        got.contains("O'Brien") && got.contains("42"),
        "the imported row should round-trip, apostrophe included: {got:?}"
    );
    let _ = std::fs::remove_file(&json);
}

// ── The column type: the last unvalidated interpolation in the SQL family ────
//
// `db_sqlite_create_table` builds `"<name> <type>"` per column.
// `safety::sql_identifier` covered the name; the type was interpolated as
// written, behind a comment saying that constraining it "means inventing a
// grammar for SQL type expressions, which is a deliberate decision rather than a
// drive-by one". That note was carried across three sessions. It is now taken.
//
// The builtin is still unregistered, so nothing here was exploitable — which is
// exactly why it kept being deferred. What the fix removes is a *precondition*:
// registering it used to require a security decision nobody had made, and that
// is the kind of debt that gets paid by whoever is registering builtins in a
// hurry.

#[test]
fn a_column_type_cannot_close_the_statement() {
    use iron_shell::safety::sql_column_type;
    for payload in [
        "TEXT); DROP TABLE users; --",
        "TEXT, x TEXT); ATTACH DATABASE '/tmp/e' AS e; --",
        "TEXT DEFAULT (SELECT password FROM users)",
        "TEXT/*comment*/",
        "TEXT--",
        "TEXT'",
        "TEXT\nDROP TABLE users",
    ] {
        assert!(
            sql_column_type("db_sqlite_create", payload).is_err(),
            "{payload:?} must be refused; sqlite3 runs every `;`-separated \
             statement it is handed"
        );
    }
}

#[test]
fn the_ordinary_column_types_still_work() {
    // The half that keeps the fix from being a ban. If these stop working the
    // builtin is unusable and the "fix" is a removal in disguise.
    use iron_shell::safety::sql_column_type;
    for spec in [
        "TEXT",
        "text",
        "INTEGER PRIMARY KEY",
        "INTEGER PRIMARY KEY AUTOINCREMENT",
        "VARCHAR(255)",
        "VARCHAR(255) NOT NULL",
        "DECIMAL(10,2)",
        "DECIMAL(10, 2)",
        "REAL DEFAULT 0",
        "REAL DEFAULT -1.5",
        "TEXT DEFAULT ''",
        "TEXT DEFAULT 'none'",
        "TEXT COLLATE NOCASE",
        "DATETIME DEFAULT CURRENT_TIMESTAMP",
        "BLOB",
        "DOUBLE PRECISION",
        "BOOLEAN DEFAULT TRUE",
        "TEXT NOT NULL UNIQUE",
    ] {
        assert!(
            sql_column_type("db_sqlite_create", spec).is_ok(),
            "{spec:?} is an ordinary column type and must be accepted"
        );
    }
}

#[test]
fn a_parenthesised_expression_is_refused_and_the_error_says_where_to_go() {
    // `CHECK(...)` and `REFERENCES t(c)` are legitimate SQL that this cannot
    // judge, so they are refused rather than half-parsed. The refusal is only
    // defensible because there is somewhere else to go, and the hint says so.
    use iron_shell::safety::sql_column_type;
    for spec in ["TEXT CHECK(length(x) < 10)", "INTEGER REFERENCES users(id)"] {
        let e = sql_column_type("db_sqlite_create", spec)
            .expect_err("a parenthesised expression must be refused");
        let text = format!("{e:#}");
        assert!(
            text.to_ascii_lowercase()
                .contains("whole column definition"),
            "the error must point at the raw column-definition branch, got: {text}"
        );
    }
}

#[test]
fn the_size_suffix_is_digits_only() {
    use iron_shell::safety::sql_column_type;
    assert!(sql_column_type("b", "VARCHAR(255)").is_ok());
    assert!(sql_column_type("b", "VARCHAR(x)").is_err());
    assert!(sql_column_type("b", "VARCHAR(255;DROP)").is_err());
    assert!(sql_column_type("b", "VARCHAR(").is_err());
    assert!(sql_column_type("b", "VARCHAR()").is_err());
}

#[test]
fn an_empty_type_is_refused_rather_than_silently_dropped() {
    use iron_shell::safety::sql_column_type;
    assert!(sql_column_type("b", "").is_err());
    assert!(sql_column_type("b", "   ").is_err());
}

#[test]
fn a_quoted_default_cannot_end_early() {
    // `DEFAULT ''` is accepted, so the literal form has to be exact: a token
    // carrying an interior quote could close and start something else.
    use iron_shell::safety::sql_column_type;
    assert!(sql_column_type("b", "TEXT DEFAULT 'ok'").is_ok());
    // SQL's own escape for a quote. Refused rather than parsed: the accepted
    // form is a literal with no interior quote at all, which is a narrower rule
    // than SQLite's and cannot be wrong in the dangerous direction.
    assert!(sql_column_type("b", "TEXT DEFAULT 'a''b'").is_err());
    // This one is *accepted*, and the first version of this test asserted the
    // opposite out of pattern-matching on the `);` rather than reading it.
    // `');DROP'` is a quoted string literal whose contents are `);DROP` — it
    // does not close the CREATE TABLE, because the quotes are the token. The
    // payload-shaped thing inside a literal is data, and refusing it would be
    // superstition rather than a control.
    assert!(sql_column_type("b", "TEXT DEFAULT ');DROP'").is_ok());
    // What must not be accepted is the same text *unquoted*, where it is syntax.
    assert!(sql_column_type("b", "TEXT DEFAULT );DROP").is_err());
}

#[test]
fn a_file_that_is_not_a_database_is_refused_not_run_as_sql() {
    // sqlite3 3.53 reads an existing non-database file named as the database
    // as a SQL script, then opens the next argument as the database: given
    // notes.txt, `db_sqlite_vacuum` ran the notes as SQL, made a database named
    // `VACUUM` in the working directory, and answered true.
    let _g = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let ws = std::env::temp_dir().join("ae_sqli");
    std::fs::create_dir_all(&ws).unwrap();
    std::env::set_var("IRONSHELL_MODE", "agent");
    std::env::set_var("IRONSHELL_WORKSPACE", &ws);
    let notes = ws.join("notes.txt");
    std::fs::write(&notes, "select 1;\n").unwrap();
    let n = notes.to_string_lossy().into_owned();
    let out = ws.join("notes.csv").to_string_lossy().into_owned();
    let mut row = std::collections::BTreeMap::new();
    row.insert("a".to_string(), Value::Int(1));
    let cases: Vec<(&str, Vec<Value>)> = vec![
        ("db_sqlite_insert", vec![s(&n), s("t"), Value::Record(row)]),
        ("db_sqlite_vacuum", vec![s(&n)]),
        ("db_sqlite_open", vec![s(&n)]),
        ("db_sqlite_dump", vec![s(&n)]),
        ("db_sqlite_backup", vec![s(&n)]),
        ("db_sqlite_to_csv", vec![s(&n), s("t"), s(&out)]),
    ];
    let mut ran = Vec::new();
    for (name, args) in cases {
        match call(name, args) {
            Err(e) if e.contains("E_BAD_ARG") && e.contains("not an SQLite database") => {}
            other => ran.push(format!("{name}: {other:?}")),
        }
    }
    std::env::remove_var("IRONSHELL_MODE");
    std::env::remove_var("IRONSHELL_WORKSPACE");
    assert_eq!(std::fs::read_to_string(&notes).unwrap(), "select 1;\n");
    let _ = std::fs::remove_file(&notes);
    assert!(ran.is_empty(), "not refused:\n{}", ran.join("\n"));
}

// ── `-safe` and `-readonly` on the sqlite3 CLI, and the batch refusal ────────
//
// Every builtin except `db_sqlite_exec` runs the CLI with `-safe`, which refuses
// writefile(), ATTACH and the file dot-commands; the reads also run `-readonly`.
// The WHERE clauses and raw column text are refused when they carry a `;` that
// would start a second statement. Each test below checks the file, not the
// return value.

/// A directory outside the workspace that a smuggled statement would write into.
fn outside(tag: &str) -> PathBuf {
    let dir = std::env::temp_dir().join(format!("ae_sqli_outside_{tag}"));
    let _ = std::fs::remove_dir_all(&dir);
    std::fs::create_dir_all(&dir).unwrap();
    dir
}

/// The text one query returns, read with the CLI's own defaults.
fn query_text(db: &PathBuf, sql: &str) -> String {
    let out = std::process::Command::new("sqlite3")
        .arg(db)
        .arg(sql)
        .output()
        .expect("query the database");
    String::from_utf8_lossy(&out.stdout).into_owned()
}

fn rec(pairs: &[(&str, Value)]) -> Value {
    let mut m = std::collections::BTreeMap::new();
    for (k, v) in pairs {
        m.insert(k.to_string(), v.clone());
    }
    Value::Record(m)
}

#[test]
fn to_csv_with_a_second_statement_cannot_change_the_database() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("csv_drop");
    // A SELECT is passed through as the caller's query, so the second statement
    // reaches the CLI. `-readonly` must refuse it, and the refusal must be an error.
    let r = call(
        "db_sqlite_to_csv",
        vec![s(&db.to_string_lossy()), s("SELECT 1; DROP TABLE victim;")],
    );
    assert!(
        r.is_err(),
        "a second statement in a read must be reported: {r:?}"
    );
    assert!(
        victim_survives(&db),
        "`db_sqlite_to_csv` ran a DROP TABLE carried in a SELECT; the CLI was not -readonly"
    );
}

#[test]
fn to_csv_cannot_write_a_file_through_writefile() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("csv_writefile");
    let dir = outside("writefile");
    let marker = dir.join("marker");
    let q = format!("SELECT writefile('{}','x')", marker.to_string_lossy());
    let r = call("db_sqlite_to_csv", vec![s(&db.to_string_lossy()), s(&q)]);
    assert!(r.is_err(), "writefile must be refused, not answered: {r:?}");
    assert!(
        !marker.exists(),
        "`db_sqlite_to_csv` wrote a file outside the workspace through writefile(); \
         the CLI must run with -safe"
    );
}

#[test]
fn to_csv_cannot_attach_a_database_outside_the_workspace() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("csv_attach");
    let dir = outside("attach");
    let att = dir.join("attached.db");
    let q = format!(
        "SELECT 1 FROM victim; ATTACH '{}' AS o",
        att.to_string_lossy()
    );
    let r = call("db_sqlite_to_csv", vec![s(&db.to_string_lossy()), s(&q)]);
    assert!(r.is_err(), "ATTACH must be refused, not answered: {r:?}");
    assert!(
        !att.exists(),
        "`db_sqlite_to_csv` created a database outside the workspace through ATTACH"
    );
}

#[test]
fn update_refuses_a_batch_in_its_where_clause() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("upd_batch");
    let err = call(
        "db_sqlite_update",
        vec![
            s(&db.to_string_lossy()),
            s("victim"),
            rec(&[("id", Value::Int(2))]),
            s("1=1; DROP TABLE victim"),
        ],
    )
    .expect_err("a `;` that starts a second statement must be refused");
    assert!(err.contains("E_BAD_ARG"), "expected E_BAD_ARG, got: {err}");
    assert!(
        victim_survives(&db),
        "the refused update still ran its DROP"
    );
}

#[test]
fn a_where_clause_cannot_write_a_file_through_writefile() {
    // The WHERE clause is caller SQL with no `;`, so only `-safe` stands between
    // it and the filesystem.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("upd_writefile");
    let dir = outside("upd_writefile");
    let marker = dir.join("marker");
    let cond = format!(
        "id = 1 AND writefile('{}','x') IS NULL",
        marker.to_string_lossy()
    );
    let r = call(
        "db_sqlite_update",
        vec![
            s(&db.to_string_lossy()),
            s("victim"),
            rec(&[("id", Value::Int(2))]),
            s(&cond),
        ],
    );
    assert!(
        r.is_err(),
        "writefile in a WHERE clause must be refused: {r:?}"
    );
    assert!(
        !marker.exists(),
        "a WHERE clause wrote a file outside the workspace"
    );
}
#[test]
fn delete_and_create_table_refuse_a_batch_too() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("del_batch");
    let err = call(
        "db_sqlite_delete",
        vec![
            s(&db.to_string_lossy()),
            s("victim"),
            s("1=1; DROP TABLE victim"),
        ],
    )
    .expect_err("a batch in a DELETE condition must be refused");
    assert!(err.contains("E_BAD_ARG"), "expected E_BAD_ARG, got: {err}");
    assert!(victim_survives(&db));

    let err = call(
        "db_sqlite_create_table",
        vec![
            s(&db.to_string_lossy()),
            s("t1"),
            s("x TEXT); DROP TABLE victim; --"),
        ],
    )
    .expect_err("a batch in raw column text must be refused");
    assert!(err.contains("E_BAD_ARG"), "expected E_BAD_ARG, got: {err}");
    assert!(victim_survives(&db));
}

#[test]
fn a_semicolon_inside_a_string_literal_in_a_where_clause_still_works() {
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("upd_literal");
    let _ = query_text(
        &db,
        "CREATE TABLE notes(id INTEGER, tag TEXT); INSERT INTO notes VALUES (1,'a;b'),(2,'c');",
    );
    call(
        "db_sqlite_update",
        vec![
            s(&db.to_string_lossy()),
            s("notes"),
            rec(&[("tag", s("x"))]),
            s("tag = 'a;b'"),
        ],
    )
    .expect("a `;` inside a string literal is not a second statement and must still work");
    assert_eq!(
        query_text(&db, "SELECT tag FROM notes WHERE id = 1;").trim(),
        "x"
    );
    assert_eq!(
        query_text(&db, "SELECT tag FROM notes WHERE id = 2;").trim(),
        "c"
    );
}

#[test]
fn the_ordinary_builtins_still_work_under_safe_and_readonly() {
    // The counterweight: `-safe` and `-readonly` must not break a normal call.
    if !have_sqlite3() {
        eprintln!("skipping: sqlite3 not on PATH");
        return;
    }
    let _l = ENV.lock().unwrap_or_else(|e| e.into_inner());
    let db = fixture("ordinary");
    let dbs = db.to_string_lossy().into_owned();

    let csv = match call(
        "db_sqlite_to_csv",
        vec![s(&dbs), s("SELECT id FROM victim")],
    ) {
        Ok(Value::Str(t)) => t,
        other => panic!("a plain SELECT to CSV must succeed: {other:?}"),
    };
    assert!(csv.contains("id") && csv.contains('1'), "csv was {csv:?}");

    call(
        "db_sqlite_update",
        vec![
            s(&dbs),
            s("victim"),
            rec(&[("id", Value::Int(2))]),
            s("id = 1"),
        ],
    )
    .expect("a plain update must succeed");
    assert_eq!(query_text(&db, "SELECT id FROM victim;").trim(), "2");

    call(
        "db_sqlite_create_table",
        vec![s(&dbs), s("fresh"), rec(&[("id", s("INTEGER"))])],
    )
    .expect("create_table with a record must succeed");
    call(
        "db_sqlite_create_table",
        vec![s(&dbs), s("raw"), s("a TEXT, b INTEGER")],
    )
    .expect("create_table with raw column text must succeed");
    assert!(query_text(&db, "SELECT name FROM sqlite_master WHERE name='raw';").contains("raw"));

    let bak = std::env::temp_dir().join("ae_sqli").join("ordinary.bak");
    let _ = std::fs::remove_file(&bak);
    call("db_sqlite_backup", vec![s(&dbs), s(&bak.to_string_lossy())])
        .expect("a backup must succeed");
    assert_eq!(
        query_text(&bak, "SELECT id FROM victim;").trim(),
        "2",
        "the backup must be a copy of the database as it was"
    );

    match call("db_sqlite_dump", vec![s(&dbs)]) {
        Ok(Value::Str(t)) => assert!(t.contains("CREATE TABLE victim"), "dump was {t:?}"),
        other => panic!("dump must succeed: {other:?}"),
    }
}

#[test]
fn reject_sql_batch_refuses_a_separator_outside_quotes_and_comments() {
    use iron_shell::safety::reject_sql_batch;
    for frag in [
        "1=1; DROP TABLE victim",
        "1=1 -- note\n; DROP TABLE victim",
        "a = 'x' ; DROP TABLE victim",
        "a = 'it''s' ; DROP TABLE victim",
        "a = \"q\" ; DROP TABLE victim",
        "a = `q` ; DROP TABLE victim",
        "a = [q] ; DROP TABLE victim",
        "a = 1 /* c */ ; DROP TABLE victim",
        "a = 1 ;",
    ] {
        let e = reject_sql_batch("t", frag).expect_err(frag);
        assert!(
            format!("{e:#}").contains("E_BAD_ARG"),
            "{frag:?} must be E_BAD_ARG: {e:#}"
        );
    }
}

#[test]
fn reject_sql_batch_accepts_a_separator_inside_a_literal_identifier_or_comment() {
    use iron_shell::safety::reject_sql_batch;
    for frag in [
        "tag = 'a;b'",
        "tag = 'it''s;ok'",
        "\"a;b\" = 1",
        "[a;b] = 1",
        "`a;b` = 1",
        "a = 1 -- ; not a separator\n",
        "a = 1 /* ; */",
        "a = 'x' -- '\n",
        "a = '--;' AND b = 1",
        "a = 1 /* it's */ AND b = 2",
    ] {
        assert!(
            reject_sql_batch("t", frag).is_ok(),
            "{frag:?} has no statement separator and must be accepted"
        );
    }
}