T09 · Insecure Skill Coding Practices
Error
- Location
- SKILL.md:44
- Finding
- Unsafe SQL Interpolation Against Sensitive Apple Books Databases## Vulnerability Details **File Location**: `SKILL.md:44-54`, `SKILL.md:86-97`, and `SKILL.md:101-115` **Vulnerability Type**: SQL injection through unescaped textual substitution and unenforced read-only access **Risk Level**: High The Skill directs the Agent to insert user-controlled search terms, asset identifiers, and a database path directly into SQL strings. It invokes SQLite without the `-readonly` option, so its stated read-only policy is not technically enforced. ### Vulnerable Code `SKILL.md:44-54`: ```bash BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)" sqlite3 "$BKLIBRARY_DB" \ "SELECT ZTITLE, ZAUTHOR, ZGENRE, ZREADINGPROGRESS, ZASSETID FROM ZBKLIBRARYASSET WHERE ZTITLE IS NOT NULL AND (ZTITLE LIKE '%SEARCH_TERM%' OR ZAUTHOR LIKE '%SEARCH_TERM%') ORDER BY ZLASTOPENDATE DESC;" ``` ```text Replace `SEARCH_TERM` with the user's query. ``` `SKILL.md:86-97`: ```bash AEANNOTATION_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite 2>/dev/null | head -1)" sqlite3 "$AEANNOTATION_DB" \ "SELECT ZANNOTATIONSELECTEDTEXT, ZANNOTATIONNOTE, ZANNOTATIONSTYLE, datetime(ZANNOTATIONCREATIONDATE + 978307200, 'unixepoch', 'localtime') AS created FROM ZAEANNOTATION WHERE ZANNOTATIONDELETED = 0 AND ZANNOTATIONASSETID = 'ASSET_ID' AND length(ZANNOTATIONSELECTEDTEXT) > 0 ORDER BY ZPLLOCATIONRANGESTART ASC;" ``` ```text Replace `ASSET_ID` with the book's `ZASSETID` from the library query. ``` `SKILL.md:101-115`: ```bash BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)" AEANNOTATION_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite 2>/dev/null | head -1)" sqlite3 "$AEANNOTATION_DB" \ "ATTACH DATABASE '$BKLIBRARY_DB' AS lib; SELECT lib.ZBKLIBRARYA ...[truncated 3382 chars]
- Remediation
- ## Remediation Suggestions 1. Open every database using enforced read-only mode: ```bash sqlite3 -readonly "$BKLIBRARY_DB" ``` Also consider SQLite URI options such as `mode=ro` and `immutable=1` when compatible with live Apple Books databases. 2. Replace textual substitution with SQLite parameter binding. For example, use the CLI `.parameter` facility and bind the complete pattern rather than inserting user text into SQL: ```bash sqlite3 -readonly "$BKLIBRARY_DB" <<'SQL' .parameter init .parameter set @pattern '%user-supplied-value%' SELECT ZTITLE, ZAUTHOR, ZGENRE, ZREADINGPROGRESS, ZASSETID FROM ZBKLIBRARYASSET WHERE ZTITLE IS NOT NULL AND (ZTITLE LIKE @pattern OR ZAUTHOR LIKE @pattern) ORDER BY ZLASTOPENDATE DESC; SQL ``` The implementation must pass the value through a mechanism that preserves it as data, not generate the `.parameter` command itself through unsafe string concatenation. 3. Bind `ASSET_ID` as a query parameter. If binding is unavailable, validate it against the exact expected identifier format and reject all unexpected characters rather than relying only on quote escaping. 4. Do not interpolate database paths into `ATTACH DATABASE` statements. Prefer opening the required database separately, or use a trusted helper that binds filenames through the SQLite API. If attachment remains necessary, resolve and validate the path against the fixed Apple Books directory and reject symlinks or paths outside that directory. 5. Execute queries through a small, reviewed helper that exposes only predefined read operations and does not accept arbitrary SQL. 6. Apply least privilege where practical. Avoid granting Full Disk Access to a broad, network-facing Agent process; isolate database access in a narrowly scoped local helper and return only the requested fields. 7. Add adversarial tests covering single quotes, semicolons, SQL comments, wildcard characters, ma ...[truncated 152 chars]