Negative value in SQLite3 for TIMESTAMP field overflows #948

[OlafHaalstra]

Whilst working with iTunes backups I noted that a timestamp of: -63114076800, results in the value 281411862633856 which is exactly 2^48 - 63114076800

As far as I know has to do with the Cocoa Core Data epoch (for which 0 = 01-01-2001, so -63114076800 = 01-01-0001)

Not sure if this lies within the responsibility of the dissect framework, however it does cause issues when parsing these entries. Is there any way to circumvent this?

Values that show the problem:

• SQLite schema:

  sqlite> .schema ZPROCESS
  CREATE TABLE ZPROCESS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZFIRSTTIMESTAMP TIMESTAMP, ZTIMESTAMP TIMESTAMP, ZBUNDLENAME VARCHAR, ZEXTENSIONNAME VARCHAR, ZPROCNAME VARCHAR );

• SQLite database entry:

  sqlite> SELECT \* FROM ZPROCESS WHERE ZPROCNAME = "budd/com.apple.datausage.general";
  1304|7|1|751544175.039578|-63114076800|com.apple.datausage.general||budd/com.apple.datausage.general

• Parsed row:

  <Row table=ZPROCESS Z_PK=1304 Z_ENT=7 Z_OPT=1 ZFIRSTTIMESTAMP=751544175.039578 ZTIMESTAMP=281411862633856 ZBUNDLENAME='com.apple.datausage.general' ZEXTENSIONNAME=None ZPROCNAME='budd/com.apple.datausage.general'>

• Raw data:

  b'\t\x00\x01\t\x07\x05C\x00M\x07A\xc6e\xd3\xb7\x85\x10\xe4\xff\xf1N\x1b\x9d\x80com.apple.datausage.generalbudd/com.apple.datausage.general'

[Poeloe]

Hi Olaf, thank you for noticing and submitting this issue.

I briefly looked into the issue and it looks like that in this case key 5 for SERIAL_TYPES in c_sqlite3.py should be an int48 instead of uint48. Looking at sqlite file format in 2.1, this also explains it should be an int48, if I'm correct.

@Schamper since you've committed this code, do you maybe know how this SERIAL_TYPES mapping came to be?