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:

640

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

Image 1

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

640 (2)

Common functions:

1. column name, table name rename function

SELECT  m.column AS rcolumn FROM tablename AS  m

图片1

2. Statistical function

SELECT COUNT(*) FROM  tablename;

Or

SELECT COUNT(*) FROM  tablename;

640 (3)

3. Deduplication function

SELECT COUNT(DISTINCT(column)) FROM  tablename;

640 (4)

4. Order function

ORDER BY

5. Fuzzy matching function

SELECT * FROM tablename WHERE column LIKE ‘%xxx%’;

SalvationDATA Solution for SQLite Database Analysis

3373765333_15810443215_1D677396-394E-4e07-A248-3169484C1918

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:

Image 2E28B5FE8-E212-4128-9548-91D5B33A0962

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!