[Bug] 6.0.279225: DB migration 219->220 fails on %2x-%9x paths (QString::arg())

[pilspetsen]

Summary

When upgrading to Kobo OS 6.0.279225, the KoboReader.sqlite schema migration from DbVersion 219 to 220 silently fails and rolls back if any sideloaded book entry in the content table contains URL-encoded sequences such as %20, %28, %29, %2C (or any literal %2–%9) in its legacy !-delimited ContentID path or internal spine path.

Because the migration transaction rolls back and retries on every boot:

1. DbVersion stays stuck at 219.
2. The ShortcoverSpineData table is never created, and legacy !-style ContentIDs in content and Bookmark are never migrated to UUIDs.
3. Sideloaded EPUB/KePub books fail to resolve spine/chapter entries properly, resulting in missing chapter titles and missing chapter reading times / reading stats in the reader.

Root Cause

During the 219 -> 220 migration, rows are inserted into ShortcoverSpineData by formatting an SQL INSERT template with 9 placeholders (%1 through %9):

INSERT OR IGNORE INTO "ShortcoverSpineData"
  ("ShortcoverId", "Format", "BookPath", "SpinePath", "Fragment", "TocDepth", "AdobeChapterIndex", "PdfPageNumber", "PartId")
VALUES ('%1', %2, %3, %4, %5, %6, %7, %8, %9);

Instead of using a prepared query with bound parameters (QSqlQuery:repare + bindValue), the statement is built using sequential chained QString::arg() calls (one .arg(...) call per column, from %1 to %9).

In Qt, single-argument QString::arg() replaces all occurrences of the lowest-numbered %N placeholder currently present in the string. Once %2 (Format) and %3 (BookPath) have been substituted, any %2–%9 sequence inside a substituted BookPath (%3), SpinePath (%4), or Fragment (%5) re-introduces a lower-numbered placeholder inside the SQL string literal. Subsequent .arg() calls then overwrite characters inside the path string instead of %4–%9.

Minimal Reproduction

1. Start from a database at DbVersion = 219 (prior to OS 6.0) containing at least one sideloaded EPUB/KePub whose content entries (BookID IS NOT NULL) contain percent-encoded characters like %28, %29, %2C, or %20 in either the file path or internal XHTML spine filename (common in EPUBs with URL-encoded internal filenames). Example ContentID:
   

   /mnt/onboard/Books/Example.kepub.epub!OEBPS!Text/Example_%28illustrated%29_split_001.htm-1

2. Update the device to firmware 6.0.279225 and let Nickel boot and run DB migrations.
3. Inspect KoboReader.sqlite after boot:
   • SELECT version FROM DbVersion; still returns 219.
   • ShortcoverSpineData does not exist.
   • Opening a sideloaded KePub shows missing chapter titles and missing chapter time remaining in the reading HUD/menu.

Step-by-step SQL corruption trace

Using the example SpinePath 'Text/Example_%28illustrated%29_split_001.htm':

1. .arg(shortcoverId) replaces %1.
2. .arg(format) replaces %2 (e.g., 0).
3. .arg(sqlBookPath) replaces %3. (If BookPath itself contains %20 or %28, corruption starts on the very next step.)
4. .arg(sqlSpinePath) replaces %4 with 'Text/Example_%28illustrated%29_split_001.htm'. The query string now contains literal %2 inside %28, alongside unreplaced %5, %6, %7, %8, %9.
5. .arg(sqlFragment) searches for the lowest %N in the string — which is %2, not %5. It replaces %2 inside the SpinePath string literal (e.g. turning %28 into NULL8 or 'fragment'8 with unescaped single quotes), leaving %5 unreplaced and shifting all remaining arguments (TocDepth, AdobeChapterIndex, PdfPageNumber, PartId) off by one.
6. The malformed SQL statement fails, SQLite raises an error, and the 219 -> 220 migration transaction rolls back.

(Note: Any percent-encoded sequence starting with %2–%9 triggers this when inserted before higher-numbered placeholders — e.g. %20 space, %26 &, %28/%29 (), %2C ,, %3A :, or UTF-8 %8x–%9x.)

Suggested Fix

Replace the chained QString::arg() string formatting in the 219 -> 220 migration with a parameterized QSqlQuery (prepare(...) + bindValue(...)) reused across rows within the migration transaction:

INSERT OR IGNORE INTO "ShortcoverSpineData"
  ("ShortcoverId", "Format", "BookPath", "SpinePath", "Fragment", "TocDepth", "AdobeChapterIndex", "PdfPageNumber", "PartId")
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);

This avoids placeholder re-interpretation for any character sequence in filenames or XHTML paths (%, quotes, etc.) and avoids re-parsing the SQL statement for every row during migration.