--- name: wechat-db-access description: Read and query WeChat's local SQLite databases (EnMicroMsg.db) on Android with root. Covers SQLCipher decryption, message table schema, image/voice file path resolution, message content parsing, and building read-only tools on top of WeChat data. Load when building any WeChat data viewer, extractor, digest generator, or data pipeline. version: 1.0 category: android --- # WeChat DB Access Patterns ## When to Use - Building a WeChat message viewer, search tool, or data export - Extracting messages from specific chats for analysis - Resolving WeChat image/voice file paths for serving or processing - Querying WeChat contact and chatroom data - Any project that reads directly from WeChat's SQLite databases ## DB Location & Key WeChat's main message DB is `EnMicroMsg.db` under `/data/data/com.tencent.mm/MicroMsg/<32-char-hash>/`. Find it: ```bash su -c "find /data/data/com.tencent.mm/MicroMsg -name 'EnMicroMsg.db' -printf '%T@ %p\n' 2>/dev/null | sort -rn | head -1" ``` SQLCipher key is usually a short hex string (e.g. `e03ce81`). On this device it's read from `~/pixelphone/event_bus/wechat_key` or environment variable `WECHAT_KEY`, with a hardcoded fallback. ## Querying via sqlcipher CLI Always use `su` because WeChat's data directory is owned by the WeChat UID: ```bash su -c 'sqlcipher /path/to/EnMicroMsg.db \ -cmd "PRAGMA key=;" \ -cmd "PRAGMA cipher_compatibility=1;" \ -cmd "PRAGMA cipher_page_size=1024;" \ -cmd "PRAGMA query_only=true;" \ -cmd ".mode json" \ "SELECT ..."' ``` **PITFALL**: sqlcipher prints `ok\n` before the JSON output. Strip `"ok"` prefix before `json.loads()`. **PITFALL**: sqlcipher `.mode json` may pretty-print multi-line JSON arrays. Strip the `ok\n` prefix first, THEN parse the full output as JSON — don't parse line-by-line. ## Key Tables | Table | Key Columns | Notes | |-------|------------|-------| | `message` | msgId, type, isSend, talker, content, createTime, imgPath | Main messages table | | `rcontact` | username, nickname, conRemark, alias | Contacts + group names | | `chatroom` | chatroomname, displayname, memberCount | Chatroom metadata | ## Message Types | type | Meaning | Content field | imgPath field | |------|---------|--------------|---------------| | 1 | Text | Text or XML-wrapped text | — | | 3 | Image | XML with CDN URLs | `THUMBNAIL_DIRPATH://th_` | | 34 | Voice | XML with `` | Voice filename hash | | 43 | Video | XML | — | | 47 | Emoji/sticker | XML | — | | 49 | Article/link/appmsg | XML with ``, ``, `<des>`, `<thumburl>` | — | ### High/unknown message types WeChat uses very large type numbers (e.g. 822083633, 754974769) for internal structures: - **822083633**: Reply/reference with `<refermsg>` block — references a prior message. Extract `<title>` and `<displayname>` from the XML. - **754974769**: Special unsupported message ("当前微信版本不支持展示该内容"). For unknown types, try this fallback chain: 1. Extract `<title>` from XML → if found, use it as display text 2. If `<refermsg>` present → parse reference info, show "[引用XX的消息] title" 3. If `<img>` in content → show "[引用图片]" 4. If `<voicemsg>` → show "[引用语音]" 5. Otherwise strip XML tags. If result >200 chars, truncate to 150 (likely metadata garbage). 6. If nothing useful — show "[未知消息 type=N]" ### Article card fields (type 49) For rich article display in web UIs, extract these from the XML: - `<title>` — article title - `<url>` — article URL - `<des>` — article description/summary - `<thumburl>` — CDN thumbnail (binary protocol, not HTTP — use placeholder if needed) **HTML unescaping**: WeChat XML content often contains ` ` (space), `&`, etc. Use `html.unescape()` on extracted text fields. ### Flask debug=False pitfall When building WeChat web services with Flask in production mode (`debug=False`): - Templates do NOT auto-reload on file changes - Must restart the server manually after template edits - Use `debug=True` during development, switch to `False` for stable serving ## Content Parsing ### Chatroom messages have `sender:\n` prefix ``` yibooo:\nok 我来也整一个 快速看齐 ``` Split on first `\n` when the first segment contains `:`: ```python if "\n" in content and ":" in content.split("\n")[0]: display_text = content.split("\n", 1)[1] ``` ### XML content Type 3/34/43/47/49 messages wrap data in `<msg>...</msg>` XML. For text display, strip all tags: `re.sub(r"<[^>]+>", "", text)`. For article links (type 49), extract `<url>` and `<title>` from the raw content. ## Group Names WeChat stores the custom group name in `rcontact.nickname` for `@chatroom` usernames. The `chatroom.displayname` field contains member nicknames, NOT the group name. ```sql -- Correct: get group name from rcontact SELECT COALESCE(NULLIF(r.nickname, ''), c.displayname, c.chatroomname) AS group_name FROM chatroom c LEFT JOIN rcontact r ON c.chatroomname = r.username ``` When the group has no custom name (rcontact.nickname is empty), fall back to `chatroom.displayname` (member names). ## Image Path Resolution WeChat's `imgPath` field stores paths like `THUMBNAIL_DIRPATH://th_<32-char-hash>`. Actual file layout: `image2/<first2>/<next2>/th_<hash>` under the MicroMsg hash directory. ```python hash_part = img_path.replace("THUMBNAIL_DIRPATH://", "").replace("th_", "") sub1, sub2 = hash_part[:2], hash_part[2:4] real_path = f"{WECHAT_DATA_ROOT}/image2/{sub1}/{sub2}/th_{hash_part}" ``` For full images (not thumbnails), try the same path without `th_` prefix. ## Voice Path Resolution Voice files have a complex directory structure that is NOT simply hash-prefix-based. The `imgPath` stores a bare hash (e.g. `05142107062628ef8e19f29104`), while the actual file is at `voice2/XX/YY/msg_<hash>.amr`. **Use `find` to locate voice files** — the subdirectory derivation is not straightforward: ```python # imgPath: "05142107062628ef8e19f29104" # actual: voice2/66/33/msg_05142107062628ef8e19f29104.amr su -c "find {WECHAT_DATA_ROOT}/voice2 -name '*{hash}*' | head -1" ``` ## Permission Handling Termux runs as a different UID than WeChat. All file operations on WeChat's data dir require `su`: - **Check existence**: `su -c "test -f <path> && echo yes"` - **Read/copy**: `su -c "cp <src> <tmp> && chmod 644 <tmp>"` — stage to /tmp, then read as Termux user - **`Path.exists()` will throw `PermissionError`** — wrap with try/except and fall back to `su` - **`glob.glob()` silently returns `[]` (no exception)** when the target dir is owned by another UID. This is the harder-to-diagnose failure mode: code like `for xml in glob.glob(f"{MM_BASE}/shared_prefs/*.xml")` simply loops zero times, so downstream logic gets "no files found" rather than a permission error. Any Python code that scans WeChat's data dir with `glob` must use a `su -c` fallback: ```python def _safe_glob(pattern: str) -> list[str]: """glob that falls back to su for dirs owned by another UID (e.g. WeChat shared_prefs).""" import glob as _g hits = _g.glob(pattern) if hits: return hits # fallback: su -c ls (glob returns [] silently on PermissionError, unlike Path.exists) r = subprocess.run(["su", "-c", f"ls -1 {pattern} 2>/dev/null"], capture_output=True, text=True, timeout=5) return [line.strip() for line in r.stdout.splitlines() if line.strip()] if r.returncode == 0 else [] ``` Helpers: ```python def _safe_exists(path: str) -> bool: try: return Path(path).exists() except PermissionError: r = subprocess.run(["su", "-c", f"test -f {path} && echo yes || echo no"], capture_output=True, text=True, timeout=5) return "yes" in r.stdout def _su_read_text(path: str) -> str: """Read a file owned by another UID via su (e.g. WeChat shared_prefs XML).""" r = subprocess.run(["su", "-c", f"cat {path}"], capture_output=True, text=True, timeout=5) return r.stdout if r.returncode == 0 else "" def _su_copy(src: str, dst: str, chown_uid: int = None, chown_gid: int = None) -> bool: """Copy a file owned by another UID via su. After su cp, the copy is root-owned; os.chown() will fail silently (PermissionError swallowed by except-pass). Must use su -c chown to fix ownership so non-root processes can read the copy.""" r = subprocess.run(["su", "-c", f"cp {src} {dst}"], capture_output=True, timeout=60) if r.returncode != 0: return False if chown_uid is not None: subprocess.run(["su", "-c", f"chown {chown_uid}:{chown_gid} {dst}"], capture_output=True, timeout=10) return True def _safe_chown(path: str, uid: int, gid: int) -> bool: """chown that falls back to su when os.chown fails (non-root Termux).""" try: os.chown(path, uid, gid) return True except (OSError, PermissionError): r = subprocess.run(["su", "-c", f"chown {uid}:{gid} {path}"], capture_output=True, timeout=10) return r.returncode == 0 ``` **PITFALL: `os.chown()` fails silently on non-root Termux** — after `su -c cp`, the copied file is owned by root. `os.chown()` raises `PermissionError` which gets swallowed by `except OSError: pass`, leaving the file root-owned and unreadable by the Termux process. Always use `_safe_chown()` (or `su -c chown` directly) after any `su -c cp` operation. ## Multi-Account Key Storage When multiple WeChat users share a device, each has its own `EnMicroMsg.db` under a different hash directory. Keys differ per account (MD5(IMEI + UIN)[:7]). **Per-account key file pattern** (used by WeChat Viewer): ``` ~/wechat-viewer/.wechat_key_<32-char-dir-hash> ``` Example: ```bash echo "e03ce81" > ~/wechat-viewer/.wechat_key_653cdc2320abb089e149a3f7a7683fcf echo "<other_key>" > ~/wechat-viewer/.wechat_key_ec4f423b316d41e9af5e8398155cf105 ``` **Per-account display name**: ```bash echo "小伙子" > ~/wechat-viewer/.wechat_name_653cdc2320abb089e149a3f7a7683fcf echo "火星人" > ~/wechat-viewer/.wechat_name_ec4f423b316d41e9af5e8398155cf105 ``` **Discovery**: `su -c "ls -d /data/data/com.tencent.mm/MicroMsg/*/"` finds all account dirs. Test each DB with key: `su -c "sqlcipher path -cmd 'PRAGMA key=...;' 'SELECT sqlite_version();'"`. If "file is not a database" → wrong key. **Frida key extraction**: Hook `net.sqlcipher.database.SQLiteDatabase.openDatabase()` to capture password. BUT Android 16 + Frida 17.x has attach timeout issues. Key files are more reliable. Only use Frida as last resort and with WeChat process actively running. When building Flask/Django services that serve WeChat media: 1. Stage files to `/tmp` via `su cp` — Flask can't read root-owned files 2. Cache staged files (check `/tmp` before re-copying) 3. Voice files need ffmpeg transcoding to MP3 for browser playback (AMR/SILK not browser-compatible) ## Communication skills These complement db-access (load as needed): - `wechat-voice-sender` — TTS → voice message delivery - `wechat-image-sender` — image delivery via internal hook - `wechat-group-digest` — digest generation patterns ## Reference Files - `references/task-verification.md` — 验证助手在微信里回复「已处理」的任务是否真正完成,还是卡住/失败 - `references/device-config.md` — Current device DB path, key, chatroom IDs - `references/viewer-template.md` — Flask project template location