SQLite
e' il database relazionale piu' installato nel mondo!
E' un ottimo software di pubblico dominio costituito da una libreria che puo' essere
semplicemente inclusa in un programma e per questo motivo migliaia di applicazioni lo
utilizzano.
Ma su questo ho gia' scritto in un altro documento su cui si
trova un'introduzione a SQLite.
Ora voglio presentare qualche semplice query che consente di
estrarre dati dai programmi piu' utilizzati.
Quali programmi?
Vedremo Firefox, Skype, Thunderbird, Chrome, iPhone, Android, ... ma prima
e' necessaria una breve introduzione:
continuate a leggere!
L'elenco delle query utilizzate nel seguito e' raccolto in
questo file.
Cosa c'e' sotto il programma? Un database SQLite!
Prima di vedere in dettaglio gli esempi sui database SQLite utilizzati dai programmi
dobbiamo capire come si fa a scoprirli ed ad interrogarli.
Come vedere i dati? E' facile!
La prima cosa da fare e' trovare il file che contiene il DB.
E' facile perche' i programmi scrivono sempre nello stesso posto
e tipicamente la documentazione li descrive.
Spesso i file di database hanno un suffisso ben riconoscibile come .db
o .sqlite.
Ma anche se non c'e' documentazione e' facile analizzare il contenuto di un file
e determinare se contiene un database SQLite.
Trovato il file di database basta utilizzare il programma sqlite3 ed
analizzarne il contenuto. Tipicamente i primi comandi sono .tables
(per scoprire l'elenco delle tabelle presenti) e .schema per
determinare le strutture dati. Raccolte queste informazioni di base,
nei casi piu' semplici, basta una SELECT * FROM tabella
per trovare tutto quello che vogliamo.
Ovviamente le eventuali condizioni vengono poste con la clausola WHERE
e l'ordinamento con la clausola ORDER BY.
Quando la tabella e' di dimensioni
elevate conviene utilizzare la clausola LIMIT per limitare il numero
di record restituiti e la clausola OFFSET per cambiare il punto di partenza:
.head on
SELECT type, name, sql
FROM sqlite_master
WHERE type in ('table','view')
ORDER BY name
LIMIT 100 OFFSET 0;
SQLite e' SQL standard ANSI 92, quindi il suo linguaggio di interrogazione e' molto completo. Una particolarita' e' l'assenza dei datatype per le date... ma negli esempi che seguono si vedra' come la dimensione del tempo e' trattata dai principali programmi che usano SQLite.
Oltre che da linea di comando vi sono molteplici programmi grafici per l'analisi dei dati di SQLite. Tra i piu' conosciuti: SQLite Database Browser ed il Plug-in Firefox SQLite Manager.
Ma ora vediamo esempi reali sui principali programmi ed ambienti che utilizzano SQLite: Firefox, Chrome, Safari, Thunderbird, Skype, iPhone, Android, MAC OS X, Solaris 10, ... varie ed eventuali.
Qundi non Sotto il programma... niente ma bensi' Sotto il programma... SQLite!
Firefox e' uno dei browser piu' diffusi e ricchi di funzionalita'.
I file di database di Firefox si trovano in directory differenti a seconda del sistema operativo.
Il piu' interessante e' il database places.sqlite che contiene una dozzina di tabelle tra cui il log delle URL visitate. Windows XP: C:\Documents and Settings\%user\Application Data\Mozilla\Firefox\Profiles\%profile.default\ Windows Vista: C:\Users\%user\AppData\Roaming\Mozilla\Firefox\Profiles\%profile.default\ GNU/Linux: /home/%user/.mozilla/firefox/%profile.default/ Mac OS X: /Users/%user/Library/Application Support/Firefox/Profiles/%profile.default/. Utili possono anche essere anche i file signons.sqlite e keys3.db con le password crittografate salvate dal browser (3.5>) e la chiave o il file formhistory.sqlite con le risposte alle form HTML.
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
In questo caso ho invertito l'ordinamento mostrando gli accessi piu' recenti e mostrandone solo
i primi 20: spesso l'elenco delle pagine visitate e' molto lungo!
Modificando l'offset e' possibile scorrere l'elenco... oppure si puo' impostare una condizione
sulla data.
SELECT moz_places.url, visit_count FROM moz_places ORDER BY visit_count desc LIMIT 20
Chrome e' il recente e veloce browser sviluppato da Google.
Chrome utilizza diversi database SQLite tra cui il piu' significativo
e' History che contiene gli accessi alle pagine web.
Le tabelle piu' interessanti sono ulrs, visits e downloads.
I file di database di Chrome si trovano in directory differenti a seconda del sistema operativo.
Windows XP:
C:\Documents and Settings\%user\Application Data\Google\Chrome\default\
Windows Vista:
C:\Users\%user\AppData\Local\Google\Chrome\default\
GNU/Linux:
/home/%user/.config/google-chrome/Default/
Mac OS X:
/Users/%user/Library/Application Support/Google/Chrome/Default/.
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 e' il diffuso browser sviluppato Apple. Il file di database e' Cache.db e contiene 5 tabelle.
Su Mac OS X:
/Users/%user/Library/Caches/com.apple.Safari.
SELECT cfurl_cache_response.time_stamp as data, cfurl_cache_response.request_key as url FROM cfurl_cache_response ORDER BY 1 desc LIMIT 20 OFFSET 0
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 e' uno dei client di posta piu' diffusi.
Skype e' il piu' diffuso programma VoIP. Skype e' ricco di funzionalita' tra cui
le video conferenze, un'ottima chat e molto importante, un protocollo di trasporto
in grado di adattarsi alle diverse reti e di superare i firewall sfruttando il trasporto HTTP.
Skype mantiene i propri dati sul database file main.db che contiene una decina di tabelle.
WhatsApp e' una diffusissima applicazione per lo scambio di messaggi.
I messaggi scambiati con WhatsApp sono simili agli SMS ma, utilizzando la connessione Internet,
non hanno un costo (naturalmente va pagato il canone di connessione, ma tipicamente si tratta
di tariffe flat).
E' disponibile solo su piattaforme Mobile (BlackBerry, iPhone, Android, ...) ed ha un costo
limitato: e' diffusissima.
Le configurazioni piu' semplici sono mantenute su file plist (eg. safari cookies, safari history, mail accounts, ...)
ma l'iPhone utilizza basi dati SQLite per gestire parecchie importanti informazioni.
Tra queste:
Il problema e' che per accedere a questi file deve essere ottenuta un'immagine non crittografata
dell'iPhone e per fare questo tipicamente deve essere "sprotetto" (eg. jail breaked)...
ma supponendo di avere a disposizione il file iPhone-3G-313-sms.db:
Ha fatto scalpore il caso sollevato sulla presunta raccolta di dati personali da parte di Apple
con gli iPhone 4.
La segnalazione riguardava il file di database consolidated che raccoglie dati georeferenziati
e consentiva di tracciare una storia di spostamenti. Le versioni sucessive dell'iOS hanno poi
provveduto a cancellare i dati mantenendo sul DB solo gli spostamenti piu' recenti.
Sugli iPhone < 4 l'informazione era gia' presente ma in formato diverso
e non accessibile da parte delle applicazioni: /root/Library/caches/locationd/h-cells.plist.
Il tempo? Anziche' Epoch l'iPhone usa il MAC Absolute time che parte dal 1/1/2001:
lo strano valore 978307200 corrisponde ai secondi di differenza tra le due date.
Anche il sistema operativo Android, utilizzato su decine di modelli di smartphone,
gestisce parecchie informazioni con basi dati SQLite.
Tra queste:
Le directory possono cambiare a seconda delle versioni di Android
o dalle personalizzazioni del fornitore (cfr. steve.deftlinux.net/download/android_forensic.pdf):
Se invece abbiamo a disposizione il file contacts.db questo contiene, oltre ai contatti veri e propri,
anche l'elenco delle chiamate. Per ottenere i venti numeri piu' chiamati con la durata totale e le
date della prima e dell'ultima chiamata:
Il sistema operativo MAC OS X utilizza in modo estensivo il database SQLite.
Anche il sistema operativo Solaris 10 utilizza i database SQLite.
In particolare il service management facility (SMF)
utilizza un repository SQLite mantenuto sul file /etc/svc/repository.db.
L'SMF e' un'evoluzione del classico inittab ed utilizza una versione vecchiotta
di SQLite (2.1).
Sono innumerevoli le applicazioni che utilizzano il database SQLite.
Nelle attivita' forensics spesso e' necessario analizzare database SQLite.
Anzi la crescente diffusione degli Smartphone
(tutti i dispositivi iPhone ed Android utilizzano DB SQLite)
rendera' questa attivita'
sempre piu' frequente.
La struttura dati di un database SQLite e' costituita da un solo file.
Il file ha un formato fisso ed e' identico
su tutte le piattaforme senza distinzioni: Big/Little Endian, 32/64 bit ed ovviamente Windows/Linux/Mac/Unix...
Il database puo' essere spostato tra sistemi diversi con
una semplice copia ed analizzato senza necessita' di conversione.
Riconoscere un file di database SQLite e' facile anche se viene cancellato.
Infatti ogni database contiene all'inizio una stringa fissa terminata
per un totale di 16 caratteri: "SQLite format3".
E' quindi banale configurare un programma di carving per cercare
un database SQLite. La configurazione su scalpel e' (l'ho fatta difficile mettendola in esedecimale):
Se non si dispone di un programma di carving un'alternativa per trovare i database
SQLite e':
Nella maggioranza dei casi e' sufficiente accedere in SQL ai dati per raccogliere
tutte le informazioni necessarie.
L'elenco delle query utilizzate in questa pagina e' raccolto in
questo file.
Si leggere anche i dati cancellati? In alcuni casi si!
Anche nel caso di cancellazione con DELETE, in qualche caso e' comunque possibile recuperare
qualcosa! Infatti SQLite non cancella fisicamente i dati ma cambia solo i puntatori.
I dati cancellati sono cosi' ancora presenti nel database anche se non accessibili da SQL.
Una modo per evidenziare i dati cancellati e' quello di effettuare una copia fisica del
DB, eseguire un vacuum e confrontare i due file alla ricerca delle differenze.
Trovate le differenze e' ora necessario analizzare i dati in binario...
la ricerca e' facilitata dal fatto che SQLite utilizza solo 5 diverse strutture di
memorizzazione, ma supera comunque gli obiettivi di questa semplice pagina.
I dettagli sul formato interno di una database SQLite
(pagine, freelist, rappresentazione datatype, ...) e come analizzarlo
si trovano nella documentazione ufficiale
oppure su questa paginetta in italiano.
Testo: Sotto il programma... SQLite!
E' importante sottolineare che i messaggi, gli elementi piu' importanti
per un programma di posta, sono memorizzati in formato mbox.
Thunderbird utilizza SQLite per altre funzioni specifiche, non per memorizzare i messaggi.
Molti dei file di database SQLite di Thunderbird sono analoghi a quelli di 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
Su MS Windows il database di Skype si trova
in C:\Documents and Settings\%profile\Application Data\Skype\%skype_user,
con l'eccezione di Vista and 2008 dove il file e' in
C:\Documents and Settings\%profile\AppData\Roaming\Skype\%skype_user.
Su Mac OS X:
/Users/%user/Library/Application Support/Skype/%skype_user.
Ovviamente quanto sopra vale per le versioni in inglese
(sono all'antica e di solito installo i programmi in lingua originale),
per le versioni in italiano ovviamente cambia solo qualche nome di directory...
Chiamate skype-to-phone (tutte) e skype-to-skype (solo 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
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
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
WhatsApp
La struttura dei database SQLite cambia a seconda delle versioni e dei dispositivi.
Le ultimi versioni utilizzano la crittografia dei file che non consente analisi... quindi ogni caso e' differente,
ma vediamo comunque un esempio (file ChatStorage.sqlite).
Chat WhatsApp
SELECT ZFROMJID, ZTEXT, ZMESSAGEDATE
FROM ZWAMESSAGE
iPhone
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
Curiosi?
Accediamo a RootDomain::Library/Caches/locationd/consolidated.db:
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
/data/data/com.google.android.providers.contacts/databases/contacts.db
/data/data/com.google.android.providers.googleapps/databases/accounts.db
/data/data/com.google.android.providers.im/databases/im.db
/data/data/com.google.android.providers.media/databases/media.db
/data/data/com.google.android.providers.telephony/databases/mms.db
/data/data/com.google.android.providers.telephony/databases/sms.db
/data/data/com.google.android.providers.telephony/databases/telephony.db
/data/data/com.google.android.providers.settings/databases/settings.db
/data/data/com.google.android.maps/databases/maps.db
...
Il problema e' che per accedere a questi file il cellulare deve essere "sprotetto" (jail breaked, rooted)...
ma supponendo di avere a disposizione il file 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
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
MAC OS X
/Applications/iPhoto.app/Contents/Resources/PointOfInterest.db
/Applications/iPhoto.app/Contents/Resources/VectorMaps.db
/Users/%user/.config/gcompris/gcompris_sqlite.db
/Users/%user/.dropbox/config.db
/Users/%user/.filezilla/queue.sqlite3
/Users/%user/.neubot/database.sqlite3
/Users/%user/.zenmap/zenmap.db
/Users/%user/Calibre Library/metadata.db
/Users/%user/Music/iTunes/iTunes Library Extras.itdb
/Users/%user/Pictures/iPhoto Library/face.db
/Users/%user/Pictures/iPhoto Library/iPhotoMain.db
/Users/%user/Library/Application Support/AddressBook/AddressBook-v22.abcddb
/Users/%user/Library/Application Support/Adobe/Acrobat/10.0/ReaderMessages
/Users/%user/Library/Application Support/SyncServices/Local/admin.syncdb
/Users/%user/Library/Caches/com.adobe.ARM/Cache.db
/Users/%user/Library/Preferences/com.apple.dock.db
...
Solaris 10
Varie ed eventuali
WhatsApp e' una diffusa
applicazione su iPhone ed Android che consente di scambiare messaggi gratuiti.
I database msgstore.db e wa.db
contengono in chiaro tutti i messaggi scambiati (anche quelli cancellati che
non sono visibili dall'applicazione ma vengono cancellati solo logicamente nel DB) e,
se il GPS era abilitato, la posizione
geografica (latitudine e longitudine) del cellulare.
(NdE Le ultime versioni di WhatsApp usano un DB crittografato)
Forensics
C'e' qualche piccola eccezione... SQLite 3.7 ha introdotto un nuovo formato
che non puo' essere analizzato dalle versioni precedenti. Firefox dalla versione 11 utilizza
tale formato: quindi se volete analizzare un DB di Firefox 11 utilizzate
un client 3.7! Ovviamente il client 3.7 legge tranquillamente tutti i precedenti formati.
sqlitedb y 20000000000 \x53\x51\x4C\x69\x74\x65\x20\x66\x6F\x72\x6D\x61\x74\x20\x33
find /Users -exec grep -q "SQLite format 3" {} \; -print 2>/dev/null
Ovviamente la ricerca con una grep e' terribilmente meno efficiente, opera solo su sistemi Unix like,
genera falsi positivi perche' cerca la stringa in tutto il file (e non sono nell'header),
non e' in grado di trovare file cancellati, ... insomma utilizzate foremost o scalpel!
Molte applicazioni non cancellano i dati ma semplicemente li nascondono.
Si tratta della cancellazione logica: anziche' cancellare un record si effettua
un'update impostando un flag. In questo caso l'applicazione non presentera' piu' i dati
all'utente ma, analizzandoli con l'SQL sara' possibile leggerli facilmente (eg. iPhone SMS).
Data: 1 Gennaio 2012
Versione: 1.0.4 - 1 Giugno 2012
Autore: mail@meo.bogliolo.name