sqlite_extractor.c (20091B)
1 /* 2 This file is part of libextractor. 3 Copyright (C) 2026 Vidyut Samanta and Christian Grothoff 4 5 libextractor is free software; you can redistribute it and/or modify 6 it under the terms of the GNU General Public License as published 7 by the Free Software Foundation; either version 3, or (at your 8 option) any later version. 9 10 libextractor is distributed in the hope that it will be useful, but 11 WITHOUT ANY WARRANTY; without even the implied warranty of 12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13 General Public License for more details. 14 15 You should have received a copy of the GNU General Public License 16 along with libextractor; see the file COPYING. If not, write to the 17 Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 18 Boston, MA 02110-1301, USA. 19 */ 20 /** 21 * @file plugins/sqlite_extractor.c 22 * @brief plugin to support sqlite files 23 * @author Christian Grothoff 24 * 25 * This plugin reads the 100-byte SQLite database header and nothing 26 * else. It deliberately does not link libsqlite3 and never opens the 27 * database: handing an untrusted file to a full SQL engine is a large 28 * attack surface, and a bulk pass over millions of files cannot afford 29 * the page-cache and journal recovery work that opening implies. 30 * Everything reported below lives in the first 100 bytes. 31 * 32 * Reference: "The SQLite Database File Format", https://sqlite.org/fileformat.html 33 */ 34 #include "platform.h" 35 #include "extractor.h" 36 #include "forensics.h" 37 38 /** 39 * Number of bytes in the SQLite database header. 40 */ 41 #define SQLITE_HEADER_SIZE 100 42 43 /** 44 * Number of bytes we need to tell the three members of the family 45 * apart: the database magic is 16 bytes, the write-ahead log header is 46 * 32 and the rollback journal magic is 8. 47 */ 48 #define SQLITE_PROBE_SIZE 32 49 50 /** 51 * The 16-byte magic every SQLite 3 database file starts with, 52 * including its terminating NUL. 53 */ 54 static const char sqlite_magic[16] = "SQLite format 3"; 55 56 /** 57 * Magic of a rollback journal file (the `-journal' sidecar). 58 */ 59 static const unsigned char journal_magic[8] = { 60 0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, 0xd7 61 }; 62 63 64 /** 65 * Is @a ps a page size SQLite could actually have written? 66 * 67 * Legal values are the powers of two from 512 to 32768, plus the 68 * escape value 1 which stands for 65536. Rejecting anything else 69 * keeps a corrupt or forged header from turning into an absurd 70 * "database size" further down. 71 * 72 * @param ps the raw 16-bit field 73 * @return the page size in bytes, 0 if @a ps is not legal 74 */ 75 static uint32_t 76 decode_page_size (uint16_t ps) 77 { 78 if (1 == ps) 79 return 65536; /* the documented escape for a 64 KiB page */ 80 if ( (ps < 512) || 81 (0 != (ps & (ps - 1))) ) 82 return 0; 83 return ps; 84 } 85 86 87 /** 88 * Report the SQLite library version that wrote a file. 89 * 90 * The version number is stored as X*1000000 + Y*1000 + Z. 91 * 92 * We report this as #EXTRACTOR_METATYPE_MODIFIED_BY_SOFTWARE rather 93 * than CREATED_BY_SOFTWARE because that is what the field actually 94 * means: the header documents it as the version that *most recently 95 * modified* the file, and it is rewritten on every commit. A database 96 * created by 3.8 and later written by 3.45 carries 3.45 here, so 97 * calling it the creator would be wrong in exactly the cases where the 98 * value is interesting. 99 * 100 * @param ec extraction context 101 * @param type meta data type to report under 102 * @param version the raw version number 103 * @return 1 if the caller should stop extracting, 0 to continue 104 */ 105 static int 106 emit_version (struct EXTRACTOR_ExtractContext *ec, 107 enum EXTRACTOR_MetaType type, 108 uint32_t version) 109 { 110 unsigned int major; 111 unsigned int minor; 112 unsigned int patch; 113 114 if (0 == version) 115 return 0; 116 major = version / 1000000; 117 minor = (version / 1000) % 1000; 118 patch = version % 1000; 119 if ( (major < 3) || 120 (major > 9) ) 121 return 0; /* not a version number we recognise; do not guess */ 122 return EXTRACTOR_forensic_emit_ (ec, 123 "sqlite", 124 type, 125 "SQLite %u.%u.%u", 126 major, 127 minor, 128 patch); 129 } 130 131 132 /** 133 * Handle a write-ahead log file (the `-wal' sidecar). 134 * 135 * A bulk pass meets these constantly next to the databases they belong 136 * to, and they hold committed-but-not-yet-checkpointed pages, so 137 * naming them as such is worth the twenty lines. 138 * 139 * @param ec extraction context 140 * @param hdr the first #SQLITE_PROBE_SIZE bytes of the file 141 */ 142 static void 143 handle_wal (struct EXTRACTOR_ExtractContext *ec, 144 const unsigned char *hdr) 145 { 146 uint32_t page_size; 147 148 if (0 != 149 EXTRACTOR_forensic_emit_ (ec, 150 "sqlite", 151 EXTRACTOR_METATYPE_MIMETYPE, 152 "%s", 153 "application/vnd.sqlite3-wal")) 154 return; 155 if (0 != 156 EXTRACTOR_forensic_emit_ (ec, 157 "sqlite", 158 EXTRACTOR_METATYPE_FORMAT, 159 "%s", 160 "SQLite write-ahead log")) 161 return; 162 /* file format version, offset 4, big-endian */ 163 if (0 != 164 EXTRACTOR_forensic_emit_ (ec, 165 "sqlite", 166 EXTRACTOR_METATYPE_FORMAT_VERSION, 167 "%u", 168 (unsigned int) 169 EXTRACTOR_forensic_be32_ (&hdr[4]))) 170 return; 171 /* the WAL page size is a plain 32-bit value, not the escaped 16-bit 172 field the database header uses */ 173 page_size = EXTRACTOR_forensic_be32_ (&hdr[8]); 174 if ( (page_size >= 512) && 175 (page_size <= 65536) && 176 (0 == (page_size & (page_size - 1))) && 177 (0 != EXTRACTOR_forensic_emit_ (ec, 178 "sqlite", 179 EXTRACTOR_METATYPE_BLOCK_SIZE, 180 "%u", 181 (unsigned int) page_size)) ) 182 return; 183 /* checkpoint sequence number, offset 12: how many times this log has 184 been reset, i.e. roughly how much history the pair has seen */ 185 if (0 != 186 EXTRACTOR_forensic_emit_ (ec, 187 "sqlite", 188 EXTRACTOR_METATYPE_CHANGE_COUNTER, 189 "%u", 190 (unsigned int) 191 EXTRACTOR_forensic_be32_ (&hdr[12]))) 192 return; 193 /* salt-1/salt-2 at 16/20 identify the WAL incarnation and let an 194 analyst match a log against the database it belongs to */ 195 (void) EXTRACTOR_forensic_emit_hex_ (ec, 196 "sqlite", 197 EXTRACTOR_METATYPE_VOLUME_SERIAL, 198 &hdr[16], 199 8); 200 } 201 202 203 /** 204 * Handle a rollback journal file (the `-journal' sidecar). 205 * 206 * @param ec extraction context 207 * @param hdr the first #SQLITE_PROBE_SIZE bytes of the file 208 */ 209 static void 210 handle_journal (struct EXTRACTOR_ExtractContext *ec, 211 const unsigned char *hdr) 212 { 213 uint32_t page_size; 214 uint32_t db_pages; 215 216 if (0 != 217 EXTRACTOR_forensic_emit_ (ec, 218 "sqlite", 219 EXTRACTOR_METATYPE_MIMETYPE, 220 "%s", 221 "application/vnd.sqlite3-journal")) 222 return; 223 if (0 != 224 EXTRACTOR_forensic_emit_ (ec, 225 "sqlite", 226 EXTRACTOR_METATYPE_FORMAT, 227 "%s", 228 "SQLite rollback journal")) 229 return; 230 /* page size at offset 24, sector size at 20, initial database size in 231 pages at 16 -- all big-endian */ 232 page_size = EXTRACTOR_forensic_be32_ (&hdr[24]); 233 if ( (page_size >= 512) && 234 (page_size <= 65536) && 235 (0 == (page_size & (page_size - 1))) ) 236 { 237 if (0 != 238 EXTRACTOR_forensic_emit_ (ec, 239 "sqlite", 240 EXTRACTOR_METATYPE_BLOCK_SIZE, 241 "%u", 242 (unsigned int) page_size)) 243 return; 244 db_pages = EXTRACTOR_forensic_be32_ (&hdr[16]); 245 /* the size the database had before the transaction started: a 246 journal left behind says the transaction never completed */ 247 if ( (0 != db_pages) && 248 (db_pages <= UINT32_MAX / page_size) && 249 (0 != EXTRACTOR_forensic_emit_size_ (ec, 250 "sqlite", 251 EXTRACTOR_METATYPE_VOLUME_SIZE, 252 ((uint64_t) db_pages) 253 * page_size)) ) 254 return; 255 } 256 (void) EXTRACTOR_forensic_emit_ (ec, 257 "sqlite", 258 EXTRACTOR_METATYPE_COMMENT, 259 "%s", 260 "hot journal: a transaction was" 261 " interrupted or the database is open"); 262 } 263 264 265 /** 266 * Main entry method for the sqlite extraction plugin. 267 * 268 * @param ec extraction context provided to the plugin 269 */ 270 void 271 EXTRACTOR_sqlite_extract_method (struct EXTRACTOR_ExtractContext *ec); 272 273 void 274 EXTRACTOR_sqlite_extract_method (struct EXTRACTOR_ExtractContext *ec) 275 { 276 unsigned char hdr[SQLITE_HEADER_SIZE]; 277 uint64_t file_size; 278 uint32_t page_size; 279 uint32_t page_count; 280 uint32_t change_counter; 281 uint32_t version_valid_for; 282 uint32_t freelist_pages; 283 uint32_t app_id; 284 uint16_t raw_page_size; 285 unsigned char write_version; 286 unsigned char read_version; 287 288 if (! EXTRACTOR_forensic_read_ (ec, 289 0, 290 hdr, 291 SQLITE_PROBE_SIZE)) 292 return; /* too short to be anything of ours */ 293 /* The two sidecar formats share the family but not the magic; check 294 them first, they are cheap and would otherwise fall through. */ 295 if ( (0x37 == hdr[0]) && 296 (0x7f == hdr[1]) && 297 (0x06 == hdr[2]) && 298 ( (0x82 == hdr[3]) || 299 (0x83 == hdr[3]) ) ) 300 { 301 handle_wal (ec, 302 hdr); 303 return; 304 } 305 if (0 == memcmp (hdr, 306 journal_magic, 307 sizeof (journal_magic))) 308 { 309 handle_journal (ec, 310 hdr); 311 return; 312 } 313 if (0 != memcmp (hdr, 314 sqlite_magic, 315 sizeof (sqlite_magic))) 316 return; /* not a SQLite database */ 317 if (! EXTRACTOR_forensic_read_ (ec, 318 0, 319 hdr, 320 SQLITE_HEADER_SIZE)) 321 return; /* magic matched but the header is truncated */ 322 if (0 != 323 EXTRACTOR_forensic_emit_ (ec, 324 "sqlite", 325 EXTRACTOR_METATYPE_MIMETYPE, 326 "%s", 327 "application/vnd.sqlite3")) 328 return; 329 file_size = ec->get_size (ec->cls); 330 331 /* -- page size, offset 16, big-endian, 1 meaning 65536 -- */ 332 raw_page_size = EXTRACTOR_forensic_be16_ (&hdr[16]); 333 page_size = decode_page_size (raw_page_size); 334 if ( (0 != page_size) && 335 (0 != EXTRACTOR_forensic_emit_ (ec, 336 "sqlite", 337 EXTRACTOR_METATYPE_BLOCK_SIZE, 338 "%u", 339 (unsigned int) page_size)) ) 340 return; 341 342 /* -- journal mode, from the write/read format versions at 18 and 19 -- */ 343 write_version = hdr[18]; 344 read_version = hdr[19]; 345 if ( (1 == write_version) || 346 (2 == write_version) ) 347 { 348 if (0 != 349 EXTRACTOR_forensic_emit_ (ec, 350 "sqlite", 351 EXTRACTOR_METATYPE_JOURNAL_MODE, 352 "%s", 353 (2 == write_version) 354 ? "wal" 355 : "rollback journal")) 356 return; 357 } 358 if ( (read_version > 2) && 359 (0 != EXTRACTOR_forensic_emit_ (ec, 360 "sqlite", 361 EXTRACTOR_METATYPE_COMMENT, 362 "read format version %u:" 363 " newer than this library understands", 364 (unsigned int) read_version)) ) 365 return; 366 367 /* -- change counter (24) against version-valid-for (92) -- */ 368 change_counter = EXTRACTOR_forensic_be32_ (&hdr[24]); 369 version_valid_for = EXTRACTOR_forensic_be32_ (&hdr[92]); 370 if (0 != 371 EXTRACTOR_forensic_emit_ (ec, 372 "sqlite", 373 EXTRACTOR_METATYPE_CHANGE_COUNTER, 374 "%u", 375 (unsigned int) change_counter)) 376 return; 377 378 /* -- page count, offset 28 -- */ 379 page_count = EXTRACTOR_forensic_be32_ (&hdr[28]); 380 if (0 != 381 EXTRACTOR_forensic_emit_ (ec, 382 "sqlite", 383 EXTRACTOR_METATYPE_ENTRY_COUNT, 384 "%u", 385 (unsigned int) page_count)) 386 return; 387 /* The page count is only authoritative when version-valid-for equals 388 the change counter; otherwise the file was last written by a 389 SQLite older than 3.7.0, which did not maintain the field. */ 390 if ( (change_counter != version_valid_for) && 391 (0 != EXTRACTOR_forensic_emit_ (ec, 392 "sqlite", 393 EXTRACTOR_METATYPE_COMMENT, 394 "%s", 395 "stale page count: last written by" 396 " a SQLite older than 3.7.0")) ) 397 return; 398 399 /* -- free space: freelist page count at 36, in pages -- */ 400 freelist_pages = EXTRACTOR_forensic_be32_ (&hdr[36]); 401 if (0 != page_size) 402 { 403 /* a 32-bit page count times a page size of at most 64 KiB stays 404 well inside 64 bits */ 405 /* Freelist pages are where deleted rows survive, so a large value 406 against a small database is a strong sign of recoverable data. */ 407 if (0 != 408 EXTRACTOR_forensic_emit_size_ (ec, 409 "sqlite", 410 EXTRACTOR_METATYPE_FREE_SPACE, 411 ((uint64_t) freelist_pages) 412 * page_size)) 413 return; 414 } 415 416 /* -- schema cookie, offset 40 -- */ 417 if (0 != 418 EXTRACTOR_forensic_emit_ (ec, 419 "sqlite", 420 EXTRACTOR_METATYPE_SCHEMA_VERSION, 421 "schema cookie %u", 422 (unsigned int) 423 EXTRACTOR_forensic_be32_ (&hdr[40]))) 424 return; 425 /* -- user_version, offset 60; also a schema version, but the one the 426 application chose rather than the one SQLite maintains -- */ 427 if (0 != 428 EXTRACTOR_forensic_emit_ (ec, 429 "sqlite", 430 EXTRACTOR_METATYPE_SCHEMA_VERSION, 431 "user_version %u", 432 (unsigned int) 433 EXTRACTOR_forensic_be32_ (&hdr[60]))) 434 return; 435 436 /* -- vacuum modes: largest root b-tree page (52) and incremental 437 vacuum flag (64) -- */ 438 if (0 != EXTRACTOR_forensic_be32_ (&hdr[52])) 439 { 440 if (0 != 441 EXTRACTOR_forensic_emit_ (ec, 442 "sqlite", 443 EXTRACTOR_METATYPE_ATTRIBUTES, 444 "%s", 445 (0 != EXTRACTOR_forensic_be32_ (&hdr[64])) 446 ? "incremental vacuum" 447 : "auto-vacuum")) 448 return; 449 } 450 451 /* -- text encoding, offset 56 -- */ 452 switch (EXTRACTOR_forensic_be32_ (&hdr[56])) 453 { 454 case 1: 455 if (0 != 456 EXTRACTOR_forensic_emit_ (ec, 457 "sqlite", 458 EXTRACTOR_METATYPE_CHARACTER_SET, 459 "%s", 460 "UTF-8")) 461 return; 462 break; 463 case 2: 464 if (0 != 465 EXTRACTOR_forensic_emit_ (ec, 466 "sqlite", 467 EXTRACTOR_METATYPE_CHARACTER_SET, 468 "%s", 469 "UTF-16LE")) 470 return; 471 break; 472 case 3: 473 if (0 != 474 EXTRACTOR_forensic_emit_ (ec, 475 "sqlite", 476 EXTRACTOR_METATYPE_CHARACTER_SET, 477 "%s", 478 "UTF-16BE")) 479 return; 480 break; 481 default: 482 break; /* 0 means "not yet decided"; anything else is corrupt */ 483 } 484 485 /* -- application id, offset 68 -- */ 486 app_id = EXTRACTOR_forensic_be32_ (&hdr[68]); 487 if (0 != app_id) 488 { 489 /* This is the single best answer to "which program wrote this 490 file". Values are registered upstream in SQLite's `magic.txt'; 491 many of them are chosen to spell a four-character ASCII tag, so 492 render that too when every byte is printable. */ 493 if (0 != 494 EXTRACTOR_forensic_emit_ (ec, 495 "sqlite", 496 EXTRACTOR_METATYPE_APPLICATION_ID, 497 "%u", 498 (unsigned int) app_id)) 499 return; 500 if ( (0x20 <= hdr[68]) && (hdr[68] < 0x7f) && 501 (0x20 <= hdr[69]) && (hdr[69] < 0x7f) && 502 (0x20 <= hdr[70]) && (hdr[70] < 0x7f) && 503 (0x20 <= hdr[71]) && (hdr[71] < 0x7f) && 504 (0 != EXTRACTOR_forensic_emit_text_ (ec, 505 "sqlite", 506 EXTRACTOR_METATYPE_APPLICATION_ID, 507 (const char *) &hdr[68], 508 4)) ) 509 return; 510 } 511 512 /* -- the SQLite version that last wrote the file, offset 96 -- */ 513 if (0 != 514 emit_version (ec, 515 EXTRACTOR_METATYPE_MODIFIED_BY_SOFTWARE, 516 EXTRACTOR_forensic_be32_ (&hdr[96]))) 517 return; 518 519 /* -- logical size, and whether the file lives up to it -- */ 520 if ( (0 != page_size) && 521 (0 != page_count) ) 522 { 523 uint64_t logical = ((uint64_t) page_count) * page_size; 524 525 if (0 != 526 EXTRACTOR_forensic_emit_size_ (ec, 527 "sqlite", 528 EXTRACTOR_METATYPE_VOLUME_SIZE, 529 logical)) 530 return; 531 /* A file materially shorter than its own page count claims was 532 truncated -- by a carver, a partial copy or a wiper. Allow one 533 page of slack so that a merely unflushed tail does not trip it. */ 534 if ( (UINT64_MAX != file_size) && 535 (logical > page_size) && 536 (file_size < logical - page_size) && 537 (0 != EXTRACTOR_forensic_emit_ (ec, 538 "sqlite", 539 EXTRACTOR_METATYPE_COMMENT, 540 "truncated database: header claims" 541 " %llu bytes, file holds %llu", 542 (unsigned long long) logical, 543 (unsigned long long) file_size)) ) 544 return; 545 } 546 } 547 548 549 /* end of sqlite_extractor.c */