Editor’s Note: Today we are going to talk about some common knowledge about mobile forensics, including SQLite file analysis, SQLite file structure, SQLite analysis tool, SQLite common statement, etc…as we all know, most app data stored in SQLite database files, usually when we want to perform mobile forensics, we need to decrypt and analyze SQLite files, so we hope you can have a better understanding of SQLite database files through this article.
1. SQLite file structure
SQLite is a lightweight database, a relational database management system that complies with ACID, the data type of SQLite is Typelessness, The entire database (definition, tables, indexes, and data itself) is stored in a single file on the hosting host, the “magic number” of SQLite file is “0x53514C69746520666F726D61742033”, as shown in the picture below:
From the perspective of logical structure, the smallest management unit of an SQLite database file is a “page” structure. The types of pages are Btree pages, free pages, and overflow pages. The page size is fixed and is set when the database is created. Generally, the default size is 1024 bytes. Each Btree page consists of four parts, which are: page header, cell pointer array, unallocated space, and cell content area. Some temporary files are generated during the use of the database, including Journal files (* .journal) and Write Ahead Logs (* .wal) files.
* .journal: When the transaction is to modify the page, the unmodified page is first stored in the journal. If the transaction rolls back, the data before the modification is obtained from the journal, and the changed data is covered to achieve transaction consistency. In general, the journal is another file different from the database file. It is created at the beginning of a transaction and deleted when the transaction ends;
* .wal: It is a log mode. A new write-ahead log mechanism was introduced after SQLite 3.7.0. When each transaction executes a change, the data page is modified, and a log is generated at the same time. After the transaction is committed, the modified dirty page does not need to be flushed to disk, and only the log generated by the transaction can be returned to disk.
2. Some common SQLite analysis tool
3. SQLite common statement
Common structure: SELECT Row name FROM Table name WHERE Row Operational character Value ORDER BY Row
Note:
- Case-insensitive in SQL statements
- The red part is the simplest structure of the SELECT in the SQL statement
- To query all columns, you can use the symbol * instead of the column name, such as: SELECT * FROM tablename
Statement structure:
SELECT tabelA.column1, tabelA.column2, tabelB.column1，tabelB.column2 FROM tabelA LEFT JOIN tabelB ON tabelA.column3 = tabelB.column4
The following takes the WeChat database query as an example, and the statement is written as:
Select message.createTime,message.talker,message.content,rcontact.username,rcontact.nickname from message left join rcontact on rcontact.username = message.talker
Common functions:
1. column name, table name rename function
SELECT m.column AS rcolumn FROM tablename AS m
2. Statistical function
SELECT COUNT(*) FROM tablename;
Or
SELECT COUNT(*) FROM tablename;
3. Deduplication function
SELECT COUNT(DISTINCT(column)) FROM tablename;
4. Order function
ORDER BY
5. Fuzzy matching function
SELECT * FROM tablename WHERE column LIKE ‘%xxx%’;
SalvationDATA Solution for SQLite Database Analysis
After the study, some basic knowledge of SQLite database, now please be noted that all the technology and solutions mentioned above are already integrated into SPF Pro (SmartPhone Forensic System Professional). and is free to use for all our customers, including trial. The great feature of SQLite Master-Pro is user friendly, just see the tool interface below:
Conclusion
Thanks for your reading, if you are interested in our forensic solutions, come and check out our website for more information. You can also go to our resource page to download our forensic products for free. We welcome you to contact us and claim your free product trial!
SalvationDATA Blog 