{"id":356,"date":"2026-11-01T09:50:00","date_gmt":"2026-11-01T09:50:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=356"},"modified":"2026-11-21T11:30:51","modified_gmt":"2026-11-21T11:30:51","slug":"encrypt-sqlite-database-sqlcipher-guide","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/11\/01\/encrypt-sqlite-database-sqlcipher-guide\/","title":{"rendered":"Encrypting SQLite Databases with SQLCipher: A Practical Guide"},"content":{"rendered":"<p class=\"lead\">A standard SQLite database is an open book. Copy the file to another machine, open it in any SQLite tool, and every table is readable &mdash; no password, no permission check. For customer records, credentials, health data or anything else private, that is not good enough. <strong>SQLCipher<\/strong> is the widely used open-source extension that fixes it by encrypting the entire database file. This guide covers how it works, how to open, create, encrypt, decrypt and re-key databases, the version differences that trip people up, and the mistakes that undo the protection.<\/p>\n<h2>What SQLCipher actually encrypts<\/h2>\n<p>SQLCipher is a modified build of SQLite that encrypts at the <strong>page level<\/strong>. Every page of the database file &mdash; tables, indexes, the schema, even the free space &mdash; is encrypted before it is written to disk and decrypted when it is read. Without the key, the file looks like random bytes; there is no readable SQLite header and no visible table names.<\/p>\n<p>With the default SQLCipher 4 settings:<\/p>\n<ul>\n<li>Each page is encrypted with <strong>AES-256<\/strong> using a fresh random initialisation vector.<\/li>\n<li>Each page carries an <strong>HMAC<\/strong> (SHA-512) so tampering or a wrong key is detected instead of silently returning garbage.<\/li>\n<li>Your passphrase is turned into a key with <strong>PBKDF2-HMAC-SHA512 and 256,000 iterations<\/strong>, using a random salt stored in the first 16 bytes of the file. The high iteration count makes guessing passphrases slow.<\/li>\n<li>WAL and journal files are encrypted too, so the companion files do not leak data.<\/li>\n<\/ul>\n<p>Once a connection is keyed, everything works exactly like normal SQLite: same SQL, same PRAGMAs, same file. The one catch is that <strong>only SQLCipher-aware tools can open it<\/strong> &mdash; a stock SQLite build will report &ldquo;file is not a database&rdquo;.<\/p>\n<h2>Opening and creating an encrypted database<\/h2>\n<p>The key is supplied with <code>PRAGMA key<\/code>, and it must be the <strong>first statement<\/strong> after opening the connection. SQLCipher does not actually check the key until the first real read, so the usual pattern is to key the connection and then run a cheap query to verify it:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Open (or create) customers.db, then immediately:\nPRAGMA key = 'correct horse battery staple';\n\n-- Verify the key: fails with \"file is not a database\" if it is wrong\nSELECT count(*) FROM sqlite_master;\n\n-- Confirm you are running a SQLCipher build at all\nPRAGMA cipher_version;<\/code><\/pre>\n<p>Creating a new encrypted database is the same: open a path that does not exist yet, issue <code>PRAGMA key<\/code>, then create your tables. The first write produces an encrypted file.<\/p>\n<p>If your application manages its own key material, you can skip the passphrase derivation and pass a raw 256-bit key as hex:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- 64 hex characters = 32 bytes; no PBKDF2 step\nPRAGMA key = \"x'2DD29CA851E7B56E4697B0E1F08507293D761A05CE4D1B628663F411A8086D99'\";<\/code><\/pre>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Open Encrypted Databases with a Password Prompt<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\"><strong>SQLiteDesk<\/strong> is SQLCipher-aware out of the box: open an encrypted <code>.db<\/code> and it simply asks for the password, then you browse, edit and query it like any other database. Passwords are never persisted &mdash; not in settings, not in logs &mdash; and encrypted databases are never prompted for at startup; their saved query tabs simply attach when you open them.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>SQLCipher 3 vs SQLCipher 4<\/h2>\n<p>The single most common &ldquo;my password is right but it will not open&rdquo; problem is a version mismatch. SQLCipher 4 changed its defaults, so a database created with SQLCipher 3 will not open in a SQLCipher 4 build with default settings, and vice versa:<\/p>\n<table>\n<thead>\n<tr>\n<th>Setting<\/th>\n<th>SQLCipher 3 default<\/th>\n<th>SQLCipher 4 default<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Page size<\/td>\n<td>1024 bytes<\/td>\n<td>4096 bytes<\/td>\n<\/tr>\n<tr>\n<td>KDF iterations<\/td>\n<td>64,000<\/td>\n<td>256,000<\/td>\n<\/tr>\n<tr>\n<td>KDF algorithm<\/td>\n<td>PBKDF2-HMAC-SHA1<\/td>\n<td>PBKDF2-HMAC-SHA512<\/td>\n<\/tr>\n<tr>\n<td>Page HMAC<\/td>\n<td>HMAC-SHA1<\/td>\n<td>HMAC-SHA512<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>You have two options. Open the old database in compatibility mode for the session, or permanently upgrade it to the new format in place:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Option 1: read a SQLCipher 3 database without changing it\nPRAGMA key = 'secret';\nPRAGMA cipher_compatibility = 3;\nSELECT count(*) FROM sqlite_master;\n\n-- Option 2: upgrade it to SQLCipher 4 format (back it up first!)\nPRAGMA key = 'secret';\nPRAGMA cipher_migrate;          -- returns 0 on success<\/code><\/pre>\n<p>Individual settings such as <code>cipher_page_size<\/code> and <code>kdf_iter<\/code> can also be set by hand, but every tool that opens the file must then use exactly the same values. Unless you have a reason, stick to the defaults.<\/p>\n<h2>Encrypting an existing plain database<\/h2>\n<p>You cannot encrypt a plaintext database in place with <code>PRAGMA rekey<\/code>. Instead, open the plain database <em>without<\/em> a key, attach a new encrypted database, and copy everything across with <code>sqlcipher_export()<\/code>:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Connected to plain.db (no PRAGMA key)\nATTACH DATABASE 'encrypted.db' AS encrypted KEY 'new passphrase';\nSELECT sqlcipher_export('encrypted');\nDETACH DATABASE encrypted;<\/code><\/pre>\n<p>Two details are easy to miss. First, <code>sqlcipher_export()<\/code> copies schema and data but <strong>not the <code>user_version<\/code><\/strong> header value many apps use to track schema migrations &mdash; read it from the source and set <code>PRAGMA encrypted.user_version<\/code> yourself before detaching. Second, check the new file opens with the key before you delete the original.<\/p>\n<h2>Decrypting and changing the password<\/h2>\n<p>Decrypting is the same trick in reverse: open the encrypted database with its key and export into an attached database with an <strong>empty<\/strong> key.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>PRAGMA key = 'current passphrase';\nATTACH DATABASE 'plain.db' AS plaintext KEY '';\nSELECT sqlcipher_export('plaintext');\nDETACH DATABASE plaintext;<\/code><\/pre>\n<p>To change the passphrase of an encrypted database, key it with the current passphrase and then issue <code>PRAGMA rekey<\/code>. This rewrites every page, so on a large database it takes a while, and it should not be interrupted:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>PRAGMA key = 'old passphrase';\nPRAGMA rekey = 'new passphrase';<\/code><\/pre>\n<p><code>rekey<\/code> only changes the key. To change cipher settings such as page size or iteration count, export into a new database configured the way you want.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Encrypt, Decrypt and Change the Password from a Menu<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Skip the ATTACH-and-export ritual. In <strong>SQLiteDesk<\/strong> you can <strong>encrypt, decrypt or change the password<\/strong> on any existing database from the Maintenance menu. The rewritten file is verified before it replaces the original, so an interrupted operation never costs you your data. New passwords are typed twice, and the database&rsquo;s <code>user_version<\/code>, <code>application_id<\/code> and journal mode are preserved.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Mistakes that undo the encryption<\/h2>\n<p>Strong encryption on disk does nothing if the passphrase leaks somewhere else. These are the usual culprits:<\/p>\n<table>\n<thead>\n<tr>\n<th>Mistake<\/th>\n<th>Why it hurts<\/th>\n<th>Better<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Passphrase hard-coded in the app<\/td>\n<td>Anyone with the program can extract it<\/td>\n<td>Derive from user input or the OS credential store<\/td>\n<\/tr>\n<tr>\n<td><code>PRAGMA key<\/code> saved in query history or logs<\/td>\n<td>The secret sits in plain text next to the database<\/td>\n<td>Use tools that keep keys out of history<\/td>\n<\/tr>\n<tr>\n<td>Passphrase in command-line arguments<\/td>\n<td>Visible in shell history and process lists<\/td>\n<td>Prompt for it interactively<\/td>\n<\/tr>\n<tr>\n<td>Exporting a decrypted copy &ldquo;temporarily&rdquo;<\/td>\n<td>Plain copies get forgotten, synced or backed up<\/td>\n<td>Decrypt only when you truly must, then delete<\/td>\n<\/tr>\n<tr>\n<td>Weak or short passphrase<\/td>\n<td>PBKDF2 slows guessing, it does not stop it<\/td>\n<td>Long passphrases or random raw keys<\/td>\n<\/tr>\n<tr>\n<td>No record of the passphrase<\/td>\n<td>There is no recovery &mdash; lost key means lost data<\/td>\n<td>Store it in a password manager<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Also remember what encryption at rest does <em>not<\/em> do. While your app has the database open and keyed, the data is decrypted in memory, and anything running as the same user may be able to reach it. SQLCipher protects copied files, lost laptops and stray backups &mdash; not a compromised running machine.<\/p>\n<h2>Backing up encrypted databases<\/h2>\n<p>Good news: a copy of an encrypted database is still encrypted, so backups are safe to store on external drives or shared storage. The same backup rules as plain SQLite apply &mdash; avoid copying the file while it is being written, and prefer an online backup. Test a restore occasionally, and test it with the passphrase as you have it recorded, not as you remember it.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Encrypted Backups, No Leaked Keys<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">In <strong>SQLiteDesk<\/strong>, backup and restore work on encrypted databases too, with the option to re-encrypt the result. SQLCipher keys never reach your query history, and the app runs 100% offline &mdash; no telemetry, no cloud sync, no accounts. Available on the Microsoft Store with a free trial, no subscription and no ads.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>Plain SQLite files are <strong>not encrypted<\/strong>; SQLCipher encrypts every page, including the schema and the WAL.<\/li>\n<li>Open with <code>PRAGMA key<\/code> as the <strong>first statement<\/strong>, then verify with a query on <code>sqlite_master<\/code>.<\/li>\n<li>&ldquo;File is not a database&rdquo; with the right password usually means a <strong>SQLCipher 3 vs 4<\/strong> mismatch &mdash; use <code>cipher_compatibility<\/code> or <code>cipher_migrate<\/code>.<\/li>\n<li>Encrypt or decrypt existing files with <code>ATTACH<\/code> + <code>sqlcipher_export()<\/code> (and copy <code>user_version<\/code> yourself); change passwords with <code>PRAGMA rekey<\/code>.<\/li>\n<li>Protect the passphrase as carefully as the data: no hard-coding, no logs, no query history, and keep it somewhere you can recover it.<\/li>\n<li><strong>SQLiteDesk<\/strong> opens SQLCipher databases with a password prompt and encrypts, decrypts and changes passwords from its Maintenance menu, verifying the result before replacing the original.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>A plain SQLite file can be read by anyone who can copy it. This guide shows how SQLCipher encrypts the whole database, how to open one with PRAGMA key, how to encrypt, decrypt and re-key existing databases, how SQLCipher 3 and 4 differ, and the mistakes that leak the passphrase anyway.<\/p>\n","protected":false},"author":1,"featured_media":419,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8],"tags":[36,32,33],"class_list":["post-356","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlite","category-tutorials","tag-sqlcipher","tag-sqlite","tag-sqlitedesk"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=356"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/356\/revisions"}],"predecessor-version":[{"id":420,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/356\/revisions\/420"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/419"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}