[parser] Apollo - more SQL queries

[cvandeplas]

See https://github.com/mac4n6/APOLLO/issues/29

Thank you very muc

h for this excellent work ! Amazing ! I wanted to give a heads up that we started using your code (a bit revamped) in the https://github.com/EC-DIGIT-CSIRC/sysdiagnose project.

The apple sysdiagnose files contain some of the db files you're parsing, so your code really comes in handy. For now I have just copy pasted the modules, merged a few of the contributions some other people did. (@Shadow0ps, @stark4n6, @raidrob). It's not the best approach on long-term, but was an easy way to keep on moving while figuring out things.

One of the big challenges we see is to continue developing the modules / SQL queries. Maintaining these is important if we want to keep up with changes in the database structures, and appearance of new databases.

A few questions come up for @mac4n6 and @Shadow0ps, @stark4n6, @raidrob:

Have you had some time to think on your plans about this project? Do you plan to maintain it further or not? Do you have some recommendations, processes and/or tooling on how to track delta's between database formats?

[dummys]

Hello, I have an idea for the apollo module. Is it possible to implement a new switch to the sysdiagnose tool in order to tell the IOS/MAC version ? Because the apollo query rely mainly on the version of the OS. For example in the module tcc_db.txt the second query produce error on IOS 18:

[WARNING] [apollo] WARNING: Cannot fetch query contents for query with name: tcc_db.
Traceback (most recent call last):
  File "/virtualenvs/sysdiagnose/lib/python3.13/site-packages/sysdiagnose/utils/apollo.py", line 144, in parse_db
    cur.execute(module_query['sql'])
    ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such column: ALLOWED

The query is:

[SQL Query 12,13,10.14,10.15]
QUERY=
	SELECT 
		DATETIME(LAST_MODIFIED,'UNIXEPOCH') AS "LAST MODIFIED",
		SERVICE AS 'SERVICE',
		CLIENT AS 'CLIENT',
		CASE ALLOWED 
			WHEN 0 THEN 'NOT ALLOWED'
			WHEN 1 THEN 'ALLOWED'
		END AS 'ALLOWED',
		CLIENT_TYPE AS 'CLIENT TYPE',
		PROMPT_COUNT AS 'PROMPT COUNT',
		INDIRECT_OBJECT_IDENTIFIER AS 'INDIRECT OBJECT IDENTIFIER'
	FROM ACCESS

And the database format is:

CREATE TABLE access (    service        TEXT        NOT NULL,     client         TEXT        NOT NULL,     client_type    INTEGER     NOT NULL,     auth_value     INTEGER     NOT NULL,     auth_reason    INTEGER     NOT NULL,     auth_version   INTEGER     NOT NULL,     csreq          BLOB,     policy_id      INTEGER,     indirect_object_identifier_type    INTEGER,     indirect_object_identifier         TEXT NOT NULL DEFAULT 'UNUSED',     indirect_object_code_identity      BLOB,     flags          INTEGER,     last_modified  INTEGER     NOT NULL DEFAULT (CAST(strftime('%s','now') AS INTEGER)), pid INTEGER, pid_version INTEGER, boot_uuid TEXT NOT NULL DEFAULT 'UNUSED', last_reminded INTEGER NOT NULL DEFAULT 0,     PRIMARY KEY (service, client, client_type, indirect_object_identifier),    FOREIGN KEY (policy_id) REFERENCES policies(id) ON DELETE CASCADE ON UPDATE CASCADE);

So there is no PROMPT_COUNT and there is no ALLOWED too, thus if we can use like --os ios --version 18, then we can workon the apollo modules and adapt it, because otherwise it produce a lot of error on a lot of query.

[cvandeplas]

thank you @dummys for the idea. We do agree it would be better. Actually sysdiagnose analysis framework (SAF) already extracts iOS versions. However ... there are a few challenges:

1. Apollo was amazing work by @mac4n6 about 5 years ago and does not seem to be maintained anymore. As you can see in your example the query format of Apollo mentions with which iOS version it is compatible. There is some massive work needed to review all the queries and add all newer iOS versions with which they are compatible. Also to add more queries. Filtering now on the iOS version is therefore not a good idea as almost no queries will match. The easy route I chose was to just do the "I'm feeling lucky" approach and discard failures by catching the exception. This is clearly the biggest challenge.
2. (to be verified) We may be processing leftovers of some older files, which are in a structure that is older than the current iOS version. So our solution should also make sure to not discard these files. Ideally without user-intervention...