Coverage for src/local_deep_research/database/sqlcipher_utils.py: 97%
281 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-10-22 21:20 +0000
1"""
2SQLCipher utility functions for consistent database operations.
4This module centralizes all SQLCipher-specific operations to ensure
5consistent password handling and PRAGMA settings across the codebase.
6"""
8import os
9import re
10import secrets
11import sqlite3
12import threading
13import time
14from hashlib import pbkdf2_hmac
15from pathlib import Path
16from typing import Any, Callable, Dict, Optional, Tuple, Type, TypeVar, Union
18from loguru import logger
20from ..security.log_sanitizer import redact_secrets
21from ..settings.env_registry import get_env_setting
22from ..utilities.type_utils import to_bool
24# Lock to protect cipher_default_* global state during creation
25_cipher_default_lock = threading.Lock()
27# Salt file constants
28SALT_FILE_SUFFIX = ".salt"
29SALT_SIZE = 32 # 256 bits
32def get_salt_file_path(db_path: Union[str, Path]) -> Path:
33 """
34 Get the path to the salt file for a database.
36 Args:
37 db_path: Path to the database file
39 Returns:
40 Path to the corresponding .salt file
41 """
42 return Path(db_path).with_suffix(Path(db_path).suffix + SALT_FILE_SUFFIX)
45def get_salt_for_database(db_path: Union[str, Path]) -> bytes:
46 """
47 Get the salt for a database file.
49 For new databases (v2+): reads from the .salt file alongside the database.
50 For legacy databases (v1): returns LEGACY_PBKDF2_SALT for backwards compatibility.
52 A salt file with a CR byte inserted before every LF byte, as a Windows
53 text-mode write produces (see ``create_database_salt``), is read back
54 as the original salt, and a warning names the file. The file is not
55 rewritten: this function never writes to disk.
57 Args:
58 db_path: Path to the database file
60 Returns:
61 The salt bytes to use for key derivation
63 Raises:
64 ValueError: If the salt file has any other unexpected size.
65 """
66 salt_file = get_salt_file_path(db_path)
68 try:
69 salt = salt_file.read_bytes()
70 except FileNotFoundError:
71 # v1: Legacy salt for backwards compatibility
72 logger.warning(
73 f"Database '{Path(db_path).name}' uses the legacy shared salt "
74 f"(deprecated). Consider creating a new database to benefit from "
75 f"per-database salt security. See issue #1439 for migration details."
76 )
77 return LEGACY_PBKDF2_SALT
79 # v2: Per-database random salt
80 if len(salt) != SALT_SIZE:
81 # Undo Windows text-mode newline translation. A text-mode write
82 # stores a CR (0x0D) before every LF (0x0A) and changes no other
83 # byte. So every LF in a damaged file is an original salt byte, and
84 # the byte just before it is the CR that the write inserted.
85 # Replacing each CR LF pair with LF removes exactly those CRs: two
86 # pairs never share a byte, since no byte is both CR and LF, and an
87 # original CR stays, even one just before an original LF (stored as
88 # CR CR LF). The round-trip check accepts the file only if it is
89 # exactly what a text-mode write of the recovered salt produces.
90 # This runs on every platform, since a data directory can be
91 # copied from one system to another.
92 recovered = salt.replace(b"\r\n", b"\n")
93 if (
94 len(recovered) != SALT_SIZE
95 or recovered.replace(b"\n", b"\r\n") != salt
96 ):
97 raise ValueError(
98 f"Salt file {salt_file} has unexpected size ({len(salt)} bytes), "
99 f"expected {SALT_SIZE}. The salt file may be corrupted."
100 )
101 logger.warning(
102 f"Salt file {salt_file} has a CR byte inserted before each LF "
103 f"byte (Windows text-mode newline translation). Using the "
104 f"recovered {SALT_SIZE}-byte salt; the file is left unchanged."
105 )
106 return recovered
107 return salt
110def create_database_salt(db_path: Union[str, Path]) -> bytes:
111 """
112 Create and store a new random salt for a database.
114 This should be called when creating a new database.
115 The salt is stored in a .salt file alongside the database.
117 WARNING: If this salt file is deleted, the associated database becomes
118 permanently unreadable. Always back up .salt files alongside their .db files.
120 Args:
121 db_path: Path to the database file
123 Returns:
124 The newly generated salt bytes
126 Raises:
127 FileExistsError: If a salt file already exists for this database
128 """
129 salt_file = get_salt_file_path(db_path)
131 if salt_file.exists():
132 raise FileExistsError(
133 f"Salt file already exists: {salt_file}. "
134 f"Refusing to overwrite to prevent data loss."
135 )
137 salt = secrets.token_bytes(SALT_SIZE)
139 # Ensure parent directory exists.
140 # Lazy import: this runs during early database bootstrap, so avoid a
141 # top-level security -> ... -> database import cycle.
142 from ..security.directory_creation import create_directory
144 create_directory(salt_file.parent, context="database salt file directory")
146 # Write salt file with owner-only permissions (0o600). O_BINARY exists
147 # only on Windows, where os.open() otherwise gives a text-mode
148 # descriptor and os.write() stores a CR before every LF byte, so about
149 # 12% of salts would be saved with extra bytes.
150 flags = os.O_WRONLY | os.O_CREAT | os.O_EXCL | getattr(os, "O_BINARY", 0)
151 fd = os.open(str(salt_file), flags, 0o600)
152 try:
153 os.write(fd, salt)
154 finally:
155 os.close(fd)
157 logger.info(f"Created new database salt file: {salt_file}")
158 return salt
161def has_per_database_salt(db_path: Union[str, Path]) -> bool:
162 """
163 Check if a database has a per-database salt file (v2).
165 Args:
166 db_path: Path to the database file
168 Returns:
169 True if the database has a .salt file, False otherwise
170 """
171 return get_salt_file_path(db_path).exists()
174def _get_key_from_password(
175 password: str, salt: bytes, kdf_iterations: int
176) -> bytes:
177 """
178 Generates an encryption key from the user's password and salt.
180 Args:
181 password: The password.
182 salt: The salt bytes to use for key derivation.
183 kdf_iterations: Number of PBKDF2 iterations.
185 Returns:
186 The generated key.
187 """
188 logger.debug(
189 f"Generating DB encryption key with {kdf_iterations} iterations..."
190 )
192 start = time.perf_counter()
193 key = pbkdf2_hmac(
194 "sha512",
195 password.encode(),
196 salt,
197 kdf_iterations,
198 )
199 elapsed_ms = (time.perf_counter() - start) * 1000
201 if elapsed_ms > 500:
202 logger.info(
203 f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
204 f"({kdf_iterations} iterations)"
205 )
206 else:
207 logger.debug(
208 f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
209 f"({kdf_iterations} iterations)"
210 )
211 return key
214def get_key_from_password(
215 password: str, db_path: Optional[Union[str, Path]] = None
216) -> bytes:
217 """
218 Wrapper that gets salt and settings, then calls the key derivation.
220 Args:
221 password: The password.
222 db_path: Optional path to the database file. If provided, uses
223 per-database salt. If not provided, uses legacy salt.
225 Returns:
226 The derived encryption key bytes.
227 """
228 if db_path is not None:
229 salt = get_salt_for_database(db_path)
230 else:
231 salt = LEGACY_PBKDF2_SALT
233 settings = get_sqlcipher_settings()
234 return _get_key_from_password(password, salt, settings["kdf_iterations"])
237def set_sqlcipher_key(
238 cursor_or_conn: Any,
239 password: str,
240 db_path: Optional[Union[str, Path]] = None,
241) -> None:
242 """
243 Set the SQLCipher encryption key using hexadecimal encoding.
245 This avoids SQL injection and escaping issues with special characters.
247 Args:
248 cursor_or_conn: SQLCipher cursor or connection object
249 password: The password to use for encryption
250 db_path: Optional path to the database file. If provided, uses
251 per-database salt. If not provided, uses legacy salt.
252 """
253 key = get_key_from_password(password, db_path=db_path) # gitleaks:allow
254 # 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
255 cursor_or_conn.execute(f"PRAGMA key = \"x'{key.hex()}'\"")
258def set_sqlcipher_key_from_hex(cursor_or_conn: Any, hex_key: str) -> None:
259 """
260 Set the SQLCipher encryption key from a pre-derived hex key string.
262 Used by connection closures to avoid capturing plaintext passwords.
264 Args:
265 cursor_or_conn: SQLCipher cursor or connection object
266 hex_key: Pre-derived hex key string (from get_key_from_password().hex())
267 """
268 if (
269 not isinstance(hex_key, str)
270 or len(hex_key) % 2 != 0
271 or re.fullmatch(r"[0-9a-fA-F]+", hex_key) is None
272 ):
273 raise ValueError("hex_key must be non-empty even-length ASCII hex")
274 # 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
275 cursor_or_conn.execute(f"PRAGMA key = \"x'{hex_key}'\"") # gitleaks:allow
278class SQLCipherRekeyError(RuntimeError):
279 """Raised when ``PRAGMA rekey`` fails.
281 Deliberately carries no detail beyond the failing exception's type: the
282 underlying error text embeds the rekey statement, which embeds the
283 derived master key in hex.
284 """
287# Shortest run of key hex treated as a leak by :func:`_rekey_failure_detail`.
288# 16 hex characters is 8 bytes of the 32-byte master key -- far too little to
289# be useful to an attacker, and far more than any coincidental hex run in a
290# SQLite error message ("database disk image is malformed", a page number, a
291# file path). Well under the length at which a renderer would ellipsise.
292_KEY_FRAGMENT_LENGTH = 16
295def _contains_key_fragment(message: str, key_hex: str) -> bool:
296 """Whether *message* still shows any run of the key, not just the whole."""
297 return any(
298 key_hex[start : start + _KEY_FRAGMENT_LENGTH] in message
299 for start in range(len(key_hex) - _KEY_FRAGMENT_LENGTH + 1)
300 )
303def _rekey_failure_detail(
304 exc: BaseException, key_hex: str, statement: str
305) -> str:
306 """The failure's own message with the derived key scrubbed out of it.
308 The type name alone cannot tell ``database is locked`` from ``file is not
309 a database`` or a disk-full error, and on the raw ``sqlcipher3``
310 connection path the DBAPI message never contained the statement in the
311 first place -- so there is a real diagnostic here worth keeping, once the
312 key is gone.
314 ``redact_secrets`` replaces literal substrings, which covers the
315 SQLAlchemy path (``_sql_message`` appends the statement verbatim). It
316 cannot cover a renderer that truncated the hex mid-run, so this fails
317 closed: if any fragment of the key survives, the whole detail is dropped
318 and the caller is left with the type name.
319 """
320 detail = redact_secrets(str(exc), statement, key_hex)
321 if _contains_key_fragment(detail, key_hex):
322 return ""
323 return detail
326def set_sqlcipher_rekey(
327 cursor_or_conn: Any,
328 new_password: str,
329 db_path: Optional[Union[str, Path]] = None,
330) -> None:
331 """
332 Change the SQLCipher encryption key using hexadecimal encoding.
334 Uses the same PBKDF2 key derivation as set_sqlcipher_key() to ensure
335 consistency when re-opening databases after password change.
337 Returning normally does NOT prove the key changed: SQLCipher's
338 ``PRAGMA rekey`` ignores the result of the underlying rekey and answers
339 ``ok`` without raising when, for example, another connection holds a
340 write transaction past the busy timeout, leaving the file under the old
341 key. Callers must prove the new key by opening the database with it
342 (``DatabaseManager.change_password`` does).
344 Args:
345 cursor_or_conn: SQLCipher cursor or connection object
346 new_password: The new password to use for encryption
347 db_path: Optional path to the database file. If provided, uses
348 per-database salt. If not provided, uses legacy salt.
349 """
350 # Use the same key derivation as set_sqlcipher_key for consistency
351 key = get_key_from_password(new_password, db_path=db_path) # gitleaks:allow
352 key_hex = key.hex()
353 del key
355 # The hex encoding already prevents injection since it only contains [0-9a-f]
356 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA rekey cannot be bound; key.hex() is [0-9a-f] only
357 safe_sql = f"PRAGMA rekey = \"x'{key_hex}'\""
359 # Any failure here must not carry ``safe_sql`` outwards. SQLAlchemy wraps
360 # DBAPI errors in ``StatementError``, whose ``_sql_message`` appends
361 # ``"[SQL: %s]" % self.statement`` -- so the raw exception's ``str()``,
362 # its ``.statement`` attribute and its rendered traceback all contain the
363 # derived master key in hex. ``change_password``'s handler only redacts
364 # the two plaintext passwords, so an unwrapped error would put the key in
365 # the log. Capture the failure, then raise a key-free error *outside* the
366 # ``except`` block: ``from None`` clears ``__cause__`` and raising after
367 # the handler has exited leaves ``__context__`` unset, so no attribute of
368 # the propagated exception references the key. The raising frame's own
369 # locals are a separate surface, and are dropped further down.
370 #
371 # Keep the failure's *name* and its scrubbed message rather than the
372 # exception object: holding ``exc`` past the ``except`` block would pin
373 # its own traceback -- through SQLAlchemy frames that carry the statement
374 # -- to this frame for as long as the new error propagates.
375 failure_type: Optional[str] = None
376 failure_detail = ""
377 try:
378 try:
379 # Try SQLAlchemy connection (needs text() wrapper)
380 from sqlalchemy import text
382 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: safe_sql interpolates only the derived key bytes.hex output into PRAGMA rekey
383 cursor_or_conn.execute(text(safe_sql))
384 except TypeError:
385 # Raw SQLCipher connection - use string directly
386 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: safe_sql interpolates only the derived key bytes.hex output into PRAGMA rekey
387 cursor_or_conn.execute(safe_sql)
388 except Exception as exc: # noqa: BLE001 - re-raised below as a key-free error
389 failure_type = type(exc).__name__
390 failure_detail = _rekey_failure_detail(exc, key_hex, safe_sql)
392 # This frame stays in the propagated exception's traceback, so a sink that
393 # renders frame locals (loguru ``diagnose=True``) would print whatever is
394 # still bound here. ``exc`` is already gone -- Python unbinds it at the end
395 # of the ``except`` block -- so the key and the statement are what is left.
396 # ``new_password`` stays: the caller's own frame holds it either way, so
397 # dropping it here would suggest a protection that is not there.
398 del key_hex, safe_sql
400 if failure_type is not None:
401 message = f"PRAGMA rekey failed ({failure_type})"
402 if failure_detail:
403 message = f"{message}: {failure_detail}"
404 raise SQLCipherRekeyError(message) from None
407# Default SQLCipher configuration (can be overridden by settings)
408DEFAULT_KDF_ITERATIONS = 256000
409DEFAULT_PAGE_SIZE = 16384 # 16KB pages for maximum performance with caching
410DEFAULT_HMAC_ALGORITHM = "HMAC_SHA512"
411DEFAULT_KDF_ALGORITHM = "PBKDF2_HMAC_SHA512"
413# How long a connection waits on another connection's lock before SQLITE_BUSY.
414# Applied by apply_performance_pragmas, and before key verification wherever a
415# busy verification read would otherwise be mistaken for a wrong key. This is
416# the default of the operator-tunable ``db_config.busy_timeout_ms`` setting;
417# read the effective value through ``get_sqlcipher_busy_timeout_ms()``.
418SQLCIPHER_BUSY_TIMEOUT_MS = 30000
419# Fixed statement template; only an ``int`` is ever formatted into it.
420_BUSY_TIMEOUT_PRAGMA = "PRAGMA busy_timeout = {:d}"
423def get_sqlcipher_busy_timeout_ms() -> int:
424 """Return the effective ``busy_timeout`` in milliseconds.
426 Honours ``db_config.busy_timeout_ms`` so the pre-verification wait and
427 the per-connection performance pragma always agree.
428 """
429 return int(
430 get_env_setting("db_config.busy_timeout_ms", SQLCIPHER_BUSY_TIMEOUT_MS)
431 )
434def sqlcipher_busy_timeout_pragma() -> str:
435 """Return the ``PRAGMA busy_timeout`` statement for the effective value.
437 PRAGMA takes no bind parameters, so the value is formatted into the
438 statement. It is coerced with ``int()`` here (on top of
439 ``get_sqlcipher_busy_timeout_ms``), and the template's ``{:d}`` format
440 spec refuses anything that is not an integer, so the statement can
441 only ever carry an integer literal.
442 """
443 timeout_ms = int(get_sqlcipher_busy_timeout_ms())
444 return _BUSY_TIMEOUT_PRAGMA.format(timeout_ms)
447# Valid page sizes (powers of 2 within the SQLite range).
448# IntegerSetting validates min/max but not that the value is a power of 2,
449# so we check against this set as an additional safeguard.
450VALID_PAGE_SIZES = frozenset({512, 1024, 2048, 4096, 8192, 16384, 32768, 65536})
451MAX_KDF_ITERATIONS = 1_000_000
453# Production minimum KDF iterations. Relaxed automatically in test/CI environments.
454MIN_KDF_ITERATIONS_PRODUCTION = 100_000
455MIN_KDF_ITERATIONS_TESTING = 1
458def _get_min_kdf_iterations() -> int:
459 """Get minimum KDF iterations, relaxed for test/CI environments.
461 Only relaxes when PYTEST_CURRENT_TEST (set automatically by pytest) or
462 LDR_TEST_MODE (project-specific) is set. Generic env vars like CI or
463 TESTING are NOT checked to avoid accidentally weakening production
464 encryption in Docker/CD pipelines that set CI=true.
466 PYTEST_CURRENT_TEST is presence-based: pytest sets it to a descriptive
467 non-boolean string (e.g. "tests/foo.py::test_bar (call)"), so its mere
468 presence signals a test run. LDR_TEST_MODE is parsed as a proper boolean
469 so an explicit LDR_TEST_MODE=0 / =false does NOT relax the floor — a bare
470 truthiness check would treat any non-empty string (including "false") as
471 enabled and silently weaken encryption for someone trying to disable it.
472 """
473 is_testing = bool(os.environ.get("PYTEST_CURRENT_TEST")) or to_bool(
474 os.environ.get("LDR_TEST_MODE")
475 )
476 return (
477 MIN_KDF_ITERATIONS_TESTING
478 if is_testing
479 else MIN_KDF_ITERATIONS_PRODUCTION
480 )
483# Legacy salt for backwards compatibility with databases created before v2.
484# New databases use per-database random salts stored in .salt files.
485# WARNING: Do NOT change this value - it would break all existing legacy databases!
486LEGACY_PBKDF2_SALT = b"no salt"
488# Alias for backwards compatibility with code that references the old name
489PBKDF2_PLACEHOLDER_SALT = LEGACY_PBKDF2_SALT
492def get_sqlcipher_settings() -> dict:
493 """
494 Get SQLCipher settings from environment variables or use defaults.
496 These settings cannot be changed after database creation, so they
497 must be configured via environment variables only.
499 Settings are read via the env settings registry, which handles
500 canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
501 to deprecated names (LDR_DB_*) and deprecation warnings.
503 Returns:
504 Dictionary with SQLCipher configuration
505 """
506 # HMAC algorithm - registry validates against allowed values
507 hmac_algorithm = get_env_setting(
508 "db_config.hmac_algorithm", DEFAULT_HMAC_ALGORITHM
509 )
511 # KDF algorithm - registry validates against allowed values
512 kdf_algorithm = get_env_setting(
513 "db_config.kdf_algorithm", DEFAULT_KDF_ALGORITHM
514 )
516 # Page size - registry validates range, we also check power-of-2
517 page_size = get_env_setting("db_config.page_size", DEFAULT_PAGE_SIZE)
518 if page_size not in VALID_PAGE_SIZES:
519 logger.warning(
520 f"Invalid page_size value '{page_size}', using default "
521 f"'{DEFAULT_PAGE_SIZE}'. Valid values: {sorted(VALID_PAGE_SIZES)}"
522 )
523 page_size = DEFAULT_PAGE_SIZE
525 # KDF iterations - registry validates basic range, then apply CI-aware minimum
526 kdf_iterations = get_env_setting(
527 "db_config.kdf_iterations", DEFAULT_KDF_ITERATIONS
528 )
529 min_kdf = _get_min_kdf_iterations()
530 if not (min_kdf <= kdf_iterations <= MAX_KDF_ITERATIONS):
531 logger.warning(
532 f"KDF iterations value '{kdf_iterations}' outside safe range "
533 f"[{min_kdf}, {MAX_KDF_ITERATIONS}], using default "
534 f"'{DEFAULT_KDF_ITERATIONS}'."
535 )
536 kdf_iterations = DEFAULT_KDF_ITERATIONS
538 return {
539 "kdf_iterations": kdf_iterations,
540 "page_size": page_size,
541 "hmac_algorithm": hmac_algorithm,
542 "kdf_algorithm": kdf_algorithm,
543 }
546def warn_if_weak_kdf_with_existing_databases(
547 data_dir: Union[str, Path],
548) -> bool:
549 """Warn loudly when the effective SQLCipher KDF is below the production
550 floor *and* user databases already exist in ``data_dir``.
552 The KDF iteration count is derived from the environment at open time
553 (see :func:`get_sqlcipher_settings`) and is NOT stored with the
554 database. So if the floor is relaxed (test mode) on a deployment that
555 already holds real data, newly created databases get a weak at-rest work
556 factor, and — separately — any existing database that was created at a
557 *higher* KDF can no longer be opened: its on-disk key is unchanged, but
558 the server now derives a different, weaker key, so decryption fails and
559 login returns a generic "Invalid username or password" 401 that is
560 indistinguishable from a wrong password (the KDF-mismatch symptom class
561 behind PR #4775).
563 Scope: this only catches the "server is now weak" direction, and only a
564 *sub-floor* effective KDF. It cannot detect the inverse (a production-KDF
565 server opening databases created weak) or general in-range KDF drift,
566 because the creation-time KDF is not persisted to compare against — only
567 a sub-floor effective KDF is observable from the environment alone.
569 Intentionally silent on a fresh deployment (no pre-existing databases →
570 nothing to mismatch) and when the effective KDF is at/above the floor
571 (e.g. the 256000 default) — that is harmless.
573 Returns ``True`` if a warning was emitted (used by tests).
574 """
575 effective_kdf = get_sqlcipher_settings()["kdf_iterations"]
576 if effective_kdf >= MIN_KDF_ITERATIONS_PRODUCTION:
577 return False
579 # Only count + truthiness are used, so no need to sort the glob result.
580 # nosemgrep: semgrep.rules.path-traversal-risk, reason: Startup diagnostic enumerates a fixed DB filename pattern under the operator-configured data directory
581 existing = list(Path(data_dir).glob("ldr_user_*.db"))
582 if not existing:
583 return False
585 logger.warning(
586 "SQLCipher KDF is configured to {} iterations — below the production "
587 "floor of {} (reached only in test mode, e.g. LDR_TEST_MODE) — while "
588 "{} user database(s) already exist in {}. New databases created now "
589 "get this weak at-rest work factor. Separately, any of those existing "
590 "databases that was created at a higher KDF can no longer be opened: "
591 "its on-disk key is unchanged, but the server now derives a different, "
592 "weaker key, so decryption fails and login returns a generic 'Invalid "
593 "username or password' 401 for every affected user (the KDF-mismatch "
594 "symptom class behind PR #4775). If this is a production deployment, "
595 "unset LDR_TEST_MODE (and any low LDR_DB_CONFIG_KDF_ITERATIONS).",
596 effective_kdf,
597 MIN_KDF_ITERATIONS_PRODUCTION,
598 len(existing),
599 data_dir,
600 )
601 return True
604def apply_cipher_defaults_before_key(
605 cursor_or_conn: Any,
606) -> None:
607 """
608 Apply cipher_default_* pragmas BEFORE PRAGMA key for new database creation.
610 Per SQLCipher 4.x docs, cipher_default_* pragmas set the defaults that
611 apply when a key is set on a NEW database. These MUST be called before
612 PRAGMA key.
614 For EXISTING databases, cipher_page_size/cipher_hmac_algorithm/
615 cipher_kdf_algorithm are set AFTER the key via apply_sqlcipher_pragmas().
617 Args:
618 cursor_or_conn: SQLCipher cursor or connection object
619 """
620 settings = get_sqlcipher_settings()
622 logger.debug(
623 f"Applying cipher_default_* pragmas for new DB: settings={settings}"
624 )
626 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; page_size is an int in VALID_PAGE_SIZES
627 cursor_or_conn.execute(
628 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA page_size is an int checked against VALID_PAGE_SIZES
629 f"PRAGMA cipher_default_page_size = {settings['page_size']}"
630 )
631 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; hmac_algorithm is an EnumSetting allow-list value
632 cursor_or_conn.execute(
633 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA HMAC algorithm comes from the EnumSetting allowlist
634 f"PRAGMA cipher_default_hmac_algorithm = {settings['hmac_algorithm']}"
635 )
636 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; kdf_algorithm is an EnumSetting allow-list value
637 cursor_or_conn.execute(
638 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA KDF algorithm comes from the EnumSetting allowlist
639 f"PRAGMA cipher_default_kdf_algorithm = {settings['kdf_algorithm']}"
640 )
643def apply_sqlcipher_pragmas(
644 cursor_or_conn: Any,
645 creation_mode: bool = False,
646) -> None:
647 """
648 Apply SQLCipher PRAGMA settings that are set AFTER the key.
650 For SQLCipher 4.x:
651 - New databases: cipher_default_* are set before key via
652 apply_cipher_defaults_before_key(). This function only sets kdf_iter.
653 - Existing databases: cipher_page_size, cipher_hmac_algorithm,
654 cipher_kdf_algorithm MUST be set AFTER the key (not before).
655 This function handles that.
657 Args:
658 cursor_or_conn: SQLCipher cursor or connection object
659 creation_mode: If True, only sets kdf_iter (defaults already applied).
660 If False, sets cipher_* settings + kdf_iter for existing DB.
661 """
662 settings = get_sqlcipher_settings()
664 if not creation_mode:
665 # For existing databases: cipher_* pragmas go AFTER the key
666 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; page_size is an int in VALID_PAGE_SIZES
667 cursor_or_conn.execute(
668 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA page_size is an int checked against VALID_PAGE_SIZES
669 f"PRAGMA cipher_page_size = {settings['page_size']}"
670 )
671 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; hmac_algorithm is an EnumSetting allow-list value
672 cursor_or_conn.execute(
673 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA HMAC algorithm comes from the EnumSetting allowlist
674 f"PRAGMA cipher_hmac_algorithm = {settings['hmac_algorithm']}"
675 )
676 # nosemgrep: semgrep.rules.sql-execute-with-string-format, reason: PRAGMA cannot be bound; kdf_algorithm is an EnumSetting allow-list value
677 cursor_or_conn.execute(
678 # nosemgrep: semgrep.rules.sql-string-concatenation, reason: PRAGMA KDF algorithm comes from the EnumSetting allowlist
679 f"PRAGMA cipher_kdf_algorithm = {settings['kdf_algorithm']}"
680 )
682 # kdf_iter can be set after the key (applies to future derivation)
683 # 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
684 cursor_or_conn.execute(f"PRAGMA kdf_iter = {settings['kdf_iterations']}")
686 # cipher_memory_security is a runtime PRAGMA. ON zeroes SQLCipher buffers
687 # and calls mlock() to prevent swap; OFF skips this. Defaulting to OFF
688 # because the password already sits unprotected in Flask session, db_manager,
689 # and thread-local storage — mlock on SQLCipher's buffers alone doesn't help.
690 # Users can opt in with LDR_DB_CONFIG_CIPHER_MEMORY_SECURITY=ON + IPC_LOCK.
691 # Applied on every connection (not just creation) so env var overrides work.
692 mem_security = get_env_setting("db_config.cipher_memory_security", "OFF")
693 # Defense-in-depth: the env registry's EnumSetting already restricts
694 # this to {ON, OFF}, but an f-string interpolation into PRAGMA is a
695 # SQL-injection primitive if that registry check is ever bypassed
696 # (e.g. a refactor that swaps `get_env_setting` for raw `os.getenv`
697 # in a future patch). Assert explicitly so the call site fails loud
698 # instead of becoming an injection vector.
699 if mem_security not in ("ON", "OFF"): 699 ↛ 700line 699 didn't jump to line 700 because the condition on line 699 was never true
700 raise ValueError(
701 f"cipher_memory_security must be ON or OFF, got: {mem_security!r}"
702 )
703 # nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value checked against ("ON"; "OFF") above
704 cursor_or_conn.execute(f"PRAGMA cipher_memory_security = {mem_security}")
707def apply_performance_pragmas(cursor_or_conn: Any) -> None:
708 """
709 Apply performance-related PRAGMA settings from environment variables.
711 Settings are read via the env settings registry, which handles
712 canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
713 to deprecated names (LDR_DB_*) and deprecation warnings.
715 Args:
716 cursor_or_conn: SQLCipher cursor or connection object
717 """
718 # Default values that are always applied
719 cursor_or_conn.execute("PRAGMA temp_store = MEMORY")
721 # SQLite serialises all writes through a single file lock, so any
722 # in-flight transaction blocks every other writer until it commits
723 # or rolls back. ``busy_timeout`` is the wall-clock budget the
724 # SQLite driver waits for that lock before failing with
725 # ``SQLITE_BUSY`` ("database is locked"). The background document
726 # scheduler can hold a session across multi-second PDF downloads
727 # (it queries ResearchHistory, then iterates calling
728 # DownloadService.download_as_text per resource, then commits the
729 # ``last_run`` update); a 10 s budget used to fail the rate-limit
730 # tracker's persistence path -- and the scheduler's per-resource
731 # processing -- every time the rate-limit write landed inside the
732 # scheduler's transaction. 30 s gives those short writers enough
733 # headroom to land their commit instead of swallowing the error.
734 # Operators who want to tune this further can override
735 # ``db_config.busy_timeout_ms``; the lower bound (100 ms) keeps
736 # the negative-control contention test fast (it has to drive a
737 # writer-against-holder race to a real timeout) without being so
738 # tight that the spinner can't ride out even a single held
739 # transaction, and the upper bound (300 s) caps tail latency if a
740 # query goes pathological.
741 busy_timeout_sql = sqlcipher_busy_timeout_pragma()
742 cursor_or_conn.execute(busy_timeout_sql)
744 # SQLite defaults foreign_keys to OFF — without this every
745 # ondelete="CASCADE"/"SET NULL" declared on an FK is inert,
746 # including for raw-SQL DELETEs issued by Query.delete().
747 cursor_or_conn.execute("PRAGMA foreign_keys = ON")
749 # Cache size - registry validates min/max range. Force int before
750 # f-string interpolation so a non-numeric value surfaces as TypeError
751 # at this point rather than becoming an injection vector downstream.
752 cache_mb = int(get_env_setting("db_config.cache_size_mb", 64))
753 cache_pages = -(cache_mb * 1024) # Negative for KB cache size
754 # nosemgrep: semgrep.rules.sql-execute-with-string-format, semgrep.rules.sql-string-concatenation, reason: PRAGMA cannot be bound; value derived from int() above
755 cursor_or_conn.execute(f"PRAGMA cache_size = {cache_pages}")
757 # Journal mode - registry validates against allowed values. Belt-and-
758 # braces: assert the allowlist explicitly at the f-string call site.
759 journal_mode = get_env_setting("db_config.journal_mode", "WAL")
760 if journal_mode not in (
761 "DELETE",
762 "TRUNCATE",
763 "PERSIST",
764 "MEMORY",
765 "WAL",
766 "OFF",
767 ):
768 raise ValueError(f"Invalid journal_mode: {journal_mode!r}")
769 if journal_mode == "WAL":
770 # 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
771 cursor_or_conn.execute(f"PRAGMA journal_mode = {journal_mode}")
772 else:
773 # Leaving WAL mode closes the WAL inside SQLite's pager. On SQLite
774 # 3.51.0/3.51.1 that takes the same inverted VFS mutexes as closing
775 # a WAL connection, so serialize it with guarded opens and closes.
776 from .sqlcipher_compat import wal_lifecycle_guard
778 with wal_lifecycle_guard():
779 # 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
780 cursor_or_conn.execute(f"PRAGMA journal_mode = {journal_mode}")
782 # Synchronous mode - same defense-in-depth pattern.
783 sync_mode = get_env_setting("db_config.synchronous", "NORMAL")
784 if sync_mode not in ("OFF", "NORMAL", "FULL", "EXTRA"): 784 ↛ 785line 784 didn't jump to line 785 because the condition on line 784 was never true
785 raise ValueError(f"Invalid synchronous mode: {sync_mode!r}")
786 # 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
787 cursor_or_conn.execute(f"PRAGMA synchronous = {sync_mode}")
789 # WAL autocheckpoint frame threshold. SQLite's default of 1000 frames
790 # paired with our 16 KB page size means the WAL can grow to ~16 MB
791 # before SQLite triggers a PASSIVE checkpoint at commit. Lowering the
792 # threshold bounds the WAL high-water-mark on disk for users who never
793 # log out (the explicit TRUNCATE checkpoint runs on dispose, not here).
794 wal_autocheckpoint = get_env_setting("db_config.wal_autocheckpoint", 250)
795 # 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
796 cursor_or_conn.execute(f"PRAGMA wal_autocheckpoint = {wal_autocheckpoint}")
799# ---------------------------------------------------------------------------
800# Transient-lock retry helper
801# ---------------------------------------------------------------------------
802#
803# SQLite serialises all writes through a single file lock, and any
804# writer that lands inside a long-running transaction has to wait for
805# ``busy_timeout`` before either getting the lock or failing with
806# ``SQLITE_BUSY`` ("database is locked"). ``apply_performance_pragmas``
807# above bumps the default timeout from 10 s to 30 s, which gives a
808# short writer (e.g. the rate-limit tracker's per-engine estimate
809# persistence) enough headroom to land its commit during a long
810# in-flight transaction like the document scheduler's PDF-download
811# loop.
812#
813# ``busy_timeout`` only protects a single ``BEGIN IMMEDIATE`` /
814# ``COMMIT`` pair -- if the holder runs longer than the timeout, the
815# writer's first commit attempt still fails. The helper below catches
816# that exact failure and retries the callable with exponential
817# backoff, so the caller's outer ``except Exception`` log (the rate-limit
818# tracker's "Failed to persist rate limit estimate") only fires when
819# every retry is exhausted, not on the first transient lock contention.
821_T = TypeVar("_T")
824def _db_lock_error_types() -> Tuple[Type[BaseException], ...]:
825 """Return the exception classes that signal ``database is locked``.
827 The stdlib ``sqlite3`` module and ``sqlcipher3`` have *separate*
828 exception hierarchies -- ``sqlcipher3.dbapi2.OperationalError``
829 does NOT inherit from ``sqlite3.OperationalError`` -- so a
830 defensive retry helper has to catch both. We also include
831 ``sqlalchemy.exc.OperationalError`` because the production call
832 site (``rate_limit_tracker._persist_estimate``) commits through a
833 SQLAlchemy session, and SQLAlchemy wraps the DBAPI lock error in
834 its own ``OperationalError`` subclass that does NOT inherit from
835 either DBAPI class -- without it the helper would silently be a
836 no-op on the wrapped path and the retry would never fire. The
837 ``"locked" in str(exc)`` message guard below filters the SQLAlchemy
838 subclass correctly: on a ``SQLITE_BUSY`` the wrapped message
839 preserves the substring; on every other ``OperationalError``
840 (broken pipe, schema error, etc.) it does not.
841 """
842 types: list[Type[BaseException]] = [sqlite3.OperationalError]
843 try:
844 from sqlcipher3 import dbapi2 as _sqlcipher_dbapi2
846 types.append(_sqlcipher_dbapi2.OperationalError)
847 except ImportError:
848 pass
849 try:
850 from sqlalchemy.exc import OperationalError as _SQLAOperationalError
852 types.append(_SQLAOperationalError)
853 except ImportError:
854 pass
855 return tuple(types)
858_DB_LOCK_ERROR_TYPES = _db_lock_error_types()
861def retry_on_db_lock(
862 callable_: Callable[[], _T],
863 *,
864 attempts: int = 3,
865 base_delay_seconds: float = 0.1,
866) -> _T:
867 """Run ``callable_``, retrying on SQLite ``database is locked`` errors.
869 Only retries ``OperationalError`` whose message contains
870 ``"locked"`` -- every other exception (connection errors,
871 IntegrityError, ProgrammingError, plain ``OperationalError`` without
872 ``"locked"`` in the message, etc.) propagates immediately so the
873 caller can decide how to handle it.
875 The total wall-clock budget before the helper gives up is bounded
876 by ``attempts * busy_timeout + (attempts - 1) * base_delay_seconds *
877 (2**(attempts-1) - 1)``: each attempt can wait up to the connection's
878 ``busy_timeout`` for the writer lock, and the helper itself sleeps
879 ``base_delay_seconds * 2 ** (n - 1)`` between attempts. At the
880 defaults (3 attempts, 0.1 s base delay, 30 s busy_timeout) that's
881 at most ``3 * 30 + 0.1 + 0.2 = 90.3 s`` of waiting before the
882 terminal ``database is locked`` raises to the caller. That upper
883 bound is conservative: the connection's spinner waits the full
884 timeout only on the very last attempt, and earlier attempts usually
885 succeed while the holder's transaction is still in flight.
887 This helper does not open, close, or pool database connections
888 itself. ``callable_`` is responsible for its own connection
889 lifecycle (open the SQLAlchemy session, ``with metrics_writer.get_session(...)``,
890 etc.); on retry the helper simply calls ``callable_()`` again, so
891 a caller that opens a fresh connection per call naturally gets a
892 fresh attempt each time. Reusing a connection that already failed
893 with ``SQLITE_BUSY`` is fine -- ``busy_timeout`` is reapplied per
894 connection and the SQLite driver clears the busy state on the next
895 BEGIN -- but most callers already pool short-lived sessions and
896 don't need to special-case this.
898 Args:
899 callable_: The no-argument callable to run. Each attempt calls
900 it fresh -- any partial state must be inside the callable's
901 own context manager (e.g. a ``with metrics_writer.get_session(...)``).
902 attempts: Maximum number of attempts (including the first).
903 ``1`` disables the retry behaviour.
904 base_delay_seconds: Initial backoff. The delay between
905 attempts is ``base_delay_seconds * 2 ** (attempt - 1)`` --
906 0.1 s, 0.2 s, 0.4 s by default.
908 Returns:
909 Whatever ``callable_`` returns on the first successful run.
911 Raises:
912 sqlite3.OperationalError / sqlcipher3.OperationalError /
913 sqlalchemy.exc.OperationalError: ``database is locked`` if
914 every attempt is exhausted. Other exceptions propagate
915 without retry (the message-substring guard filters the
916 SQLAlchemy wrapper to lock errors specifically).
917 """
918 for attempt in range(1, attempts + 1):
919 try:
920 return callable_()
921 except _DB_LOCK_ERROR_TYPES as exc:
922 if "locked" not in str(exc).lower() or attempt >= attempts:
923 raise
924 logger.debug(
925 "SQLite 'database is locked' on attempt "
926 f"{attempt}/{attempts}; retrying in "
927 f"{base_delay_seconds * (2 ** (attempt - 1)):.2f}s"
928 )
929 time.sleep(base_delay_seconds * (2 ** (attempt - 1)))
930 # Unreachable: the loop returns on success or raises on the final
931 # attempt. The explicit ``raise`` above satisfies the type checker.
932 raise RuntimeError( # pragma: no cover -- defensive
933 "retry_on_db_lock: loop exited without returning or raising"
934 )
937# SQLite's answer when page 1 does not decrypt (SQLITE_NOTADB): with
938# SQLCipher, a wrong key. Older builds word it the second way.
939_WRONG_KEY_MESSAGES = (
940 "file is not a database",
941 "file is encrypted or is not a database",
942)
943_DBAPI_MODULE_PREFIXES = ("sqlcipher3", "sqlite3", "pysqlcipher3")
946def _exception_chain(error: BaseException):
947 seen: set[int] = set()
948 pending: list[BaseException | None] = [error]
949 while pending:
950 current = pending.pop()
951 if current is None or id(current) in seen:
952 continue
953 seen.add(id(current))
954 yield current
955 pending.extend(
956 (
957 getattr(current, "orig", None),
958 current.__cause__,
959 current.__context__,
960 )
961 )
964def is_wrong_key_error(error: BaseException) -> bool:
965 """True when ``error`` (or one it wraps) is SQLite's wrong-key answer."""
966 return any(
967 any(message in str(item) for message in _WRONG_KEY_MESSAGES)
968 for item in _exception_chain(error)
969 )
972def is_operational_sqlite_error(error: BaseException) -> bool:
973 """True for a SQLite failure that says nothing about the key.
975 A driver ``OperationalError`` (database locked or busy past the busy
976 timeout, disk I/O error, unable to open the file) anywhere in the
977 chain, unless the chain also carries the wrong-key answer. Callers use
978 it to keep such a failure from being reported as a wrong password.
979 """
980 if is_wrong_key_error(error):
981 return False
982 return any(
983 type(item).__name__ == "OperationalError"
984 and type(item).__module__.startswith(_DBAPI_MODULE_PREFIXES)
985 for item in _exception_chain(error)
986 )
989def check_sqlcipher_key(cursor: Any) -> bool:
990 """Run the key verification query; False only for a wrong key.
992 Unlike ``verify_sqlcipher_connection``, an operational failure
993 (``is_operational_sqlite_error``) propagates instead of reading as a
994 wrong key, so password-checking callers can answer it as retryable.
995 """
996 try:
997 cursor.execute("SELECT 1")
998 result = cursor.fetchone()
999 except Exception as error:
1000 if is_operational_sqlite_error(error):
1001 raise
1002 logger.warning(
1003 f"SQLCipher key verification failed: {type(error).__name__}"
1004 )
1005 return False
1006 return result == (1,)
1009def verify_sqlcipher_connection(cursor_or_conn: Any) -> bool:
1010 """
1011 Verify that the SQLCipher connection is working correctly.
1013 Args:
1014 cursor_or_conn: SQLCipher cursor or connection object
1016 Returns:
1017 True if the connection is valid, False otherwise
1018 """
1019 try:
1020 cursor_or_conn.execute("SELECT 1")
1021 result = (
1022 cursor_or_conn.fetchone()
1023 if hasattr(cursor_or_conn, "fetchone")
1024 else cursor_or_conn.execute("SELECT 1").fetchone()
1025 )
1026 is_valid = result == (1,)
1027 if not is_valid:
1028 logger.error(
1029 f"SQLCipher verification failed: result {result} != (1,)"
1030 )
1031 return is_valid
1032 except Exception:
1033 logger.exception("SQLCipher verification failed")
1034 return False
1037def get_sqlcipher_version(cursor_or_conn: Any) -> Optional[str]:
1038 """
1039 Get the SQLCipher version string.
1041 Args:
1042 cursor_or_conn: SQLCipher cursor or connection object
1044 Returns:
1045 Version string (e.g. "4.6.1 community") or None if unavailable
1046 """
1047 try:
1048 cursor_or_conn.execute("PRAGMA cipher_version")
1049 result = cursor_or_conn.fetchone()
1050 return result[0] if result else None
1051 except Exception:
1052 logger.debug("Could not query SQLCipher version", exc_info=True)
1053 return None
1056def create_sqlcipher_connection(
1057 db_path: Union[str, Path],
1058 password: Optional[str] = None,
1059 creation_mode: bool = False,
1060 connect_kwargs: Optional[Dict[str, Any]] = None,
1061 hex_key: Optional[str] = None,
1062) -> Any:
1063 """
1064 Create a properly configured SQLCipher connection.
1066 Implements the full PRAGMA sequence with proper error cleanup:
1067 - Creation: cipher_default_* -> key -> kdf_iter -> performance -> verify
1068 - Existing: key -> cipher_* + kdf_iter -> performance -> verify
1070 Uses per-database salt if a .salt file exists alongside the database,
1071 otherwise falls back to legacy salt for backwards compatibility.
1073 Args:
1074 db_path: Path to the database file
1075 password: The password for encryption (mutually exclusive with hex_key)
1076 creation_mode: If True, set cipher_default_* before key (new DB)
1077 connect_kwargs: Extra kwargs passed to sqlcipher3.connect()
1078 hex_key: Pre-derived hex key (skips PBKDF2 derivation)
1080 Returns:
1081 SQLCipher connection object
1083 Raises:
1084 ImportError: If sqlcipher3 is not available
1085 ValueError: If the connection cannot be established
1086 """
1087 from .sqlcipher_compat import connect_sqlcipher, get_sqlcipher_module
1089 try:
1090 sqlcipher3 = get_sqlcipher_module()
1091 except ImportError:
1092 raise ImportError(
1093 "sqlcipher3 is not available for encrypted databases. "
1094 "Ensure SQLCipher system library is installed, then run: pdm install"
1095 )
1097 conn = connect_sqlcipher(sqlcipher3, str(db_path), **(connect_kwargs or {}))
1098 try:
1099 cursor = conn.cursor()
1101 if creation_mode:
1102 with _cipher_default_lock:
1103 apply_cipher_defaults_before_key(cursor)
1105 # Set encryption key (uses per-database salt when password + db_path)
1106 if hex_key:
1107 set_sqlcipher_key_from_hex(cursor, hex_key)
1108 elif password: 1108 ↛ 1111line 1108 didn't jump to line 1111 because the condition on line 1108 was always true
1109 set_sqlcipher_key(cursor, password, db_path=db_path)
1110 else:
1111 raise ValueError("Either password or hex_key must be provided") # noqa: TRY301 — except does connection cleanup before re-raise
1113 # Apply post-key pragmas (cipher_* for existing, kdf_iter for both)
1114 apply_sqlcipher_pragmas(cursor, creation_mode=creation_mode)
1116 # Apply performance settings
1117 apply_performance_pragmas(cursor)
1119 # Verify connection works (an operational failure propagates; see
1120 # check_sqlcipher_key)
1121 if not check_sqlcipher_key(cursor):
1122 raise ValueError( # noqa: TRY301 — except does connection cleanup before re-raise
1123 "Failed to establish encrypted database connection"
1124 )
1126 cursor.close()
1127 return conn
1128 except Exception:
1129 from ..utilities.resource_utils import safe_close
1131 safe_close(conn, "SQLCipher connection")
1132 raise
1135# Backwards compatibility alias — old name still importable
1136apply_cipher_settings_before_key = apply_cipher_defaults_before_key