## Most intresting SQLite forensics queries
## Raccolta di query per accedere ai dati dei piu' diffusi programmi che utilizzano SQLite
##
## v. 1.0.1 - April 1st 2012
## 2012 (C) Copyright meo bogliolo 
## meo [at] xenialab.it

# *
# *
# Table list
# Elenco delle tabelle e viste contenute nel DB SQLite
.head on
SELECT type, name, sql 
FROM   sqlite_master 
WHERE  type in ('table','view') 
ORDER BY name

# Firefox
# places.sqlite
# Last visited sites
# Ultimi siti visitati con Firefox
SELECT datetime(moz_historyvisits.visit_date/1000000,'unixepoch') as data, moz_places.url
FROM   moz_places, moz_historyvisits 
WHERE  moz_places.id = moz_historyvisits.place_id
ORDER BY 1 desc
LIMIT 20 OFFSET 0

# Firefox
# places.sqlite
# Most visited sites
# Siti piu' visitati con Firefox
SELECT moz_places.url, visit_count
FROM   moz_places
ORDER BY visit_count desc
LIMIT 20

# Chrome
# History
# Last visited sites
# Siti visitati con Chrome
SELECT datetime((visit_time-11644473600000000)/1000000,'unixepoch', 'localtime') as data,
       urls.url, urls.title as titolo 
FROM  urls, visits 
WHERE urls.id = visits.url
ORDER BY 1 desc
LIMIT 20 OFFSET 0

# Safari
# Cache.db
# Last visited pages
# Pagine richieste con Safari
SELECT  cfurl_cache_response.time_stamp as data, cfurl_cache_response.request_key as url,
        cfurl_cache_blob_data.receiver_data as contenuto     
FROM    cfurl_cache_blob_data, cfurl_cache_response
WHERE   cfurl_cache_blob_data.entry_ID=cfurl_cache_response.entry_ID
ORDER BY 1 desc
LIMIT 20 OFFSET 0

# Thunderbird
# signons.sqlite
# User list
# Utenze Thunderbird
SELECT id,hostname,httpRealm,usernameField,passwordField,
       encryptedUsername,encryptedPassword,
       datetime(timeCreated/1000,'unixepoch'),
       datetime(timeLastUsed/1000,'unixepoch'),
       datetime(timePasswordChanged/1000,'unixepoch')
FROM   moz_logins

# Skype
# main.db
# skype-to-phone and skype-to-skype calls
# Chiamate skype-to-phone (tutte) e skype-to-skype (se terminate regolarmente)
SELECT identity as chiamante, guid, call_duration/60 as durata_minuti,
       strftime('%Y-%m-%d %H:%M:%S', start_timestamp,'unixepoch','localtime') as inizio_chiamata
FROM   CallMembers
ORDER BY id

# Skype
# main.db
# skype-to-skype calls
# Chiamate skype-to-skype (tutte)
SELECT host_identity as chiamante, current_video_audience as destinazione, duration/60 as durata_minuti,
       strftime('%Y-%m-%d %H:%M:%S', begin_timestamp,'unixepoch','localtime') as inizio_chiamata 
FROM   Calls 
ORDER BY id

# Skype
# main.db
# Skype chats
# Chat Skype
SELECT author as chiamante, chatname, body_xml as messaggio,
       strftime('%Y-%m-%d %H:%M:%S', timestamp,'unixepoch','localtime') as inizio_chiamata
FROM   messages
ORDER BY timestamp

# iPhone
# sms.db
# SMS list
# Elenco messaggi iPhone (sms.db)
SELECT ROWID, case flags when 2 then 'Ricevuto' when 3 then 'Inviato' when 33 then 'Fail' 
              when 129 then '*Del' else 'Unkn' end as tipo,
       address as numero_tel, datetime(date,'unixepoch','localtime') as data, text as messaggio
FROM   message

# iPhone
# consolidated.db
# Localization
# Rilevamenti GPS e WiFi su iPhone
SELECT datetime(Timestamp+978307200,'unixepoch','localtime') as Time,
       Latitude, Longitude, 'WiFi' as Source
FROM   WifiLocation
UNION
SELECT datetime(Timestamp+978307200,'unixepoch','localtime') as Time,
       Latitude, Longitude, 'Cell' as Source
FROM   CellLocation
ORDER BY 1;

# Android
# mmssms.db
# SMS list
# Elenco SMS Android (mmssms.db)
SELECT datetime(date/1000,'unixepoch','localtime') as data, address as indirizzo, subject as soggetto, body as testo
FROM   sms
ORDER BY date desc

# Android
# contacts.db
# Call statistics
# Statistica chiamate Android
SELECT number as numero, number_key as chiave, count(*) as numero, sum(duration) as durata,
       min(datetime(date/1000,'unixepoch','localtime')) as prima_chiamata, 
       max(datetime(date/1000,'unixepoch','localtime')) as ultima_chiamata
FROM   calls
GROUP BY number, number_key
ORDER BY 3 DESC
LIMIT 20