SQL Recursive Commo...
 
Notifications
Clear all

SQL Recursive Common Table Expressions and Google Chrome

5 Posts
3 Users
0 Likes
500 Views
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
Topic starter
 

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//sandersonforensics.com/forum/content.php?137-articles

Paul

 
Posted : 03/12/2014 2:02 pm
(@alexc)
Posts: 301
Reputable Member
 

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.

 
Posted : 08/12/2014 3:34 pm
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
Topic starter
 

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

 
Posted : 08/12/2014 8:34 pm
Chris_Ed
(@chris_ed)
Posts: 314
Reputable Member
 

Excellent stuff again, Paul.

I would totally buy a "SQLite for Forensics Cookbook". JUST SAYING, GUYS.

 
Posted : 09/12/2014 1:16 pm
PaulSanderson
(@paulsanderson)
Posts: 651
Honorable Member
Topic starter
 

)

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.

 
Posted : 09/12/2014 1:54 pm
Share: