±Forensic Focus Partners

Become an advertising partner

±Your Account


Username
Password

Forgotten password/username?

Site Members:

New Today: 0 Overall: 35667
New Yesterday: 6 Visitors: 127

±Follow Forensic Focus

Forensic Focus Facebook PageForensic Focus on TwitterForensic Focus LinkedIn GroupForensic Focus YouTube Channel

RSS feeds: News Forums Articles

±Latest Articles

±Latest Webinars

SQL Recursive Common Table Expressions and Google Chrome

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
 
  

PaulSanderson
Senior Member
 

SQL Recursive Common Table Expressions and Google Chrome

Post Posted: Dec 03, 14 15:02

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.

www.forensicfocus.com/...c/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.

sandersonforensics.com...7-articles

Paul
_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 
  

AlexC
Senior Member
 

Re: SQL Recursive Common Table Expressions and Google Chrome

Post Posted: Dec 08, 14 16:34

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.  
 
  

PaulSanderson
Senior Member
 

Re: SQL Recursive Common Table Expressions and Google Chrome

Post Posted: Dec 08, 14 21:34

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



_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 
  

Chris_Ed
Senior Member
 

Re: SQL Recursive Common Table Expressions and Google Chrome

Post Posted: Dec 09, 14 14:16

Excellent stuff again, Paul.

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

PaulSanderson
Senior Member
 

Re: SQL Recursive Common Table Expressions and Google Chrome

Post Posted: Dec 09, 14 14:54

Smile

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.
_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 

Page 1 of 1