Summary of SQLite3 Database recovery method

Recently do SQLite 3 database recovery, find a more relevant aspects of the paper, here to record.

One, based on the SQLite file system Recovery

In the previous article, the file structure of SQLite 3 was recorded, which mentions the underlying changes in the database before and after the deletion of the record unit, but not in detail. Tell me more about it here.

The deletion of the SQLite 3 database is similar to the deletion of the PC's file system data, that is, the original data will not be deleted during the deletion process, it will remain on the ground floor until the new datastore is overwritten. In addition, in the process of deletion, when the number of record units is deleted, the database consolidates free blocks, so that a free block may contain multiple record units, and when the data of a page is deleted, the page forms a free page. In general, the free blocks formed after the recording unit is deleted may have these conditions:

1. A free block contains part of a recording unit (partially covered case)

2. A free block contains a complete recording unit

3. A free block contains multiple complete recording units

In addition, it is the situation that forms the free page.

Method One

The recovery steps based on the SQLite 3 database file system structure are usually:

　　Preparation Phase : Read the database header, get the database page size and encoding method.

Read the system table to get the root page of the target table to be restored.

From the root page, recursively traverse all leaf pages.

Traverse all the leaf pages and find all the free blocks of the table.

　　judgment Stage : For the above mentioned free block scenario 2, the purpose of the judgment phase is to determine whether a free block is a complete recording unit. The method of judging is: The number of bytes in the +type region of the N value +data whether the number of bytes in the region is the same as the size of the free block record. (The initial value of n is 4, which is the next free block offset of two bytes and the size of this free block of bytes, and n is a maximum of 28). When the judgment result is the same, it goes to the next stage and recovers the data.

In this phase, the key point is to analyze clearly whether the recording unit has become a free block before and after the original record unit type area is not covered. In this issue, Baijin, Sun Hong wins, Hu Zeming "A data recovery method based on the SQLite3 file format," the article detailed analysis, they said that the value of n range between 3-6, so delete before and after the type area is covered in three cases:

1, the unit size +rowid+headersize=3 bytes, this situation shows that the database data is small, then the N=4,type area is overwritten by a byte, but at the bottom of the trace, I found that each record unit of the type area of the first byte of the value is always 0x00, That is, the length of the corresponding data region is 0, so overwriting a byte has little effect, but the number of bytes calculated for the type appears to start at 2.

2, the unit size +rowid+headersize=4 bytes, when the N=4,type area is not covered, and calculate the entire free block, it is possible to collar type starting from 1

3, the third case is when the database data is large, the cell size +rowid+headersize>4 bytes, then the n value will be greater than 4, and the type area is also not overwritten and starting from 1

About the above three points, just recorded the conclusion, no specific analysis of the reasons, you can look at the paper, it is very clear.

　　recovery phase : The work of the recovery phase is to encode each element of the data region according to the records of the type region, which is troublesome because the type region records the length and type of the subsequent data field.

This method can only be used for the second situation mentioned above, the result of recovery is not very good. The result of my experiment was:

　　

The above is the recovery of several tables, you can see, when the table record volume is small, delete data less time, recovery can be, but when the table record volume increase, delete data volume, the second case of free block is not so much, so the recovery is not ideal.

In view of the above, continue to look at the SQLite recovery paper, found in one paper mentioned a method: similarity type matching estimation method .

　　Method two similarity type matching estimation method

　　This method is also essentially based on the SQLite database file structure, the preparation phase and method one, but also to traverse all leaf pages, in order to find all the free blocks, in addition to the normal record unit of the type area. But in the judgment phase, it is no longer a single free block, but a comparison with the existence of the recording unit, from which we can also understand the source of the method name: Compare the type of the free block and the type of the record unit is similar.

Judging phase: Starting from the nth byte of the free block (n values are also discussed in the method one), the similar type matches successfully? Recovery phase: n++; After a free block recovery is complete, for the third case of the free block, determine if the free block is over? Store recovery information: continue similar type matching.

The recovery phase and method type are not mentioned.

This method is Chen Fei in his thesis, "Intelligent Mobile terminal application of data forensics technology research" proposed. I'll try to restore the free block in this way, and I think it's a lot better than the method.

Summary of SQLite3 Database recovery method