±Forensic Focus Partners

±Your Account


Nickname
Password


Forgotten password/username?


Membership:
New Today: 0
New Yesterday: 4
Overall: 27389
Visitors: 45

±Follow Forensic Focus

Join our LinkedIn group

Subscribe to news

Subscribe to forums

Subscribe to blog

Subscribe to tweets

Unexpected SQLite field data in WhatsApp databases

Computer forensics discussion. Please ensure that your post is not better suited to one of the forums below (if it is, please post it there instead!)
Reply to topicReply to topic Printer Friendly Page
Forum FAQSearchView unanswered posts
Go to page 1, 2  Next 
  

Unexpected SQLite field data in WhatsApp databases

Post Posted: Mon Jun 30, 2014 6:21 pm

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?  

francesco
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Tue Jul 01, 2014 6:08 am

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  

minime2k9
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Tue Jul 01, 2014 12:30 pm

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.  

francesco
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Wed Jul 02, 2014 12:47 am

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?  

minime2k9
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Wed Jul 02, 2014 2:48 am

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.

sandersonforensics.com...e-Recovery
_________________
Paul Sanderson
Forensic Toolkit for SQLite
sandersonforensics.com...ic-Toolkit
www.twitter.com/sandersonforens
www.facebook.com/recon...resoftware 


Last edited by PaulSanderson on Wed Jul 02, 2014 10:13 am; edited 1 time in total

PaulSanderson
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Wed Jul 02, 2014 4:56 am

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:

Code:
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:

Code:
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:

Code:
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:

Code:
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:

Code:
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:
Code:
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.  

AlexC
Senior Member
 
 
  

Re: Unexpected SQLite field data in WhatsApp databases

Post Posted: Fri Jul 04, 2014 5:54 pm

- minime2k9
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.
- AlexC
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.  

francesco
Senior Member
 
 
Reply to topicReply to topic

Share this forum topic to encourage more replies



Page 1 of 2
Go to page 1, 2  Next