An experiment in SQLCipher, key management, recovery, and AI-assisted verification, born during the development of a local desktop application.
This content describes a real technical experiment. It is not a security certification, nor a universal guide to cryptographic design.
The goal is to show, transparently and for teaching purposes, how a real problem was tackled and how the AI model took part in the different phases of analysis, development, and verification.
Before getting into the actual experiment, a quick recap of the two models all of modern cryptography is built on. No complex formulas needed: the underlying ideas are enough.
In the symmetric model there's a single key, used both to encrypt and to decrypt. In simplified form:
where is the plaintext, is the ciphertext, is the shared key, and are the encryption and decryption functions.
Whoever holds K can do both. It's the same principle used by , just with a far more robust algorithm than the one in the next example.
A deliberately simple, insecure example, just to see the mechanism: each character of the message is combined with the key. The same key, applied a second time, brings back the original message.
Note: this is an XOR for teaching purposes, not a secure algorithm. AES-256-GCM fixes the weaknesses of such a simple scheme (too-short key, repeating patterns, no authentication) while still being a single-key cipher.
In the asymmetric model there are two different keys, generated together and mathematically linked: a , which can be distributed to anyone, and a , which must stay secret.
Whoever encrypts with the public key can't decrypt their own message: the matching private key is needed. It's the principle behind TLS/HTTPS, digital signatures, and encrypted mail (PGP). Later on this page you can try it with a real interactive example (RSA on small numbers).
A personal accounting application can hold particularly sensitive data: transactions, balances, spending categories, plans, and financial information. The starting requirement was simple to state:
The question behind the experiment: how do you really encrypt a local SQLite database without turning the application into a system dependent on servers, cloud accounts, or an internet connection?
The implemented solution uses several layers, each with a different responsibility: , a random 256-bit master key, , a separate Recovery Key, and + for recovery.
The most important point: there's no single "database password". There's a small key-management architecture.
It's easy to picture encryption like this: password → encrypted database. But one question remains: where is the password kept? If the key were stored right next to the database in plaintext, the encryption would lose most of its value.
The encryption is there, but it doesn't protect against an attacker with filesystem access: the key is right there, next to the file.
The problem splits into two independent layers: encrypting the data, and separately protecting whoever can open that encryption.
Each layer has a precise responsibility and knows nothing about the implementation details of the others.
A random sequence of 32 bytes (256 bits), generated with . It's not derived from a username, PC, Machine ID, or predictable password.
It replaces SQLite's normal behavior with an encrypted database. In the production path the key is applied directly to the connection via sqlite3_key, not via PRAGMA key. A self-check follows with SELECT count(*) FROM sqlite_master;: if the key is wrong, the open fails.
The master key is protected with CryptProtectData, tied to the current Windows user, avoiding storing it in plaintext on the filesystem. Reopening the database uses CryptUnprotectData.
It doesn't encrypt the database: it protects the master key itself, for cases where DPAPI is no longer available or access from a different environment is needed.
The Recovery Key becomes a cryptographic key via (64 MiB, 3 iterations, parallelism 1, 16-byte salt, 32-byte output). That key encrypts the master key with , which also provides authentication: it detects whether the encrypted data has been tampered with.
Simplified excerpts from the code analyzed during the assessment, one for each step of the layered architecture seen above. They're rewritten for teaching clarity, not copied line by line from the repository.
32 random bytes from the operating system's randomness source, not from a third-party library.
use windows::Win32::Security::Cryptography::BCryptGenRandom;
let mut master_key = [0u8; 32];
BCryptGenRandom(None, &mut master_key, BCRYPT_USE_SYSTEM_PREFERRED_RNG)?;
// master_key now holds 256 bits of operating-system entropyThe highlighted line is the only place the master key is created: from here on it's only ever protected, never regenerated.
The key is passed directly to the connection, not via an SQL command โ and is immediately verified with a self-check.
ffi::sqlite3_key(
connection.handle(),
key.as_bytes().as_ptr().cast(),
key.as_bytes().len() as i32,
);
connection.query_row("SELECT count(*) FROM sqlite_master", [], |r| r.get::<_, i64>(0))?;
// if the key is wrong, this query fails: that's the self-checkWithout this self-check, a wrong key would still open a "valid" but unreadable connection โ the error would only surface much later, at a far less obvious point in the code.
use windows::Win32::Security::Cryptography::CryptProtectData;
let protected_blob = CryptProtectData(&master_key_blob, None, None, None, None, flags)?;
fs::write(vault_path, &protected_blob)?;
// the blob is only readable by the current Windows userFrom here on, the file on disk never contains the master key in plaintext: it contains a blob that Windows can only open for the same account that created it.
use argon2::{Argon2, Params};
let params = Params::new(64 * 1024, 3, 1, Some(32))?; // memory in KiB, iterations, parallelism
let argon2 = Argon2::new(argon2::Algorithm::Argon2id, argon2::Version::V0x13, params);
let mut derived_key = [0u8; 32];
argon2.hash_password_into(recovery_key.as_bytes(), &salt, &mut derived_key)?;The highlighted parameters are exactly the ones you can move around in the simulator further down.
use aes_gcm::{Aes256Gcm, Nonce, aead::Aead};
let cipher = Aes256Gcm::new_from_slice(&derived_key)?;
let encrypted_master_key = cipher.encrypt(nonce, master_key.as_ref())?;
// encrypted_master_key also includes the GCM authentication tagIf someone alters even a single byte of encrypted_master_key, the GCM authentication tag makes decryption fail instead of silently returning corrupted data.
This section is a deliberate detour from the database experiment: it's not about SQLCipher, but about the second major cryptography model mentioned above, so it can actually be compared instead of just described.
The classic scenario for understanding it: Alice wants to send a message to Bob over a channel Trudy can intercept. Bob has never met Alice before, so they can't already have agreed on a shared secret key โ the problem symmetric cryptography, on its own, doesn't solve. The message we'll try to transfer is simply a short word written by Alice; in real practice it would more often be a symmetric key to deliver securely, not a long message.
It's not an arbitrary sequence: every step exists for a precise mathematical reason, and this is exactly where the interactive generator below comes in, to also show what happens when a step is broken.
Why primes, and not just any two numbers? Because the whole scheme rests on a task that's easy in one direction and hard in the other: multiplying two primes to get n is immediate; going back from n to the two original factors (factoring) is impractical if the numbers are large enough. With non-prime factors, this asymmetry weakens, or stops holding up the reasoning behind the later steps altogether.
n becomes the public modulus: the base on which all encryption and decryption arithmetic takes place. It can be known by anyone โ it's the secrecy of p and q taken separately, not of n, that protects the scheme.
First, the version without jargon: is simply "how many numbers, counted from 1 up to nโ1, share no common factor with n". Try a small example before reading the formula: pick n below and see which numbers stay green.
The green numbers are the ones coprime with n โ no shared factor. Count them: with n = 15 = 3 ร 5 there are exactly 8, and (3โ1)ร(5โ1) = 2ร4 = 8. That's not a coincidence: when n is the product of two distinct primes, this count always breaks down into that product โ a proven property, not an empirical rule that just happens to work.
Why we need it: there's a theorem (Euler's) stating that raising a number coprime with n to the power ฯ(n) always leaves remainder 1 when divided by n โ a sort of "full loop" in modular arithmetic. Applied twice, once in encryption and once in decryption, it's exactly this identity that brings back the original message. And computing ฯ(n) is easy if you know p and q, but stays as hard as factoring n if you only know n: the same one-way door seen in step 1.
"Coprime" means e and ฯ(n) share no common factor greater than 1 โ their greatest common divisor (GCD) is 1. This matters because in the next step we need to find the multiplicative inverse of e modulo ฯ(n): such an inverse only exists if the two numbers are coprime.
In practice e is often chosen small and fixed โ 65537 is a common value in real RSA, here we use 17 to keep the numbers small โ because it makes encryption faster; it's then checked that it really is coprime with the resulting ฯ(n). If it isn't, another e is tried: it doesn't need any special form, it just has to pass this check.
d is the unique number such that e ร d, divided by ฯ(n), leaves remainder 1 โ in symbols, e ร d โก 1 (mod ฯ(n)). It's computed with the , a variant of the GCD algorithm that also returns the coefficients expressing that GCD as a combination of e and ฯ(n): the inverse is read off directly from there.
This is the step that makes d computable only by whoever knows ฯ(n) โ so only by whoever knows p and q separately, not just their product n.
Full numeric example with p = 61, q = 53:
What travels where: Bob publishes (e, n) โ anyone, including Trudy, can see them. (d, n) stays only with Bob. Alice uses (e, n) to encrypt; only (d, n) can decrypt.
Change p, q and e below. First try two primes different from the example (for instance p = 7, q = 11), then deliberately try to break the scheme with p = 9, q = 11, e = 7 โ 9 is not prime โ and notice that the decrypted message no longer comes back readable, even using "the right key" generated from those numbers.
Honest note: with some non-prime number combinations, the message may still come back readable, by pure arithmetic coincidence โ not because the "you need two primes" rule is wrong, but because small numbers occasionally create special cases. With numbers the size real RSA uses, this coincidence is irrelevant: it's an effect visible only because the numbers here are deliberately small.
n = 3233 ยท ฯ(n) = 3120 ยท gcd(e, ฯ(n)) = 1 โ ยท d = 2753 โ
If you choose a non-prime p or q, the generator still computes n and (pโ1)(qโ1) to show you the result โ but that number isn't the real ฯ(n), so even with the "right" decryption, the message you get back won't be the original one: it's the practical demonstration of step 1. If you choose very small numbers, even with prime p and q the message can degrade because more characters end up sharing the same remainder modulo n โ a limitation of this toy scheme with the ASCII alphabet, not of RSA itself.
There's a question the math above doesn't cover: when Bob sends (e, n) to Alice over that same insecure channel, how can Alice be sure that key really belongs to Bob and not to Trudy, who pretended to be Bob and sent her own pair instead?
RSA alone doesn't answer this question: it guarantees that only whoever holds d can decrypt, but it says nothing about who holds that d. If Trudy manages to convince Alice that her public key is Bob's, Alice will encrypt a message that Trudy can read without any trouble โ an attack called man-in-the-middle.
In practice the problem is solved by binding the public key to an identity through a trusted third party (a digital certificate, signed by a Certificate Authority โ the mechanism behind the browser's HTTPS padlock), or by verifying the key's fingerprint through a different, already-trusted channel.
Why SQLCipher doesn't use this scheme. Setting the authenticity problem aside, RSA encrypts only a few bytes at a time and is orders of magnitude slower than AES on large amounts of data: encrypting an entire database row by row with RSA would be impractical. In real systems (TLS, PGP) the public/private key doesn't encrypt the data itself: it encrypts or securely exchanges a symmetric key, which then does the heavy lifting โ the same role that, in our architecture, is played by DPAPI and Argon2id: they protect the symmetric master key, they don't replace it with a key pair.
A single key. Very fast even across an entire database. It only needs a secure way to hold that key (DPAPI, Recovery Key) โ no channel, no authenticity problem.
Two mathematically linked keys. Useful for exchanging a secret between parties who don't already know each other (TLS, digital signatures), but only if the public key's authenticity is guaranteed by something else (a certificate). Too slow to encrypt the entire contents of a local database.
The Alice/Bob scenario isn't abstract: it's literally what happens every time you open your bank's website. The browser (Alice) has to send sensitive data to the bank (Bob) over a network it treats as insecure by definition.
The key security property, stated precisely: it's not that whoever encrypts "has no secret at all" โ they still have to generate the session key without having it stolen before sending it. The real property is that the public key itself doesn't need to stay secret: the bank could print it on a billboard, anyone could see it, and that wouldn't help anyone decrypt anything. It's the opposite of symmetric cryptography, where whoever obtains the key can both encrypt and decrypt, and so both parties must protect it equally. With public-key cryptography, only one side โ the one holding the private key โ has to protect a secret.
None of these threats are about "RSA's math itself being wrong" โ they're all about how keys are generated, distributed, or used.
Seen above: if the public key's authenticity isn't verified (a fake or unchecked certificate), an intermediary can pass themselves off as the legitimate recipient.
If n is too short, an attacker can work back to p and q and reconstruct the private key on their own. How practical that is depends entirely on the size of n โ that's the topic of the next subsection.
If p and q aren't chosen with a truly unpredictable random number generator, or are too close to each other, mathematical shortcuts exist (like Fermat's factorization method) that are much faster than generic factoring.
Some famous historical attacks (like Bleichenbacher's) don't break RSA's math: they exploit errors in how an implementation handles malformed ciphertexts, to extract information one bit at a time.
They measure hardware side effects โ computation time, power consumption, electromagnetic emissions โ while the private key is in use, to infer its bits without ever factoring anything.
Shor's algorithm, if run on a large enough quantum computer, would factor n quickly. No quantum computer that exists today comes close to this capability on real-size keys โ which is why work on alternative "post-quantum" schemes is already underway.
The simplest way to break RSA starting from just the public key (e, n) is trial division: try dividing n by 2, then by 3, then by 5... until one of the attempts leaves remainder zero. The first one that works is p; from there q = n / p comes for free. It's also the slowest method possible: real attacks use much smarter algorithms โ in particular the general number field sieve (GNFS) โ but they're still governed by the same underlying idea: the time explodes as the digits of n grow.
Choose an n and press "Factor".
The attempt stops on its own after a few million divisions or a few seconds, so it doesn't lock up the page โ that's already enough to feel the difference between "instant" and "already uncomfortable" while staying on tiny numbers.
No real attacker would use a single processor on numbers this size. The space of possible divisors would be split across many machines, each responsible for a slice โ exactly like what really happened with RSA-768, distributed across hundreds of machines for almost two years. Here you can see it at a tiny scale: same principle, same type of n, but the search is split across several simulated "machines".
Choose how many machines to simulate and press "Start distributed attack".
The machines are simulated in turn inside a single browser (there's no real multi-core here), but each one only works through its own slice of the search space โ just like real independent machines would. That's why the time to find the factor drops as you increase the number of machines: try comparing the time with 1 machine and with 64.
Real RSA keys don't use moduli like these: theirs run to hundreds of decimal digits. Here's what the factorizations actually completed so far tell us, using the fastest algorithm known today โ not trial division:
RSA-100 (100 decimal digits): factored by April 1, 1991, within weeks using the means available at the time.
RSA-155 (512 bits): the factorization required about 4,000 MIPS-years of distributed computation, completed in a few months by coordinating multiple computing centers.
RSA-768 (768 bits, 232 digits): the sieving step alone, on a single processor of the era, would have taken about fifteen hundred years; distributed across hundreds of machines, the entire effort took about two and a half years.
RSA-1024: the same authors who factored RSA-768 estimated that factoring a 1024-bit modulus would be roughly a thousand times harder; to date, no public factorization of a number between RSA-704 and RSA-2048 has ever been completed.
RSA-2048, today's recommended size: the difficulty grows by a factor enormously larger still than RSA-1024 โ well beyond what today's classical computers can tackle in any useful timeframe.
Note: quantum computers, if powerful enough, would change this picture thanks to Shor's algorithm mentioned above among the threats โ which is why research into "post-quantum" schemes is already active today, not in anticipation of some far-off problem.
Argon2id turns the Recovery Key into a cryptographic key by making it "cost" time and memory: the more it costs you to open the database once, the more it costs an attacker to try billions of passwords. Move the controls to see the trade-off โ this is a teaching simulation, not a real security measurement.
Note: the curves used here are simplified for teaching purposes, to show the direction of the trade-off (higher memory and iterations โ slower opening but a costlier attack). They don't represent a real Argon2id benchmark, nor an estimate calibrated against existing attack hardware.
Click the steps to follow what happens in each of the three scenarios.
The application already existed with an unencrypted SQLite database. The process doesn't crudely overwrite the original file.
PRAGMA integrity_check.AI wasn't treated as a function that "just writes code". Separating roles reduces a risk typical of AI-assisted development: asking the same model to implement something and then automatically treating its own implementation as correct.
Helps break the problem down โ protecting the database, protecting the key, handling recovery and migration โ and compare architectural alternatives.
Implements narrowly scoped changes in the repository, in limited sprints: initial analysis โ plan โ limited patch โ test โ verification.
Rereads the code in read-only mode, without changing it, and reconstructs what the code actually does โ distinguishing observed from inferred.
Turns the resulting code into an understandable explanation, like this very page.
Final control always comes back to the code, the tests, and the evidence โ never to the model's word about itself.
Besides writing the code, a separate session was run dedicated purely to verifying it: an AI model reread it in read-only mode โ without changing anything โ with the sole task of reconstructing what the program actually does. This is the "Reviewer" role seen in the previous section; we call this session the assessment.
To keep a session like this from turning an assumption into a fact, every statement that came out of it was classified into one of three categories, depending on how it was established: observed directly in the code, inferred from a library's known behavior, or unverifiable without actually running the program. Filter the entries below to see the difference.
BCryptGenRandom actually generates the master key's 32 bytes โ observed in the source.
The key is applied to the connection with sqlite3_key, not PRAGMA key โ observed in the source.
The self-check runs SELECT count(*) FROM sqlite_master; right after the key is applied.
The way the 32 bytes are passed to sqlite3_key implies a specific internal SQLCipher behavior โ a conclusion based on the library's documented behavior, not observed directly.
The level of protection DPAPI offers depends on the policies of the Windows account in use โ inferred from Microsoft documentation, not measured in this experiment.
The exact version of SQLCipher actually loaded during a specific run requires a runtime measurement.
DPAPI's behavior under a Windows account with particular corporate policies wasn't tested in this experiment.
Did this experiment help you understand how it works?