# SQLCipher WeChat DB Troubleshooting

## Session Context
- Date: 2026-06-25
- Device: Android 16 (Termux), rooted with Magisk
- Problem: MCP `get_wechat_messages` returned "file is not a database (26)"
- Root cause: Multiple interacting issues

## Issue 1: Wrong DB Path Selected

**Symptom**: `Parse error near line 5: file is not a database (26)`

**Cause**: Device had TWO WeChat accounts with EnMicroMsg.db in different MD5 directories:
- `/data/data/com.tencent.mm/MicroMsg/653cdc2320abb089e149a3f7a7683fcf/EnMicroMsg.db` (active, 12MB, recently modified)
- `/data/data/com.tencent.mm/MicroMsg/ec4f423b316d41e9af5e8398155cf105/EnMicroMsg.db` (inactive, 22MB, older)

Old discovery code used `head -1` which returned the first directory alphabetically — the inactive account's DB. This DB was either corrupted or had a different encryption key, causing "file is not a database".

**Fix**: Pick the most recently modified DB:
```python
# Find all EnMicroMsg.db files and pick the one with latest mtime
paths = [p.strip() for p in result.stdout.strip().splitlines() if p.strip()]
newest = max(paths, key=lambda p: os.stat(p).st_mtime)
```

## Issue 2: sqlcipher CLI TTY Requirement

**Symptom**: Same "file is not a database" even with correct DB path

**Cause**: sqlcipher CLI needs a TTY for the password prompt. Using `input=script` via `subprocess.run()` fails because the PRAGMA key isn't properly applied before SQL execution.

**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 ... FROM message LIMIT 3;"
```

**Key insight**: The `-cmd` flags execute BEFORE the SQL statement, so the DB is properly decrypted when the query runs. Pipe-based `input=script` can fail because the PRAGMAs and SQL may be parsed differently.

## Issue 3: Shell Quoting with SQL Special Characters

**Symptom**: `can't open msg: No such file or directory` or `Parse error in 10th command line argument`

**Cause**: SQL containing `<`, `>`, `%`, `@`, single quotes gets mangled by shell interpretation in `su -c "..."`.

Examples that break:
- `content NOT LIKE '%<msg>%'` — `<msg>` is interpreted as shell redirection
- `'%@chatroom'` — `%` may trigger shell expansion in some contexts
- Single quotes inside the SQL string conflict with the outer shell quoting

**Fix**: Use `shlex.quote()` for EVERY argument when building the shell command:
```python
import shlex
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], ...)
```

**Even better**: Use parameter binding (`?` placeholders) to avoid inline SQL literals:
```python
# Instead of:
sql = "AND talker NOT LIKE '%@chatroom'"  # Dangerous: shell sees % and '

# Use:
sql = "AND talker NOT LIKE ?"
params = ["%@chatroom"]
# Then _bind_sql() inlines the properly-quoted value
```

## Issue 4: MCP Process Not Reloading After Code Changes

**Symptom**: Code fixes applied but MCP still returns old errors

**Cause**: The `android_mcp.py` MCP server process was still running with old code. Hermes auto-restarts it, but only after the old process dies.

**Fix**: Kill the old process explicitly:
```bash
ps aux | grep android_mcp | grep python
# Then: kill <pid>
```

Or use the `deploy_phoneuse_code` MCP tool if available (it pushes code and restarts).

## Verification Commands

```bash
# 1. Check which DB is active (most recently modified)
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"

# 2. Test sqlcipher directly with -cmd flags
su -c "sqlcipher /path/to/EnMicroMsg.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;'"

# 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: random bytes (e.g., 86 b4 ff a9 ...), NOT 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00
```

## Summary of Fixes Applied

1. **DB discovery**: `head -1` → find all + pick by mtime
2. **sqlcipher invocation**: `input=script` → `-cmd` flags + `shlex.quote`
3. **SQL parameter binding**: Inline `'%@chatroom'` → `?` placeholder with `_bind_sql()`
4. **MCP reload**: Kill old process after code changes

## Related

- `android-root-troubleshooting` — Root verification when user insists they have root
- `wechat-group-digest` — WeChat data extraction and summarization