# SQLCipher WeChat DB Access — Reference ## Context - Date: 2026-06-25 - Device: Android 16, Termux, Magisk root - Problem: MCP `get_wechat_messages` returned "file is not a database (26)" - Resolution: Four interacting fixes required ## Critical: SQLCipher Key Derivation (New Finding) **The WeChat EnMicroMsg.db encryption key is NOT simply `MD5(UIN)[:7]`.** ### What WeChat Uses WeChat's SQLCipher database uses a key derived from device-specific parameters. The common derivations are: 1. **Simple UIN** (older WeChat versions): `MD5(str(UIN))[:7]` - Example: UIN=215004919 → `MD5("215004919")` → `8eada17...` → key=`8eada17` - This works on some older WeChat versions but **fails on newer ones** 2. **IMEI + UIN** (common): `MD5(IMEI + UIN)[:7]` - Requires the device's IMEI number - IMEI can be found via: `service call iphonesubinfo 1` (may need root) - Or in `/data/data/com.tencent.mm/shared_prefs/` files (varies by version) 3. **Full 32-char MD5** (some versions): Use the complete MD5 hash, not truncated ### How to Verify the Key ```bash # Check if the key works - if you get "file is not a database (26)", the key is wrong sqlcipher /path/to/EnMicroMsg.db \ -cmd "PRAGMA key='YOUR_KEY';" \ -cmd "SELECT count(*) FROM sqlite_master;" ``` **Database header verification**: ```bash # Encrypted header (SQLCipher 4.x) looks like: xxd /path/to/EnMicroMsg.db | head -1 # 00000000: 56d3 ac26 7fcd 1a65 07bd 7efa f7b2 dbba V..&...e..~..... # ^^ The first bytes 56 d3 ac 26 confirm SQLCipher 4.x encryption # With correct key, after decryption it becomes standard SQLite: # 00000000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3. ``` ### Finding the Correct Key If the simple UIN key fails: 1. **Try the full 32-char MD5** instead of truncating to 7 chars: ```python import hashlib uin = 215004919 full_key = hashlib.md5(str(uin).encode()).hexdigest() # 32 chars short_key = full_key[:7] # 7 chars ``` 2. **Find the IMEI** and try `MD5(IMEI + UIN)`: ```bash # Method 1: Android service call (may need root) su -c "service call iphonesubinfo 1" # Method 2: Check WeChat prefs (varies by version) su -c "cat /data/data/com.tencent.mm/shared_prefs/*.xml | grep -i imei" # Method 3: dumpsys su -c "dumpsys telephony.registry | grep deviceId" ``` 3. **Check if WeChat uses a different key file**: ```bash # Some versions store the key in a .ini file next to the DB su -c "cat /data/data/com.tencent.mm/MicroMsg/*/EnMicroMsg.db.ini" ``` 4. **Use the `sqlcipher` CLI with `-cmd` to test keys quickly**: ```bash # Test a key without entering interactive mode sqlcipher /path/to/EnMicroMsg.db \ -cmd "PRAGMA key='8eada17';" \ -cmd "SELECT count(*) FROM sqlite_master;" ``` 5. **Try different cipher_compatibility values** if the key seems correct but still fails: - `PRAGMA cipher_compatibility = 1;` (older WeChat) - `PRAGMA cipher_compatibility = 2;` (intermediate) - `PRAGMA cipher_compatibility = 3;` (SQLCipher 4.x default) - `PRAGMA cipher_compatibility = 4;` (newest) ## Fix 1: Multi-Account DB Selection WeChat stores each account's data in a separate MD5-named directory: ``` /data/data/com.tencent.mm/MicroMsg/ 653cdc2320abb089e149a3f7a7683fcf/EnMicroMsg.db ← active account (12MB, recent) ec4f423b316d41e9af5e8398155cf105/EnMicroMsg.db ← inactive account (22MB, older) ``` **Pitfall**: Using `head -1` or `find ... | head -1` picks the first alphabetically, which may be the inactive/corrupted DB. **Fix**: Pick the most recently modified DB: ```python import subprocess, os result = subprocess.run( ["su", "-c", "find /data/data/com.tencent.mm/MicroMsg -name 'EnMicroMsg.db'"], capture_output=True, text=True, timeout=5 ) paths = [p.strip() for p in result.stdout.strip().splitlines() if p.strip()] newest = max(paths, key=lambda p: os.stat(p).st_mtime) ``` ## Fix 2: sqlcipher CLI Invocation **Pitfall A**: The `sqlcipher` binary installed via `pkg install sqlcipher` is at `/data/data/com.termux/files/usr/bin/sqlcipher`. When running via `su -c`, the root shell may not have Termux in its PATH, causing "sqlcipher: inaccessible or not found". Always use the full path: `/data/data/com.termux/files/usr/bin/sqlcipher`. **Pitfall B**: Piping PRAGMAs via `input=script` to sqlcipher fails because the CLI needs a TTY for the password prompt, even when using `PRAGMA key=`. **Fix**: Use `-cmd` flags to pass PRAGMAs as command-line arguments: ```bash sqlcipher "$DB" \ -cmd "PRAGMA key=e03ce81;" \ -cmd "PRAGMA cipher_compatibility=1;" \ -cmd "PRAGMA cipher_page_size=1024;" \ -cmd ".mode json" \ "SELECT msgId, talker, content FROM message LIMIT 3;" ``` The `-cmd` flags execute BEFORE the SQL statement, so the DB is properly decrypted when the query runs. ## Fix 3: Shell Quoting with SQL Special Characters **Pitfall**: SQL containing `<`, `>`, `%`, `@`, single quotes gets mangled by shell interpretation in `su -c "..."`. Examples that break: - `content NOT LIKE '%%'` — `` interpreted as shell redirection - `'%@chatroom'` — `%` may trigger shell expansion - Single quotes inside SQL conflict with outer shell quoting **Fix**: Use `shlex.quote()` for EVERY argument: ```python import shlex, subprocess cmd_list = [ sqlcipher, db_path, "-cmd", "PRAGMA key={};".format(key), "-cmd", "PRAGMA cipher_compatibility=1;", "-cmd", "PRAGMA cipher_page_size=1024;", "-cmd", ".mode json", bound_sql + ";", ] quoted_cmd = " ".join([shlex.quote(arg) for arg in cmd_list]) result = subprocess.run(["su", "-c", quoted_cmd], capture_output=True, text=True, timeout=15) ``` **Even better**: Use parameter binding (`?` placeholders) to avoid inline SQL literals: ```python # Instead of: sql = "AND talker NOT LIKE '%@chatroom'" # Dangerous # Use: sql = "AND talker NOT LIKE ?" params = ["%@chatroom"] # Then inline via _bind_sql() before shlex.quote ``` ## Fix 4: MCP Process Reload After Code Changes **Pitfall**: After fixing `android_mcp.py`, the MCP server process still runs old code. **Fix**: Kill the old process and let Hermes auto-restart: ```bash ps aux | grep android_mcp | grep python kill ``` Or use `mcp_android_phone_deploy_phoneuse_code(restart=True)` if available. ## Verification Commands ```bash # Check which DB is active su -c "ls -la /data/data/com.tencent.mm/MicroMsg/*/EnMicroMsg.db" su -c "stat -c '%Y' /data/data/com.tencent.mm/MicroMsg/*/EnMicroMsg.db" # Test sqlcipher directly with a key su -c "sqlcipher /path/to/EnMicroMsg.db \ -cmd 'PRAGMA key=8eada17;' \ -cmd 'PRAGMA cipher_compatibility=1;' \ -cmd 'PRAGMA cipher_page_size=1024;' \ -cmd '.mode json' \ 'SELECT msgId, talker, content FROM message LIMIT 3;'" # Check DB file header (should be encrypted, not SQLite magic) su -c "head -c 16 /path/to/EnMicroMsg.db | od -A x -t x1 -v" # Expected encrypted: 56 d3 ac 26 ... (SQLCipher 4.x) # Expected decrypted: 53 51 4c 69 ... (SQLite format 3) # Find WeChat UIN (needed for key derivation) su -c "cat /data/data/com.tencent.mm/shared_prefs/auth_info_key_prefs.xml | grep _auth_uin" ``` ## Key Parameters | Parameter | Value | Notes | |-----------|-------|-------| | SQLCipher key | Varies | `MD5(UIN)[:7]` or `MD5(IMEI+UIN)[:7]` or full 32-char MD5 | │ cipher_compatibility | 1 | Required for WeChat DB format | │ cipher_page_size | 1024 | Required for WeChat DB format | │ query_only | true | Best practice for read-only access — prevents accidental writes | │ .mode json | — | For machine-parseable output | ## Fix 5: sqlcipher "ok\\n" Output Prefix **Pitfall**: When sqlcipher CLI runs successfully (especially via `su -c`), it prints `ok\n` BEFORE the JSON output. This breaks `json.loads()`: ``` Raw stdout: ok [{"talker":"3164323182@chatroom","content":"...","createTime":1783420530000}] Naive json.loads("ok\n[...]") → JSONDecodeError ``` **Fix**: Strip the "ok" prefix before parsing: ```python output = result.stdout.strip() if output.startswith("ok"): output = output[2:].strip() rows = json.loads(output) # now: [{"talker":..., ...}] ``` **Also note**: With `.mode json`, arrays with multiple rows are pretty-printed across multiple lines. Do NOT attempt line-by-line JSON parsing of array fragments — strip the "ok" prefix first, then parse the entire remaining string as a single JSON document. ## Key Derivation Troubleshooting Flow ``` 1. Get UIN from auth_info_key_prefs.xml ↓ 2. Try simple key: MD5(UIN)[:7] ↓ 3. If fails ("file is not a database"): a. Try full 32-char MD5 b. Try MD5(IMEI + UIN)[:7] (need IMEI) c. Check EnMicroMsg.db.ini for key hints d. Try different cipher_compatibility values (1, 2, 3) ↓ 4. Verify with sqlcipher -cmd "SELECT count(*) FROM sqlite_master;" ↓ 5. If count > 0, key is correct! ``` ## Related - `android-root-troubleshooting` — Root verification when user insists they have root - `wechat-group-digest` — WeChat data extraction and summarization workflows