Skip to main content

weirdnet_storage/
db.rs

1//! Opening, keying, rekeying, converting, migrating and copying SQLCipher databases.
2use crate::error::{Result, StorageError};
3use crate::files::{create_private, ensure_dir, set_mode, sync_parent};
4use rusqlite::{Connection, OpenFlags, Transaction};
5use std::path::Path;
6use zeroize::Zeroizing;
7
8/// A raw SQLCipher key (the caller's data-encryption key). Applied as-is, never derived.
9pub type RawKey = [u8; 32];
10
11const BUSY: std::time::Duration = std::time::Duration::from_secs(5);
12const PLAINTEXT_HEADER: &[u8; 16] = b"SQLite format 3\0";
13
14/// `x'<hex>'`, built in place so no unzeroized copy of the key is left behind.
15fn key_literal(k: &RawKey) -> Zeroizing<String> {
16    const HEX: &[u8; 16] = b"0123456789abcdef";
17    let mut s = Zeroizing::new(String::with_capacity(2 + 64 + 1));
18    s.push_str("x'");
19    for b in k {
20        s.push(HEX[(b >> 4) as usize] as char);
21        s.push(HEX[(b & 0x0f) as usize] as char);
22    }
23    s.push('\'');
24    s
25}
26
27/// Whether this SQLite is SQLCipher (a key would otherwise be ignored).
28pub fn has_cipher(conn: &Connection) -> bool {
29    conn.query_row("PRAGMA cipher_version", [], |r| r.get::<_, String>(0)).is_ok()
30}
31
32fn require_cipher(conn: &Connection) -> Result<()> {
33    if has_cipher(conn) { Ok(()) } else { Err(StorageError::NoCipher) }
34}
35
36/// Apply a raw key (`PRAGMA key = "x'…'"`); SQLCipher's own logging stays off stderr.
37pub fn apply_key(conn: &Connection, key: Option<&RawKey>) -> Result<()> {
38    let _ = conn.query_row("PRAGMA cipher_log_level = NONE", [], |_| Ok(()));
39    if let Some(k) = key {
40        require_cipher(conn)?;
41        conn.pragma_update(None, "key", key_literal(k).as_str())?;
42    }
43    Ok(())
44}
45
46fn check_key(conn: &Connection, what: &str) -> Result<()> {
47    conn.query_row("SELECT count(*) FROM sqlite_master", [], |r| r.get::<_, i64>(0))
48        .map(|_| ())
49        .map_err(|e| StorageError::WrongKey { what: what.to_string(), detail: e.to_string() })
50}
51
52fn is_plaintext_file(path: &Path) -> Result<bool> {
53    use std::io::Read;
54    let mut head = [0u8; 16];
55    let mut f = std::fs::File::open(path)?;
56    let n = f.read(&mut head)?;
57    Ok(n == 16 && &head == PLAINTEXT_HEADER)
58}
59
60/// Re-encrypt an open, keyed database with a new raw key (`PRAGMA rekey`).
61///
62/// SQLCipher cannot rekey in WAL mode, so the journal is switched to rollback (DELETE) mode
63/// first. The rekey runs as one SQLCipher transaction: if it fails the file keeps its old
64/// key. SQLCipher can report success without rekeying, so for a file the result is checked
65/// on a fresh connection: a plaintext file (rekey cannot encrypt one) is
66/// [`StorageError::NotEncrypted`], and a file the new key does not open is
67/// [`StorageError::RekeyFailed`].
68pub fn rekey(conn: &Connection, new_key: &RawKey) -> Result<()> {
69    require_cipher(conn)?;
70    conn.query_row("PRAGMA journal_mode = DELETE", [], |_| Ok(()))?;
71    conn.pragma_update(None, "rekey", key_literal(new_key).as_str())?;
72    conn.query_row("SELECT count(*) FROM sqlite_master", [], |r| r.get::<_, i64>(0))?;
73    if let Some(p) = conn.path().filter(|p| !p.is_empty()) {
74        let p = Path::new(p);
75        if is_plaintext_file(p)? {
76            return Err(StorageError::NotEncrypted);
77        }
78        let check = Connection::open_with_flags(p, OpenFlags::SQLITE_OPEN_READ_ONLY)?;
79        apply_key(&check, Some(new_key))?;
80        check_key(&check, "rekeyed").map_err(|_| StorageError::RekeyFailed)?;
81    }
82    Ok(())
83}
84
85/// Open (creating if needed, with a private parent folder and a file that is 0600 from the
86/// start) and verify the key. `what` names the database in the wrong-key error. A file this
87/// call created is removed again if opening fails.
88pub fn open(path: &Path, key: Option<&RawKey>, what: &str) -> Result<Connection> {
89    if let Some(parent) = path.parent()
90        && !parent.as_os_str().is_empty()
91    {
92        ensure_dir(parent)?;
93    }
94    let created = !path.exists();
95    if created {
96        drop(create_private(path)?);
97    }
98    let r = (|| {
99        let conn = Connection::open(path)?;
100        apply_key(&conn, key)?;
101        conn.busy_timeout(BUSY)?;
102        check_key(&conn, what)?;
103        Ok(conn)
104    })();
105    if r.is_err() && created {
106        let _ = std::fs::remove_file(path);
107    }
108    r
109}
110
111/// Open an existing database read-only (no writes, no migrations) and verify the key.
112pub fn open_readonly(path: &Path, key: Option<&RawKey>, what: &str) -> Result<Connection> {
113    let conn = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY)?;
114    apply_key(&conn, key)?;
115    conn.busy_timeout(BUSY)?;
116    check_key(&conn, what)?;
117    Ok(conn)
118}
119
120/// A private in-memory database (tests, scratch work).
121pub fn open_in_memory() -> Result<Connection> {
122    Ok(Connection::open_in_memory()?)
123}
124
125/// Open someone else's SQLite file read-only (URIs allowed), with their SQLCipher
126/// passphrase if it has one. The passphrase goes to SQLCipher as given; nothing is derived
127/// here and the key is not checked.
128pub fn open_foreign_readonly(path: &Path, passphrase: Option<&str>) -> Result<Connection> {
129    let conn = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_URI)?;
130    if let Some(k) = passphrase {
131        require_cipher(&conn)?;
132        conn.pragma_update(None, "key", k)?;
133    }
134    Ok(conn)
135}
136
137/// Encrypt an existing plaintext SQLite file in place with `key`.
138///
139/// `sqlcipher_export` writes a sibling file (`path` with the extension `encrypting`, private
140/// from creation), which is reopened with the key and checked to hold as many schema entries
141/// as the original before it is renamed over it; stale WAL/SHM files are removed. Until the
142/// rename the original is untouched, and on any failure the sibling is removed.
143pub fn encrypt_plaintext_in_place(path: &Path, key: &RawKey) -> Result<()> {
144    let tmp = path.with_extension("encrypting");
145    if tmp.exists() {
146        std::fs::remove_file(&tmp)?;
147    }
148    drop(create_private(&tmp)?);
149    let r = (|| -> Result<()> {
150        let objects = {
151            let conn = Connection::open(path)?;
152            let _ = conn.query_row("PRAGMA cipher_log_level = NONE", [], |_| Ok(()));
153            require_cipher(&conn)?;
154            let n: i64 = conn.query_row("SELECT count(*) FROM sqlite_master", [], |r| r.get(0)).map_err(|_| StorageError::NotPlaintext)?;
155            let lit = key_literal(key);
156            let mut sql = Zeroizing::new(String::with_capacity(48 + lit.len()));
157            sql.push_str("ATTACH DATABASE ?1 AS enc KEY \"");
158            sql.push_str(&lit);
159            sql.push('"');
160            conn.execute(sql.as_str(), [tmp.to_string_lossy()])?;
161            conn.query_row("SELECT sqlcipher_export('enc')", [], |_| Ok(()))?;
162            conn.execute("DETACH DATABASE enc", [])?;
163            n
164        };
165        let enc = Connection::open_with_flags(&tmp, OpenFlags::SQLITE_OPEN_READ_ONLY)?;
166        apply_key(&enc, Some(key))?;
167        let m: i64 = enc.query_row("SELECT count(*) FROM sqlite_master", [], |r| r.get(0))?;
168        drop(enc);
169        if m != objects || is_plaintext_file(&tmp)? {
170            return Err(StorageError::invalid("conversion", "the encrypted copy does not match the original"));
171        }
172        Ok(())
173    })();
174    if let Err(e) = r {
175        let _ = std::fs::remove_file(&tmp);
176        return Err(e);
177    }
178    if let Some(ext) = path.extension().and_then(|e| e.to_str()) {
179        for side in ["wal", "shm"] {
180            let _ = std::fs::remove_file(path.with_extension(format!("{ext}-{side}")));
181        }
182    }
183    std::fs::rename(&tmp, path)?;
184    set_mode(path, 0o600)?;
185    sync_parent(path);
186    Ok(())
187}
188
189/// Apply `migrations[..upto]` not yet recorded in `schema_version`, all in one transaction
190/// (any failure leaves the database as it was). `applied(tx, i)` runs right after migration
191/// `i` (0-based) and before it is recorded: data upgrades and seed rows go there.
192///
193/// Every call also sets the pragma policy: foreign keys on, WAL, `synchronous = NORMAL`.
194pub fn migrate<E: From<StorageError>>(
195    conn: &mut Connection,
196    migrations: &[&str],
197    upto: usize,
198    mut applied: impl FnMut(&Transaction, usize) -> std::result::Result<(), E>,
199) -> std::result::Result<(), E> {
200    let sql = |r: rusqlite::Result<()>| r.map_err(|e| E::from(StorageError::from(e)));
201    sql(conn.execute_batch("PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;"))?;
202    sql(conn.execute_batch("CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL);"))?;
203    let v: i64 =
204        conn.query_row("SELECT COALESCE(MAX(version), 0) FROM schema_version", [], |r| r.get(0)).map_err(|e| E::from(StorageError::from(e)))?;
205    let tx = conn.transaction().map_err(|e| E::from(StorageError::from(e)))?;
206    for (i, m) in migrations.iter().enumerate().take(upto) {
207        let target = i as i64 + 1;
208        if v < target {
209            sql(tx.execute_batch(m))?;
210            applied(&tx, i)?;
211            tx.execute("INSERT INTO schema_version(version) VALUES (?1)", [target]).map_err(|e| E::from(StorageError::from(e)))?;
212        }
213    }
214    tx.commit().map_err(|e| E::from(StorageError::from(e)))?;
215    Ok(())
216}
217
218/// The highest applied migration (`schema_version`).
219pub fn schema_version(conn: &Connection) -> Result<i64> {
220    Ok(conn.query_row("SELECT MAX(version) FROM schema_version", [], |r| r.get(0))?)
221}
222
223/// Copy an open database to `dest` with SQLite's online backup, keyed with `key` (an
224/// encrypted source stays encrypted). `dest` is replaced, private from creation, and removed
225/// again if the copy fails.
226pub fn snapshot_to(conn: &Connection, dest: &Path, key: Option<&RawKey>) -> Result<()> {
227    if dest.exists() {
228        std::fs::remove_file(dest)?;
229    }
230    drop(create_private(dest)?);
231    let r = (|| -> Result<()> {
232        let mut dst = Connection::open(dest)?;
233        apply_key(&dst, key)?;
234        {
235            let b = rusqlite::backup::Backup::new(conn, &mut dst)?;
236            b.run_to_completion(1024, std::time::Duration::from_millis(0), None)?;
237        }
238        drop(dst);
239        Ok(())
240    })();
241    if r.is_err() {
242        let _ = std::fs::remove_file(dest);
243    }
244    r?;
245    set_mode(dest, 0o600)?;
246    Ok(())
247}
248
249#[cfg(test)]
250mod tests {
251    use super::*;
252
253    const M: &[&str] = &["CREATE TABLE a (x INTEGER);", "CREATE TABLE b (y INTEGER);"];
254
255    fn none(_: &Transaction, _: usize) -> Result<()> {
256        Ok(())
257    }
258
259    #[test]
260    fn key_literal_is_raw_hex() {
261        let mut k = [0u8; 32];
262        k[0] = 0xab;
263        k[31] = 0x01;
264        let l = key_literal(&k);
265        assert_eq!(l.as_str(), format!("x'{}'", hex::encode(k)));
266    }
267
268    #[test]
269    fn encrypted_open_rekey_and_wrong_key() {
270        let t = tempfile::tempdir().unwrap();
271        let p = t.path().join("d").join("x.database");
272        let (k1, k2) = ([1u8; 32], [2u8; 32]);
273        {
274            let mut c = open(&p, Some(&k1), "test").unwrap();
275            assert!(has_cipher(&c));
276            migrate(&mut c, M, M.len(), none).unwrap();
277            c.execute("INSERT INTO a VALUES (7)", []).unwrap();
278        }
279        assert!(!std::fs::read(&p).unwrap().starts_with(b"SQLite format 3"));
280        let e = open(&p, Some(&k2), "test").unwrap_err();
281        assert!(matches!(e, StorageError::WrongKey { .. }));
282        assert!(e.to_string().starts_with("wrong key or not a test database ("), "{e}");
283        {
284            let c = open(&p, Some(&k1), "test").unwrap();
285            rekey(&c, &k2).unwrap();
286        }
287        assert!(open(&p, Some(&k1), "test").is_err());
288        let c = open_readonly(&p, Some(&k2), "test").unwrap();
289        assert_eq!(c.query_row("SELECT x FROM a", [], |r| r.get::<_, i64>(0)).unwrap(), 7);
290        assert_eq!(schema_version(&c).unwrap(), 2);
291        // a read-only connection cannot rekey: reported, and the key stays as it was
292        assert!(matches!(rekey(&c, &k1), Err(StorageError::RekeyFailed)));
293        drop(c);
294        assert!(open(&p, Some(&k2), "test").is_ok());
295    }
296
297    #[test]
298    fn rekey_refuses_to_pretend_a_plaintext_file_is_encrypted() {
299        let t = tempfile::tempdir().unwrap();
300        let p = t.path().join("plain.db");
301        let c = open(&p, None, "test").unwrap();
302        c.execute_batch("CREATE TABLE a (x INTEGER)").unwrap();
303        // SQLCipher either refuses or silently does nothing; either way it is an error here
304        assert!(rekey(&c, &[3u8; 32]).is_err());
305        drop(c);
306        let c = open(&p, None, "test").unwrap();
307        assert_eq!(c.query_row("SELECT count(*) FROM a", [], |r| r.get::<_, i64>(0)).unwrap(), 0);
308    }
309
310    #[test]
311    fn a_failed_first_open_leaves_no_file() {
312        let t = tempfile::tempdir().unwrap();
313        let p = t.path().join("x.database");
314        std::fs::write(&p, b"definitely not a database, longer than a header").unwrap();
315        assert!(open(&p, Some(&[1u8; 32]), "test").is_err());
316        assert!(p.exists(), "an existing file is never removed");
317        let q = t.path().join("missing").join("y.database");
318        assert!(open_readonly(&q, None, "test").is_err());
319        assert!(!q.exists());
320    }
321
322    #[cfg(unix)]
323    #[test]
324    fn new_files_and_folders_are_private() {
325        use std::os::unix::fs::PermissionsExt;
326        let t = tempfile::tempdir().unwrap();
327        let p = t.path().join("d").join("x.database");
328        let c = open(&p, Some(&[1u8; 32]), "test").unwrap();
329        assert_eq!(std::fs::metadata(&p).unwrap().permissions().mode() & 0o777, 0o600);
330        assert_eq!(std::fs::metadata(p.parent().unwrap()).unwrap().permissions().mode() & 0o777, 0o700);
331        let snap = t.path().join("snap.database");
332        snapshot_to(&c, &snap, Some(&[1u8; 32])).unwrap();
333        assert_eq!(std::fs::metadata(&snap).unwrap().permissions().mode() & 0o777, 0o600);
334        assert!(open(&snap, Some(&[1u8; 32]), "test").is_ok());
335        assert!(open(&snap, Some(&[2u8; 32]), "test").is_err());
336    }
337
338    #[test]
339    fn migrations_run_once_in_order_with_hooks() {
340        let mut c = Connection::open_in_memory().unwrap();
341        let mut seen = vec![];
342        migrate(&mut c, M, 1, |_, i| {
343            seen.push(i);
344            Ok::<(), StorageError>(())
345        })
346        .unwrap();
347        migrate(&mut c, M, M.len(), |_, i| {
348            seen.push(i);
349            Ok::<(), StorageError>(())
350        })
351        .unwrap();
352        assert_eq!(seen, [0, 1]);
353        assert_eq!(schema_version(&c).unwrap(), 2);
354    }
355
356    #[test]
357    fn a_failing_migration_or_hook_rolls_back_everything() {
358        let mut c = Connection::open_in_memory().unwrap();
359        migrate(&mut c, M, 1, none).unwrap();
360        let bad: &[&str] = &[M[0], M[1], "CREATE TABLE c (z INTEGER); THIS IS NOT SQL;"];
361        assert!(migrate(&mut c, bad, bad.len(), none).is_err());
362        assert_eq!(schema_version(&c).unwrap(), 1);
363        assert!(!crate::table_exists(&c, "b").unwrap() && !crate::table_exists(&c, "c").unwrap());
364        let hook = migrate(&mut c, M, M.len(), |_, _| Err(StorageError::invalid("test", "hook failed")));
365        assert!(hook.is_err());
366        assert_eq!(schema_version(&c).unwrap(), 1);
367        assert!(!crate::table_exists(&c, "b").unwrap());
368    }
369
370    #[test]
371    fn plaintext_converts_in_place() {
372        let t = tempfile::tempdir().unwrap();
373        let p = t.path().join("x.database");
374        Connection::open(&p).unwrap().execute_batch("CREATE TABLE a (x INTEGER); INSERT INTO a VALUES (3);").unwrap();
375        encrypt_plaintext_in_place(&p, &[5u8; 32]).unwrap();
376        assert!(!p.with_extension("encrypting").exists());
377        assert!(open(&p, None, "test").is_err());
378        let c = open(&p, Some(&[5u8; 32]), "test").unwrap();
379        assert_eq!(c.query_row("SELECT x FROM a", [], |r| r.get::<_, i64>(0)).unwrap(), 3);
380        drop(c);
381        // not plaintext any more: refused, and the encrypted file is untouched
382        let before = std::fs::read(&p).unwrap();
383        assert!(matches!(encrypt_plaintext_in_place(&p, &[5u8; 32]), Err(StorageError::NotPlaintext)));
384        assert_eq!(std::fs::read(&p).unwrap(), before);
385        assert!(!p.with_extension("encrypting").exists());
386        #[cfg(unix)]
387        {
388            use std::os::unix::fs::PermissionsExt;
389            assert_eq!(std::fs::metadata(&p).unwrap().permissions().mode() & 0o777, 0o600);
390        }
391    }
392}