Why a Green Test Suite Can't Prove Your Database Is Encrypted

The claim said encrypted. The file said otherwise.

Our store disclosure for a Flutter ledger app stated that the on-device SQLite database — the embedded file where the app keeps everything locally — was encrypted at rest, meaning encrypted while sitting on disk. We pulled the actual file off a real device to check. It opened as plaintext. The first bytes read SQLite format 3, and the contents were legible with no key at all.

The unit tests were green the whole time. That is the part worth your attention, because it is not a story about one careless line. Three independent defects were stacked on top of each other, and the test harness was structurally incapable of surfacing any of them. If you ship a security or compliance claim on the strength of a passing suite, this is the shape of the gap you are trusting.

Defect one: a PRAGMA that accepts the wrong thing without complaining

To key an encrypted SQLite connection you run PRAGMA key = ... with the key material. We passed a hex blob literal, x'...'. That is invalid syntax here: a PRAGMA value can only be an identifier, a string, or a number — never an expression, which is what a blob literal is. The correct form wraps the hex in double quotes so it arrives as a string.

So the statement should have errored. Instead, drift — the ORM we use over SQLite — swallowed the exception inside its setup callback. The app shrugged and carried on against an unkeyed, plaintext database. No crash, no log that mattered, no failing test. An API that silently accepts malformed input is one of the most reliable ways for a system to quietly diverge from what you believe it is doing.

Defect two: two code paths opening the same file

Only one of them was fixed. The app’s database provider got updated to key the connection properly. But a second path ran before runApp() — it opened the raw file early to read setup-completion state for routing, deciding which screen to show first. That path was never updated. It created the file unencrypted, and everything downstream simply opened the file that already existed.

A codebase with two places that “might” open the same resource is a classic divergence point. Fix one, feel done, and the other keeps writing plaintext.

Defect three: rekey corrupts a WAL database

This one would have bitten us even if the first two were clean. On a WAL-mode database — write-ahead logging, which every real install uses — PRAGMA rekey does not properly convert the file. It leaves the plaintext header in place while writing re-encrypted pages elsewhere, which corrupts the database outright. The migration path we would have reached for to encrypt existing user data was itself destructive on exactly the configuration every device runs.

Why the tests could never have caught this

Here is the trap in one sentence: flutter test runs a materially different SQLite than production does.

The host test environment has no encryption-capable SQLite at all — there is no extension present to refuse an unkeyed open or to care about a key. And it defaults to journal_mode=delete, not WAL. So the false-encryption bug had nothing to assert against, and the rekey-corruption bug could not appear because the host never runs the mode that triggers it. A suite running that environment can be green forever while the shipped artifact does the opposite of what it claims.

What caught all three was an on-device integration test using the real libsqlite3mc.so — the actual encryption-capable library the app ships with — against a file pulled from a real device.

The part that generalises

A passing suite tells you the code around a behaviour works. It tells you nothing about the behaviour itself when the harness swaps in a different implementation than production uses. Two specifics are worth carrying into any project:

• A security or compliance claim made to a third party — an app store, a customer, an auditor — has to be verified against the shipped artifact on the actual target. Not inferred from unit tests of the surrounding code. The cheapest honest check is to produce the file the way a user would and inspect it directly.
• Silent-acceptance APIs and duplicate resource paths are where systems lie to you. A PRAGMA that parses the wrong thing without erroring, an ORM callback that eats an exception, a second code path that opens the file early — each one individually is subtle. Stacked, they produce a database that reports encrypted and stores plaintext, with every light green.

We test on-device for this class of claim now. If the thing you are asserting depends on a native library or a disk-level mode, assert it where that library and that mode actually run.