I tried the following command on Telegram a db_sqlite file (in ~/Library/Group Containers/XXX.ru.keepcoder.Telegram/appstore/account-XXX/postbox/db on MacOS).

$ sqlite3 -readonly -header -separator $'\t' db_sqlite 'select * from sqlite_schema;'
Error: in prepare, unsupported file format
$ file db_sqlite
db_sqlite: SQLite 3.x database, application id 725912995, user version -1656195813, last written using SQLite version -447277415, writer version 2, read version 2, unused bytes 80, file counter 22, database pages 917264, 1st free page 4041604518, free pages 2554609024, cookie 0xfccbce3d, schema 3936997175, cache page size 2747461051, largest root page 2225227023, unknown 0x150332b1 encoding, vacuum mode 2922976394, reserved 0xbd28c816fae1795, version-valid-for 1050460445

Why does it say "unsupported" when the file is an sqlite database file?

What are you hoping to achieve with these options?

-header -separator $'t'

I should have thought they'd only be used with a CSV file, not an sqlite database.

Your command line is completely foobar. Use sqlite3 --help for help.

Just incant:

sqlite3 db_sqlite

in the directory containing the db_sqlite file, and then enter the commands to sqlite3 at the sqlite3 prompt. Of course, it is possible that the db_sqlite is not, in fact, a standard sqlite3 database. It may be something else masquerading as an SQLite3 database. (Which is pretty simple to do -- just make the first few bytes of the file match the SQLite3 file stamp, just like you can make any file into a DOS executable by simply putting Mickey Zelane (MZ) as the first two characters).

The file does appear to be mostly an SQLite file as there are two other files accompanying it.

• db_sqlite-shm
• db_sqlite-wal

How to find out exactly what has been changed so that one can dump content from this database file db_sqlite?

THose are just names. Anything can use any name it wants at any time.

If you believe that it is truly an sqlite3 file, then why not open it with sqlite3?

That is, in the directory which contains the thing you think is an sqlite3 file issue the command:

sqlite3 filename

where filename is the name of the file you think is an sqlite3 database. If the file opens and you get an sqlite> prompt, you may then enter valid sqlite3 dot commands or SQL statements. Type .help for help and a listing of available dot commands. .help dot-command will give you help on a specific dot-command.

SQL is standard SQL. Refer to the website for syntax details.

How to find out exactly what has been changed so that one can dump content from this database file db_sqlite?

If you tried opening the file using the command sqlite3 filename without all the other cruft and crappola and you got the same error when you tried to run an SQL command, that means that whomever wrote the application that created the file made it "look like" a standard SQLite3 database file, but it is not.

You would have to contact the people who wrote the application that made the file and get the answer from them.

(FWIW) I second Keith's conclusion here. I will add to it also.

Even with the extraneous invocation options, (aka "cruft and crappola"), if the file named by "db_sqlite" had been a legitimate SQLite version 3 database file, the OP would have gotten a different error message. Those "extra" options have no effect on the successful execution of the query in the command tail when a true, SQLite version 3 database file is opened. So I conclude that the named file is not a simple SQLite 3 DB (or DB plus WAL etc.)

I would also bring out the possibility that the filenames may not be merely cavalier abuse of sensible naming conventions. It may be that the files are encrypted, and cannot be opened without having the decrypt layer in place.

I would suggest you ask the Telegram developers.

They probably used Sqlite originally, but modified it.

Another example is Linux Tracker3, which I was checking a couple of days ago.

I could get the schema, but couldn't go much further.

if you search for "telegram sqlite db" one of the top hits you get is https://www.bleepingcomputer.com/news/security/telegram-desktop-saves-conversations-locally-in-plain-text/

If you open the file with less, you get binary gobbledegook, but the content starts with SQLite format 3

➜  ~ cd ~/Library/Group\ Containers/6N38VWS5BX.ru.keepcoder.Telegram/appstore/account-1743458192770843184/postbox/db
➜  db ls -l
total 36264
-rw-r--r--@ 1 punkish  staff    14M Feb 13 20:03 db_sqlite
-rw-------@ 1 punkish  staff     0B Aug 31  2021 db_sqlite-guard
-rw-r--r--@ 1 punkish  staff    32K Feb 13 20:03 db_sqlite-shm
-rw-r--r--@ 1 punkish  staff   4.0M Feb 13 20:03 db_sqlite-wal
➜  db less db_sqlite
SQLite format 3^@^P^@^B^BP@  ^@^@^@F^@^@^M<9F>  ^N<FF>^LQ<CB>P<80>v+<D9>ue<EC>}><FD><9F>>^EO<B0>#<F7>?<BC>^F^E^K<F5>I㒞<B5><FB><F3>@d^^M<86><9B>^F&<F6>m<B3>#<E5><A8>N!<A2>^?`G <A8>+^?!<B9>sRv^E5^Z<AE>.<AB><A3><A5><j<85><CE><F1>ˑ

with a little bit of imagination, I think Telegram is indeed using sqlite, but while this db was openable by the end-user as of 2018, Telegram is likely encrypting this now, which is why you are unable to open it directly on your computer.

Indeed, as you realized, the db is encrypted.

Assuming that you're trying to analyze your local Telegram db, you may be interested in checking out this effort that uses Python:

extract_telegram_macos.ipynb

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

https://gist.github.com/stek29/8a7ac0e673818917525ec4031d77a713

I generally like to avoid argument about what posts say. However, ...

Keith Medcalf and Larry Brasfield's replies make no sense. If the Telegram db had completely no relation to sqlite3, they would not have accidentally used the suffix -wal and -shm. It is more likely that they use some sqlite3 code but with encryption or some other alterations.

What Keith and I said is that certain CLI arguments were extraneous. Keith said that you might see whether your purported DB file was a SQLite DB file by trying to open it with the CLI without those arguments. I said that, given the facts adduced at that point, your purported DB file was clearly not a SQLite DB file but might be an encrypted SQLite DB file.

To claim that Keith's and my own observations "make no sense" demonstrates more a failure of reading comprehension on the reader's part than an absence of "sense" in the observations. As most people can see, those were germane and accurate. In particular, my observation, "It may be that the files are encrypted, and cannot be opened without having the decrypt layer in place." is perfectly consistent with your own statement, "It is more likely that they use some sqlite3 code but with encryption or some other alterations."

I was referring to something like this. This kind of reply did not help at all. I don't know to open the file with sqlite3? What is the point of replying like this?

"THose are just names. Anything can use any name it wants at any time.

If you believe that it is truly an sqlite3 file, then why not open it with sqlite3?"

And you seconded it. Did I say all you wrote did not make sense? I didn't. If there is one piece in what your wrote not making sense. Then, it is not making sense.

... What is the point of replying like this?

It is unclear what "this" is to which you refer. As to a "point", see below.

... And you seconded it.

I will only explain why I was able to "second" a "conclusion" in Keith's post.

His observations: (1) that you should be able to use the SQLite CLI to open a SQLite 3 database file; and (2) that the filenames do not have to mean that the files were created as a SQLite database because filenames can be chosen by their creating program; are perfectly correct. They make sense.

Keith's question, "why not open it with sqlite3?", is quite reasonable in its context, where you seemed to have ignored his suggestion that you do that if you believed "it" was a SQLite DB file.

However, what I seconded was his "conclusion", not any questions or irrelevant commentary or characterization that might be found alongside those conclusions.

Did I say all you wrote did not make sense? I didn't.

The simplest parsing of "replies make no sense" (from your post #11) is that it asserts that the replies contain nothing that makes sense. There are ways of saying that portions of something make no sense, so a sweeping, unqualified "replies" is most sensibly taken to refer to whole replies.

If there is one piece in what your wrote not making sense. Then, it is not making sense.

If have to disagree. And I would urge you, when deciding to ignore what people have to say here, to extract what sense you can from their posts. And for those points which do not make sense to you, a request for clarification, detailing what was unclear (or made no sense to you), may very well lead closer to a resolution of the ostensible problem which is a thread's topic.

... Is this so difficult?

I also wonder if it is that difficult to realize that the "extra" options are very much legit options to setup a viewing convenience.

Nothing esoteric or "crapola", god forbid, just a --help away:

sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]

   -[no]header          turn headers on or off
   -readonly            open the database read-only
   -separator SEP       set output column separator. Default: '|'

Pointing the OP at the presence of these options on the command-line is indeed unhelpful, this does not solve the problem of opening the db, nor reveals that the db may be encrypted.

Why do you assume that I did not know it? Of course, it didn't work.