Unexpected SQLite f...
 
Notifications
Clear all

Unexpected SQLite field data in WhatsApp databases

8 Posts
4 Users
0 Likes
590 Views
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

In WhatsApp databases I've found fields where the field type doesn't seem to match neither the type declared in the schema nor the possible affinities. E.g. the thumb_image field (TEXT) and the raw_data field (BLOB) can have Constant0(integer) values.

The SQLite documentation says that TEXT affinity can be "NULL, TEXT OR BLOB" (none of those are integers like Constant0) and that BLOB doesn't have any affinity (so it couldn't be an integer like Constant0). I also can't seem to reproduce those kind of records with SQLite Database Browser, a BLOB with value '\0' never seems to be saved as Constant0.

Any idea about what the reason could be?

 
Posted : 01/07/2014 5:21 am
minime2k9
(@minime2k9)
Posts: 481
Honorable Member
 

Later versions of SQLite introduced a constant '1' and '0' field.
In the header I believe these have the value 8 and 9 respectively.
Have a look at the documentation on the sqlite3 website (http//www.sqlite.org/fileformat.html)
Below is the table of data types.

Serial Type Content Size Meaning
0 0 NULL
1 1 8-bit twos-complement integer
2 2 Big-endian 16-bit twos-complement integer
3 3 Big-endian 24-bit twos-complement integer
4 4 Big-endian 32-bit twos-complement integer
5 6 Big-endian 48-bit twos-complement integer
6 8 Big-endian 64-bit twos-complement integer
7 8 Big-endian IEEE 754-2008 64-bit floating point number
8 0 Integer constant 0. Only available for schema format 4 and higher.
9 0 Integer constant 1. Only available for schema format 4 and higher.
10,11 Not used. Reserved for expansion.
N≥12 and even (N-12)/2 A BLOB that is (N-12)/2 bytes in length
N≥13 and odd (N-13)/2 A string in the database encoding and (N-13)/2 bytes in length. The nul terminator is omitted.

Hope this helps

 
Posted : 01/07/2014 5:08 pm
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

I was already familiar with that field type (Constant 0) however from how I interpreted the specifications a BLOB (or a TEXT) field cannot have that value because neither have affinity with INTEGER/NUMERIC. I created a test database with SQLite Browser and wrote an empty BLOB field and a BLOB field with a '\0' value. The first BLOB was serialized with a '12' serial type (0 bytes-long BLOB) while the second BLOB was serialized with a '14' serial type (1 byte-long BLOB) along with the associated 1-byte data containing the '\0' character so what WhatsApp does doesn't seem like a standard/documented behavior.

 
Posted : 01/07/2014 11:30 pm
minime2k9
(@minime2k9)
Posts: 481
Honorable Member
 

Have you looked at the record header? If so does that field relate to a blob/text field or does it show a constant value field?

Just thinking, is it an optional field and if so is it possible it was just omitted rather than NULL value?

 
Posted : 02/07/2014 11:47 am
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
 

If I understand correctly this is reasonably common and I have seen a number of databases where (for instance) a text field has been written to an integer column - mac mail seems to do this a lot.

My program SQLite Recovery can carve from unalloctaed (multiple databases and tables at one time) and can deal with recovered data where the field type does not match the defined affinity.

http//sandersonforensics.com/forum/content.php?190-SQLite-Recovery

 
Posted : 02/07/2014 1:48 pm
(@alexc)
Posts: 301
Reputable Member
 

Remember that SQLite declared types are more of a suggestion than a rule, if the underlying code is forecably putting an "int" into a "blob" field, there's nothing in SQLite to stop that from happening. Also it might depend on where in the API (and which layer on top) you're coming from, bare in mind that many (most?) API's won't just be hard coding statements, there'll be paramatised queries to think about so there'll be some mapping of that language's types into SQLite types. I have python to hand so let's try


import sqlite3

conn = sqlite3.connect("test.sqlite")
cur = conn.cursor()
cur.execute("CREATE TABLE t1 (c1 TEXT, c2, INTEGER, c3 BLOB, c4 FLOAT);")
cur.execute("INSERT INTO t1 VALUES(?, ?, ?, ?, ?)", (1, 2, 3, 4, 5)) # all "ints" in Python
conn.commit()
conn.close()

This code creates a record where all of the columns save for the first contain an actual integer type; the first contains a a string.
The record looks like this

0B 01 06 0F 01 01 01 01 31 02 03 04 05
No errors or warnings from doing this. The text column has coerced the integer to a string (as it should) with serial type 15 (1 byte string), but the blob column is quite happy to receive and store an integer actually as an integer.

Let's try putting Python's "bytes" objects in


import sqlite3

conn = sqlite3.connect("test.sqlite")
cur = conn.cursor()
cur.execute("CREATE TABLE t1 (c1 TEXT, c2, INTEGER, c3 BLOB, c4 FLOAT);")
cur.execute("INSERT INTO t1 VALUES(?, ?, ?, ?, ?)", (b"\x00", b"\x00", b"\x00", b"\x00", b"\x00")) # bytes objects
conn.commit()
conn.close()

I'd expect SQLite to store blob fields here, and looking at the record

0B 01 06 0E 0E 0E 0E 0E 00 00 00 00 00
All five columns have the serial type 14 (1 byte blob) - again, despite any declared types.

Let's try strings


import sqlite3

conn = sqlite3.connect("test.sqlite")
cur = conn.cursor()
cur.execute("CREATE TABLE t1 (c1 TEXT, c2, INTEGER, c3 BLOB, c4 FLOAT);")
cur.execute("INSERT INTO t1 VALUES(?, ?, ?, ?, ?)", ("1", "2", "3", "4", "5")) # strings
conn.commit()
conn.close()

The record looks like this
0B 01 06 0F 0F 0F 0F 01 31 32 33 34 05
Which is weird, because only the FLOAT declared column actually coerced the data, and even then it was coerced to an integer (that's normal behavior for SQLite though, it'll chose a more compact numeric encoding if it can)

I'm not sure if this helps, but I think it's interesting at least, and I suspect that if you came in from a different level, or with a different API you might not find that everything still goes this way.

 
Posted : 02/07/2014 3:56 pm
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

Have you looked at the record header? If so does that field relate to a blob/text field or does it show a constant value field?

Just thinking, is it an optional field and if so is it possible it was just omitted rather than NULL value?

Whoops I didn't notice my first post could be interpreted as a high-level behavior. I was referring to how the data was saved in the file. The field type in the header for those fields is 08 (constant value 0). Those fields were likely omitted but usually omitted fields are null, not stored with a different type which has no affinity to the declared one.

I'm not sure if this helps, but I think it's interesting at least, and I suspect that if you came in from a different level, or with a different API you might not find that everything still goes this way.

Actually it helps a lot knowing that affinities mean nothing. Much less informations to keep in mind. Thanks for the tests, they really helped.

 
Posted : 05/07/2014 4:54 am
(@alexc)
Posts: 301
Reputable Member
 

No worries, glad to help. Writing Epilog means I've learnt more about the internals of the format (and the weirdness that various situations cause) than I ever wanted to!

It was nice to run those tests again to see if anything has changed/become less weird - it hasn't!

 
Posted : 07/07/2014 2:35 pm
Share: