Extract Telegram messages from db_sqlite PostBox – made for Telegram for macOS, but should work with Telegram for iOS

extract_telegram_macos.ipynb

Nice one, thank you! Would be great to be able to export whole dialogues to CSV

Awesome work! This works really great on SQLite db's pulled from iOS devices!

Works like a charm
Thanks

For take decrypted db:

pip install mmh3 pycryptodome pycryptodomex

# extract key

key_file = os.path.expanduser(
    '~/Library/Group Containers/6N38VWS5BX.ru.keepcoder.Telegram/stable/.tempkeyEncrypted'
)
with open(key_file, 'rb') as f:
    tempkeyEnc = f.read()
    dbKey, dbSalt = tempkey_parse(tempkeyEnc, DEFAULT_PASSWORD)
    print(tempkey_pragma(dbKey, dbSalt))

brew install sqlcipher

sqlcipher ~/Library/Group\ Containers/6N38VWS5BX.ru.keepcoder.Telegram/stable/account-*/postbox/db/db_sqlite
# enter key

# save db as decrypted copy

$ sqlcipher postbox/db/db_sqlite
SQLite version 3.37.2 2022-01-06 13:25:41 (SQLCipher 4.5.1 community)
Enter ".help" for usage hints.
sqlite> PRAGMA user_version;
-1179143289
sqlite> PRAGMA cipher_plaintext_header_size=32;
sqlite> PRAGMA cipher_default_plaintext_header_size=32;
sqlite> PRAGMA key="x'*****************************'";
ok
sqlite> ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';
sqlite> SELECT sqlcipher_export('plaintext');
sqlite> DETACH

Hi everyone,

thanks a lot for this script. I am having some problems in generating the right key from .tempkeyEncrypted. I am using chazer's script, which runs, but I believe returns me an incorrect key.
I am not sure I understand the def murmur(d): # seed from telegram return mmh3.hash(d, seed=-137723950) command properly. Could anyone please explain how I should obtain my own seed from Telegram? Also, should I leave DEFAULT_PASSWORD='no_matter_key' or should I populate it differently?

Many thanks!

Seed -137723950 is the default value from Telegram sources, keep it.
If you setup password in Telegram app – use next code tempkey_parse(tempkeyEnc, 'your-password-here')
Right script output should be SQL code like this:
PRAGMA key="x'1234567890abcdef1234567890abcdef1234567890abcdef1234567890abcdef'";

I can't find the tempkeyEncrypted in the current app, there is a notificationsKey fille but it is json and using either it directly or just the data field did not yield results, getting ValueError: Data must be padded to 16 byte boundary in CBC mode instead.

Thanks for the script btw.

@chicocvenancio notificationsKey is per account, you need instance level file .tempkeyEncrypted (notice the dot -- it's a hidden file).

~/Library/Group Containers/6N38VWS5BX.ru.keepcoder.Telegram/beta/account-*/notificationsKey -- the file you have found
~/Library/Group Containers/6N38VWS5BX.ru.keepcoder.Telegram/beta/.tempkeyEncrypted -- the file you need

@stek29 Thanks. it's in ~/Library/Group Containers/6N38VWS5BX.ru.keepcoder.Telegram/stable now but I did manage to decrypt it. Alas the messages I was trying to recover are not in the db. Kind of sad Telegram allows other parties to nuke the history with no possibility of recovery.

Where did you find that seed value of -137723950 ?
I can't get the hash to match for a binlog file generated with tdlib-1.8.5.so

@drzraf this is for native telegram clients for iOS/macOS/*OS, no idea on how tdlib does it.

And do you remember where in GitHub did you encountered the reference to this seed's value (or was it done using bitwise operators in a loop)? Would help to later spot its counterpart of TG Desktop and TDLib.

@drzraf just use search

@stek29 Thanks. it's in ~/Library/Group Containers/6N38VWS5BX.ru.keepcoder.Telegram/stable now but I did manage to decrypt it. Alas the messages I was trying to recover are not in the db. Kind of sad Telegram allows other parties to nuke the history with no possibility of recovery.

@chicocvenancio
TimeMachine helps me here)

there’s a tool called iLEAPP which now includes this code, but it’s targeted on iOS backup forensics

https://github.com/abrignoni/iLEAPP

@chazer @stek29

I was trying to recover my message from postbox/db/db_sqlite and i followed all steps, it also generated a 40 MB file plaintext.db but when i tried to open that with https://inloop.github.io/sqlite-viewer/

I'm getting this kind of data, I was expecting plain text messages. Any suggestions here? I am using Telegram on my Mac

@rohitcoder suggestion is to use the code literally in this gist to decode the database. they’re not in plaintext, they’re encoded in custom format

aww, I've got:
raise Exception (f'hash mismatch: {dbHash} != {calcHash}")
and mine crypted file was by path
/Group\ Containers/6N38VWS5BX.ru.keepcoder.Telegram/appstore/.tempkeyEncrypted
Anybody knows how to handle this?

@DreadfulBot
Maybe you setup password for Telegram?
Try change 'no-matter-key' to it.

@stek29 @chazer

This is awesome!

I followed the instructions and managed to get a 90Mb "plaintext.db" file, and the instruction:

con = sqlite3.connect('plaintext.db')

does connect to the DB, But it crashes when trying to access any of the tables. For example:

OperationalError Traceback (most recent call last)
Input In [31], in <cell line: 1>()
----> 1 for idx, msg in get_all_messages(f=lambda idx: idx.timestamp > 1443989880):
2 if MessageFlags.Incoming in msg['flags'] and 'web versions of Telegram' in msg['text']:
3 print_message(idx, msg)

Input In [9], in get_all_messages(f, decode)
2 cur = con.cursor()
3 try:
----> 4 cur.execute("SELECT key, value FROM t7 ORDER BY key")
5 for key, value in cur:
6 idx = MessageIndex.from_bytes(key)

OperationalError: no such table: t7

Any ideas?

I see that passcode is not discussed here. If the user sets passcode, will it affect the generation of the key and the decryption of the final sqlite?

@fr0zenrain local passcode is used instead of no-matter-key, if it’s set. This is different from 2FA password set on servers.
I’m not sure about iOS App digit passcode though.

I wonder, is there a way to get tempkey on Windows?

@yunasc what do you mean? there's only telegram desktop for windows, and it has no local database

It has an encrypted sqlite DB. I'm using Unigram client which uses tdlib which has local database.

You're lifesaver
saved my channel content from telegram who deleted it without warning and never responded to any of communication channels

By running the script above, I can only find one participant of the conversation.
When it is outgoing, there is no information about the sender.
When it is incoming, there is no information about the recipient.
It seems that the information of the owner of the phone is not available.

How to get the account details of the owner?

Does anyone know by any chance if there's a way to know if the auto-delete feature is enabled in a chat/conversation or not?