MSSQL forensics (3) - Slot Array & Deleted Record

In my previous article, I have described record structure and traced 3 records including 1 deleted record in sample database (4n6ist_sample.zip) . This time I will focus on slot array & how to identify deleted record.

Slot array

In MDF, each page maintains the offset to each record at slot array which is located at the end of the page. I show a layout of a page again.

SlotArray_View

Let's check slot array of page 368 in the database.

DataPage_SlotArray

Each entry of slot array consists of 2 bytes and  first entry starts at the end of the page.

The figure shows that first entry (0x2E1FFE-0x2E1FFF) indicates the offset (0x0060) of first record. Second entry (0x2E1FFC-0x2E1FFD) indicates the offset (0x00C5) of second record. Third entry (0x2E1FFA-0x2E1FFB) is 0 (0x0000), because the third record has already been deleted. It would be 0x012A before the record is deleted. Usual tools rely on the information of slot array to identify each position of allocated record.

What will happen to slot array when a new record is inserted? I have inserted new sample record in the table with the following query (You can download second sample here).

INSERT INTO pictures(id, date, category, filename, data)
SELECT 4, '20200223', 'Lights', 'PIXNIO-2363819', BulkColumn
FROM OPENROWSET(BULK N'E:\dataset\PIXNIO-2363819-1200x800.jpg', SINGLE_BLOB) AS image

Lights

Here is page 368 in the database after insertion.

DataPage_SlotArray2

Now third entry (0x2E1FFA-0x2E1FFB) has changed from 0 to 0x018F, which points to new record (4th). We can confirm that data of third record still remains.

Parsing deleted records

Considering these fasts, I assume that deleted record can be identified and recovered when we parse each record from the start of records area and verify corresponding entry of slot array. I have written a python script based on the assumption. The script parses each record of specific data page, indicates [DELETED] when its record has no reference in slot array.

MDF_Parse_DataPage_Record

The script has -d option to show only deleted records. It is useful when we want to focus on only deleted records and a data page has a lot of records.

MDF_Parse_DataPage_DeletedRecord

Applying table schema to records

The script is able to find deleted records. However, output is not easy to read for us. If we provide corresponding table schema to parse records, we can extract each column with human-readable format. Again, here is columns of pictures table.

  • 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)

I have added a function to the script for the schema of pictures table in accordance with record structure as follows:

print_for_specific_table

When I enable the function and run the script, output also shows human-readable information.

Applying_Table_Schema_Code

Applying_Table_Schema

We can understand deleted information easily if we provide specific table schema. I assume such schema information is defined somewhere in MDF. If we are able to parse table schema and added as code to the script, we will view human-readable information without such manual work.

I haven't mentioned data column (Data Type: image) which contains large object data (LOB) yet. I will cover structure of LOB next time.