Carving a SQLite database from a FiiO X1's raw NAND

· 8 min read
Contents

I went into the flash chip of a second-hand music player looking for the previous owner's music database, and the one intact database I found was mine. Six tracks, one album, from my own SD card. Stored inside it, as if nothing had happened, were ten favourites I had never set.

The player is a FiiO X1, bought used, and its 128 MB NAND flash chip holds both the firmware and the player's own files. Part 6 copied the whole chip over USB into one raw image; Part 7 mapped it and found thousands of file paths from the old owner's library in its user-data blocks. The X1 runs HiBy's firmware on the µC/OS-II real-time kernel, and this part finds out what keeps those paths: which database engine, which tables, and how much of it can be read back.

SQLite 3.7.14 inside the firmware#

The firmware binary, sys.bin decrypted, carries the string 3.7.14 at file offset 0x36cd0c, and at 0x367594 the source date and check-in hash that go with it:

2012-09-03 15:42:36 c0d89d4a9752922f9e367362366efde4f1b06f2a

That is SQLite, compiled whole into the firmware image. The database paths sit in the string table too: y:\usrlocal_media.db, and a second one, y:\usrlocal_media2.db. Drive y: is the user-data volume on the NAND, which the firmware reaches through its flash translation layer (FTL), the code that decides which physical flash block holds which logical one.

The media schema: fourteen tables#

Every CREATE TABLE statement is a string literal in the binary. This is the main one, as the database itself stores it:

CREATE TABLE MEDIA_TABLE(
  id INT,
  path TEXT COLLATE NOCASE,
  name TEXT COLLATE NOCASE,
  album TEXT COLLATE NOCASE,
  artist TEXT COLLATE NOCASE,
  genre TEXT COLLATE NOCASE,
  collect INT,
  ck_id INT,
  has_child_file INT,
  begin_time INT,
  end_time INT,
  cue_id INT,
  character TEXT COLLATE NOCASE,
  PRIMARY KEY(path, name, cue_id)
);

There are three copies of it: MEDIA_TABLE, MEDIA2_TABLE, MEDIA3_TABLE. Then ALBUM_TABLE, ARTIST_TABLE and GENRE_TABLE, each with a character column for sorting; COLLECT_TABLE for favourites and COLLECT_OPERATE_TABLE for pending changes to them; four tables for history, playlists, now playing and search; VERSION_TABLE and COUNT_TABLE. Fourteen in all.

The rest of the design is in the SQL strings around them. begin_time, end_time and cue_id serve CUE sheets. has_child_file is 0 for a plain track, 1 for a CUE container and 2 for a multi-track container, with 3 and 4 appearing only in the staging table. The indexer fills staging tables first, copies them into the final ones with INSERT...SELECT...ORDER BY <col> COLLATE pinyin, a custom collation named for the romanization of Chinese, and drops the staging set. And PRAGMA synchronous=OFF: write speed over crash safety. My guess at the reasoning is that a broken index costs a music player nothing but a rescan of the card.

One SQLite header in 128 megabytes#

Every SQLite file starts with the same sixteen bytes, SQLite format 3 and a zero byte. A scan of the whole raw image for them found exactly one, at offset 0x06b79000: block 859 of 1,024, inside the user-data region Part 7 mapped.

The header decodes cleanly: page size 1,024 bytes, 60 pages, schema cookie 59, a freelist starting at page 38 with 16 free pages, UTF-8, file change counter 78,570, SQLite version 3007014. A 60 KB database. The 128 MB chip holds one database header, and the library behind all those paths is a lot bigger than 60 KB, so whatever this is, it isn't the whole index.

The contiguous carve: three flagged pages, all of them free#

Sixty pages, taken in order from offset 0x06b79000 and written to a file. That offset is 100 KB into block 859, so the first 28 pages come from block 859 and the other 32 from block 860. Opening it:

OPEN FAIL: database disk image is malformed

A page-by-page check of the b-tree type byte flagged three of the sixty: page 38 with 0x00, and pages 41 and 42 starting with 0xFF, the value of erased flash (page 42 is erased from end to end). All three are on the freelist. The header names page 38 as the freelist trunk, and the trunk lists fifteen free pages, 39 to 53, which with the trunk itself makes the header's sixteen. A trunk page is a list of page numbers, not a b-tree page, and the file format says of free leaf pages that they "contain no information". The check had picked the three pages in the file that are allowed to hold anything at all.

What breaks the file is in the schema table, the b-tree that lists every table. Its last leaf, page 58, holds no rows, and SQLite stops there: in a copy of the file with page 58 cut out of that tree, the schema reads. Page 60 holds seven schema rows that no tree points to: the index of SEARCH_TABLE, and the three staging tables the indexer drops when it finishes, each with its index. One of those rows names page 60 itself as an index root. Pages from different versions of the same file sit side by side.

That looks like what a flash translation layer leaves behind. The pages a file is made of don't have to sit next to each other on the chip: the FTL keeps a map from logical blocks to physical ones and moves blocks when they are rewritten. Carving physical pages in a row reads whatever happens to sit there, and this carve runs across a block boundary. Which physical block holds the current copy of page 58 is in that map, and the map isn't in this image; Part 18 is about why.

sqlite3 .recover: fifteen tables, integrity ok#

SQLite's command-line shell has a .recover command for exactly this: it walks every page it can read, pulls rows out of intact leaf pages and writes SQL that rebuilds a clean database. The commands that ran:

sqlite3 dumps/carved/media_06b79000_60p.db ".recover" 2>/dev/null > dumps/carved/media_recovered.sql
sqlite3 dumps/carved/media_RECOVERED.db < dumps/carved/media_recovered.sql 2>&1 | head -5

The rebuilt database passes PRAGMA integrity_check with ok. It has all fourteen tables plus a fifteenth, lost_and_found, with 95 rows .recover could not assign to a table. VERSION_TABLE holds one value, 20151011. The firmware's version check at 0x80121aa0 compares the stored value against the constant 0x1337ae3, which is 20151011 in decimal, and throws the database away on a mismatch. The official update file x1.fw is dated 2016-06-07 in its header, so the schema is about eight months older than the firmware build that ships it.

Whose database it is#

Then I read the rows. MEDIA_TABLE has six, and all six are tracks from one album folder on my own SD card. COUNT_TABLE agrees: six, one, one, one, which reads as six tracks, one album, one artist, one genre. The one intact database on the chip is the player's index of my card.

Rule six: before I write about whose data something is, I count whose data it is. The draft of this post had a paragraph about the previous owner's habits, built on this database, and the database turned out to describe me.

Not all of it, though. COLLECT_TABLE has ten rows, and none of them points into my folder. They are the old owner's favourites, paths into the old library, and they were still sitting in the same file as my six tracks when the indexer rebuilt it. Whatever indexed my card replaced the media rows and left the favourites alone.

12,629 paths, counted two ways#

Most of the old library survives only as text. A scan of the whole raw image for strings that start with the SD card's drive name a:\ and end in an audio extension found 12,629 unique paths:

ExtensionPaths
.mp38,382
.flac4,246
.ape1
Total12,629

Six of them are the tracks in my own album folder; the rest is the previous owner's. The hits are densest in blocks 959 to 988, 30 blocks or 3.75 MB of flash, which is probably where the main usrlocal_media.db lives, scattered by the FTL across physical blocks and without a header that survived. Part 7 counts the same region differently, 13,205 distinct path strings in blocks 786 to 994. The two scans don't count the same thing: this pattern accepts printable ASCII only, so any path with a non-ASCII character in it, Cyrillic for one, never matches, and Part 7's count includes those. Either way the shape is the one Part 7 described: roughly two MP3 paths for every FLAC, about two thirds of them in one flat folder and most of the rest in folders named after genres. Names stay out of this post, the previous owner's and mine.

What firmware updates leave behind#

Part 7 established that a firmware update rewrites the firmware region of the chip and leaves the user data alone. This dump shows what that means in practice. The previous owner's library index is still there as thousands of path strings. Their favourites are still there as rows, inside a database the player rebuilt for someone else's card. The index of my six tracks is the only thing on the chip that still opens as a database, and even that needed .recover.

None of it is protected or hidden. It is plaintext in flash, readable by anyone with a USB cable, a debug stub and a night to spend copying 128 megabytes. When I sell this player, I will not be trusting a firmware update to clean up after me.

Filed under: reverse-engineering, mips, embedded, fiio, hardware, sqlite

Sources#