SQLite Forum

Trace queries from external application on SQLite file

Trace queries from external application on SQLite file

(1) By Pseudo Nym (pseduo-nym) on 2020-04-14 07:44:02 [link] [source]

Dear forum,

I'd like to trace the queries an application makes on an SQLite file.

However, I don't have the sources of said application to enable any kind of tracing there. Instead, I'd like to e.g. connect to the SQLite file in parallel, configure the .sqliterc file accordingly, or emulate a virtual SQLite file, so that every incoming query is logged to an external file.

I've not found a proper way, yet. Any ideas?

Best regards

(2) By Stephan Beal (stephan) on 2020-04-14 07:55:54 in reply to 1 [source]

Having access to the db file would not provide any access to the SQL - queries do not run through the database file. In order to trace the SQL you have to have access to the C API (i.e. you need the app source code or the ability to link against it and get a handle to its sqlite3 db object) and to configure the connection using the trace API.

(3) By Simon Slavin (slavin) on 2020-04-14 16:06:04 in reply to 1 [link] [source]

Tracing when another application is making changes might be possible using PRAGMA data_version.

Tracing when another application is using SQLite to read the file is possible only by doing it the same way you would if the file was a text file. If you can find some feature of your operating system which lets you do it with a text file, just do the same thing with the SQLite database file.

(4) By ddevienne on 2020-04-14 16:32:01 in reply to 1 [link] [source]

It's not possible in general, if you do not control that application.
SQLite is an embedded database, so it runs within the application
itself, so without full control of the source of that app, no can do.

Unless that application depends on an SQLite shared library, as
opposed to statically linked against it, and you provide you own
SQLite shared-lib that intercepts all calls. But good luck doing that :)

On Linux, at the OS level, you can trace the IO done against the DB
but that wouldn't tell you what SQL the app is running, only which
pages it read, which could, if you knew what the page mean (via the
sqlite_analyzer app, or DBSTAT virtual table) at least know which
tables are accessed.

In all cases, that's a tall order... --DD