A little while back I took part in a discussion on here re Browser history timestamps and the thread drifted a bit into some of the data held in the SQLite tables for Google Chrome. Some interesting SQL was developed to create custom reports on the history beyond what you would see in any generic browser/internet histroy tool. That thread is here.
http//www.forensicfocus.com/Forums/viewtopic/t=12232/
This got me thinking though and it lead me to look at some of the newer SQLite fucntionality in a newer light. I was delighted to find that SQL has a lot more to offer than just returning a subset of rows of data from one or more tables but can do some quite complex stuff.
In the example at the article below I show how you can use something called a Recursive Common Table Expression to recurse through Chrome history starting with the ID of a page in that history and create an ordered list of all of the pages that where clicked on/referred the user to the page of interest.
The SQL developed should be usable in any recent SQLite browser and the techniques could of course have use for many other databases.
http//
Paul
Paul,
Thanks so much for sharing, this is an area of SQL that I've barely touched in the past; I'd usually switch to using Python to do the recursive stuff but it's great to see an actual example of how you can do this in pure SQL. Really useful post.
Thanks Alex
Exactly my approach in the past too.
On a side, but possibly obvious, note. Of course the two queries in the two above threads can be combined to produce a report showing the chain of web pages along with the CoreTransition and the qualifier.
Cheers
Paul
Excellent stuff again, Paul.
I would totally buy a "SQLite for Forensics Cookbook". JUST SAYING, GUYS.
)
I have already started some notes and a little structure along those lines. Need to make sure there is enough in it to justify it from a readers pov.