sqlcipher-wechat-db-access.md 8.87 KB

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

# 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:

# 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:

    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):

    # 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"
  1. Use the sqlcipher CLI with -cmd to test keys quickly:

    # Test a key without entering interactive mode
    sqlcipher /path/to/EnMicroMsg.db \
     -cmd "PRAGMA key='8eada17';" \
     -cmd "SELECT count(*) FROM sqlite_master;"
    
  2. 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:

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:

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 '%<msg>%' — <msg> 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:

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:

# 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:

ps aux | grep android_mcp | grep python
kill <pid>

Or use mcp_android_phone_deploy_phoneuse_code(restart=True) if available.

Verification Commands

# 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:

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!
  • android-root-troubleshooting — Root verification when user insists they have root
  • wechat-group-digest — WeChat data extraction and summarization workflows