Have you heard about binary JSON in SQLite? I hadn't. Today I was made aware of it by digital forensics examiner and software developer extraordinaire Alex Caithness.
The latest SQLite version (Version 3.45.0) has the ability to encode and decode JSON data from plain text to binary format and back. Details of this functionality can be found here: https://sqlite.org/draft/jsonb.html
Why would this data need to be in binary format? Per the jsonb specification there will be a reduction in data size as well as faster processing speed.
After downloading SQLite 3.45 on a Windows VM, I generated some synthetic plain text JSON data.
After importing the data I encoded the blob by using the following query:
UPDATE data set jsonblobdata = jsonb(jsonblobdata);
After the UPDATE query I ran a SELECT query to see how the data would look.
 |
| JSON binary blob |
Here is the blob field view with hex.
 |
| JSON binary blob with hex |
One of the issues with binary data structures is that text searching an extraction will be less and less productive. SQLite binary JSON does not seem to compress data that much but I can foresee a future where it will just like LevelDB or other formats. Being aware of compressed binary data, and the need to access it in clear text format, will be a key function for digital examiners today and into the future.
In order to present the blob data in clear text I used the following query:
SELECT keyf, json(jsonblobdata) from data;
The json() function does all the work for you.
 |
| Clear-text JSON |
After that one can deal with the JSON data as one usually does.
For questions or comments find me on all social media here: https://linqapp.com/abrignoni
