learningcircuit--local-deep-research/tests/auth_tests/test_sqlcipher_key_derivation.py

"""
Tests for SQLCipher key derivation functions.

These tests verify that password-to-key derivation is consistent,
deterministic, and secure.
"""

import shutil
import tempfile
from pathlib import Path

import pytest

from src.local_deep_research.database.sqlcipher_utils import (
    LEGACY_PBKDF2_SALT,
    _get_key_from_password,
    get_sqlcipher_settings,
    set_sqlcipher_key,
    set_sqlcipher_rekey,
)
from src.local_deep_research.database.sqlcipher_compat import (
    get_sqlcipher_module,
)


@pytest.fixture
def temp_db_path():
    """Create a temporary database path."""
    temp_dir = tempfile.mkdtemp()
    db_path = Path(temp_dir) / "test_key.db"
    yield db_path
    shutil.rmtree(temp_dir)


@pytest.fixture
def sqlcipher_module():
    """Get the SQLCipher module."""
    return get_sqlcipher_module()


class TestKeyDerivation:
    """Tests for password-to-key derivation."""

    def test_key_derivation_deterministic(self):
        """Verify same password always produces same key."""
        password = "test_password_123"

        key1 = _get_key_from_password(
            password,
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )
        key2 = _get_key_from_password(
            password,
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )

        assert key1 == key2, "Same password should always produce same key"

    def test_key_derivation_different_passwords(self):
        """Verify different passwords produce different keys."""
        key1 = _get_key_from_password(
            "password1",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )
        key2 = _get_key_from_password(
            "password2",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )

        assert key1 != key2, "Different passwords should produce different keys"

    def test_key_hex_encoding_only_hex_chars(self):
        """Verify derived key only contains hex characters (SQL injection safe)."""
        key = _get_key_from_password(
            "any_password",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )
        hex_string = key.hex()

        # Verify only hex characters
        valid_hex_chars = set("0123456789abcdef")
        assert all(c in valid_hex_chars for c in hex_string), (
            "Key hex encoding should only contain [0-9a-f]"
        )

    def test_special_characters_in_password(self):
        """Verify passwords with special characters are handled correctly."""
        special_passwords = [
            "password with spaces",
            "password'with'quotes",
            'password"with"double"quotes',
            "password\\with\\backslashes",
            "password\nwith\nnewlines",
            "password\twith\ttabs",
            "unicode: 日本語 emoji: 🔐🔑",
            "sql_injection: '; DROP TABLE users; --",
            "null_byte: \x00test",
        ]

        keys = []
        for password in special_passwords:
            try:
                key = _get_key_from_password(
                    password,
                    LEGACY_PBKDF2_SALT,
                    get_sqlcipher_settings()["kdf_iterations"],
                )
                keys.append(key)
                # Verify key is valid bytes
                assert isinstance(key, bytes), (
                    f"Key should be bytes for: {password}"
                )
                assert len(key) > 0, f"Key should not be empty for: {password}"
            except Exception as e:
                pytest.fail(f"Password '{password}' caused error: {e}")

        # All keys should be unique
        assert len(set(keys)) == len(keys), (
            "All special passwords should produce unique keys"
        )

    def test_key_length_correct(self):
        """Verify derived key has correct length (64 bytes = 512 bits for SHA512)."""
        key = _get_key_from_password(
            "test_password",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )

        # SHA512-based PBKDF2 produces 64-byte key (512 bits)
        assert len(key) == 64, f"Key should be 64 bytes, got {len(key)}"

    def test_empty_password_produces_key(self):
        """Verify empty password still produces a valid key (validation is elsewhere)."""
        # Note: Empty password validation happens in DatabaseManager, not in key derivation
        # The key derivation function itself should work with any input
        key = _get_key_from_password(
            "", LEGACY_PBKDF2_SALT, get_sqlcipher_settings()["kdf_iterations"]
        )
        assert isinstance(key, bytes), (
            "Empty password should still produce bytes"
        )
        assert len(key) == 64, (
            "Key length should be consistent (64 bytes for SHA512)"
        )


class TestKeyDerivationWithDatabase:
    """Tests for key derivation in actual database operations."""

    def test_derived_key_opens_database(self, sqlcipher_module, temp_db_path):
        """Verify derived key can create and open a database."""
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_cipher_defaults_before_key,
        )

        password = "test_database_password"

        # Create database
        conn = sqlcipher_module.connect(str(temp_db_path))
        cursor = conn.cursor()
        # New database, so use creation_mode=True
        apply_cipher_defaults_before_key(cursor)
        set_sqlcipher_key(cursor, password)
        cursor.close()

        conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
        conn.execute("INSERT INTO test VALUES (1)")
        conn.commit()  # Important: commit before closing
        conn.close()

        # Reopen with same password
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
        )

        conn2 = sqlcipher_module.connect(str(temp_db_path))
        cursor2 = conn2.cursor()
        # Existing database: key first, then cipher_* pragmas
        set_sqlcipher_key(cursor2, password)
        apply_sqlcipher_pragmas(cursor2, creation_mode=False)
        cursor2.close()

        result = conn2.execute("SELECT * FROM test").fetchone()
        assert result == (1,), "Should be able to read data with same password"
        conn2.close()

    def test_wrong_password_cannot_open(self, sqlcipher_module, temp_db_path):
        """Verify wrong password cannot open database."""
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_cipher_defaults_before_key,
        )

        # Create database with password1
        conn = sqlcipher_module.connect(str(temp_db_path))
        cursor = conn.cursor()
        # New database, so use creation_mode=True
        apply_cipher_defaults_before_key(cursor)
        set_sqlcipher_key(cursor, "password1")
        cursor.close()

        conn.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
        conn.commit()
        conn.close()

        # Try to open with password2
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
        )

        conn2 = sqlcipher_module.connect(str(temp_db_path))
        cursor2 = conn2.cursor()
        # Existing database: key first, then cipher_* pragmas
        set_sqlcipher_key(cursor2, "password2")
        apply_sqlcipher_pragmas(cursor2, creation_mode=False)
        cursor2.close()

        # Should fail when trying to access data
        with pytest.raises(Exception):
            conn2.execute("SELECT * FROM test").fetchone()

        conn2.close()

    def test_rekey_uses_same_derivation(self, sqlcipher_module, temp_db_path):
        """Verify rekey produces key compatible with set_sqlcipher_key."""
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_cipher_defaults_before_key,
        )

        # Create database
        conn = sqlcipher_module.connect(str(temp_db_path))
        cursor = conn.cursor()
        # New database, so use creation_mode=True
        apply_cipher_defaults_before_key(cursor)
        set_sqlcipher_key(cursor, "original_password")
        cursor.close()

        conn.execute("CREATE TABLE test (id INTEGER, value TEXT)")
        conn.execute("INSERT INTO test VALUES (1, 'secret_data')")
        conn.commit()

        # Rekey to new password
        set_sqlcipher_rekey(conn, "new_password")
        conn.close()

        # Open with new password using set_sqlcipher_key
        from src.local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
        )

        conn2 = sqlcipher_module.connect(str(temp_db_path))
        cursor2 = conn2.cursor()
        # Existing database: key first, then cipher_* pragmas
        set_sqlcipher_key(cursor2, "new_password")
        apply_sqlcipher_pragmas(cursor2, creation_mode=False)
        cursor2.close()

        # Should be able to read data
        result = conn2.execute("SELECT value FROM test WHERE id = 1").fetchone()
        assert result[0] == "secret_data", "Data should be readable after rekey"
        conn2.close()

        # Original password should no longer work
        conn3 = sqlcipher_module.connect(str(temp_db_path))
        cursor3 = conn3.cursor()
        # Existing database: key first, then cipher_* pragmas
        set_sqlcipher_key(cursor3, "original_password")
        apply_sqlcipher_pragmas(cursor3, creation_mode=False)
        cursor3.close()

        with pytest.raises(Exception):
            conn3.execute("SELECT * FROM test").fetchone()
        conn3.close()


class TestKeyDerivationSecurity:
    """Security-focused tests for key derivation."""

    def test_pbkdf2_iterations_sufficient(self):
        """Verify KDF iterations are set to a secure value."""
        settings = get_sqlcipher_settings()
        iterations = settings["kdf_iterations"]

        # OWASP recommends at least 600,000 for PBKDF2-HMAC-SHA512
        # We use 256,000 which is a reasonable balance for performance
        assert iterations >= 100000, (
            f"KDF iterations should be at least 100000, got {iterations}"
        )

    def test_key_not_stored_in_plaintext(self):
        """Verify derived key is bytes, not plaintext password."""
        password = "my_secret_password"
        key = _get_key_from_password(
            password,
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )

        # Key should be bytes, not string
        assert isinstance(key, bytes), "Key should be bytes"

        # Key should not contain the password
        assert password.encode() not in key, (
            "Key should not contain plaintext password"
        )

    def test_different_salts_would_produce_different_keys(self):
        """
        Document that our fixed salt means same password = same key.

        This is intentional for database compatibility - if salt changed,
        existing databases would become inaccessible.
        """
        # Same password should always produce same key (fixed salt)
        key1 = _get_key_from_password(
            "test",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )
        key2 = _get_key_from_password(
            "test",
            LEGACY_PBKDF2_SALT,
            get_sqlcipher_settings()["kdf_iterations"],
        )

        assert key1 == key2, (
            "Fixed salt ensures consistent key derivation for database compatibility"
        )