MSSQL forensics (2) - Record Structure

In my previous article, I have described the basics of MDF and parsed each page headers in MDF file. This time I will focus on a record structure in a data page.

Sample database (again)

In the series I prepared 4n6ist_sample.zip, which holds "pictures" table. Here is a schema.

Pictures_Schema

In concrete terms, each column is represented as follows:

  • id - Fixed-size integer (4 bytes)
  • date - Fixed-size ASCII string (8 bytes)
  • category - Fixed-size Unicode string (32 bytes)
  • filename - Variable-size Unicode string (up to 510 bytes)
  • data - Variable-size data (no specific limits)

In pictures table, there are 2 allocated records, and 1 deleted records which we are not able to see on usual tool like SQL Server Management Studio.

SELECT_from_pictures

Data page

We have already confirmed with DBCC IND query that page 368 is data page (page type=1) in the database as follows.

Page368_DataPage

Also we have checked with DBCC PAGE query that pminlen of the page is 48.

Page368_pminlen

Record structure

Now let's dig into records in the page. I found a whitepaper "Forensic Analysis of a SQLServer 2005 Database Server" by Kevvie Fowler. He mentioned and explained about record structure. I have checked with 4n6ist_sample.mdf and created a table of my understanding.

Offset Bytes Description
0 1 StatusBits
1 1 Unused
2 2 Offset to a location indicating number of columns
4 pminlen - 4 Data of fixed-length columns
pminlen 2 Total number of columns
pminlen + 2 1 + x/8 Null bitmap
pminlen + 3 + x/8 2 Number of variable-length columns
pminlen + 3 + x/8 + 2 2 End offset of first variable-length column
... ... ...
pminlen + 3 + x/8 + 2*y 2 End offset of last variable-length column
pminlen + 4 + x/8 + 2*y variable-length Data of variable-length columns

(x: Total number of columns, y: Number of variable-length columns)

Null bitmap consumes 1 bit for a column, therefore the size is 1 byte if the total number of columns is less than 8. "pminlen" is one of important values and 48 at this time.

I apply the table to records of page 368 in 4n6ist_sample.mdf, the page is located from offset 0x2E0000 to 0x2E2000 because of this:

Page No. 368 (0x170) *  Page Size 8,192 (0x2000) = 3,014,656 (0x2E0000)

The size of a page header is 96 (0x60) bytes, it means between 0x2E0000 and 0x2E005F. Record structure starts at offset 0x2E0060. A region of first record are highlighted at the following figure.

First record (allocated)

Record_Structure

The result of dissection is as follows. (Following offset means relative offset at 0x2E0060)

Offset Bytes Description Data
0 1 StatusBits 0x30
1 1 Unused 0x00
2 2 Offset to a location indicating number of columns 48 (0x0030)
4 44 Data of fixed-length columns 1 (id: 4 bytes)
20120815 (date: 8 bytes)
Castles (category: 32 bytes)
48 (0x30) 2 Total number of columns 5 (0x0005)
50 (0x32) 1 Null bitmap 0x00
51 (0x33) 2 Number of variable-length columns 2 (0x0002)
53 (0x35) 2 End offset of first variable-length column 85 (0x0055)
55 (0x37) 2 End offset of second variable-length column 101 (0x8065)
* Most significant bit(MSB) will be ignored
57 (0x39) - Data of variable-length columns PIXNIO-194113 (filename: 28 bytes)
Slot data for LOB (image: 16 bytes)

We can see values of each column except image column on first record. I will cover the treatment of such large data next time or later. Continue the same procedure for the following records.

Second record (allocated)

Record_Structure2

The result of dissection is as follows. (Following offset means relative offset at 0x2E00C5)

Offset Bytes Description Data
0 1 StatusBits 0x30
1 1 Unused 0x00
2 2 Offset to a location indicating number of columns 48 (0x0030)
4 44 Data of fixed-length columns 2 (id: 4 bytes)
20191230 (date: 8 bytes)
Fire Flames (category: 32 bytes)
48 (0x30) 2 Total number of columns 5 (0x0005)
50 (0x32) 1 Null bitmap 0x00
51 (0x33) 2 Number of variable-length columns 2 (0x0002)
53 (0x35) 2 End offset of first variable-length column 85 (0x0055)
55 (0x37) 2 End offset of second variable-length column 101 (0x8065)
* MSB will be ignored
57 (0x39) - Data of variable-length columns PIXNIO-2321940 (filename: 28 bytes)
Slot data for LOB (image: 16 bytes)

Third record (deleted)

Record_Structure3

The result of dissection is as follows. (Following offset means relative offset at 0x2E012A)

Offset Bytes Description Data
0 1 StatusBits 0x30
1 1 Unused 0x00
2 2 Offset to a location indicating number of columns 48 (0x0030)
4 44 Data of fixed-length columns 3 (id: 4 bytes)
20191215 (date: 8 bytes)
Horses (category: 32 bytes)
48 (0x30) 2 Total number of columns 5 (0x0005)
50 (0x32) 1 Null bitmap 0x00
51 (0x33) 2 Number of variable-length columns 2 (0x0002)
53 (0x35) 2 End offset of first variable-length column 85 (0x0055)
55 (0x37) 2 End offset of second variable-length column 101 (0x8065)
* MSB will be ignored
57 (0x39) - Data of variable-length columns PIXNIO-2315661 (filename: 28 bytes)
Slot data for LOB (image: 16 bytes)

We can easily see the data of third record which has been deleted on the database. It's a simple case, but at least it implies that deletion process doesn't clear data or truncate a record immediately.

Pminlen

At page 368, a value of pminlen is 48. We have confirmed the value with DBCC IND query, which parses the page header and record at specific page. In fact, we can also calculate the value from table schema. The total bytes of the size of fixed-length columns in pictures table is 44:

  • 4 (id) + 8(date) + 32(category) = 44

pminlen is 48 because the value includes first 4 bytes of record structure:

  • pminlen = 1(StatusBits) + 1(Unused) + 2(Offset to a location indicating number of columns) + 44(Total bytes of the size of fixed-length columns) = 48

When we analyze MDF file and try to recover records from specific table, we have to identify which page is important for us (i.e. pages containing data records). One of hints is page type because data page holds data records (i.e. m_type=1). Secondly, it is pminlen which can be identified from table schema.

With these two value we can expect the page number on which we should focus on. From previous article, here is the output of mdf_parse_pageheader.py, which parses each page header in MDF.

mdf_parse_pageheader

When we filter by "m_type=1 & pminlen=48", the result is as follows:

Filtered_Pageheader

We can narrow down total 1024 pages to 32 candidate pages and it includes number 368. This way is not smart yet, we may be able to narrow down candidates using other value in page header.

I will cover slot array and deleted record next time.

コメントを追加

プレーンテキスト

  • HTMLタグは利用できません。
  • 行と段落は自動的に折り返されます。
  • ウェブページのアドレスとメールアドレスは自動的にリンクに変換されます。