SQLCipher encryption not activating with v1.14.28 despite SQLCipher v4.5.6 and CGO flags

[MattLCE]

Environment:
Go: go version: go1.23.8 linux/amd64
mattn/go-sqlite3: github.com/mattn/go-sqlite3 v1.14.28
OS/Environment: Debian Bookworm (via mcr.microsoft.com/devcontainers/go:1.23 Docker image)
SQLCipher: 3.44.2 2023-11-24 11:41:44 ebead0e7230cd33bcec9f95d2183069565b9e709bf745c9b5db65cc0cbf9alt1 (64-bit) (SQLCipher 4.5.6 community) Built from source
Build Tool: go test

Problem:
Attempts to create or open an encrypted SQLite database using mattn/go-sqlite3 with SQLCipher support enabled result in an unencrypted database file, even when SQLCipher v4.5.6 is correctly built, installed, and discoverable by the linker. Both DSN parameters (_pragma_key=) and explicit PRAGMA key = '...' execution after opening fail to enable encryption.

Code & CGO Flags:
File: internal/sqlite/securedb.go
//go:build cgo

package sqlite

/*
#cgo CFLAGS: -DSQLITE_HAS_CODEC
#cgo LDFLAGS: -lsqlcipher
*/
import (
"database/sql"
"fmt"

_ "github.com/mattn/go-sqlite3"

)

// Open returns an encrypted handle. Relies on PRAGMA key.
func Open(path string, key []byte) (*sql.DB, error) {
// DSN without key parameter.
dsn := fmt.Sprintf(
"file:%s?_pragma_cipher_page_size=4096&_busy_timeout=10000",
path,
)

db, err := sql.Open("sqlite3", dsn)
if err != nil {
	return nil, fmt.Errorf("sql open failed: %w", err)
}

// --- Explicitly execute PRAGMA key AFTER open ---
pragmaKeySQL := fmt.Sprintf("PRAGMA key = \"x'%x'\";", key)
if _, err := db.Exec(pragmaKeySQL); err != nil {
	// If *this* fails, it might indicate an issue with the key or encryption setup
	_ = db.Close() // Close on error
	return nil, fmt.Errorf("failed to execute PRAGMA key: %w", err)
}
// --- End Explicit PRAGMA ---

// --- Minimal Check: Ping AFTER setting key ---
// If PRAGMA key worked, Ping should still succeed.
// If PRAGMA key failed silently earlier but encryption *was* active, Ping might fail here.
if err := db.Ping(); err != nil {
	_ = db.Close()
	return nil, fmt.Errorf("db ping failed after setting key: %w", err)
}

// Assume success if PRAGMA key and Ping didn't error.
// Let the caller perform actual read/write checks.
return db, nil

}

File: internal/sqlite/securedb_test.go
package sqlite

import (
"bytes"
"fmt"
"os" // Import os for file reading
"path/filepath"
"testing"

"github.com/n1/n1/internal/crypto"
"github.com/stretchr/testify/require"

)

// --- Keep your existing Open function in securedb.go (Simplified version above) ---

func TestEncryptionEndToEnd(t *testing.T) {
tmpDir := t.TempDir()
dbPath := filepath.Join(tmpDir, "vault_e2e.db")
mk, _ := crypto.Generate(32)
t.Logf("Database path: %s", dbPath)

const testData = "this is secret"
const testID = 1

// --- 1. Create DB, Write Data with Correct Key ---
dbWrite, err := Open(dbPath, mk)
require.NoError(t, err, "E2E: Initial open with correct key failed")
_, err = dbWrite.Exec(`CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT)`)
require.NoError(t, err, "E2E: Create table failed")
_, err = dbWrite.Exec(`INSERT INTO secrets (id, data) VALUES (?, ?)`, testID, testData)
require.NoError(t, err, "E2E: Insert data failed")
err = dbWrite.Close()
require.NoError(t, err, "E2E: Close after write failed")

// --- DIAGNOSTIC: Check file header by reading raw bytes ---
t.Logf("--- Reading file header directly ---")
fileBytes, readErr := os.ReadFile(dbPath)
if readErr != nil {
	t.Logf("!!! Error reading database file: %v", readErr)
} else if len(fileBytes) < 16 {
	t.Logf("!!! Database file is too short (< 16 bytes) !!!")
} else {
	header := fileBytes[:16]
	expectedHeader := []byte("SQLite format 3\000") // Note the null terminator \000
	// Log the actual header bytes found
	t.Logf("Actual file header bytes: %x", header)
	if bytes.Equal(header, expectedHeader) {
		t.Logf("!!! DIAGNOSTIC: File header matches 'SQLite format 3' - DATABASE IS NOT ENCRYPTED !!!")
	} else {
		t.Logf("--- DIAGNOSTIC: File header does not match 'SQLite format 3' - Likely Encrypted (as expected) ---")
	}
}
t.Logf("--- Finished file header check ---")
// --- End DIAGNOSTIC ---

// --- 2. Attempt to Open with WRONG Key and READ Data ---
wrongKey, _ := crypto.Generate(32)
dbReadWrong, openErrWrong := Open(dbPath, wrongKey)
var readDataWrong string
var queryErrWrong error
if dbReadWrong != nil {
	queryErrWrong = dbReadWrong.QueryRow(`SELECT data FROM secrets WHERE id = ?`, testID).Scan(&readDataWrong)
	_ = dbReadWrong.Close()
} else {
	queryErrWrong = fmt.Errorf("E2E: Open itself failed with wrong key: %w", openErrWrong)
}
require.Error(t, queryErrWrong, "E2E: Reading data with WRONG key succeeded, expected an error!")
t.Logf("E2E: Successfully failed to read with wrong key (as required by test logic): %v", queryErrWrong)

// --- 3. Open with CORRECT Key and READ Data ---
dbReadCorrect, err := Open(dbPath, mk)
require.NoError(t, err, "E2E: Reopen with correct key failed")
var readDataCorrect string
err = dbReadCorrect.QueryRow(`SELECT data FROM secrets WHERE id = ?`, testID).Scan(&readDataCorrect)
require.NoError(t, err, "E2E: Reading data with CORRECT key failed")
require.Equal(t, testData, readDataCorrect, "E2E: Data read with correct key does not match original data")
err = dbReadCorrect.Close()
require.NoError(t, err, "E2E: Close after correct read failed")

}

Steps to Reproduce:
Set up a Debian Bookworm environment (e.g., using mcr.microsoft.com/devcontainers/go:1.23).
Install build dependencies and build+install SQLCipher v4.5.6 from source (ensuring ldconfig registers /usr/local/lib).

Simplified example from devcontainer postCreateCommand

sudo apt-get update && sudo apt-get install -y build-essential tclsh libssl-dev git sqlite3
cd /tmp && git clone --depth 1 --branch v4.5.6 https://github.com/sqlcipher/sqlcipher.git
cd sqlcipher && ./configure CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-lcrypto" --with-crypto-lib=openssl && make -j$(nproc) && sudo make install && sudo ldconfig

Use the Go code provided above (internal/sqlite/securedb.go and internal/sqlite/securedb_test.go).
Run go test -v ./... in the project root.

Actual Results:
The test fails at the require.Error assertion in step 2.
Reading the header of the database file created in step 1 confirms it starts with SQLite format 3\0, indicating it is not encrypted. (Diagnostic check added to test code confirms this).
The PRAGMA key = ... command in the Open function does not return an error but does not result in an encrypted database or prevent reads with an incorrect key.

Expected Results:
The database file created in step 1 should have an encrypted header (not starting with SQLite format 3\0).
The call to dbReadWrong.QueryRow(...).Scan(...) in step 2 should return an error (e.g., "file is not a database", "bad parameter or other API misuse", etc.) because the wrong key was used.
The require.Error(t, queryErrWrong, ...) assertion should pass.
The overall test TestEncryptionEndToEnd should pass.

Troubleshooting Already Attempted:
Verified SQLCipher v4.5.6 installation (sqlcipher --version) and linker path (ldconfig -p).
Verified CGO flags (-DSQLITE_HAS_CODEC, -lsqlcipher) are present in verbose build logs (go test -x).
Tried both DSN _pragma_key and explicit PRAGMA key after sql.Open.
Attempted static linking (-tags=sqlite_static with appropriate LDFLAGS), which also resulted in an unencrypted database.
Downgraded to v1.14.22 and v1.14.17 with no change in test results.

Any guidance on why encryption might not be activating under these conditions would be appreciated.

[MattLCE]

Thanks for creating go-sqlite3. I'm still facing issues getting SQLCipher encryption to activate correctly and wanted to provide an update based on further debugging, incorporating suggestions from others.

Summary of Latest Findings:
SQLCipher v4.7.0 Built from Source: Successfully built SQLCipher v4.7.0 from the v4.7.0 tag source using the ./configure flags specified in the SQLCipher README (--with-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_EXTRA_INIT=sqlcipher_extra_init -DSQLITE_EXTRA_SHUTDOWN=sqlcipher_extra_shutdown" LDFLAGS="-lcrypto").
Installation Verified During Build: The postCreateCommand logs confirm make install completed and installed libsqlite3.so, libsqlite3.a, sqlite3.h, and the sqlite3 CLI (which reports as SQLCipher v4.7.0) into /usr/local/lib and /usr/local/bin. ldconfig was run afterwards, and ldconfig -p during the build showed /usr/local/lib/libsqlite3.so.0 correctly registered and prioritized.
Runtime LD_LIBRARY_PATH Set: Added containerEnv: { "LD_LIBRARY_PATH": "/usr/local/lib:${containerEnv:LD_LIBRARY_PATH}" } to devcontainer.json. echo $LD_LIBRARY_PATH in the running container confirms /usr/local/lib is prepended.
CGO Flags Tested:

• Simple dynamic linking (#cgo LDFLAGS: -lsqlcipher)
• Dynamic linking with explicit path (#cgo LDFLAGS: -L/usr/local/lib -lsqlcipher)
• Using pkg-config directive (#cgo pkg-config: sqlite3) along with setting PKG_CONFIG_PATH=/usr/local/lib/pkgconfig during go test (verified sqlite3.pc exists and pkg-config works standalone when path is set).
  Persistent Failure: Despite all the above, running the TestEncryptionEndToEnd test (code provided previously) still results in an unencrypted database file.
  Proof: Reading the first 16 bytes of the file created by the test (os.ReadFile) consistently yields the header 53514c69746520666f726d6174203300 (SQLite format 3\0).
  Symptom: The test fails because db.QueryRow(...).Scan(...) succeeds when using a connection supposedly opened with the wrong key.
  Linker Issue Suspected: Running ldd on the compiled test executable (go test -c ...) shows it's only linked against libc.so.6 and ld-linux-x86-64.so.2, completely missing the expected dependencies on libsqlcipher.so.0 (or libsqlite3.so.0 from /usr/local/lib) and libcrypto.so. This strongly suggests the Go/CGO linker is not correctly embedding the dynamic library dependencies specified by the CGO flags (-lsqlcipher).

Conclusion:
It appears the Go/CGO build process in this environment (Go 1.23.8 on Debian Bookworm base, with SQLCipher v4.7.0 built from source) is failing to properly link against the dynamically provided libsqlcipher.so, even when installation, linker cache (during build), LD_LIBRARY_PATH, and CGO flags seem correct. The PRAGMA key execution doesn't error but also doesn't activate encryption because the wrong library (or no effective crypto library) is being used at runtime.

Could this be related to how Go 1.23 handles CGO linking in this configuration, or is there a known interaction I might be missing?

[MattLCE]

Following up on the previous debugging steps, I have a critical update:

Minimal CGO Test SUCCEEDS: I created a minimal Go program (code below/attached) that bypasses database/sql and go-sqlite3 entirely, using only direct CGO calls (sqlite3_open_v2, sqlite3_exec for PRAGMA key, sqlite3_exec for CREATE TABLE, sqlite3_close, etc.) with the same CGO flags (#cgo CFLAGS: -DSQLITE_HAS_CODEC -I/usr/local/include, #cgo LDFLAGS: -lsqlite3 -lcrypto).

Result: This minimal test successfully creates an encrypted database (file header is not "SQLite format 3") and correctly fails when attempting to read data after re-opening with the wrong key (returns SQLITE_NOTADB error code 26 after internal HMAC check failures). This proves the compiled SQLCipher v4.7.0 library, CGO linking, and runtime library loading (LD_LIBRARY_PATH) are functioning correctly at a basic level.

go-sqlite3 Test STILL FAILS: Running the original test using database/sql and go-sqlite3 (v1.14.28), even with the Open function performing an explicit PRAGMA key and a subsequent write/read check (using a TEMP table), still results in an unencrypted database being created (header is "SQLite format 3") and allows reads with the wrong key.

Conclusion:
The problem appears to be isolated to the go-sqlite3 driver layer or its interaction with database/sql's initialization process. Despite being linked against a correctly built and functional SQLCipher v4 library (proven by the minimal CGO test), the driver is not activating the encryption hooks (sqlcipher_extra_init?) correctly at runtime when the connection is established via sql.Open. The subsequent PRAGMA key command executes without Go-level error but has no effect because encryption isn't active.

Environment Recap:
Go: go version go1.23.8 linux/amd64
mattn/go-sqlite3: v1.14.28
OS/Environment: Debian Bookworm (via mcr.microsoft.com/devcontainers/go:1.23)
SQLCipher: 4.7.0 community (Built from source tag v4.7.0 with correct flags)
LD_LIBRARY_PATH includes /usr/local/lib.

Could there be an issue with how the driver handles SQLITE_EXTRA_INIT, or some other initialization step required for SQLCipher v4 that's being missed when used through database/sql?

cgo_test/main.go

// Filename: main.go
package main

/*
// CGO flags tell Go how to compile/link the C parts.

// CFLAGS: Flags passed to the C compiler.
#cgo CFLAGS: -DSQLITE_HAS_CODEC -I/usr/local/include

// LDFLAGS: Flags passed to the linker.
// Link against libsqlite3 (which IS our SQLCipher v4 build) and libcrypto.
#cgo LDFLAGS: -lsqlite3 -lcrypto

// Include standard C headers needed by our C code below
#include <stdlib.h>
// Include the SQLCipher header file (found via CFLAGS -I)
#include <sqlite3.h> // Header name is still sqlite3.h

// Simple C wrapper functions to make calling from Go easier
// (These remain the same as before)
static inline int sqlcipher_bridge_key(sqlite3 *db, void *pKey, int nKey) {
    return sqlite3_key(db, pKey, nKey);
}
static inline int sqlcipher_bridge_exec(sqlite3 *db, const char *sql) {
    return sqlite3_exec(db, sql, 0, 0, 0);
}
*/
import "C" // This imports the C pseudo-package
import (
	"bytes"
	"fmt"
	"os"
	"unsafe" // Required for Cgo pointer conversions
)

const dbFilename = "cgo_test_vault.db"
const correctKeyHex = "0102030405060708090a0b0c0d0e0f100102030405060708090a0b0c0d0e0f10" // 32 bytes / 64 hex chars
const wrongKeyHex = "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF"

func main() {
	fmt.Println("--- Minimal CGO SQLCipher Test ---")

	// --- Cleanup previous run ---
	_ = os.Remove(dbFilename)
	defer os.Remove(dbFilename) // Cleanup at the end

	// --- 1. Open/Create DB, Key, Write ---
	fmt.Println("\n--- Step 1: Creating and writing to DB ---")
	var db *C.sqlite3 // Pointer to the C sqlite3 struct
	cDbPath := C.CString(dbFilename)
	defer C.free(unsafe.Pointer(cDbPath))

	// Open with Create flag using C API directly
	rc := C.sqlite3_open_v2(cDbPath, &db, C.SQLITE_OPEN_READWRITE|C.SQLITE_OPEN_CREATE, nil)
	if rc != C.SQLITE_OK {
		fmt.Printf("!!! FAIL: sqlite3_open_v2 (create) failed: code %d\n", rc)
		return
	}
	fmt.Println("  sqlite3_open_v2 (create): OK")

	// Key the database using PRAGMA via exec (more reliable)
	cCorrectKeyPragma := C.CString(fmt.Sprintf("PRAGMA key = \"x'%s'\";", correctKeyHex))
	defer C.free(unsafe.Pointer(cCorrectKeyPragma))
	rc = C.sqlcipher_bridge_exec(db, cCorrectKeyPragma) // Use the C wrapper

	if rc != C.SQLITE_OK {
		fmt.Printf("!!! FAIL: PRAGMA key (correct key) failed: code %d\n", rc)
		C.sqlite3_close(db)
		return
	}
	fmt.Println("  PRAGMA key (correct key): OK")

	// Create a table using C API directly
	cCreateSQL := C.CString("CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT);")
	defer C.free(unsafe.Pointer(cCreateSQL))
	rc = C.sqlcipher_bridge_exec(db, cCreateSQL) // Use the C wrapper
	if rc != C.SQLITE_OK {
		fmt.Printf("!!! FAIL: sqlite3_exec (create table) failed: code %d\n", rc)
		C.sqlite3_close(db)
		return
	}
	fmt.Println("  sqlite3_exec (create table): OK")

	// Close the database using C API directly
	rc = C.sqlite3_close(db)
	if rc != C.SQLITE_OK {
		fmt.Printf("!!! WARN: sqlite3_close after write failed: code %d\n", rc)
	} else {
		fmt.Println("  sqlite3_close: OK")
	}

	// --- 2. Verify Header of Created File ---
	fmt.Println("\n--- Step 2: Verifying file header ---")
	fileBytes, readErr := os.ReadFile(dbFilename)
	if readErr != nil {
		fmt.Printf("!!! FAIL: Could not read DB file '%s': %v\n", dbFilename, readErr)
		return
	}
	if len(fileBytes) < 16 {
		fmt.Printf("!!! FAIL: DB file '%s' is too short (< 16 bytes)\n", dbFilename)
		return
	}
	header := fileBytes[:16]
	expectedUnencryptedHeader := []byte("SQLite format 3\000")
	fmt.Printf("  Actual file header bytes: %x\n", header)
	if bytes.Equal(header, expectedUnencryptedHeader) {
		fmt.Println("  !!! DIAGNOSTIC: File header IS UNENCRYPTED !!!")
	} else {
		fmt.Println("  --- DIAGNOSTIC: File header is NOT plain SQLite - Likely Encrypted ---")
	}

	// --- 3. Reopen with WRONG Key and Attempt Read ---
	fmt.Println("\n--- Step 3: Reopening with WRONG key ---")
	// Open existing file (no create flag)
	rc = C.sqlite3_open_v2(cDbPath, &db, C.SQLITE_OPEN_READWRITE, nil)
	if rc != C.SQLITE_OK {
		fmt.Printf("  sqlite3_open_v2 (reopen) failed: code %d (This might be expected if header encrypted)\n", rc)
		return // Can't proceed if open fails
	}
	fmt.Println("  sqlite3_open_v2 (reopen): OK (Opened file handle)")

	// Try to key with WRONG key using PRAGMA via exec
	cWrongKeyPragma := C.CString(fmt.Sprintf("PRAGMA key = \"x'%s'\";", wrongKeyHex))
	defer C.free(unsafe.Pointer(cWrongKeyPragma))
	rc = C.sqlcipher_bridge_exec(db, cWrongKeyPragma) // Use the C wrapper

	if rc != C.SQLITE_OK {
		// *** EXPECTED SUCCESS PATH 1: Keying failed ***
		fmt.Printf("  ---> SUCCESS: PRAGMA key (wrong key) failed as expected: code %d\n", rc)
		C.sqlite3_close(db)
		fmt.Println("\n--- TEST SUCCEEDED (Encryption likely working at CGO level) ---")
		return
	}
	// If keying didn't immediately fail, proceed (this is unexpected but possible)
	fmt.Println("  !!! WARN: PRAGMA key (wrong key) SUCCEEDED (Returned SQLITE_OK) - Checking if read fails...")

	// Try to read (THIS MUST FAIL if encryption is truly active)
	cReadSQL := C.CString("SELECT count(*) FROM secrets;")
	defer C.free(unsafe.Pointer(cReadSQL))
	rc = C.sqlcipher_bridge_exec(db, cReadSQL) // Use the C wrapper

	if rc != C.SQLITE_OK {
		// *** EXPECTED SUCCESS PATH 2: Read after wrong key failed ***
		fmt.Printf("  ---> SUCCESS: sqlite3_exec (read with wrong key) failed as expected: code %d\n", rc)
		fmt.Println("\n--- TEST SUCCEEDED (Encryption likely working at CGO level) ---")
	} else {
		// *** FAILURE PATH ***
		fmt.Printf("  !!! FAIL: sqlite3_exec (read with wrong key) SUCCEEDED (Returned SQLITE_OK)\n")
		fmt.Println("\n--- TEST FAILED (Encryption NOT working at CGO level) ---")
	}
	C.sqlite3_close(db)
}

internal/sqlite/securedb.go

//go:build cgo

package sqlite

/*
#cgo CFLAGS: -DSQLITE_HAS_CODEC -I/usr/local/include
// Use the library name found by ls and link crypto
#cgo LDFLAGS: -lsqlite3 -lcrypto
*/
import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
)

// Open returns an encrypted handle. Includes write/read check for key verification.
func Open(path string, key []byte) (*sql.DB, error) {
	// DSN without key parameter.
	dsn := fmt.Sprintf(
		"file:%s?_pragma_cipher_page_size=4096&_busy_timeout=10000",
		path,
	)

	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, fmt.Errorf("sql open failed: %w", err)
	}

	// --- Explicitly execute PRAGMA key AFTER open ---
	pragmaKeySQL := fmt.Sprintf("PRAGMA key = \"x'%x'\";", key)
	if _, err := db.Exec(pragmaKeySQL); err != nil {
		// This might catch *some* immediate errors, but not guaranteed for wrong key
		_ = db.Close()
		return nil, fmt.Errorf("failed to execute PRAGMA key: %w", err)
	}
	// --- End Explicit PRAGMA ---

	// --- Verification Step: Write and Read via Temp Table ---
	// This forces interaction with encrypted data pages and *should* fail if the PRAGMA key was wrong.
	tx, err := db.Begin()
	if err != nil {
		// If Begin fails, the key might be wrong or DB corrupt/inaccessible
		_ = db.Close()
		return nil, fmt.Errorf("failed to begin verification transaction (check key/db): %w", err)
	}
	// Ensure rollback happens if any step below fails
	defer tx.Rollback() // Rollback is a no-op if Commit succeeds

	// Create a temporary table (exists only for this connection)
	_, err = tx.Exec(`CREATE TEMP TABLE _key_verify (id INTEGER PRIMARY KEY, check_val INTEGER)`)
	if err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to create temp verification table (check key/db): %w", err)
	}

	// Insert a value - requires encrypting the data page.
	_, err = tx.Exec(`INSERT INTO _key_verify (check_val) VALUES (1)`)
	if err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to insert into temp verification table (check key/db): %w", err)
	}

	// Read the value back - requires decrypting the data page.
	var checkVal int
	err = tx.QueryRow(`SELECT check_val FROM _key_verify LIMIT 1`).Scan(&checkVal)
	if err != nil {
		// *** THIS is where the error SHOULD occur if the wrong key was used ***
		_ = db.Close()
		return nil, fmt.Errorf("failed to read from temp verification table (check key/db): %w", err)
	}

	// If we read back *something*, compare it (sanity check)
	if checkVal != 1 {
		_ = db.Close()
		return nil, fmt.Errorf("verification read back unexpected value (%d != 1)", checkVal)
	}

	// If all steps succeeded, commit the transaction
	if err = tx.Commit(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to commit verification transaction: %w", err)
	}
	// --- End Verification Step ---

	// If we got here, the key worked for read/write operations.
	return db, nil
}

internal/sqlite/securedb_test.go
package sqlite

import (
	"bytes"
	"fmt"
	"os" // Import os for file reading
	"path/filepath"
	"testing"

	"github.com/n1/n1/internal/crypto"
	"github.com/stretchr/testify/require"
)

// --- Keep your existing Open function in securedb.go (Simplified version above) ---

func TestEncryptionEndToEnd(t *testing.T) {
	tmpDir := t.TempDir()
	dbPath := filepath.Join(tmpDir, "vault_e2e.db")
	mk, _ := crypto.Generate(32)
	t.Logf("Database path: %s", dbPath)

	const testData = "this is secret"
	const testID = 1

	// --- 1. Create DB, Write Data with Correct Key ---
	dbWrite, err := Open(dbPath, mk)
	require.NoError(t, err, "E2E: Initial open with correct key failed")
	_, err = dbWrite.Exec(`CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT)`)
	require.NoError(t, err, "E2E: Create table failed")
	_, err = dbWrite.Exec(`INSERT INTO secrets (id, data) VALUES (?, ?)`, testID, testData)
	require.NoError(t, err, "E2E: Insert data failed")
	err = dbWrite.Close()
	require.NoError(t, err, "E2E: Close after write failed")

	// --- DIAGNOSTIC: Check file header by reading raw bytes ---
	t.Logf("--- Reading file header directly ---")
	fileBytes, readErr := os.ReadFile(dbPath)
	if readErr != nil {
		t.Logf("!!! Error reading database file: %v", readErr)
	} else if len(fileBytes) < 16 {
		t.Logf("!!! Database file is too short (< 16 bytes) !!!")
	} else {
		header := fileBytes[:16]
		expectedHeader := []byte("SQLite format 3\000") // Note the null terminator \000
		// Log the actual header bytes found
		t.Logf("Actual file header bytes: %x", header)
		if bytes.Equal(header, expectedHeader) {
			t.Logf("!!! DIAGNOSTIC: File header matches 'SQLite format 3' - DATABASE IS NOT ENCRYPTED !!!")
		} else {
			t.Logf("--- DIAGNOSTIC: File header does not match 'SQLite format 3' - Likely Encrypted (as expected) ---")
		}
	}
	t.Logf("--- Finished file header check ---")
	// --- End DIAGNOSTIC ---

	// --- 2. Attempt to Open with WRONG Key and READ Data ---
	wrongKey, _ := crypto.Generate(32)
	dbReadWrong, openErrWrong := Open(dbPath, wrongKey)
	var readDataWrong string
	var queryErrWrong error
	if dbReadWrong != nil {
		queryErrWrong = dbReadWrong.QueryRow(`SELECT data FROM secrets WHERE id = ?`, testID).Scan(&readDataWrong)
		_ = dbReadWrong.Close()
	} else {
		queryErrWrong = fmt.Errorf("E2E: Open itself failed with wrong key: %w", openErrWrong)
	}
	require.Error(t, queryErrWrong, "E2E: Reading data with WRONG key succeeded, expected an error!")
	t.Logf("E2E: Successfully failed to read with wrong key (as required by test logic): %v", queryErrWrong)

	// --- 3. Open with CORRECT Key and READ Data ---
	dbReadCorrect, err := Open(dbPath, mk)
	require.NoError(t, err, "E2E: Reopen with correct key failed")
	var readDataCorrect string
	err = dbReadCorrect.QueryRow(`SELECT data FROM secrets WHERE id = ?`, testID).Scan(&readDataCorrect)
	require.NoError(t, err, "E2E: Reading data with CORRECT key failed")
	require.Equal(t, testData, readDataCorrect, "E2E: Data read with correct key does not match original data")
	err = dbReadCorrect.Close()
	require.NoError(t, err, "E2E: Close after correct read failed")
}

[MattLCE]

Here's the terminal from building the container and immediately running these tests.

vscode ➜ /workspaces/n1 (main) $ ls -l /usr/local/lib/libsqlite*
/usr/local/bin/sqlite3 --version
echo $LD_LIBRARY_PATH
-rw-r--r-- 1 root root 1597420 Apr 28 17:23 /usr/local/lib/libsqlite3.a
lrwxrwxrwx 1 root root 20 Apr 28 17:23 /usr/local/lib/libsqlite3.so -> libsqlite3.so.3.49.1
lrwxrwxrwx 1 root root 20 Apr 28 17:23 /usr/local/lib/libsqlite3.so.0 -> libsqlite3.so.3.49.1
-rwxr-xr-x 1 root root 1396472 Apr 28 17:23 /usr/local/lib/libsqlite3.so.3.49.1
3.49.1 2025-02-18 13:38:58 873d4e274b4988d260ba8354a9718324a1c26187a4ab4c1cc0227c03d0f1alt1 (64-bit) (SQLCipher 4.7.0 community)
/usr/local/lib:${containerEnv:LD_LIBRARY_PATH}
vscode ➜ /workspaces/n1 (main) $ cd /workspaces/n1
go clean -cache && go test -v ./...
? github.com/n1/n1/cgo_test [no test files]
? github.com/n1/n1/cmd/bosr [no test files]
? github.com/n1/n1/internal/holdr [no test files]
? github.com/n1/n1/internal/log [no test files]
=== RUN TestGenerate
--- PASS: TestGenerate (0.00s)
PASS
ok github.com/n1/n1/internal/crypto 0.023s
=== RUN TestRoundTrip
--- PASS: TestRoundTrip (0.00s)
PASS
ok github.com/n1/n1/internal/secretstore 0.010s
=== RUN TestEncryptionEndToEnd
securedb_test.go:20: Database path: /tmp/TestEncryptionEndToEnd3521660607/001/vault_e2e.db
securedb_test.go:36: --- Reading file header directly ---
securedb_test.go:46: Actual file header bytes: 53514c69746520666f726d6174203300
securedb_test.go:48: !!! DIAGNOSTIC: File header matches 'SQLite format 3' - DATABASE IS NOT ENCRYPTED !!!
securedb_test.go:53: --- Finished file header check ---
securedb_test.go:67:
Error Trace: /workspaces/n1/internal/sqlite/securedb_test.go:67
Error: An error is expected but got nil.
Test: TestEncryptionEndToEnd
Messages: E2E: Reading data with WRONG key succeeded, expected an error!
--- FAIL: TestEncryptionEndToEnd (0.11s)
FAIL
FAIL github.com/n1/n1/internal/sqlite 0.129s
FAIL
vscode ➜ /workspaces/n1 (main) $ cd /workspaces/n1/cgo_test
go run main.go
--- Minimal CGO SQLCipher Test ---

--- Step 1: Creating and writing to DB ---
sqlite3_open_v2 (create): OK
PRAGMA key (correct key): OK
sqlite3_exec (create table): OK
sqlite3_close: OK

--- Step 2: Verifying file header ---
Actual file header bytes: 2f4d59844333a7e3afc84958f3e80af6
--- DIAGNOSTIC: File header is NOT plain SQLite - Likely Encrypted ---

--- Step 3: Reopening with WRONG key ---
sqlite3_open_v2 (reopen): OK (Opened file handle)
!!! WARN: PRAGMA key (wrong key) SUCCEEDED (Returned SQLITE_OK) - Checking if read fails...
2025-04-28 17:30:55.498: ERROR CORE sqlcipher_page_cipher: hmac check failed for pgno=1
2025-04-28 17:30:55.499: ERROR CORE sqlite3Codec: error decrypting page 1 data: 1
2025-04-28 17:30:55.499: ERROR CORE sqlcipher_codec_ctx_set_error 1
---> SUCCESS: sqlite3_exec (read with wrong key) failed as expected: code 26

--- TEST SUCCEEDED (Encryption likely working at CGO level) ---
vscode ➜ /workspaces/n1/cgo_test (main) $

[MattLCE]

Further testing provides more specific insight, isolating the issue to reopening databases with the libsqlite3 tag.

Summary:
Minimal CGO Test Success: As previously reported, a minimal Go program using direct CGO calls (bypassing go-sqlite3 and database/sql) successfully creates encrypted databases with SQLCipher v4.7.0 and correctly handles key validation (fails reads with wrong key). This confirms the underlying library build, installation, CGO linking (#cgo LDFLAGS: -lsqlite3 -lcrypto), and runtime environment (LD_LIBRARY_PATH) are functional at the basic C API level.
go-sqlite3 with -tags=libsqlite3 Behavior: When using mattn/go-sqlite3 (v1.14.28) built with -tags=libsqlite3:
Initial Create: Calling sql.Open + explicit PRAGMA key = '...' on a non-existent file path successfully creates an encrypted database. Verified by checking the file header, which is not the standard "SQLite format 3".
Reopen Failure (Explicit PRAGMA): Attempting to reopen this same encrypted database using sql.Open + explicit PRAGMA key = '...' (with the correct key) fails. The db.Exec(PRAGMA key...) call returns the error invalid key or database corrupted: file is not a database (SQLITE_NOTADB).
Reopen Failure (DSN Keying): Attempting to reopen the encrypted database using sql.Open with the key in the DSN (_pragma_key=x'...') also fails. The sql.Open call succeeds, but the subsequent db.Ping() fails with the error db ping failed after setting/checking key: file is not a database.

Conclusion:
When built with -tags=libsqlite3 against a custom SQLCipher v4.7.0 library:
Encryption is correctly enabled on initial database creation.
The driver fails to correctly reopen an existing encrypted database, regardless of whether the key is supplied via explicit PRAGMA or the DSN _pragma_key. Both methods result in a "file is not a database" error, even with the correct key.

This strongly suggests an issue within the go-sqlite3 driver's initialization or state management specific to the libsqlite3 build path when dealing with existing encrypted databases, as the direct CGO approach works fine for both creation and reopening.

[samfweb]

This may be relevant: #1109