~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.
Python academiaanthropicarxivbravedeep-researchencryptionhome-automationhomeserverlocallocal-deep-researchlocal-llmmistralollamaopenaipubmedresearchresearch-toolretrieval-augmented-generationsearxngself-hosted
"""
SQLCipher utility functions for consistent database operations.
This module centralizes all SQLCipher-specific operations to ensure
consistent password handling and PRAGMA settings across the codebase.
"""
import os
import re
import secrets
import sqlite3
import threading
import time
from hashlib import pbkdf2_hmac
from pathlib import Path
from typing import Any, Callable, Dict, Optional, Tuple, Type, TypeVar, Union
from loguru import logger
from ..security.log_sanitizer import redact_secrets
from ..settings.env_registry import get_env_setting
from ..utilities.type_utils import to_bool
# Lock to protect cipher_default_* global state during creation
_cipher_default_lock = threading.Lock()
# Salt file constants
SALT_FILE_SUFFIX = ".salt"
SALT_SIZE = 32 # 256 bits
def get_salt_file_path(db_path: Union[str, Path]) -> Path:
"""
Get the path to the salt file for a database.
Args:
db_path: Path to the database file
Returns:
Path to the corresponding .salt file
"""
return Path(db_path).with_suffix(Path(db_path).suffix + SALT_FILE_SUFFIX)
def get_salt_for_database(db_path: Union[str, Path]) -> bytes:
"""
Get the salt for a database file.
For new databases (v2+): reads from the .salt file alongside the database.
For legacy databases (v1): returns LEGACY_PBKDF2_SALT for backwards compatibility.
A salt file with a CR byte inserted before every LF byte, as a Windows
text-mode write produces (see ``create_database_salt``), is read back
as the original salt, and a warning names the file. The file is not
rewritten: this function never writes to disk.
Args:
db_path: Path to the database file
Returns:
The salt bytes to use for key derivation
Raises:
ValueError: If the salt file has any other unexpected size.
"""
salt_file = get_salt_file_path(db_path)
try:
salt = salt_file.read_bytes()
except FileNotFoundError:
# v1: Legacy salt for backwards compatibility
logger.warning(
f"Database '{Path(db_path).name}' uses the legacy shared salt "
f"(deprecated). Consider creating a new database to benefit from "
f"per-database salt security. See issue #1439 for migration details."
)
return LEGACY_PBKDF2_SALT
# v2: Per-database random salt
if len(salt) != SALT_SIZE:
# Undo Windows text-mode newline translation. A text-mode write
# stores a CR (0x0D) before every LF (0x0A) and changes no other
# byte. So every LF in a damaged file is an original salt byte, and
# the byte just before it is the CR that the write inserted.
# Replacing each CR LF pair with LF removes exactly those CRs: two
# pairs never share a byte, since no byte is both CR and LF, and an
# original CR stays, even one just before an original LF (stored as
# CR CR LF). The round-trip check accepts the file only if it is
# exactly what a text-mode write of the recovered salt produces.
# This runs on every platform, since a data directory can be
# copied from one system to another.
recovered = salt.replace(b"\r\n", b"\n")
if (
len(recovered) != SALT_SIZE
or recovered.replace(b"\n", b"\r\n") != salt
):
raise ValueError(
f"Salt file {salt_file} has unexpected size ({len(salt)} bytes), "
f"expected {SALT_SIZE}. The salt file may be corrupted."
)
logger.warning(
f"Salt file {salt_file} has a CR byte inserted before each LF "
f"byte (Windows text-mode newline translation). Using the "
f"recovered {SALT_SIZE}-byte salt; the file is left unchanged."
)
return recovered
return salt
def create_database_salt(db_path: Union[str, Path]) -> bytes:
"""
Create and store a new random salt for a database.
This should be called when creating a new database.
The salt is stored in a .salt file alongside the database.
WARNING: If this salt file is deleted, the associated database becomes
permanently unreadable. Always back up .salt files alongside their .db files.
Args:
db_path: Path to the database file
Returns:
The newly generated salt bytes
Raises:
FileExistsError: If a salt file already exists for this database
"""
salt_file = get_salt_file_path(db_path)
if salt_file.exists():
raise FileExistsError(
f"Salt file already exists: {salt_file}. "
f"Refusing to overwrite to prevent data loss."
)
salt = secrets.token_bytes(SALT_SIZE)
# Ensure parent directory exists.
# Lazy import: this runs during early database bootstrap, so avoid a
# top-level security -> ... -> database import cycle.
from ..security.directory_creation import create_directory
create_directory(salt_file.parent, context="database salt file directory")
# Write salt file with owner-only permissions (0o600). O_BINARY exists
# only on Windows, where os.open() otherwise gives a text-mode
# descriptor and os.write() stores a CR before every LF byte, so about
# 12% of salts would be saved with extra bytes.
flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL | getattr(os, "O_BINARY", 0)
fd = os.open(str(salt_file), flags, 0o600)
try:
os.write(fd, salt)
finally:
os.close(fd)
logger.info(f"Created new database salt file: {salt_file}")
return salt
def has_per_database_salt(db_path: Union[str, Path]) -> bool:
"""
Check if a database has a per-database salt file (v2).
Args:
db_path: Path to the database file
Returns:
True if the database has a .salt file, False otherwise
"""
return get_salt_file_path(db_path).exists()
def _get_key_from_password(
password: str, salt: bytes, kdf_iterations: int
) -> bytes:
"""
Generates an encryption key from the user's password and salt.
Args:
password: The password.
salt: The salt bytes to use for key derivation.
kdf_iterations: Number of PBKDF2 iterations.
Returns:
The generated key.
"""
logger.debug(
f"Generating DB encryption key with {kdf_iterations} iterations..."
)
start = time.perf_counter()
key = pbkdf2_hmac(
"sha512",
password.encode(),
salt,
kdf_iterations,
)
elapsed_ms = (time.perf_counter() - start) * 1000
if elapsed_ms > 500:
logger.info(
f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
f"({kdf_iterations} iterations)"
)
else:
logger.debug(
f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
f"({kdf_iterations} iterations)"
)
return key
def get_key_from_password(
password: str, db_path: Optional[Union[str, Path]] = None
) -> bytes:
"""
Wrapper that gets salt and settings, then calls the key derivation.
Args:
password: The password.
db_path: Optional path to the database file. If provided, uses
per-database salt. If not provided, uses legacy salt.
Returns:
The derived encryption key bytes.
"""
if db_path is not None:
salt = get_salt_for_database(db_path)
else:
salt = LEGACY_PBKDF2_SALT
settings = get_sqlcipher_settings()
return _get_key_from_password(password, salt, settings["kdf_iterations"])
def set_sqlcipher_key(
cursor_or_conn: Any,
password: str,
db_path: Optional[Union[str, Path]] = None,
) -> None:
"""
Set the SQLCipher encryption key using hexadecimal encoding.
This avoids SQL injection and escaping issues with special characters.
Args:
cursor_or_conn: SQLCipher cursor or connection object
password: The password to use for encryption
db_path: Optional path to the database file. If provided, uses
per-database salt. If not provided, uses legacy salt.
"""
key = get_key_from_password(password, db_path=db_path) # gitleaks:allow
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA key cannot be bound; key.hex() is [0-9a-f] only
cursor_or_conn.execute(f"PRAGMA key = \"x'{key.hex()}'\"")
def set_sqlcipher_key_from_hex(cursor_or_conn: Any, hex_key: str) -> None:
"""
Set the SQLCipher encryption key from a pre-derived hex key string.
Used by connection closures to avoid capturing plaintext passwords.
Args:
cursor_or_conn: SQLCipher cursor or connection object
hex_key: Pre-derived hex key string (from get_key_from_password().hex())
"""
if (
not isinstance(hex_key, str)
or len(hex_key) % 2 != 0
or re.fullmatch(r"[0-9a-fA-F]+", hex_key) is None
):
raise ValueError("hex_key must be non-empty even-length ASCII hex")
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA key cannot be bound; hex_key is validated as non-empty even-length ASCII hex above
cursor_or_conn.execute(f"PRAGMA key = \"x'{hex_key}'\"") # gitleaks:allow
class SQLCipherRekeyError(RuntimeError):
"""Raised when ``PRAGMA rekey`` fails.
Deliberately carries no detail beyond the failing exception's type: the
underlying error text embeds the rekey statement, which embeds the
derived master key in hex.
"""
# Shortest run of key hex treated as a leak by :func:`_rekey_failure_detail`.
# 16 hex characters is 8 bytes of the 32-byte master key -- far too little to
# be useful to an attacker, and far more than any coincidental hex run in a
# SQLite error message ("database disk image is malformed", a page number, a
# file path). Well under the length at which a renderer would ellipsise.
_KEY_FRAGMENT_LENGTH = 16
def _contains_key_fragment(message: str, key_hex: str) -> bool:
"""Whether *message* still shows any run of the key, not just the whole."""
return any(
key_hex[start : start + _KEY_FRAGMENT_LENGTH] in message
for start in range(len(key_hex) - _KEY_FRAGMENT_LENGTH + 1)
)
def _rekey_failure_detail(
exc: BaseException, key_hex: str, statement: str
) -> str:
"""The failure's own message with the derived key scrubbed out of it.
The type name alone cannot tell ``database is locked`` from ``file is not
a database`` or a disk-full error, and on the raw ``sqlcipher3``
connection path the DBAPI message never contained the statement in the
first place -- so there is a real diagnostic here worth keeping, once the
key is gone.
``redact_secrets`` replaces literal substrings, which covers the
SQLAlchemy path (``_sql_message`` appends the statement verbatim). It
cannot cover a renderer that truncated the hex mid-run, so this fails
closed: if any fragment of the key survives, the whole detail is dropped
and the caller is left with the type name.
"""
detail = redact_secrets(str(exc), statement, key_hex)
if _contains_key_fragment(detail, key_hex):
return ""
return detail
def set_sqlcipher_rekey(
cursor_or_conn: Any,
new_password: str,
db_path: Optional[Union[str, Path]] = None,
) -> None:
"""
Change the SQLCipher encryption key using hexadecimal encoding.
Uses the same PBKDF2 key derivation as set_sqlcipher_key() to ensure
consistency when re-opening databases after password change.
Returning normally does NOT prove the key changed: SQLCipher's
``PRAGMA rekey`` ignores the result of the underlying rekey and answers
``ok`` without raising when, for example, another connection holds a
write transaction past the busy timeout, leaving the file under the old
key. Callers must prove the new key by opening the database with it
(``DatabaseManager.change_password`` does).
Args:
cursor_or_conn: SQLCipher cursor or connection object
new_password: The new password to use for encryption
db_path: Optional path to the database file. If provided, uses
per-database salt. If not provided, uses legacy salt.
"""
# Use the same key derivation as set_sqlcipher_key for consistency
key = get_key_from_password(new_password, db_path=db_path) # gitleaks:allow
key_hex = key.hex()
del key
# The hex encoding already prevents injection since it only contains [0-9a-f]
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA rekey cannot be bound; key.hex() is [0-9a-f] only
safe_sql = f"PRAGMA rekey = \"x'{key_hex}'\""
# Any failure here must not carry ``safe_sql`` outwards. SQLAlchemy wraps
# DBAPI errors in ``StatementError``, whose ``_sql_message`` appends
# ``"[SQL: %s]" % self.statement`` -- so the raw exception's ``str()``,
# its ``.statement`` attribute and its rendered traceback all contain the
# derived master key in hex. ``change_password``'s handler only redacts
# the two plaintext passwords, so an unwrapped error would put the key in
# the log. Capture the failure, then raise a key-free error *outside* the
# ``except`` block: ``from None`` clears ``__cause__`` and raising after
# the handler has exited leaves ``__context__`` unset, so no attribute of
# the propagated exception references the key. The raising frame's own
# locals are a separate surface, and are dropped further down.
#
# Keep the failure's *name* and its scrubbed message rather than the
# exception object: holding ``exc`` past the ``except`` block would pin
# its own traceback -- through SQLAlchemy frames that carry the statement
# -- to this frame for as long as the new error propagates.
failure_type: Optional[str] = None
failure_detail = ""
try:
try:
# Try SQLAlchemy connection (needs text() wrapper)
from sqlalchemy import text
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: safe_sql interpolates only the derived key bytes.hex output into PRAGMA rekey
cursor_or_conn.execute(text(safe_sql))
except TypeError:
# Raw SQLCipher connection - use string directly
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: safe_sql interpolates only the derived key bytes.hex output into PRAGMA rekey
cursor_or_conn.execute(safe_sql)
except Exception as exc: # noqa: BLE001 - re-raised below as a key-free error
failure_type = type(exc).__name__
failure_detail = _rekey_failure_detail(exc, key_hex, safe_sql)
# This frame stays in the propagated exception's traceback, so a sink that
# renders frame locals (loguru ``diagnose=True``) would print whatever is
# still bound here. ``exc`` is already gone -- Python unbinds it at the end
# of the ``except`` block -- so the key and the statement are what is left.
# ``new_password`` stays: the caller's own frame holds it either way, so
# dropping it here would suggest a protection that is not there.
del key_hex, safe_sql
if failure_type is not None:
message = f"PRAGMA rekey failed ({failure_type})"
if failure_detail:
message = f"{message}: {failure_detail}"
raise SQLCipherRekeyError(message) from None
# Default SQLCipher configuration (can be overridden by settings)
DEFAULT_KDF_ITERATIONS = 256000
DEFAULT_PAGE_SIZE = 16384 # 16KB pages for maximum performance with caching
DEFAULT_HMAC_ALGORITHM = "HMAC_SHA512"
DEFAULT_KDF_ALGORITHM = "PBKDF2_HMAC_SHA512"
# How long a connection waits on another connection's lock before SQLITE_BUSY.
# Applied by apply_performance_pragmas, and before key verification wherever a
# busy verification read would otherwise be mistaken for a wrong key. This is
# the default of the operator-tunable ``db_config.busy_timeout_ms`` setting;
# read the effective value through ``get_sqlcipher_busy_timeout_ms()``.
SQLCIPHER_BUSY_TIMEOUT_MS = 30000
# Fixed statement template; only an ``int`` is ever formatted into it.
_BUSY_TIMEOUT_PRAGMA = "PRAGMA busy_timeout = {:d}"
def get_sqlcipher_busy_timeout_ms() -> int:
"""Return the effective ``busy_timeout`` in milliseconds.
Honours ``db_config.busy_timeout_ms`` so the pre-verification wait and
the per-connection performance pragma always agree.
"""
return int(
get_env_setting("db_config.busy_timeout_ms", SQLCIPHER_BUSY_TIMEOUT_MS)
)
def sqlcipher_busy_timeout_pragma() -> str:
"""Return the ``PRAGMA busy_timeout`` statement for the effective value.
PRAGMA takes no bind parameters, so the value is formatted into the
statement. It is coerced with ``int()`` here (on top of
``get_sqlcipher_busy_timeout_ms``), and the template's ``{:d}`` format
spec refuses anything that is not an integer, so the statement can
only ever carry an integer literal.
"""
timeout_ms = int(get_sqlcipher_busy_timeout_ms())
return _BUSY_TIMEOUT_PRAGMA.format(timeout_ms)
# Valid page sizes (powers of 2 within the SQLite range).
# IntegerSetting validates min/max but not that the value is a power of 2,
# so we check against this set as an additional safeguard.
VALID_PAGE_SIZES = frozenset({512, 1024, 2048, 4096, 8192, 16384, 32768, 65536})
MAX_KDF_ITERATIONS = 1_000_000
# Production minimum KDF iterations. Relaxed automatically in test/CI environments.
MIN_KDF_ITERATIONS_PRODUCTION = 100_000
MIN_KDF_ITERATIONS_TESTING = 1
def _get_min_kdf_iterations() -> int:
"""Get minimum KDF iterations, relaxed for test/CI environments.
Only relaxes when PYTEST_CURRENT_TEST (set automatically by pytest) or
LDR_TEST_MODE (project-specific) is set. Generic env vars like CI or
TESTING are NOT checked to avoid accidentally weakening production
encryption in Docker/CD pipelines that set CI=true.
PYTEST_CURRENT_TEST is presence-based: pytest sets it to a descriptive
non-boolean string (e.g. "tests/foo.py::test_bar (call)"), so its mere
presence signals a test run. LDR_TEST_MODE is parsed as a proper boolean
so an explicit LDR_TEST_MODE=0 / =false does NOT relax the floor — a bare
truthiness check would treat any non-empty string (including "false") as
enabled and silently weaken encryption for someone trying to disable it.
"""
is_testing = bool(os.environ.get("PYTEST_CURRENT_TEST")) or to_bool(
os.environ.get("LDR_TEST_MODE")
)
return (
MIN_KDF_ITERATIONS_TESTING
if is_testing
else MIN_KDF_ITERATIONS_PRODUCTION
)
# Legacy salt for backwards compatibility with databases created before v2.
# New databases use per-database random salts stored in .salt files.
# WARNING: Do NOT change this value - it would break all existing legacy databases!
LEGACY_PBKDF2_SALT = b"no salt"
# Alias for backwards compatibility with code that references the old name
PBKDF2_PLACEHOLDER_SALT = LEGACY_PBKDF2_SALT
def get_sqlcipher_settings() -> dict:
"""
Get SQLCipher settings from environment variables or use defaults.
These settings cannot be changed after database creation, so they
must be configured via environment variables only.
Settings are read via the env settings registry, which handles
canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
to deprecated names (LDR_DB_*) and deprecation warnings.
Returns:
Dictionary with SQLCipher configuration
"""
# HMAC algorithm - registry validates against allowed values
hmac_algorithm = get_env_setting(
"db_config.hmac_algorithm", DEFAULT_HMAC_ALGORITHM
)
# KDF algorithm - registry validates against allowed values
kdf_algorithm = get_env_setting(
"db_config.kdf_algorithm", DEFAULT_KDF_ALGORITHM
)
# Page size - registry validates range, we also check power-of-2
page_size = get_env_setting("db_config.page_size", DEFAULT_PAGE_SIZE)
if page_size not in VALID_PAGE_SIZES:
logger.warning(
f"Invalid page_size value '{page_size}', using default "
f"'{DEFAULT_PAGE_SIZE}'. Valid values: {sorted(VALID_PAGE_SIZES)}"
)
page_size = DEFAULT_PAGE_SIZE
# KDF iterations - registry validates basic range, then apply CI-aware minimum
kdf_iterations = get_env_setting(
"db_config.kdf_iterations", DEFAULT_KDF_ITERATIONS
)
min_kdf = _get_min_kdf_iterations()
if not (min_kdf <= kdf_iterations <= MAX_KDF_ITERATIONS):
logger.warning(
f"KDF iterations value '{kdf_iterations}' outside safe range "
f"[{min_kdf}, {MAX_KDF_ITERATIONS}], using default "
f"'{DEFAULT_KDF_ITERATIONS}'."
)
kdf_iterations = DEFAULT_KDF_ITERATIONS
return {
"kdf_iterations": kdf_iterations,
"page_size": page_size,
"hmac_algorithm": hmac_algorithm,
"kdf_algorithm": kdf_algorithm,
}
def warn_if_weak_kdf_with_existing_databases(
data_dir: Union[str, Path],
) -> bool:
"""Warn loudly when the effective SQLCipher KDF is below the production
floor *and* user databases already exist in ``data_dir``.
The KDF iteration count is derived from the environment at open time
(see :func:`get_sqlcipher_settings`) and is NOT stored with the
database. So if the floor is relaxed (test mode) on a deployment that
already holds real data, newly created databases get a weak at-rest work
factor, and — separately — any existing database that was created at a
*higher* KDF can no longer be opened: its on-disk key is unchanged, but
the server now derives a different, weaker key, so decryption fails and
login returns a generic "Invalid username or password" 401 that is
indistinguishable from a wrong password (the KDF-mismatch symptom class
behind PR #4775).
Scope: this only catches the "server is now weak" direction, and only a
*sub-floor* effective KDF. It cannot detect the inverse (a production-KDF
server opening databases created weak) or general in-range KDF drift,
because the creation-time KDF is not persisted to compare against — only
a sub-floor effective KDF is observable from the environment alone.
Intentionally silent on a fresh deployment (no pre-existing databases →
nothing to mismatch) and when the effective KDF is at/above the floor
(e.g. the 256000 default) — that is harmless.
Returns ``True`` if a warning was emitted (used by tests).
"""
effective_kdf = get_sqlcipher_settings()["kdf_iterations"]
if effective_kdf >= MIN_KDF_ITERATIONS_PRODUCTION:
return False
# Only count + truthiness are used, so no need to sort the glob result.
# nosemgrep: semgrep.rules.path-traversal-risk, reason: Startup diagnostic enumerates a fixed DB filename pattern under the operator-configured data directory
existing = list(Path(data_dir).glob("ldr_user_*.db"))
if not existing:
return False
logger.warning(
"SQLCipher KDF is configured to {} iterations — below the production "
"floor of {} (reached only in test mode, e.g. LDR_TEST_MODE) — while "
"{} user database(s) already exist in {}. New databases created now "
"get this weak at-rest work factor. Separately, any of those existing "
"databases that was created at a higher KDF can no longer be opened: "
"its on-disk key is unchanged, but the server now derives a different, "
"weaker key, so decryption fails and login returns a generic 'Invalid "
"username or password' 401 for every affected user (the KDF-mismatch "
"symptom class behind PR #4775). If this is a production deployment, "
"unset LDR_TEST_MODE (and any low LDR_DB_CONFIG_KDF_ITERATIONS).",
effective_kdf,
MIN_KDF_ITERATIONS_PRODUCTION,
len(existing),
data_dir,
)
return True
def apply_cipher_defaults_before_key(
cursor_or_conn: Any,
) -> None:
"""
Apply cipher_default_* pragmas BEFORE PRAGMA key for new database creation.
Per SQLCipher 4.x docs, cipher_default_* pragmas set the defaults that
apply when a key is set on a NEW database. These MUST be called before
PRAGMA key.
For EXISTING databases, cipher_page_size/cipher_hmac_algorithm/
cipher_kdf_algorithm are set AFTER the key via apply_sqlcipher_pragmas().
Args:
cursor_or_conn: SQLCipher cursor or connection object
"""
settings = get_sqlcipher_settings()
logger.debug(
f"Applying cipher_default_* pragmas for new DB: settings={settings}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; page_size is an int in VALID_PAGE_SIZES
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA page_size is an int checked against VALID_PAGE_SIZES
f"PRAGMA cipher_default_page_size = {settings['page_size']}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; hmac_algorithm is an EnumSetting allow-list value
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA HMAC algorithm comes from the EnumSetting allowlist
f"PRAGMA cipher_default_hmac_algorithm = {settings['hmac_algorithm']}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; kdf_algorithm is an EnumSetting allow-list value
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA KDF algorithm comes from the EnumSetting allowlist
f"PRAGMA cipher_default_kdf_algorithm = {settings['kdf_algorithm']}"
)
def apply_sqlcipher_pragmas(
cursor_or_conn: Any,
creation_mode: bool = False,
) -> None:
"""
Apply SQLCipher PRAGMA settings that are set AFTER the key.
For SQLCipher 4.x:
- New databases: cipher_default_* are set before key via
apply_cipher_defaults_before_key(). This function only sets kdf_iter.
- Existing databases: cipher_page_size, cipher_hmac_algorithm,
cipher_kdf_algorithm MUST be set AFTER the key (not before).
This function handles that.
Args:
cursor_or_conn: SQLCipher cursor or connection object
creation_mode: If True, only sets kdf_iter (defaults already applied).
If False, sets cipher_* settings + kdf_iter for existing DB.
"""
settings = get_sqlcipher_settings()
if not creation_mode:
# For existing databases: cipher_* pragmas go AFTER the key
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; page_size is an int in VALID_PAGE_SIZES
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA page_size is an int checked against VALID_PAGE_SIZES
f"PRAGMA cipher_page_size = {settings['page_size']}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; hmac_algorithm is an EnumSetting allow-list value
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA HMAC algorithm comes from the EnumSetting allowlist
f"PRAGMA cipher_hmac_algorithm = {settings['hmac_algorithm']}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; kdf_algorithm is an EnumSetting allow-list value
cursor_or_conn.execute(
# nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA KDF algorithm comes from the EnumSetting allowlist
f"PRAGMA cipher_kdf_algorithm = {settings['kdf_algorithm']}"
)
# kdf_iter can be set after the key (applies to future derivation)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; kdf_iterations is a range-checked IntegerSetting
cursor_or_conn.execute(f"PRAGMA kdf_iter = {settings['kdf_iterations']}")
# cipher_memory_security is a runtime PRAGMA. ON zeroes SQLCipher buffers
# and calls mlock() to prevent swap; OFF skips this. Defaulting to OFF
# because the password already sits unprotected in Flask session, db_manager,
# and thread-local storage — mlock on SQLCipher's buffers alone doesn't help.
# Users can opt in with LDR_DB_CONFIG_CIPHER_MEMORY_SECURITY=ON + IPC_LOCK.
# Applied on every connection (not just creation) so env var overrides work.
mem_security = get_env_setting("db_config.cipher_memory_security", "OFF")
# Defense-in-depth: the env registry's EnumSetting already restricts
# this to {ON, OFF}, but an f-string interpolation into PRAGMA is a
# SQL-injection primitive if that registry check is ever bypassed
# (e.g. a refactor that swaps `get_env_setting` for raw `os.getenv`
# in a future patch). Assert explicitly so the call site fails loud
# instead of becoming an injection vector.
if mem_security not in ("ON", "OFF"):
raise ValueError(
f"cipher_memory_security must be ON or OFF, got: {mem_security!r}"
)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value checked against ("ON"; "OFF") above
cursor_or_conn.execute(f"PRAGMA cipher_memory_security = {mem_security}")
def apply_performance_pragmas(cursor_or_conn: Any) -> None:
"""
Apply performance-related PRAGMA settings from environment variables.
Settings are read via the env settings registry, which handles
canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
to deprecated names (LDR_DB_*) and deprecation warnings.
Args:
cursor_or_conn: SQLCipher cursor or connection object
"""
# Default values that are always applied
cursor_or_conn.execute("PRAGMA temp_store = MEMORY")
# SQLite serialises all writes through a single file lock, so any
# in-flight transaction blocks every other writer until it commits
# or rolls back. ``busy_timeout`` is the wall-clock budget the
# SQLite driver waits for that lock before failing with
# ``SQLITE_BUSY`` ("database is locked"). The background document
# scheduler can hold a session across multi-second PDF downloads
# (it queries ResearchHistory, then iterates calling
# DownloadService.download_as_text per resource, then commits the
# ``last_run`` update); a 10 s budget used to fail the rate-limit
# tracker's persistence path -- and the scheduler's per-resource
# processing -- every time the rate-limit write landed inside the
# scheduler's transaction. 30 s gives those short writers enough
# headroom to land their commit instead of swallowing the error.
# Operators who want to tune this further can override
# ``db_config.busy_timeout_ms``; the lower bound (100 ms) keeps
# the negative-control contention test fast (it has to drive a
# writer-against-holder race to a real timeout) without being so
# tight that the spinner can't ride out even a single held
# transaction, and the upper bound (300 s) caps tail latency if a
# query goes pathological.
busy_timeout_sql = sqlcipher_busy_timeout_pragma()
cursor_or_conn.execute(busy_timeout_sql)
# SQLite defaults foreign_keys to OFF — without this every
# ondelete="CASCADE"/"SET NULL" declared on an FK is inert,
# including for raw-SQL DELETEs issued by Query.delete().
cursor_or_conn.execute("PRAGMA foreign_keys = ON")
# Cache size - registry validates min/max range. Force int before
# f-string interpolation so a non-numeric value surfaces as TypeError
# at this point rather than becoming an injection vector downstream.
cache_mb = int(get_env_setting("db_config.cache_size_mb", 64))
cache_pages = -(cache_mb * 1024) # Negative for KB cache size
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value derived from int() above
cursor_or_conn.execute(f"PRAGMA cache_size = {cache_pages}")
# Journal mode - registry validates against allowed values. Belt-and-
# braces: assert the allowlist explicitly at the f-string call site.
journal_mode = get_env_setting("db_config.journal_mode", "WAL")
if journal_mode not in (
"DELETE",
"TRUNCATE",
"PERSIST",
"MEMORY",
"WAL",
"OFF",
):
raise ValueError(f"Invalid journal_mode: {journal_mode!r}")
if journal_mode == "WAL":
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: journal_mode is one of the six literal modes checked above; PRAGMA mode cannot be bound
cursor_or_conn.execute(f"PRAGMA journal_mode = {journal_mode}")
else:
# Leaving WAL mode closes the WAL inside SQLite's pager. On SQLite
# 3.51.0/3.51.1 that takes the same inverted VFS mutexes as closing
# a WAL connection, so serialize it with guarded opens and closes.
from .sqlcipher_compat import wal_lifecycle_guard
with wal_lifecycle_guard():
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: journal_mode is one of the six literal modes checked above; PRAGMA mode cannot be bound
cursor_or_conn.execute(f"PRAGMA journal_mode = {journal_mode}")
# Synchronous mode - same defense-in-depth pattern.
sync_mode = get_env_setting("db_config.synchronous", "NORMAL")
if sync_mode not in ("OFF", "NORMAL", "FULL", "EXTRA"):
raise ValueError(f"Invalid synchronous mode: {sync_mode!r}")
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value checked against an allow-list above
cursor_or_conn.execute(f"PRAGMA synchronous = {sync_mode}")
# WAL autocheckpoint frame threshold. SQLite's default of 1000 frames
# paired with our 16 KB page size means the WAL can grow to ~16 MB
# before SQLite triggers a PASSIVE checkpoint at commit. Lowering the
# threshold bounds the WAL high-water-mark on disk for users who never
# log out (the explicit TRUNCATE checkpoint runs on dispose, not here).
wal_autocheckpoint = get_env_setting("db_config.wal_autocheckpoint", 250)
# nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; wal_autocheckpoint is a range-checked IntegerSetting
cursor_or_conn.execute(f"PRAGMA wal_autocheckpoint = {wal_autocheckpoint}")
# ---------------------------------------------------------------------------
# Transient-lock retry helper
# ---------------------------------------------------------------------------
#
# SQLite serialises all writes through a single file lock, and any
# writer that lands inside a long-running transaction has to wait for
# ``busy_timeout`` before either getting the lock or failing with
# ``SQLITE_BUSY`` ("database is locked"). ``apply_performance_pragmas``
# above bumps the default timeout from 10 s to 30 s, which gives a
# short writer (e.g. the rate-limit tracker's per-engine estimate
# persistence) enough headroom to land its commit during a long
# in-flight transaction like the document scheduler's PDF-download
# loop.
#
# ``busy_timeout`` only protects a single ``BEGIN IMMEDIATE`` /
# ``COMMIT`` pair -- if the holder runs longer than the timeout, the
# writer's first commit attempt still fails. The helper below catches
# that exact failure and retries the callable with exponential
# backoff, so the caller's outer ``except Exception`` log (the rate-limit
# tracker's "Failed to persist rate limit estimate") only fires when
# every retry is exhausted, not on the first transient lock contention.
_T = TypeVar("_T")
def _db_lock_error_types() -> Tuple[Type[BaseException], ...]:
"""Return the exception classes that signal ``database is locked``.
The stdlib ``sqlite3`` module and ``sqlcipher3`` have *separate*
exception hierarchies -- ``sqlcipher3.dbapi2.OperationalError``
does NOT inherit from ``sqlite3.OperationalError`` -- so a
defensive retry helper has to catch both. We also include
``sqlalchemy.exc.OperationalError`` because the production call
site (``rate_limit_tracker._persist_estimate``) commits through a
SQLAlchemy session, and SQLAlchemy wraps the DBAPI lock error in
its own ``OperationalError`` subclass that does NOT inherit from
either DBAPI class -- without it the helper would silently be a
no-op on the wrapped path and the retry would never fire.
``_is_database_busy`` then reads the DBAPI error the SQLAlchemy
subclass wraps, by its ``sqlite_errorcode`` when the driver sets one
and by its message otherwise, so only ``SQLITE_BUSY`` is retried.
"""
types: list[Type[BaseException]] = [sqlite3.OperationalError]
try:
from sqlcipher3 import dbapi2 as _sqlcipher_dbapi2
types.append(_sqlcipher_dbapi2.OperationalError)
except ImportError:
pass
try:
from sqlalchemy.exc import OperationalError as _SQLAOperationalError
types.append(_SQLAOperationalError)
except ImportError:
pass
return tuple(types)
_DB_LOCK_ERROR_TYPES = _db_lock_error_types()
def _is_database_busy(exc: BaseException) -> bool:
"""Whether ``exc`` is SQLite's busy error, ``database is locked``.
``str()`` of a SQLAlchemy ``OperationalError`` also renders the SQL
statement and its parameters, so the DBAPI error it wraps
(``exc.orig``) is read instead. When the driver sets
``sqlite_errorcode``, the code decides: ``SQLITE_BUSY`` or one of its
extended codes is busy, whatever the wording of the message. Without a
code, the message must be exactly ``database is locked``.
``database table is locked`` (SQLITE_LOCKED) is not a busy-timeout
condition and matches neither.
"""
original = getattr(exc, "orig", None) or exc
code = getattr(original, "sqlite_errorcode", None)
if isinstance(code, int) and not isinstance(code, bool):
return code & 0xFF == sqlite3.SQLITE_BUSY
return str(original).strip().lower() == "database is locked"
def retry_on_db_lock(
callable_: Callable[[], _T],
*,
attempts: int = 3,
base_delay_seconds: float = 0.1,
) -> _T:
"""Run ``callable_``, retrying on SQLite's busy error.
Only retries an ``OperationalError`` that ``_is_database_busy`` reads
as SQLITE_BUSY: by the DBAPI error's ``sqlite_errorcode`` when the
driver sets one (``SQLITE_BUSY`` or one of its extended codes), and
otherwise by a message of exactly ``database is locked``. Every other
exception (connection errors, IntegrityError, ProgrammingError, any
other ``OperationalError``, including SQLITE_LOCKED's ``database table
is locked`` and an error whose SQL or parameters happen to contain
``locked``) propagates immediately so the caller can decide how to
handle it.
The total wall-clock budget before the helper gives up is bounded
by ``attempts * busy_timeout + (attempts - 1) * base_delay_seconds *
(2**(attempts-1) - 1)``: each attempt can wait up to the connection's
``busy_timeout`` for the writer lock, and the helper itself sleeps
``base_delay_seconds * 2 ** (n - 1)`` between attempts. At the
defaults (3 attempts, 0.1 s base delay, 30 s busy_timeout) that's
at most ``3 * 30 + 0.1 + 0.2 = 90.3 s`` of waiting before the
terminal ``database is locked`` raises to the caller. That upper
bound is conservative: the connection's spinner waits the full
timeout only on the very last attempt, and earlier attempts usually
succeed while the holder's transaction is still in flight.
This helper does not open, close, or pool database connections
itself. ``callable_`` is responsible for its own connection
lifecycle (open the SQLAlchemy session, ``with metrics_writer.get_session(...)``,
etc.); on retry the helper simply calls ``callable_()`` again, so
a caller that opens a fresh connection per call naturally gets a
fresh attempt each time. Reusing a connection that already failed
with ``SQLITE_BUSY`` is fine -- ``busy_timeout`` is reapplied per
connection and the SQLite driver clears the busy state on the next
BEGIN -- but most callers already pool short-lived sessions and
don't need to special-case this.
Args:
callable_: The no-argument callable to run. Each attempt calls
it fresh -- any partial state must be inside the callable's
own context manager (e.g. a ``with metrics_writer.get_session(...)``).
attempts: Maximum number of attempts (including the first).
``1`` disables the retry behaviour.
base_delay_seconds: Initial backoff. The delay between
attempts is ``base_delay_seconds * 2 ** (attempt - 1)`` --
0.1 s, 0.2 s, 0.4 s by default.
Returns:
Whatever ``callable_`` returns on the first successful run.
Raises:
sqlite3.OperationalError / sqlcipher3.OperationalError /
sqlalchemy.exc.OperationalError: ``database is locked`` if
every attempt is exhausted. Other exceptions propagate
without retry (``_is_database_busy`` filters the SQLAlchemy
wrapper to lock errors specifically).
"""
for attempt in range(1, attempts + 1):
try:
return callable_()
except _DB_LOCK_ERROR_TYPES as exc:
if not _is_database_busy(exc) or attempt >= attempts:
raise
logger.debug(
"SQLite 'database is locked' on attempt "
f"{attempt}/{attempts}; retrying in "
f"{base_delay_seconds * (2 ** (attempt - 1)):.2f}s"
)
time.sleep(base_delay_seconds * (2 ** (attempt - 1)))
# Unreachable: the loop returns on success or raises on the final
# attempt. The explicit ``raise`` above satisfies the type checker.
raise RuntimeError( # pragma: no cover -- defensive
"retry_on_db_lock: loop exited without returning or raising"
)
# SQLite's answer when page 1 does not decrypt (SQLITE_NOTADB): with
# SQLCipher, a wrong key. Older builds word it the second way.
_WRONG_KEY_MESSAGES = (
"file is not a database",
"file is encrypted or is not a database",
)
_DBAPI_MODULE_PREFIXES = ("sqlcipher3", "sqlite3", "pysqlcipher3")
def _exception_chain(error: BaseException):
seen: set[int] = set()
pending: list[BaseException | None] = [error]
while pending:
current = pending.pop()
if current is None or id(current) in seen:
continue
seen.add(id(current))
yield current
pending.extend(
(
getattr(current, "orig", None),
current.__cause__,
current.__context__,
)
)
def is_wrong_key_error(error: BaseException) -> bool:
"""True when ``error`` (or one it wraps) is SQLite's wrong-key answer."""
return any(
any(message in str(item) for message in _WRONG_KEY_MESSAGES)
for item in _exception_chain(error)
)
def is_operational_sqlite_error(error: BaseException) -> bool:
"""True for a SQLite failure that says nothing about the key.
A driver ``OperationalError`` (database locked or busy past the busy
timeout, disk I/O error, unable to open the file) anywhere in the
chain, unless the chain also carries the wrong-key answer. Callers use
it to keep such a failure from being reported as a wrong password.
"""
if is_wrong_key_error(error):
return False
return any(
type(item).__name__ == "OperationalError"
and type(item).__module__.startswith(_DBAPI_MODULE_PREFIXES)
for item in _exception_chain(error)
)
def check_sqlcipher_key(cursor: Any) -> bool:
"""Run the key verification query; False only for a wrong key.
Unlike ``verify_sqlcipher_connection``, an operational failure
(``is_operational_sqlite_error``) propagates instead of reading as a
wrong key, so password-checking callers can answer it as retryable.
"""
try:
cursor.execute("SELECT 1")
result = cursor.fetchone()
except Exception as error:
if is_operational_sqlite_error(error):
raise
logger.warning(
f"SQLCipher key verification failed: {type(error).__name__}"
)
return False
return result == (1,)
def verify_sqlcipher_connection(cursor_or_conn: Any) -> bool:
"""
Verify that the SQLCipher connection is working correctly.
Args:
cursor_or_conn: SQLCipher cursor or connection object
Returns:
True if the connection is valid, False otherwise
"""
try:
cursor_or_conn.execute("SELECT 1")
result = (
cursor_or_conn.fetchone()
if hasattr(cursor_or_conn, "fetchone")
else cursor_or_conn.execute("SELECT 1").fetchone()
)
is_valid = result == (1,)
if not is_valid:
logger.error(
f"SQLCipher verification failed: result {result} != (1,)"
)
return is_valid
except Exception:
logger.exception("SQLCipher verification failed")
return False
def get_sqlcipher_version(cursor_or_conn: Any) -> Optional[str]:
"""
Get the SQLCipher version string.
Args:
cursor_or_conn: SQLCipher cursor or connection object
Returns:
Version string (e.g. "4.6.1 community") or None if unavailable
"""
try:
cursor_or_conn.execute("PRAGMA cipher_version")
result = cursor_or_conn.fetchone()
return result[0] if result else None
except Exception:
logger.debug("Could not query SQLCipher version", exc_info=True)
return None
def create_sqlcipher_connection(
db_path: Union[str, Path],
password: Optional[str] = None,
creation_mode: bool = False,
connect_kwargs: Optional[Dict[str, Any]] = None,
hex_key: Optional[str] = None,
) -> Any:
"""
Create a properly configured SQLCipher connection.
Implements the full PRAGMA sequence with proper error cleanup:
- Creation: cipher_default_* -> key -> kdf_iter -> performance -> verify
- Existing: key -> cipher_* + kdf_iter -> performance -> verify
Uses per-database salt if a .salt file exists alongside the database,
otherwise falls back to legacy salt for backwards compatibility.
Args:
db_path: Path to the database file
password: The password for encryption (mutually exclusive with hex_key)
creation_mode: If True, set cipher_default_* before key (new DB)
connect_kwargs: Extra kwargs passed to sqlcipher3.connect()
hex_key: Pre-derived hex key (skips PBKDF2 derivation)
Returns:
SQLCipher connection object
Raises:
ImportError: If sqlcipher3 is not available
ValueError: If the connection cannot be established
"""
from .sqlcipher_compat import connect_sqlcipher, get_sqlcipher_module
try:
sqlcipher3 = get_sqlcipher_module()
except ImportError:
raise ImportError(
"sqlcipher3 is not available for encrypted databases. "
"Ensure SQLCipher system library is installed, then run: pdm install"
)
conn = connect_sqlcipher(sqlcipher3, str(db_path), **(connect_kwargs or {}))
try:
cursor = conn.cursor()
if creation_mode:
with _cipher_default_lock:
apply_cipher_defaults_before_key(cursor)
# Set encryption key (uses per-database salt when password + db_path)
if hex_key:
set_sqlcipher_key_from_hex(cursor, hex_key)
elif password:
set_sqlcipher_key(cursor, password, db_path=db_path)
else:
raise ValueError("Either password or hex_key must be provided") # noqa: TRY301 — except does connection cleanup before re-raise
# Apply post-key pragmas (cipher_* for existing, kdf_iter for both)
apply_sqlcipher_pragmas(cursor, creation_mode=creation_mode)
# Apply performance settings
apply_performance_pragmas(cursor)
# Verify connection works (an operational failure propagates; see
# check_sqlcipher_key)
if not check_sqlcipher_key(cursor):
raise ValueError( # noqa: TRY301 — except does connection cleanup before re-raise
"Failed to establish encrypted database connection"
)
cursor.close()
return conn
except Exception:
from ..utilities.resource_utils import safe_close
safe_close(conn, "SQLCipher connection")
raise
# Backwards compatibility alias — old name still importable
apply_cipher_settings_before_key = apply_cipher_defaults_before_key