Forensics Analysis of Sqlite Pointer Map Pages
Introduction to Sqlite Pointer map pages
With the widespread usage of Sqlite applications embedded in other programs, its forensics value has also increased. Sqlite Pointer map pages can also be one of the crucial elements and its analysis is important. Investigators look forward to analyze the Sqlite databases belonging to various programs. Each element of databases is examined for detailed investigation. Pointer map pages in Sqlite database can be defined as a single lookup data structure that identifies the parent page for associated child pages in the database. Sqlite database pages has at most one parent page. Pointer-map page comprises of an array of 5-byte entries with one byte “type” and 4-byte parent page number.
Sqlite databases which are auto-vacuum capable use Pointer Map pages and some other page types. In a non-auto-vacuum-capable database when any data is deleted, file size of the database remains the same. However, when large amount of data is deleted, it is important to shrink the database size using SQL VACUUM command. This removes if any free page or space is available. When Auto-vacuum is enabled, all free pages are automatically moved to end of file, which is truncated automatically.
Pointer Map Pages
In Sqlite, these pages helps in moving pages from one position to another in a database file as a part of auto vacuum. When a page is moved, pointer in the parent page is updated to provide new location. This means that if the page is moved to new location because of the auto-vacuum process, this change of location will be recorded in the pointer. Hence, Pointer Maps are utilized for providing lookup table for determining parent page of certain page. It can be found within auto-vacuum capable databases.
In auto-vacuum-capable Sqlite databases, page 2 is Pointer Map page. Structurally, if you have an auto-vacuum-capable database having 24 pages, 1st page will comprise of the database header information and schema. Next page i.e. 2nd page will be Pointer Map page comprising of 5-byte record for remaining 22 pages (5 bytes for each page) which takes up 110 bytes of space within page. This means that in a 4096 bytes page, 819 records (of pages) can be stored. Additional Pointer Map pages can be added and it must be noticed that Pointer Map page does not comprise of records associated with the Pointer Map pages or page of database it only consists of records related to rest of the pages. Pointer Map 1st byte states the Page type and rest of the 4 bytes references parent page number.
Reduce Database Size from Pointer Map Page
When you count the Page Type byte in the hex codding of the Sqlite database, if the number of pages happens to be 24, this means that you have 24 pages which follows the second page i.e. Pointer map page. This also means that the total number of pages available in the database is 26 including first page and Pointer map page and rest 24 pages. 2 bytes integer at an offset of 16 bytes can be examined for determining the page size.
How Is This Important From investigation Perspective?
In order to know the details and validity of any database, these details can be very important. It is important to carve auto-vacuum capable databases and below mentioned steps can be utilized for it:
- Detect first page of the database, which can be done by detecting Sqlite format 3 header.
- Page size establishment is the next step, which can be done by reading the 2 bytes at offset 16 as 16-bit integer.
- Now check the 4 byte (32-bit) integer at offset 52 for non-zero value signaling Sqlite file is auto-vacuum capable.
- If the 2nd page of the database at Offset page size has its value as 0x01 or 0x02 or 0x03 or 0x04 or 0x05 set a counter to 1.
- Shift 5 bytes and if value is 0x01, 0x02, 0x03, 0x04, or 0x05 increment the counter by 1.
- If it is not, begin calculation of file size with this formula: database size = (counter value + 2) x page size