1 2#------------------------------------------------------------------------------ 3# $File: sql,v 1.38 2024/12/26 19:14:32 christos Exp $ 4# sql: file(1) magic for SQL files 5# 6# From: "Marty Leisner" <mleisner@eng.mc.xerox.com> 7# Recognize some MySQL files. 8# Elan Ruusamae <glen@delfi.ee>, added MariaDB signatures 9# from https://bazaar.launchpad.net/~maria-captains/maria/5.5/view/head:/support-files/magic 10# 110 beshort 0xfe01 MySQL table definition file 12>2 byte x Version %d 13>3 byte 0 \b, type UNKNOWN 14>3 byte 1 \b, type DIAM_ISAM 15>3 byte 2 \b, type HASH 16>3 byte 3 \b, type MISAM 17>3 byte 4 \b, type PISAM 18>3 byte 5 \b, type RMS_ISAM 19>3 byte 6 \b, type HEAP 20>3 byte 7 \b, type ISAM 21>3 byte 8 \b, type MRG_ISAM 22>3 byte 9 \b, type MYISAM 23>3 byte 10 \b, type MRG_MYISAM 24>3 byte 11 \b, type BERKELEY_DB 25>3 byte 12 \b, type INNODB 26>3 byte 13 \b, type GEMINI 27>3 byte 14 \b, type NDBCLUSTER 28>3 byte 15 \b, type EXAMPLE_DB 29>3 byte 16 \b, type CSV_DB 30>3 byte 17 \b, type FEDERATED_DB 31>3 byte 18 \b, type BLACKHOLE_DB 32>3 byte 19 \b, type PARTITION_DB 33>3 byte 20 \b, type BINLOG 34>3 byte 21 \b, type SOLID 35>3 byte 22 \b, type PBXT 36>3 byte 23 \b, type TABLE_FUNCTION 37>3 byte 24 \b, type MEMCACHE 38>3 byte 25 \b, type FALCON 39>3 byte 26 \b, type MARIA 40>3 byte 27 \b, type PERFORMANCE_SCHEMA 41>3 byte 127 \b, type DEFAULT 42>0x0033 ulong x \b, MySQL version %d 430 belong&0xffffff00 0xfefe0500 MySQL ISAM index file 44>3 byte x Version %d 450 belong&0xffffff00 0xfefe0600 MySQL ISAM compressed data file 46>3 byte x Version %d 470 belong&0xffffff00 0xfefe0700 MySQL MyISAM index file 48>3 byte x Version %d 49>14 beshort x \b, %d key parts 50>16 beshort x \b, %d unique key parts 51>18 byte x \b, %d keys 52>28 bequad x \b, %lld records 53>36 bequad x \b, %lld deleted records 540 belong&0xffffff00 0xfefe0800 MySQL MyISAM compressed data file 55>3 byte x Version %d 560 belong&0xffffff00 0xfefe0900 MySQL Maria index file 57>3 byte x Version %d 580 belong&0xffffff00 0xfefe0a00 MySQL Maria compressed data file 59>3 byte x Version %d 600 belong&0xffffff00 0xfefe0c00 61>4 string MACF MySQL Maria control file 62>>3 byte x Version %d 630 string \376bin MySQL replication log, 64>9 long x server id %d 65>8 byte 1 66>>13 long 69 \b, MySQL V3.2.3 67>>>19 string x \b, server version %s 68>>13 long 75 \b, MySQL V4.0.2-V4.1 69>>>25 string x \b, server version %s 70>8 byte 15 MySQL V5+, 71>>25 string x server version %s 72>4 string MARIALOG MySQL Maria transaction log file 73>>3 byte x Version %d 74 75#------------------------------------------------------------------------------ 76# iRiver H Series database file 77# From Ken Guest <ken@linux.ie> 78# As observed from iRivNavi.iDB and unencoded firmware 79# 800 string iRivDB iRiver Database file 81>11 string >\0 Version %s 82>39 string iHP-100 [H Series] 83 84#------------------------------------------------------------------------------ 85# SQLite database files 86# Ken Guest <ken@linux.ie>, Ty Sarna, Zack Weinberg 87# 88# Version 1 used GDBM internally; its files cannot be distinguished 89# from other GDBM files. 90# 91# Update: Joerg Jenderek 92# Reference: http://mark0.net/download/triddefs_xml.7z/defs/s/sqlite-2x.trid.xml 93# Note: called "SQLite 2.x database" by TrID and "SQLite Database File Format" version 2 by DROID via PUID fmt/1135 94# Version 2 used this format: 950 string **\ This\ file\ contains\ an\ SQLite SQLite 2.x database 96!:mime application/x-sqlite2 97# FileAttributesStore.db test.sqlite2 98!:ext sqlite/sqlite2/db 99 100# URL: https://en.wikipedia.org/wiki/SQLite 101# Reference: https://www.sqlite.org/fileformat.html 102# Update: Joerg Jenderek 103# TODO: missing extensions for Bentley Systems and Esri Spatially-Enabled Database 104# Version 3 of SQLite allows applications to embed their own "user version" 105# number in the database at offset 60. Later, SQLite added an "application id" 106# at offset 68 that is preferred over "user version" for indicating the 107# associated application. 108# 1090 string SQLite\040format\0403\0 110# skip DROID fmt-729-signature-id-1053.sqlite by checking for valid page size 111>16 ubeshort >0 SQLite 3.x 112# deprecated 113#!:mime application/x-sqlite3 114# seldom found extension sqlite3 like in SyncData.sqlite3 115# db 116# db3 like: AddrBook.db3 cgipcrvp.db3 117# https://www.maplesoft.com/support/help/Maple/view.aspx?path=worksheet%2freference%2fhelpdatabase 118# help is used for newer Maple help database 119# SQLite database weewx.sdb used by weather software weewx 120# https://www.weewx.com/docs/usersguide.htm 121# Avira Antivir use extension "dbe" like in avevtdb.dbe, avguard_tchk.dbe 122# ide is used in storage.ide 123# localstorage like in Enigma2 http_itv.ard.de_0.localstorage 124# xowa like in home-html.user.xowa http://fileformats.archiveteam.org/wiki/XOWA 125# sqlar like in sqlar-src-4824e73896.sqlar http://fileformats.archiveteam.org/wiki/SQLite_Archive 126# sketch http://fileformats.archiveteam.org/wiki/Sketch 127# ftb http://fileformats.archiveteam.org/wiki/MyHeritage_Family_Tree_Builder 128# lrcat http://fileformats.archiveteam.org/wiki/Lightroom_catalog 129# without suffix like in "Diagnostic Data" 130# Unfortunately extension sqlite also used for other databases starting with string 131# "TTCONTAINER" like in tracks.sqlite contentconsumer.sqlite contentproducerrepository.sqlite 132# and with string "ZV-zlib" in like extra.sqlite 133# Reference: http://mark0.net/download/triddefs_xml.7z/defs/m/maple-sqlite.trid.xml 134>>68 belong =0x5CDE09EF database 135# maple is used for Maple Workbook 136!:ext maple 137# From: Joerg Jenderek 138# URL: https://en.wikipedia.org/wiki/Audacity_(audio_editor) 139# http://fileformats.archiveteam.org/wiki/Audacity_Project_Format 140# Reference: http://mark0.net/download/triddefs_xml.7z/defs/a/aup3.trid.xml 141# Note: called "Audacity 3 Project" by TrID and "Audacity Project File" version 3.x by DROID via PUID fmt/1826 142# with user version 03000000h whereas older versions *.AUP are not SQLite based 143>>68 belong =0x41554459 database 144!:mime application/x-audacity-project+sqlite3 145!:ext aup3 146# From: Joerg Jenderek 147# URL: http://fileformats.archiveteam.org/wiki/TeXnicard 148# Reference: http://fileformats.archiveteam.org/wiki/TeXnicard_card_database 149# Note: no examples found 150>>68 belong =0x6A035744 database 151!:mime application/vnd.sqlite3 152!:ext db 153# Update: Joerg Jenderek 154# URL: http://fileformats.archiveteam.org/wiki/Fossil_repository_database 155# Reference: http://mark0.net/download/triddefs_xml.7z/defs/f/fossil.trid.xml 156# Note: called "Fossil repository data base" by TrID 157>>68 belong =0x0F055111 database 158!:mime application/vnd.sqlite3 159!:ext fossil/fsl 160# URL: http://fileformats.archiveteam.org/wiki/Fossil_checkout_database 161# Reference: http://mark0.net/download/triddefs_xml.7z/defs/f/fossil-checkout.trid.xml 162# Note: called "Fossil checkout data base" by TrID 163>>68 belong =0x0F055112 database 164!:mime application/vnd.sqlite3 165# name _FOSSIL_ on Windows or .fslckout else 166!:ext /fslckout 167# Update: Joerg Jenderek 168# URL: http://fileformats.archiveteam.org/wiki/Fossil_configuration_database 169# Reference: http://mark0.net/download/triddefs_xml.7z/defs/f/fossil-config.trid.xml 170# Note: called "Fossil configuration data base" by TrID 171>>68 belong =0x0F055113 database 172!:mime application/vnd.sqlite3 173# %LOCALAPPDATA%\_fossil on Windows and ~/.fossil or fossil.db else 174!:ext /fossil/db 175# Update: Joerg Jenderek 176# URL: http://fileformats.archiveteam.org/wiki/GeoPackage 177# Reference: http://mark0.net/download/triddefs_xml.7z/defs/g/gpkg-v1.trid.xml 178# Note: called "GeoPackage (v1)" by TrID and "OGC GeoPackage" version 1.0-1.31 by DROID via PUID fmt/1700 179# with GP10 application id 180>>68 belong =0x47503130 database 181# https://www.iana.org/assignments/media-types/application/geopackage+sqlite3 182!:mime application/geopackage+sqlite3 183# https://github.com/opengeospatial/ets-gpkg12/blob/master/src/test/resources/gpkg/bluemarble.gpkg 184!:ext gpkg 185# Reference: http://mark0.net/download/triddefs_xml.7z/defs/g/gpkg-v12.trid.xml 186# Note: called "GeoPackage (v1.2)" by TrID and "OGC GeoPackage" version 1.0-1.31 by DROID via PUID fmt/1700 187# with GPKG application id 188>>68 belong =0x47504B47 database 189!:mime application/geopackage+sqlite3 190!:ext gpkg 191# Update: Joerg Jenderek 192# URL: https://github.com/mapbox/mbtiles-spec/tree/master 193# Reference: http://mark0.net/download/triddefs_xml.7z/defs/m/mbtiles-MPBX.trid.xml 194# Note: called "MBTiles tileset (MPBX)" by TrID and many samples without application id MPBX like 195# https://ftp.gwdg.de/pub/misc/openstreetmap/openseamap/charts/mbtiles/OSM-OpenCPN2-MagellanStrait.mbtiles 196>>68 belong =0x4D504258 database 197!:mime application/vnd.sqlite3 198!:ext mbtiles 199>>68 default x database 200!:mime application/vnd.sqlite3 201# no examples found with s3db sl3 suffix 202!:ext /sqlite/sqlite3/db/db3/dbe/sdb/help/ide/localstorage/sqlar/xowa/mbtiles 203# GRR: the line above only works if in ../../src/file.h FILE_MAGICSIZE ext[] are raised or you get 204>>60 belong =0x5f4d544e (Monotone source repository) 205# if no known user version then check for Application IDs with default clause 206>>60 belong !0x5f4d544e 207# The "Application ID" set by PRAGMA application_id 208>>>68 belong =0x0f055112 (Fossil checkout) 209>>>68 belong =0x0f055113 (Fossil global configuration) 210>>>68 belong =0x0f055111 (Fossil repository) 211>>>68 belong =0x42654462 (Bentley Systems BeSQLite Database) 212>>>68 belong =0x42654c6e (Bentley Systems Localization File) 213>>>68 belong =0x47504b47 (OGC GeoPackage file) 214# https://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt 215>>>68 belong =0x47503130 (OGC GeoPackage version 1.0 file) 216>>>68 belong =0x45737269 (Esri Spatially-Enabled Database) 217>>>68 belong =0x4d504258 (MBTiles tileset) 218# https://www.maplesoft.com/support/help/errors/view.aspx?path=Formats/Maple 219>>>68 belong =0x5CDE09EF (Maple Workbook) 220# AUDY Audacity Project File 221>>>68 belong =0x41554459 (Audacity Project) 222>>>68 belong =0x6A035744 (TeXnicard card database) 223>>>68 belong =0x43455745 (Cewe Fotobuch) 224>>>68 belong =0x5E96730E (Corsair iCue package registry) 225>>>68 belong =0xF9BEB4D9 (Bitcoin wallet) 226>>>68 belong =0x56333500 (Vector35 Binary Ninja) 227>>>68 belong =0x4163726E (FlyingMeat Acorn) 228>>>68 belong =0x544f4952 (Riot Games patcher) 229>>>68 belong =0x544d5052 (Twinmotion project) 230>>>68 belong =0x43484557 (Chewing IME) 231# unknown application ID 232>>>68 default x 233>>>>68 belong !0 \b, application id %u 234>>>>68 belong 0 235>>>>>0xC70 search/0x9000 CREATE\040TABLE\040s3api_per_key_metadata\n \b, Archive Metadata 236>>>>>0x2A0 search/0x10 CREATE\040TABLE\040version_table\040(\040version\040INTEGER\040NOT\040NULL\040PRIMARY\040KEY\040) \b, Adobe SDE 237>>>>>0xEA0 search/0x10 CREATE\040TABLE\040version_table\040(\040version\040INTEGER\040NOT\040NULL\040PRIMARY\040KEY\040) \b, Adobe SDE 238>>>>>0xA0 search/0x100 CREATE\040TABLE\040IPMMessage\040( \b, Adobe IPM DB 239>>>>>0xC00 search/0x100 CREATE\040TABLE\040IPMMessage\040( \b, Adobe IPM DB 240>>>>>0x200 search/0xFFFF C\0R\0E\0A\0T\0E\0\040\0T\0A\0B\0L\0E\0\040\0A\0C\0T\0I\0O\0N\0R\0E\0C\0O\0R\0D\0S\0\040\0( \b, Microsoft Store Database 241 242 243# The "user version" as read and set by the user_version pragma like: 244# 1 2 4 5 7 9 10 25 36 43 53 400 416 131073 131074 131075 50331648 245>>60 belong !0 \b, user version %d 246# expressed as hexadecimal because some people mention version number in hexadecimal with marking that item 247>>>60 belong x (%#x) 248# SQLITE_VERSION_NUMBER like: 0 3007014 3008011 3016002 3017000 3022000 3028000 3031001 3032003 3035005 249>>96 belong x \b, last written using SQLite version %d 250# database page size in bytes; a power of two between 512 and 32768, or 1 for 65536 251# like: 512 1024 often 4096 32768 252>>16 ubeshort !4096 \b, page size 253>>>16 ubeshort !1 %u 254>>>16 ubeshort =1 65536 255# File format write version. 1 for legacy; 2 for WAL; 0 for corruptDB.sqlite 256>>18 ubyte !1 \b, writer version %u 257# File format read version. 1 for legacy; 2 for WAL; 4 for corruptDB.sqlite 258>>19 ubyte !1 \b, read version %u 259# Bytes of unused "reserved" space at the end of each page. Usually 0 260>>20 ubyte !0 \b, unused bytes %u 261# maximum embedded payload fraction. Must be 64; 1 for corruptDB.sqlite 262>>21 ubyte !64 \b, maximum payload %u 263# Minimum embedded payload fraction. Must be 32; 1 for corruptDB.sqlite 264>>22 ubyte !32 \b, minimum payload %u 265# Leaf payload fraction. Must be 32; 0 for corruptDB.sqlite 266>>23 ubyte !32 \b, leaf payload %u 267# file change counter 268>>24 ubelong x \b, file counter %u 269# Size of the database file in pages 270>>28 ubelong x \b, database pages %u 271# page number of the first freelist trunk page like: 0 2 3 4 5 9 272# 10 13 14 15 16 17 18 19 23 36 39 46 50 136 190 217 307 505 516 561 883 1659 273>>32 ubelong !0 \b, 1st free page %u 274# total number of freelist pages 275>>36 ubelong !0 \b, free pages %u 276# The schema cookie like: 2 3 4 6 7 9 A D E F 13 14 1C 25 2A 2F 33 44 4B 53 5A 5F 62 86 87 8F 91 A8 277>>40 ubelong x \b, cookie %#x 278# the schema format number. Supported formats are 1 2 3 and often 4 279# 3328 for corruptDB.sqlite and 0 for 512 byte storage.sqlite (TorBrowser Firefox Thunderbird) 280>>44 ubelong x \b, schema %u 281# Suggested cache size like: 0 2000 282>>48 ubelong !0 \b, cache page size %u 283# The page number of the largest root b-tree page when in auto-vacuum or incremental-vacuum modes, or zero otherwise. 284>>52 ubelong !0 \b, largest root page %u 285# The database text encoding; a value of 1 means UTF-8; 2 means UTF-16le; 3 means UTF-16be 286#>>56 ubelong x \b, encoding %u 287>>56 ubelong x 288>>>56 ubelong =1 \b, UTF-8 289>>>56 ubelong =2 \b, UTF-16 little endian 290>>>56 ubelong =3 \b, UTF-16 big endian 291# 0 for corruptDB.sqlite and for storage.sqlite with database pages 1 (TorBrowser Firefox Thunderbird) 292# https://mozilla.github.io/firefox-browser-architecture/text/0010-firefox-data-stores.html 293>>>56 default x 294>>>>56 ubelong x \b, unknown %#x encoding 295# True (non-zero) for incremental-vacuum mode; false (zero) otherwiseqy 296>>64 ubelong !0 \b, vacuum mode %u 297# Reserved for expansion. Must be zero 298>>72 uquad !0 \b, reserved %#llx 299# The version-valid-for number like: 300# 1 2 3 4 C F 68h 95h 266h A99h 3DCDh B7CEh 301>>92 ubelong x \b, version-valid-for %u 302 303# SQLite Write-Ahead Log from SQLite version >= 3.7.0 304# https://www.sqlite.org/fileformat.html#walformat 3050 belong&0xfffffffe 0x377f0682 SQLite Write-Ahead Log, 306!:ext sqlite-wal/db-wal 307>4 belong x version %d 308# Summary: SQLite Write-Ahead-Log index (shared memory) 309# From: Joerg Jenderek 310# URL: http://fileformats.archiveteam.org/wiki/SQLite 311# Reference: http://www.sqlite.org/draft/walformat.html#walidxfmt 312# iVersion; WAL-index format version number; always 3007000=2DE218h 3130 ulelong 0x002DE218 314>0 use shm-le 315# big endian variant not tested 3160 ubelong 0x002DE218 317>0 use \^shm-le 318# show information about SQLite Write-Ahead-Log shared memory 3190 name shm-le 320>0 ulelong x SQLite Write-Ahead Log shared memory 321#!:mime application/octet-stream 322!:mime application/vnd.sqlite3 323# db3-shm Acronis BackupAndRecovery F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm 324# dbx-shm probably Dropbox filecache.dbx-shm 325# aup3-shm Audacity project tada.aup3-shm 326# srd-shm Microsoft Windows StateRepository service StateRepository-Deployment.srd-shm StateRepository-Machine.srd-shm: 327!:ext sqlite-shm/db-shm/db3-shm/dbx-shm/aup3-shm/srd-shm 328# unused padding space; must be zero 329>4 ulelong !0 \b, unused %x 330# iChange; unsigned integer counter, incremented with each transaction 331>8 ulelong x \b, counter %u 332# isInit; the "isInit" flag; 1 when the shm file has been initialized 333>12 ubyte !1 \b, not initialized %u 334# bigEndCksum; true if the WAL file uses big-ending checksums; 0 if the WAL uses little-endian checksums 335>13 ubyte !0 \b, checksum type %u 336# szPage; database page size in bytes, or 1 if the page size is 65536 337>14 uleshort !1 \b, page size %u 338>14 uleshort =1 \b, page size 65536 339# mxFrame; number of valid and committed frames in the WAL file 340>16 ulelong x \b, %u frames 341# nPage; size of the database file in pages 342>20 ulelong x \b, %u pages 343# aFrameCksum; checksum of the last frame in the WAL file 344>24 ulelong x \b, frame checksum %#x 345# aSalt; two salt value copied from the WAL file header in the byte-order of the WAL file; might be different from machine byte-order 346>32 ulequad x \b, salt %#llx 347# aCksum; checksum over bytes 0 through 39 of this header 348>40 ulelong x \b, header checksum %#x 349# a copy of bytes 0 through 47 of header 350>48 ulelong !3007000 \b, iversion %u 351# nBackfill; number of WAL frames that have already been backfilled into the database by prior checkpoints 352>96 ulelong !0 \b, %u backfilled 353# nBackfillAttempted; number of WAL frames that have attempted to be backfilled 354>>128 ulelong x (%u attempts) 355# read-mark[0..4]; five "read marks"; each read mark is a 32-bit unsigned integer 356>100 ulelong !0 \b, read-mark[0] %#x 357>104 ulelong x \b, read-mark[1] %#x 358>108 ulelong !0xffffffff \b, read-mark[2] %#x 359>112 ulelong !0xffffffff \b, read-mark[3] %#x 360>116 ulelong !0xffffffff \b, read-mark[4] %#x 361# unused space set aside for 8 file locks 362>120 ulequad !0 \b, space %#llx 363# unused space reserved for further expansion 364>132 ulelong !0 \b, reserved %#x 365 366# SQLite Rollback Journal 367# https://www.sqlite.org/fileformat.html#rollbackjournal 3680 string \xd9\xd5\x05\xf9\x20\xa1\x63\xd7 SQLite Rollback Journal 369 370# Panasonic channel list database svl.bin or svl.db added by Joerg Jenderek 371# https://github.com/PredatH0r/ChanSort 3720 string PSDB\0 Panasonic channel list DataBase 373!:ext db/bin 374#!:mime application/x-db-svl-panasonic 375>126 string SQLite\ format\ 3 376#!:mime application/x-panasonic-sqlite3 377>>&-15 indirect x \b; contains 378 379# H2 Database from https://www.h2database.com/ 3800 string --\ H2\ 0.5/B\ --\ \n H2 Database file 381 382# DuckDB database file from https://duckdb.org 3838 string DUCK DuckDB database file 384>12 lequad x \b, version %lld 385#>20 lequad x \b, flags %#llx 386#>28 lequad x \b, flags %#llx 387