Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

12.6 KB
Raw Blame
"""A failed ``PRAGMA rekey`` must not carry the derived master key outwards.

``set_sqlcipher_rekey`` builds ``PRAGMA rekey = "x'<derived key hex>'"`` and
runs it through SQLAlchemy. SQLAlchemy wraps any DBAPI failure in a
``StatementError``, whose ``_sql_message`` appends ``"[SQL: %s]" %
self.statement`` -- so the raw error's ``str()``, its ``.statement`` attribute
and its rendered traceback each contain the derived SQLCipher master key in
hex. The only caller, ``DatabaseManager.change_password``, logs
``redact_secrets(str(e), old_password, new_password)``, and ``redact_secrets``
is a literal replace over just those two plaintext passwords -- it does not
know the key. Without scrubbing at the raise site, a failing rekey puts the
key that decrypts the user's whole database into the application log.

Subject under test is the real ``set_sqlcipher_rekey`` with a real derived key
and (in AA1) real SQLAlchemy error wrapping over real sqlite3; only the
connection's failure is arranged.

Revert that must turn these red: in
``src/local_deep_research/database/sqlcipher_utils.py::set_sqlcipher_rekey``,
drop the ``failure`` capture and the ``raise SQLCipherRekeyError(...) from
None`` and restore the bare

    try:
        cursor_or_conn.execute(text(safe_sql))
    except TypeError:
        cursor_or_conn.execute(safe_sql)

AA1 and AA2 then fail on ``str(exc)`` (and AA1 also on ``.statement``).
AA0 is the positive control: it asserts the arranged collaborator error
really does carry the key, so AA1/AA2 cannot pass by testing nothing.
"""

import sqlite3
import traceback

import pytest
from sqlalchemy import create_engine, event, exc as sa_exc, text
from sqlalchemy.sql.elements import TextClause

from local_deep_research.database.sqlcipher_utils import (
    SQLCipherRekeyError,
    get_key_from_password,
    set_sqlcipher_rekey,
)

NEW_PASSWORD = "rekey-target-password"  # noqa: S105 — test fixture value


@pytest.fixture(autouse=True)
def fast_kdf(monkeypatch):
    """Sanctioned test-mode KDF knob, as in the rest of this battery.

    Key derivation must still be the real one (the hex we assert on is the
    real derived key), just cheap.
    """
    monkeypatch.setenv("LDR_DB_CONFIG_KDF_ITERATIONS", "1000")


@pytest.fixture
def key_hex(fast_kdf):
    """The hex the production code will embed in the rekey statement.

    Depends on ``fast_kdf`` explicitly rather than relying on autouse
    ordering: derived at a different iteration count this would not be the
    hex the subject embeds, and every assertion would pass vacuously.

    ``db_path=None`` selects the legacy salt, so this is reproducible without
    a database file -- and it is derived by the same function the subject
    calls, not hard-coded.
    """
    return get_key_from_password(
        NEW_PASSWORD, db_path=None
    ).hex()  # gitleaks:allow


def _leak_surfaces(exception):
    """Every surface of the propagated exception itself: its string forms,
    ``.statement``, rendered traceback and chained-exception attributes.

    Scoped to the exception object. The frame locals reachable from
    ``exception.__traceback__``, which a loguru sink with ``diagnose=True``
    would render, are the separate surface AA4 covers (#6444).
    """
    return {
        "str(exc)": str(exception),
        "repr(exc)": repr(exception),
        "statement": str(getattr(exception, "statement", "")),
        "traceback": "".join(
            traceback.format_exception(
                type(exception), exception, exception.__traceback__
            )
        ),
        "__cause__": ""
        if exception.__cause__ is None
        else str(exception.__cause__),
        "__context__": (
            "" if exception.__context__ is None else str(exception.__context__)
        ),
    }


def _assert_no_key(exception, key_hex):
    leaked = {
        name: value
        for name, value in _leak_surfaces(exception).items()
        if key_hex in value
    }
    assert not leaked, (
        f"derived SQLCipher key reachable from the propagated exception via "
        f"{sorted(leaked)}"
    )


def _failing_sqlalchemy_connection():
    """A real SQLAlchemy connection whose statement execution fails.

    The ``do_execute`` hook raises a real ``sqlite3.OperationalError``, so
    SQLAlchemy performs its own real wrapping into a ``StatementError``
    subclass with ``.statement`` populated -- the exact shape a live
    SQLCipher rekey failure produces.
    """
    engine = create_engine("sqlite://")

    @event.listens_for(engine, "do_execute")
    def _raise(cursor, statement, parameters, context):  # noqa: ARG001
        raise sqlite3.OperationalError("file is not a database")

    return engine


def test_aa0_control_sqlalchemy_wrapping_does_embed_the_key(key_hex):
    """AA0 (positive control): the collaborator error really carries the key.

    Executes the same statement shape directly, without the subject, and
    asserts SQLAlchemy's wrapped error exposes the hex on both ``str()`` and
    ``.statement``. If this ever stops holding, AA1/AA2 have become vacuous
    and must be re-derived rather than trusted.
    """
    engine = _failing_sqlalchemy_connection()
    statement = f"PRAGMA rekey = \"x'{key_hex}'\""  # gitleaks:allow

    with engine.connect() as conn:
        with pytest.raises(sa_exc.StatementError) as caught:
            conn.execute(text(statement))

    raw = caught.value
    assert key_hex in str(raw), (
        "control: SQLAlchemy no longer inlines [SQL: ...]"
    )
    assert key_hex in str(raw.statement), "control: .statement no longer set"


def test_aa1_failed_rekey_via_sqlalchemy_does_not_expose_key(key_hex):
    """AA1: real SQLAlchemy wrapping -- no surface of the raised error
    references the derived key, and the chain is cut."""
    engine = _failing_sqlalchemy_connection()

    with engine.connect() as conn:
        with pytest.raises(SQLCipherRekeyError) as caught:
            set_sqlcipher_rekey(conn, NEW_PASSWORD, db_path=None)

    raised = caught.value
    _assert_no_key(raised, key_hex)
    assert raised.__cause__ is None
    assert raised.__context__ is None, (
        "implicit context still references the StatementError carrying the key"
    )
    assert "OperationalError" in str(raised), (
        "the failing error's type is the one diagnostic worth keeping"
    )


def test_aa2_failed_rekey_on_raw_connection_does_not_expose_key(key_hex):
    """AA2: the raw-SQLCipher-connection branch (the ``except TypeError``
    fallback) is scrubbed too.

    The stub rejects a ``TextClause`` the way a raw sqlcipher3 connection
    does, then fails on the plain string -- and the failure it raises is a
    ``StatementError`` carrying the statement, so the key is genuinely
    present in the collaborator error this branch sees.
    """
    statements = []

    class RawConnection:
        def execute(self, statement):
            if isinstance(statement, TextClause):
                raise TypeError("raw connection expects str, not TextClause")
            statements.append(statement)
            raise sa_exc.StatementError(
                message="unable to open database file",
                statement=statement,
                params=None,
                orig=sqlite3.OperationalError("unable to open database file"),
            )

    with pytest.raises(SQLCipherRekeyError) as caught:
        set_sqlcipher_rekey(RawConnection(), NEW_PASSWORD, db_path=None)

    assert statements, "the raw branch was never reached"
    assert key_hex in statements[0], (
        "control: the statement handed to the raw connection must contain the key"
    )
    _assert_no_key(caught.value, key_hex)
    assert caught.value.__context__ is None


def test_aa3_successful_rekey_still_executes_the_pragma(key_hex):
    """AA3: the scrubbing wrapper did not change the success path -- the
    statement still reaches the connection, unchanged, and nothing raises."""
    executed = []

    class RecordingConnection:
        def execute(self, statement):
            executed.append(str(statement))

    set_sqlcipher_rekey(RecordingConnection(), NEW_PASSWORD, db_path=None)

    assert len(executed) == 1
    assert executed[0] == f"PRAGMA rekey = \"x'{key_hex}'\""  # gitleaks:allow


def _subject_frame_locals(exception):
    """Locals still bound in ``set_sqlcipher_rekey``'s own frame.

    That frame sits in the propagated exception's traceback, and a loguru
    sink with ``diagnose=True`` (``LDR_LOGURU_DIAGNOSE``) renders frame
    locals — so anything left bound here is as reachable as the exception's
    own attributes. Scoped to the subject's frame: a caller's frame is the
    caller's business, and this test's own frame holds ``key_hex`` by
    construction.
    """
    values = {}
    traceback_ = exception.__traceback__
    while traceback_ is not None:
        frame = traceback_.tb_frame
        if frame.f_code.co_name == "set_sqlcipher_rekey":
            values = {
                name: repr(value) for name, value in frame.f_locals.items()
            }
        traceback_ = traceback_.tb_next
    return values


def test_aa4_raising_frame_keeps_neither_the_key_nor_the_statement(key_hex):
    """AA4: the derived key is gone from the raising frame's locals (#6444).

    ``key``/``safe_sql`` used to stay bound, and the captured exception stayed
    with them — pinning its own traceback, through SQLAlchemy frames carrying
    the statement, for as long as the new error propagated.
    """
    engine = _failing_sqlalchemy_connection()

    with engine.connect() as conn:
        with pytest.raises(SQLCipherRekeyError) as caught:
            set_sqlcipher_rekey(conn, NEW_PASSWORD, db_path=None)

    frame_locals = _subject_frame_locals(caught.value)
    assert frame_locals, "the subject's frame is no longer in the traceback"
    still_bound = {"key", "key_hex", "safe_sql"} & set(frame_locals)
    assert not still_bound, (
        f"key material still bound in the raising frame: {sorted(still_bound)}"
    )
    leaked = {
        name: value for name, value in frame_locals.items() if key_hex in value
    }
    assert not leaked, (
        f"derived key still bound in the raising frame: {sorted(leaked)}"
    )


def test_aa5_raw_connection_failures_stay_distinguishable(key_hex):
    """AA5: the DBAPI message survives on the raw path, scrubbed (#6444).

    On the raw ``sqlcipher3`` connection the message never contained the
    statement, so reducing every failure to its type name turned ``database
    is locked``, ``file is not a database`` and a disk-full error into the
    same line in the change-password log.
    """
    messages = {}
    for dbapi_message in (
        "database is locked",
        "file is not a database",
        "disk I/O error",
    ):

        class RawConnection:
            def __init__(self, message):
                self.message = message

            def execute(self, statement):
                if isinstance(statement, TextClause):
                    raise TypeError(
                        "raw connection expects str, not TextClause"
                    )
                raise sqlite3.OperationalError(self.message)

        with pytest.raises(SQLCipherRekeyError) as caught:
            set_sqlcipher_rekey(
                RawConnection(dbapi_message), NEW_PASSWORD, db_path=None
            )

        messages[dbapi_message] = str(caught.value)
        _assert_no_key(caught.value, key_hex)

    for dbapi_message, raised in messages.items():
        assert dbapi_message in raised, (
            f"{dbapi_message!r} no longer reaches the caller"
        )
        assert "OperationalError" in raised
    assert len(set(messages.values())) == len(messages), (
        "the three failures are still indistinguishable"
    )


def test_aa6_a_truncated_key_in_the_message_drops_the_detail(key_hex):
    """AA6: scrubbing fails closed on a fragment it cannot replace (#6444).

    ``redact_secrets`` replaces literal substrings. A renderer that ellipsised
    the hex mid-run would leave a fragment no literal replace can catch, so
    the detail is dropped wholesale rather than emitted in part.
    """
    fragment = key_hex[:24]

    class RawConnection:
        def execute(self, statement):
            if isinstance(statement, TextClause):
                raise TypeError("raw connection expects str, not TextClause")
            raise sqlite3.OperationalError(
                f'near "x\'{fragment}...": syntax error'
            )

    with pytest.raises(SQLCipherRekeyError) as caught:
        set_sqlcipher_rekey(RawConnection(), NEW_PASSWORD, db_path=None)

    raised = str(caught.value)
    assert fragment not in raised
    assert raised == "PRAGMA rekey failed (OperationalError)", (
        "a message carrying a key fragment must be dropped, not partially kept"
    )