[Tool] The Automati...
 
Notifications
Clear all

[Tool] The Automatic SQLite Carver v0.0.0.3

6 Posts
3 Users
0 Likes
1,604 Views
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

Description

A simple tool to recover lost entries or deleted tables from SQLite databases or anything that may contain accessible SQLite record data in it. It takes a SQLite CREATE TABLE schema and a SQLite file, or a SQLite journal file or even an uncompressed disk image file (slow on those for now!) and identifies and carves every matching record it can find. It also recovers data from the overflow pages (only on a database file, not on disk images).

As a forensic tool it can help recovering Skype messages, browser data (history, favorites, etc.), phone data (SMS/Calls/etc.) or pretty much anything stored on SQLite databases as long as you put the right schemas.

IMPORTANT If you need to recover deleted records (not just lost records or tables), e.g. deleted Skype messages or browser history or have tables which do not respect column affinity (e.g. WhatsApp) I have another test version with the option available that I still haven't publicly released yet. PM me if you want to give it a try.

System requirements

Windows XP and above with Framework 4.0 (or 4.5 or 4.5.1)

How to use

Simply put a basic CREATE statement in the appropriate box. Only field names and their type no CONSTRAINTS, REFERENCES or other clauses are supported for now (those aren't necessary anyway). Then select which file to analyze and it should detect and extract any matching record it sees.

You can copy&paste the cells or export the data to a CSV file. You can also use multiple CREATE statements at once e.g. for different Skype versions (each one on a separated line).

DISCLAIMER

The software is provided "AS IS" without any warranty, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The author will not be liable for any special, incidental, consequential or indirect damages due to loss of data or any other reason.

Download

Automatic SQLite Carver v0.0.0.3

Changelog

Automatic SQLite Carver v0.0.0.3
- Improved column affinity for WhatsApp databases.
- Added an option to disable type checks (for tables with strange column affinities).
- You can now drag&drop or copy&paste files in the input/output file boxes.
- You can now search multiple, different create statements at once.
- You can now output the extracted data to a CSV file.
- You can now specify a primary integer key in the statement, the tool will automatically fill the empty values with the record key/number.
- Added a file offset column to know where in the file the record has been found.
Automatic SQLite Carver v0.0.0.2
- Made the CREATE STATEMENT parsing error message easier to understand.
- Now you can copy the data from the grid (CTRL+C) and paste it on Excel or other software
- Changed the Framework target to 4.0 (now it can be installed and used on XP)
Automatic SQLite Carver v0.0.0.1
- Initial release

 
Posted : 30/11/2013 5:07 am
(@zul22)
Posts: 53
Trusted Member
 

Contratulations for your software, Francesco, and thank you for sharing it.

It works but I encountered the following problems

1) The data type REAL is not recognized.
(Trick Replacing this type with INTEGER can help to open a database,
although it's not the same data type, of course.)

2) The copy/paste does of records does not work if there are many records.
In my case, for a database with about 1000 records, only the first 59 were copied
to Excel or Notepad using the Ctrl+A, Ctrl+C, Ctrl+V shortcuts.

3) Having the first column of type "_id INTEGER PRIMARY KEY AUTOINCREMENT"
the rows were not numbered.
For large databases, this makes difficult to export records by small groups by copy/paste,
as you can hardly know which was the last record you copied. (also see bug from point 2)

4) Tolerance to corrupted databases could be improved.
Currently, the software can crash if the database is corrupted.

Regards.

 
Posted : 18/06/2014 10:21 pm
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

Contratulations for your software, Francesco, and thank you for sharing it.

It works but I encountered the following problems

1) The data type REAL is not recognized.
(Trick Replacing this type with INTEGER can help to open a database,
although it's not the same data type, of course.)

2) The copy/paste does of records does not work if there are many records.
In my case, for a database with about 1000 records, only the first 59 were copied
to Excel or Notepad using the Ctrl+A, Ctrl+C, Ctrl+V shortcuts.

3) Having the first column of type "_id INTEGER PRIMARY KEY AUTOINCREMENT"
the rows were not numbered.
For large databases, this makes difficult to export records by small groups by copy/paste,
as you can hardly know which was the last record you copied. (also see bug from point 2)

4) Tolerance to corrupted databases could be improved.
Currently, the software can crash if the database is corrupted.

Regards.

Hello

1) Does the program not recognize float values even if you use "FLOAT" as type instead of "REAL"? I remember fixing a similar bug but maybe I never published an updated version. It could also be a worse issue, the software that used the database could have had the SQLite library save different data than the kind declared in the schema (SQLite would then convert the data on the fly when reading it). A string could be an integer, a float value could be an integer, etc. The question is whether the carver should support multiple field types and show them converted (thus adding a lot more garbage records) or let the user figure out the true data types the program was passing the SQLite library. The carver currently supports looking for multiple record types at once but when there are records with several fields where the values could be different than declared (WhatsApp?) it starts becoming an huge problem.

2) This was more a sample WPF project than a carving software (I generally use WinForms + ObjectListView but WPF let me make a standalone executables) indeed the whole UI class is just 190 lines of code. Big mistake, it really needs a WinForms rewrite. The carving library is pretty stable and I used it on many different SQLite records and terabytes of data without issues but the UI is really something slapped together in a few minutes and would really improve drastically with a few hours of work. But if the application had to be done properly I'd have to solve the different field types problem I wrote about in point (1).

3) The tool only reads the column name and the type (only "_id" and "INTEGER"). The record structure is only a bunch of values stacked together preceded by an header declaring the type of each field and by the header, record size and the record number. Nothing more than that. The primary key aspects are handled by the SQLite library and are not stored in the record data and the tool ignores them.

4) The tool doesn't care about the database informations therefore it shouldn't be influenced if it's corrupted, it checks any file byte after byte to see if the combination of bytes can possibly make a valid matching record and then extract it. What statement did you use to cause the crash? And what was the error message?

 
Posted : 19/06/2014 4:56 am
(@zul22)
Posts: 53
Trusted Member
 

Hello,

Thanks.

1) If I use FLOAT instead of REAL, the program displays the "Done! Have a nice day." message, as well as the column names, but no record is displayed.
Only using INTEGER instead of REAL allowed to display the messages.
Yes, this is for a WhatsApp database and fields using REAL are "Latitude" and "Longitude".

2) As the GUI does not allow proper copy/paste, would it be possible to call the parser library as a DLL? Currently the software is a monolithic ".exe", but it could be a ".exe" calling a ".dll".

Alternatively, would it be possible to call the .exe from a shell, passing to it argc,argv arguments and pipe the output somewhere, as a CSV file or SQL queries including the deleted records?

3) Sure that AUTOINCREMENT values are not stored in the database.
But why not detect the statement "AUTOINCREMENT" in the query, use a counter in the loop that your carver does, generate the values "1, 2, 3, …" and maybe give them a different color so that we know they were not stored in the database?

4) I cannot currently remember which database made the program crash.
I'll write it down next time.
There was no error message, but the software was not responding.

 
Posted : 19/06/2014 3:44 pm
(@francesco)
Posts: 79
Trusted Member
Topic starter
 

Hello,

Thanks.

1) If I use FLOAT instead of REAL, the program displays the "Done! Have a nice day." message, as well as the column names, but no record is displayed.
Only using INTEGER instead of REAL allowed to display the messages.
Yes, this is for a WhatsApp database and fields using REAL are "Latitude" and "Longitude".

If it's for a WhatsApp database it's going to be quite problematic because if I remember well WhatsApp has a very annoying behavior of saving empty values with integers (e.g. 0) rather than with NULL (and very likely other bad surprises in the record data). That would mean you'd have to try a lot of different schema combinations to possibly obtain all the data. Maybe it would be better to write a little script that looks for phone numbers in the database file and outputs the record data if it figures out that it belongs to a message in order to avoid losing important data, that would also help with recovering single deleted records whose header has been partially overwritten therefore being currently undetectable by my software.

2) As the GUI does not allow proper copy/paste, would it be possible to call the parser library as a DLL? Currently the software is a monolithic ".exe", but it could be a ".exe" calling a ".dll".

Alternatively, would it be possible to call the .exe from a shell, passing to it argc,argv arguments and pipe the output somewhere, as a CSV file or SQL queries including the deleted records?

I can have the tool output the scanned data to a text file if that may help.

3) Sure that AUTOINCREMENT values are not stored in the database.
But why not detect the statement "AUTOINCREMENT" in the query, use a counter in the loop that your carver does, generate the values "1, 2, 3, …" and maybe give them a different color so that we know they were not stored in the database?

The key is not generated/made up by the SQLite library while reading, it's usually borrowed from the real record number that every SQLite record has and that is present on every record included those without keys. When the record has a primary key integer that value is stored as NULL to save space. I can have the record number column printed in every case if that can help.

4) I cannot currently remember which database made the program crash.
I'll write it down next time.
There was no error message, but the software was not responding.

Could be some issue with the UI, I don't think there's anything in the carving library that could make it hang while reading. Let me know if you run into the same issue again.

 
Posted : 19/06/2014 9:36 pm
(@loomingx)
Posts: 5
Active Member
 

Any chance you could provide a Calendar.db and richnote-journal (memo - not sure if it's app specific) SQLite CREATE TABLE schema?

 
Posted : 13/02/2016 6:39 am
Share: