09 - Deleted Record Recovery

Question 1

How is a SQLite database file structured?

Answer 1

• Page 1 (DB Header & Schema Table)
• Page 2
• Page 3
• B-Tree
• B-Tree

Question 2

Information about pages:

1. size of 1 page
2. Max number of a page
3. Minimum size of a page
4. Max size a database can have

Answer 2

1. 512 Byte - 65’536 Bytes
2. 1 - 2147483646 (2^31 - 2)
3. 512 Bytes
4. ca. 140 TB

Question 3

What kind of important information are in the header page?

Answer 3

• File signature (offset 0x00 / 16 Bytes)
• Page size in byte (offset 0x10 / Default: 0x400)
• Size of DB file in pages (offset 0x1c / 4 Bytes)
• SQLITE-VERSION_NUMBER (offset 0x60 / 4 Bytes)

Question 4

What does the schema table contains?

Answer 4

It contains information about the tables, triggers, indexes.

Schema Type can be:

• table
• index
• trigger

SQL query statement to create this table can be found after the Root Page Number.

Question 5

What are the four SQLite data types?

Answer 5

• Integer
• Text
• Blob
• Real

Question 6

What are B-Trees?

Answer 6

• Store indexes to databases on disk storage.
• Each node has one or several key values.
• Each key has an associated child that is the root of a subtree.

Each node of a B-Tree corresponds to a page of the SQL database file.

Question 7

Name the two types of pages and one main difference?

Answer 7

• Internal Page
• Leaf Page

In general, internal and Leaf pages have the same structure. However, leafe pages do not have the value of the last four byte information, because there is no child page.

Question 8

Name the x steps of the recovery of deleted records:

Answer 8

1. The first step is to identify the number of fields in a scheme table and the type of each field by using the schema table.
2. Then a page is scanned in order to collect all deleted areas offset by identifying all allocated unallocated area, including free space and free blocks within the page.
3. The next step is to investigate the deleted area and extract information. There are three cases of deleted area.

Once the scanning of all unallocated areas are completed, the child page is scanned, the scanning repeats for the rest of the pages to complete the recovery process.

Question 9

What happes in MySQL when a record gets deleted?

Answer 9

When deleting a record SQLite changes the first 2-byte of a cell to the offset value of the next free block, then changes the next 2-byte of a cell with the length of current free block.

Question 10

Name some tools for SQLite data recovery?

Answer 10

• SQLite recover
• SQL data recovery
• Stellar
• Phoenix Repair for SQLite
• Undark
• SQLite Forensic Recovery