Skip to main content

uqa_storage_sqlite/
detect.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! On-disk database format detection.
8//!
9//! `usql` and embedding applications need to decide which `Engine`
10//! open variant fits an existing file before they have a key in hand:
11//! plaintext `SQLite` and UQA compressed containers carry cleartext
12//! magic bytes, while `SQLCipher` encrypts the whole file (including the
13//! `SQLite` header), so an encrypted catalog is indistinguishable from a
14//! non-database file without attempting a keyed open.
15
16use std::fs::File;
17use std::io::Read;
18use std::path::Path;
19
20use crate::compressed_vfs::{FLAG_ENCRYPTED, HEADER_FLAGS_OFFSET, LEGACY_MAGIC, MAGIC};
21use crate::{
22    ManagedConnection, SQLiteCompressedContainerAnchor, SQLiteCompressionOptions, SQLiteError,
23};
24
25/// First 16 bytes of every plaintext `SQLite` database file.
26const SQLITE_MAGIC: &[u8; 16] = b"SQLite format 3\0";
27
28/// Number of leading bytes required to classify a file: the `SQLite`
29/// magic is 16 bytes and the compressed-container flags word ends at
30/// byte 16 as well.
31const DETECT_PREFIX_LEN: usize = 16;
32
33/// On-disk format of a database file, detected from its header bytes.
34#[derive(Debug, Clone, Copy, PartialEq, Eq)]
35pub enum DatabaseFileFormat {
36    /// The file does not exist or is empty. Opening it creates a new
37    /// database.
38    Missing,
39    /// A plaintext `SQLite` database (`SQLite format 3\0` magic).
40    PlainSQLite,
41    /// A UQA compressed container (`UQACDB2\0`, or legacy `UQACDB1\0`,
42    /// magic). `encrypted` reflects the header flag: when set, opening
43    /// requires a key.
44    CompressedContainer { encrypted: bool },
45    /// No recognizable magic. Either a `SQLCipher`-encrypted database or
46    /// not a database at all; the two cannot be told apart without
47    /// attempting an open with a key.
48    Unrecognized,
49}
50
51impl DatabaseFileFormat {
52    /// Whether opening this file is known to require an encryption
53    /// key. `Unrecognized` returns `true` because the dominant cause
54    /// for an unrecognized header on a database path is `SQLCipher`
55    /// encryption.
56    #[must_use]
57    pub fn requires_key(self) -> bool {
58        matches!(
59            self,
60            Self::CompressedContainer { encrypted: true } | Self::Unrecognized
61        )
62    }
63}
64
65/// Classify the on-disk format of `path` by reading its first bytes.
66///
67/// Only returns `Err` for I/O failures other than "file not found";
68/// a missing or empty file is reported as [`DatabaseFileFormat::Missing`].
69pub fn detect_database_file_format(path: &Path) -> std::io::Result<DatabaseFileFormat> {
70    let mut file = match File::open(path) {
71        Ok(file) => file,
72        Err(err) if err.kind() == std::io::ErrorKind::NotFound => {
73            return Ok(DatabaseFileFormat::Missing);
74        }
75        Err(err) => return Err(err),
76    };
77    let mut prefix = [0_u8; DETECT_PREFIX_LEN];
78    let mut read = 0;
79    while read < prefix.len() {
80        let n = file.read(&mut prefix[read..])?;
81        if n == 0 {
82            break;
83        }
84        read += n;
85    }
86    if read == 0 {
87        return Ok(DatabaseFileFormat::Missing);
88    }
89    if read < DETECT_PREFIX_LEN {
90        // Too short for any valid database header.
91        return Ok(DatabaseFileFormat::Unrecognized);
92    }
93    if &prefix == SQLITE_MAGIC {
94        return Ok(DatabaseFileFormat::PlainSQLite);
95    }
96    if &prefix[..MAGIC.len()] == MAGIC || &prefix[..LEGACY_MAGIC.len()] == LEGACY_MAGIC {
97        let flags = u32::from_le_bytes([
98            prefix[HEADER_FLAGS_OFFSET],
99            prefix[HEADER_FLAGS_OFFSET + 1],
100            prefix[HEADER_FLAGS_OFFSET + 2],
101            prefix[HEADER_FLAGS_OFFSET + 3],
102        ]);
103        return Ok(DatabaseFileFormat::CompressedContainer {
104            encrypted: flags & FLAG_ENCRYPTED != 0,
105        });
106    }
107    Ok(DatabaseFileFormat::Unrecognized)
108}
109
110impl ManagedConnection {
111    /// Open the detected `SQLite` file format, preserving encryption requirements.
112    ///
113    /// Missing files use `SQLCipher` when a key is supplied and plain `SQLite` otherwise. Existing compressed files retain their recorded codec. A key on an unencrypted file is rejected; encrypted or unrecognized files require a key. New compressed files require an explicit compression constructor.
114    pub fn open_auto(path: &Path, key: Option<&str>) -> Result<Self, SQLiteError> {
115        Self::open_detected(path, key, None)
116    }
117
118    /// Detect an existing encrypted compressed container and enforce its externally retained identity and committed state before opening it. Missing and other file formats are rejected without creating a database.
119    pub fn open_auto_with_anchor(
120        path: &Path,
121        key: &str,
122        trusted_anchor: SQLiteCompressedContainerAnchor,
123    ) -> Result<Self, SQLiteError> {
124        Self::open_detected(path, Some(key), Some(trusted_anchor))
125    }
126
127    fn open_detected(
128        path: &Path,
129        key: Option<&str>,
130        anchor: Option<SQLiteCompressedContainerAnchor>,
131    ) -> Result<Self, SQLiteError> {
132        let key = match key {
133            Some("") => return Err(SQLiteError::EmptyEncryptionKey),
134            other => other,
135        };
136        let format = detect_database_file_format(path)?;
137        if anchor.is_some()
138            && format != (DatabaseFileFormat::CompressedContainer { encrypted: true })
139        {
140            return Err(SQLiteError::StorageBackend(
141                "a trusted anchor requires an existing encrypted compressed container".into(),
142            ));
143        }
144        match format {
145            DatabaseFileFormat::Missing => match key {
146                Some(key) => Self::open_encrypted(path, key),
147                None => Self::open(path),
148            },
149            DatabaseFileFormat::PlainSQLite => match key {
150                Some(_) => Err(SQLiteError::NotEncrypted),
151                None => Self::open(path),
152            },
153            DatabaseFileFormat::CompressedContainer { encrypted: true } => match key {
154                Some(key) => match anchor {
155                    Some(anchor) => Self::open_compressed_encrypted_with_anchor(
156                        path,
157                        key,
158                        SQLiteCompressionOptions::default(),
159                        anchor,
160                    ),
161                    None => Self::open_compressed_encrypted(
162                        path,
163                        key,
164                        SQLiteCompressionOptions::default(),
165                    ),
166                },
167                None => Err(SQLiteError::EncryptionKeyRequired),
168            },
169            DatabaseFileFormat::CompressedContainer { encrypted: false } => match key {
170                Some(_) => Err(SQLiteError::NotEncrypted),
171                None => Self::open_compressed(path, SQLiteCompressionOptions::default()),
172            },
173            DatabaseFileFormat::Unrecognized => match key {
174                Some(key) => Self::open_encrypted(path, key),
175                None => Err(SQLiteError::EncryptionKeyRequired),
176            },
177        }
178    }
179}
180
181#[cfg(test)]
182mod tests {
183    use super::*;
184
185    use crate::compressed_vfs::SQLiteCompressionOptions;
186    use crate::connection::ManagedConnection;
187
188    fn temp_dir() -> tempfile::TempDir {
189        tempfile::tempdir().expect("tempdir")
190    }
191
192    #[test]
193    fn automatic_anchored_open_preserves_exact_state_and_rejects_missing_files() {
194        let directory = temp_dir();
195        for (index, compression) in [
196            SQLiteCompressionOptions::lz4(),
197            SQLiteCompressionOptions::zstd(),
198        ]
199        .into_iter()
200        .enumerate()
201        {
202            let path = directory.path().join(format!("anchored-{index}"));
203            let old_file = directory.path().join(format!("old-{index}"));
204            let key = "automatic-anchor-key";
205            let connection =
206                ManagedConnection::open_compressed_encrypted(&path, key, compression).unwrap();
207            connection
208                .with(|sql| {
209                    Ok(sql.execute_batch("CREATE TABLE t (n INTEGER); INSERT INTO t VALUES (7)")?)
210                })
211                .unwrap();
212            drop(connection);
213            let first = crate::read_authenticated_anchor(&path, key).unwrap();
214            std::fs::copy(&path, &old_file).unwrap();
215            for part in 0..3 {
216                let mut invalid = first;
217                match part {
218                    0 => invalid.database_id[0] ^= 1,
219                    1 => invalid.generation ^= 1,
220                    _ => invalid.state_tag[0] ^= 1,
221                }
222                assert!(ManagedConnection::open_auto_with_anchor(&path, key, invalid).is_err());
223            }
224            let connection = ManagedConnection::open_auto_with_anchor(&path, key, first).unwrap();
225            connection
226                .with(|sql| Ok(sql.execute_batch("INSERT INTO t VALUES (8)")?))
227                .unwrap();
228            drop(connection);
229            let current = crate::read_authenticated_anchor(&path, key).unwrap();
230            assert_eq!(first.database_id, current.database_id);
231            assert!(current.generation > first.generation);
232            assert_ne!(current.state_tag, first.state_tag);
233            assert!(ManagedConnection::open_auto_with_anchor(&path, key, first).is_err());
234            let connection = ManagedConnection::open_auto_with_anchor(&path, key, current).unwrap();
235            let count: i64 = connection
236                .with(|sql| Ok(sql.query_row("SELECT count(*) FROM t", [], |row| row.get(0))?))
237                .unwrap();
238            assert_eq!(count, 2);
239            drop(connection);
240            std::fs::copy(&old_file, &path).unwrap();
241            assert!(ManagedConnection::open_auto_with_anchor(&path, key, current).is_err());
242            let missing = directory.path().join(format!("missing-{index}"));
243            assert!(ManagedConnection::open_auto_with_anchor(&missing, key, current).is_err());
244            assert!(!missing.exists());
245            let plain = directory.path().join(format!("plain-{index}"));
246            let connection = ManagedConnection::open(&plain).unwrap();
247            connection
248                .with(|sql| Ok(sql.execute_batch("CREATE TABLE retained (n INTEGER)")?))
249                .unwrap();
250            drop(connection);
251            let before = std::fs::read(&plain).unwrap();
252            assert!(ManagedConnection::open_auto_with_anchor(&plain, key, current).is_err());
253            assert_eq!(std::fs::read(&plain).unwrap(), before);
254        }
255    }
256
257    #[test]
258    fn automatic_connections_preserve_data_and_encryption_across_all_codecs() {
259        let directory = temp_dir();
260        for (index, compression) in [
261            None,
262            Some(SQLiteCompressionOptions::zstd()),
263            Some(SQLiteCompressionOptions::lz4()),
264        ]
265        .into_iter()
266        .enumerate()
267        {
268            for key in [None, Some("automatic-open-test-key")] {
269                let path = directory.path().join(format!("{index}-{}", key.is_some()));
270                let connection = match (compression, key) {
271                    (None, key) => ManagedConnection::open_auto(&path, key),
272                    (Some(options), None) => ManagedConnection::open_compressed(&path, options),
273                    (Some(options), Some(key)) => {
274                        ManagedConnection::open_compressed_encrypted(&path, key, options)
275                    }
276                }
277                .unwrap();
278                connection
279                    .with(|sqlite| {
280                        Ok(sqlite.execute_batch(
281                            "CREATE TABLE retained (n INTEGER); INSERT INTO retained VALUES (101)",
282                        )?)
283                    })
284                    .unwrap();
285                drop(connection);
286                let reopened = ManagedConnection::open_auto(&path, key).unwrap();
287                let value = reopened
288                    .with(|sqlite| {
289                        Ok(sqlite
290                            .query_row("SELECT n FROM retained", [], |row| row.get::<_, i64>(0))?)
291                    })
292                    .unwrap();
293                assert_eq!(value, 101);
294                drop(reopened);
295                if key.is_some() {
296                    assert!(matches!(
297                        ManagedConnection::open_auto(&path, None),
298                        Err(SQLiteError::EncryptionKeyRequired)
299                    ));
300                    assert!(ManagedConnection::open_auto(&path, Some("wrong-key")).is_err());
301                } else {
302                    assert!(matches!(
303                        ManagedConnection::open_auto(&path, Some("unused-key")),
304                        Err(SQLiteError::NotEncrypted)
305                    ));
306                }
307            }
308        }
309    }
310
311    #[test]
312    fn automatic_connections_reject_empty_keys_before_creating_files() {
313        let directory = temp_dir();
314        let path = directory.path().join("absent.db");
315        assert!(matches!(
316            ManagedConnection::open_auto(&path, Some("")),
317            Err(SQLiteError::EmptyEncryptionKey)
318        ));
319        assert!(!path.exists());
320        std::fs::write(&path, b"a foreign file with an unrecognized header").unwrap();
321        assert!(matches!(
322            ManagedConnection::open_auto(&path, None),
323            Err(SQLiteError::EncryptionKeyRequired)
324        ));
325    }
326
327    #[test]
328    fn missing_file_detects_as_missing() {
329        let dir = temp_dir();
330        let path = dir.path().join("absent.db");
331        assert_eq!(
332            detect_database_file_format(&path).unwrap(),
333            DatabaseFileFormat::Missing
334        );
335    }
336
337    #[test]
338    fn empty_file_detects_as_missing() {
339        let dir = temp_dir();
340        let path = dir.path().join("empty.db");
341        std::fs::write(&path, b"").unwrap();
342        assert_eq!(
343            detect_database_file_format(&path).unwrap(),
344            DatabaseFileFormat::Missing
345        );
346    }
347
348    #[test]
349    fn plaintext_sqlite_detects_as_plain() {
350        let dir = temp_dir();
351        let path = dir.path().join("plain.db");
352        {
353            let conn = ManagedConnection::open(&path).unwrap();
354            conn.with(|c| Ok(c.execute_batch("CREATE TABLE t (id INTEGER)")?))
355                .unwrap();
356        }
357        assert_eq!(
358            detect_database_file_format(&path).unwrap(),
359            DatabaseFileFormat::PlainSQLite
360        );
361    }
362
363    #[test]
364    fn sqlcipher_database_detects_as_unrecognized() {
365        let dir = temp_dir();
366        let path = dir.path().join("cipher.db");
367        {
368            let conn = ManagedConnection::open_encrypted(&path, "secret").unwrap();
369            conn.with(|c| Ok(c.execute_batch("CREATE TABLE t (id INTEGER)")?))
370                .unwrap();
371        }
372        assert_eq!(
373            detect_database_file_format(&path).unwrap(),
374            DatabaseFileFormat::Unrecognized
375        );
376    }
377
378    #[test]
379    fn compressed_container_detects_with_encryption_flag() {
380        let dir = temp_dir();
381        let plain = dir.path().join("container.db");
382        {
383            let conn =
384                ManagedConnection::open_compressed(&plain, SQLiteCompressionOptions::default())
385                    .unwrap();
386            conn.with(|c| Ok(c.execute_batch("CREATE TABLE t (id INTEGER)")?))
387                .unwrap();
388        }
389        assert_eq!(
390            detect_database_file_format(&plain).unwrap(),
391            DatabaseFileFormat::CompressedContainer { encrypted: false }
392        );
393
394        let encrypted = dir.path().join("container-enc.db");
395        {
396            let conn = ManagedConnection::open_compressed_encrypted(
397                &encrypted,
398                "secret",
399                SQLiteCompressionOptions::default(),
400            )
401            .unwrap();
402            conn.with(|c| Ok(c.execute_batch("CREATE TABLE t (id INTEGER)")?))
403                .unwrap();
404        }
405        assert_eq!(
406            detect_database_file_format(&encrypted).unwrap(),
407            DatabaseFileFormat::CompressedContainer { encrypted: true }
408        );
409    }
410
411    #[test]
412    fn legacy_compressed_header_is_classified_for_an_explicit_migration_error() {
413        let dir = temp_dir();
414        let path = dir.path().join("legacy-container.db");
415        let mut prefix = [0_u8; DETECT_PREFIX_LEN];
416        prefix[..LEGACY_MAGIC.len()].copy_from_slice(LEGACY_MAGIC);
417        prefix[8..12].copy_from_slice(&1_u32.to_le_bytes());
418        prefix[HEADER_FLAGS_OFFSET..HEADER_FLAGS_OFFSET + 4]
419            .copy_from_slice(&FLAG_ENCRYPTED.to_le_bytes());
420        std::fs::write(&path, prefix).unwrap();
421        assert_eq!(
422            detect_database_file_format(&path).unwrap(),
423            DatabaseFileFormat::CompressedContainer { encrypted: true }
424        );
425    }
426
427    #[test]
428    fn short_or_foreign_files_detect_as_unrecognized() {
429        let dir = temp_dir();
430        let short = dir.path().join("short.bin");
431        std::fs::write(&short, b"abc").unwrap();
432        assert_eq!(
433            detect_database_file_format(&short).unwrap(),
434            DatabaseFileFormat::Unrecognized
435        );
436
437        let foreign = dir.path().join("foreign.bin");
438        std::fs::write(&foreign, vec![0xAB_u8; 64]).unwrap();
439        assert_eq!(
440            detect_database_file_format(&foreign).unwrap(),
441            DatabaseFileFormat::Unrecognized
442        );
443    }
444
445    #[test]
446    fn requires_key_reflects_format() {
447        assert!(!DatabaseFileFormat::Missing.requires_key());
448        assert!(!DatabaseFileFormat::PlainSQLite.requires_key());
449        assert!(!DatabaseFileFormat::CompressedContainer { encrypted: false }.requires_key());
450        assert!(DatabaseFileFormat::CompressedContainer { encrypted: true }.requires_key());
451        assert!(DatabaseFileFormat::Unrecognized.requires_key());
452    }
453}