giovedì 12 dicembre 2024

Published dicembre 12, 2024 by Django Faiola with 0 comment

iOS Foursquare Swarm - Check-in App

Indice dei contenuti

Introduzione

Foursquare Swarm è un’applicazione mobile gratuita sviluppata da Foursquare Labs, Inc. nata nel 2014 dalla scissione di Foursquare in due app: Foursquare destinata a raccogliere le recensioni dei luoghi e i voti degli utenti; Swarm dedicata ai check-in e alla parte social.

Swarm è un social heat map che mostra i luoghi più caldi del momento, ovvero i più frequentati da utenti e amici. Effettuato il check-in è possibile condividere la propria posizione con gli amici e la persona che ha effettuato più volte il check-in del luogo, viene insignita del badge di mayor (sindaco). E' possibile creare piani, organizzare incontri e tante altre attività.

Per maggiori dettagli sulle funzionalità dell'app consulta https://www.swarmapp.com.

App Store: https://apps.apple.com/us/app/foursquare-swarm-check-in-app/id870161082

Per lo studio di questa app sono state utilizzate le immagine pubbliche del CTF 2023 - Abe's iPhone e CTF 2024 - Otto's iPhone della Cellebrite con le versioni di Swarm 6.12.20 e 6.12.32.

Percorsi

Di seguito lo stralcio di Swarm del poster della SANS “iOS Third-Party Apps Forensics Reference Guide Poster” con le informazioni più rilevanti per l’analisi dell’app.

Account

Per la ricostruzione dell'account, come indicato nel poster, è sufficiente il database SQLite "/Library/Caches/foursquare.sqlite e nello specifico la tabella ZFSUSER. Questa tabella contiene sia l'utente come "account" che gli utenti come "contatti". Per l'account occorre solo filtrare il campo ZRELATIONSHIP che rappresenta il tipo di relazione con il valore self.

SELECT
    U.Z_PK AS "U_PK",
    FU.Z_PK AS "FU_PK",
    datetime(U.ZSWARMCREATEDAT + 978307200, 'unixepoch') AS "created",
    datetime(U.ZJOINEDAT + 978307200, 'unixepoch') AS "joined_at",
    U.ZFIRSTNAME,
    U.ZLASTNAME,
    U.ZGENDER,
    date(U.ZBIRTHDAY + 978307200, 'unixepoch') AS "birthday",
    U.ZHOMECITY,
    U.ZPHONE,
    U.ZEMAIL,
    FU.ZMONGOID AS "facebook",
    U.ZTWITTER,
    U.ZCANONICALURL,
    IIF(U.ZPHOTOPREFIX NOT NULL AND U.ZPHOTOSUFFIX NOT NULL, U.ZPHOTOPREFIX || 'original' || U.ZPHOTOSUFFIX, '') AS "original_photo",
    U.ZCHECKINPINGS AS "neighborhood_sharing_state",
    U.ZCHECKINSCOUNT AS "total_checkins",
    U.ZMONGOID AS "uid"
FROM ZFSUSER AS "U"
LEFT JOIN ZFSFACEBOOKUSER AS "FU" ON (U.ZFACEBOOKUSER = FU.Z_PK)
WHERE U.ZRELATIONSHIP = 'self' 

La tabella ZFSUSER:

  • Z_PK: 1 (chiave primaria);
  • ZSWARMCREATEDAT: 2022-04-24 20:10:20 (data di creazione nel formato MAC Absolute Time 24  aprile 2022 20:10:20);
  • ZJOINEDAT: 2022-04-24 20:10:19 (data di iscrizione nel formato MAC Absolute Time 24  aprile 2022 20:10:19);
  • ZFIRSTNAME: Abe (nome);
  • ZLASTNAME: Rudder (cognome);
  • ZGENDER: male (identità di genere, ad es. male, female, etc.);
  • ZBIRTHDAY: xxxx-10-25 (data di nascita/compleanno);
  • ZHOMECITY: Redmond, WA (città natale);
  • ZPHONE: 2065937xxx (numero di telefono);
  • ZEMAIL: aberudderxxx@gmail.com (indirizzo email);
  • ZFACEBOOKUSER: 1 (chiave esterna dell'account Facebook, Z_PK della tabella ZFSFACEBOOKUSER);
  • ZTWITTER: null (identificativo dell'account Twitter);
  • ZCANONICALURL: https://foursquare.com/user/1386806998 (URL del profilo);
  • ZPHOTOPREFIX: https://fastly.4sqi.net/img/user/ (parte inziale dell'URL della foto del profilo);
  • ZPHOTOSUFFIX: /1386806998_CHK2mA3y_vX4zXNuaCm-JkzaQxDcBnaAlgnKA1FgbUsgikmV9wHGHBNv9nv5C2ya4hyoYvWhQ.jpg (parte finale dell'URL della foto del profilo);
  • ZCHECKINPINGS: off (stato di condivisione del vicinato, ad es. on, off);
  • ZCHECKINSCOUNT: 38 (numero di check-in fatti);
  • ZMONGOID: 1386806998 (identificatore univoco);
  • ZRELATIONSHIP: self (tipo di relazione, ad  es. self (owner), friend (amico), pendingMe (follower), pendingThem (follower) e followingThem (following)).

L'URL della foto è formattato come "ZPHOTOPREFIX<formato>ZPHOTOSUFFIX", dove <formato> è la dimensione della foto; il valore original permette di visualizzare la foto originale. Con i valori sopra riportati il percorso completo è: "https://fastly.4sqi.net/img/user/original/1386806998_CHK2mA3y_vX4zXNuaCm-JkzaQxDcBnaAlgnKA1FgbUsgikmV9wHGHBNv9nv5C2ya4hyoYvWhQ.jpg". L'originale ha una dimensione (WxH) di 1024x1024. Per esempio un valore di <formato>=400x400 produce un'immagine scalata dell'originale.

La tabella ZFSFACEBOOKUSER (FU_PK=1):

  • Z_PK: 1 (chiave primaria);
  • ZMONGOID: 109289358430659 (identificatore dell'account di Facebook).

API: https://docs.foursquare.com/developer/reference/foursquare-apis-overview

Contatti

I contatti sono presenti sempre nel database SQLite "/Library/Caches/foursquare.sqlite", come già anticipato sopra, nella tabella ZFSUSER . Per ottenere l'elenco completo, escluso l'account, occorre filtrare ZRELATIONSHIP diverso da self o uguale a IS NULL. 

SELECT
    U.Z_PK AS "U_PK",
    FU.Z_PK AS "FU_PK",
    CASE U.ZUSERTYPE
        WHEN 'user' Then 'User'
        WHEN 'brand' Then 'Brand'
        WHEN 'celebrity' Then 'Celebrity'
        WHEN 'venuePage' Then 'Venue Page'
        WHEN 'page' Then 'Page'
        WHEN 'chain' Then 'Chain'
        ELSE U.ZUSERTYPE
    END AS "user_type",
    CASE U.ZRELATIONSHIP
        WHEN 'self' THEN 'Owner'
        WHEN 'friend' THEN 'Friend'
        WHEN 'pendingMe' THEN 'Follower'
        WHEN 'pendingThem' THEN 'Follower'
        WHEN 'followingThem' THEN 'Following'
        ELSE 'Other'
    END AS "relationship",
    U.ZFIRSTNAME, 
    U.ZLASTNAME,
    U.ZGENDER,
    date(U.ZBIRTHDAY + 978307200, 'unixepoch') AS "birthday",
    U.ZHOMECITY,
    U.ZPHONE,
    U.ZEMAIL,
    FU.ZMONGOID AS "facebook",
    U.ZTWITTER,
    U.ZCANONICALURL,
    IIF(U.ZPHOTOPREFIX NOT NULL AND U.ZPHOTOSUFFIX NOT NULL, U.ZPHOTOPREFIX || 'original' || U.ZPHOTOSUFFIX, '') AS "original_photo",
    U.ZMONGOID AS "uid"
FROM ZFSUSER AS "U"
LEFT JOIN ZFSFACEBOOKUSER AS "FU" ON (U.ZFACEBOOKUSER = FU.Z_PK)
WHERE (U.ZRELATIONSHIP != 'self') OR (U.ZRELATIONSHIP IS NULL)

La tabella ZFSUSER (i campi sono più o meno gli stessi dell'account):

  • ZUSERTYPE: null (tipo di utente, ad es. user (utente), brand (marca), celebrity (celebrità), venuePage (luogo), page (pagina), chain (catene di negozi/attività), etc.);
  • ZRELATIONSHIP: self (tipo di relazione, ad  es. self (owner), friend (amico), pendingMe (follower), pendingThem (follower) e followingThem (following)).

Check-in

I check-in è l'artefatto di maggior interesse dal punto di vista forense in quanto contiene sia le posizioni GPS che i timestamp dei luoghi visitati. Questi sono presenti nella tabella ZFSCHECKIN. Per ottenere solo i check-in bisogna filtrare ZCHECKINTYPE che rappresenta il tipo con il valore checkin.

SELECT
    CI.Z_PK AS "CI_PK",
    V.Z_PK AS "V_PK",
    U.Z_PK AS "U_PK",
    datetime(CI.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
    CASE U.ZRELATIONSHIP
        WHEN 'self' THEN 'Owner'
        WHEN 'friend' THEN 'Friend'
        WHEN 'pendingMe' THEN 'Follower'
        WHEN 'pendingThem' THEN 'Follower'
        WHEN 'followingThem' THEN 'Following'
        ELSE 'Other'
    END AS "relationship",
    IIF(U.ZLASTNAME IS NULL OR length(U.ZLASTNAME) = 0, coalesce(U.ZFIRSTNAME, ''), IIF(U.ZFIRSTNAME IS NULL OR length(U.ZFIRSTNAME) = 0, coalesce(U.ZLASTNAME, ''), U.ZFIRSTNAME || ' ' || coalesce(U.ZLASTNAME, ''))) AS "full_name",
    IIF(CI.ZPRIVATE = 1, 'Private', 'Public') AS "privacy",
    V.ZNAME AS "venue_name",
    V.ZDISTANCE AS "distance (m)",
    V.ZGEOLAT AS "latitude",
    V.ZGEOLONG AS "longitude",
    V.ZADDRESS AS "address",
    V.ZNEIGHBORHOOD AS "neighborhood",
    V.ZCOUNTRY AS "country",
    V.ZSTATE AS "state",
    V.ZCITY AS "city",
    V.ZCROSSSTREET,
    V.ZPOSTALCODE,
    V.ZPHONE,
    V.ZFACEBOOKID,
    V.ZTWITTER,
    V.ZINSTAGRAM,
    V.ZCANONICALURL AS "checkin_url",
    V.ZURL AS "website",
    IIF(CI.ZISMAYOR = 0, '', 'Yes') AS "is_major",
    CI.ZSHOUT,
    CI.ZMONGOID AS "checkin_uid"
FROM ZFSCHECKIN AS "CI"
LEFT JOIN ZFSVENUE AS "V" ON (CI.ZVENUE = V.Z_PK)
LEFT JOIN ZFSUSER AS "U" ON (CI.ZUSER = U.Z_PK)
WHERE CI.ZCHECKINTYPE = 'checkin'
ORDER BY CI.ZCREATEDAT DESC

La tabella ZFSCHECKIN (esempio CI_PK=517):

  • Z_PK: 517 (chiave primaria);
  • ZCREATEDAT: 2023-06-12 10:58:58 (data di creazione nel formato MAC Absolute Time 12  giugno 2023 10:58:58);
  • ZTIMEZONEOFFSET: -420 (offset del fuso orario in minuti, -7 ore); 
  • ZTIMEZONE: null (fuso orario del luogo, ad es. America/Denver);
  • ZPRIVATE: null (tipo di privacy, 1=privato);
  • ZISMAYOR: 0 (l'utente è un mayor del luogo del checkin? 0=no);
  • ZSOURCENAME: Swarm for iOS (nome della sorgente, ad es. Swarm for iOS);
  • ZMONGOID: 6486fa721efb251c01a199f6 (identificatore univoco);
  • ZCHECKINTYPE: checkin (tipo di checkin, ad es. checkin, passive=l'aggiornamento passivo delle posizioni);
  • ZSHOUT: null (messaggio "gridato");
  • ZVENUE: 2 (chiave esterna dei luoghi, Z_PK della tabella ZFSVENUE);
  • ZUSER: 1 (chiave esterna del contatto, Z_PK della tabella ZFSUSER).

La tabella ZFSVENUE contiene i luoghi visitati (V_PK=2):

  • Z_PK: 2 (chiave primaria);
  • ZNAME: Seattle-Tacoma International Airport (SEA) (nome del luogo);
  • ZDISTANCE: 26486.0 (distanza in metri dal luogo);
  • ZGEOLAT: 47.4435885341923 (latitudine del luogo);
  • ZGEOLONG: -122.302508354187 (longitudine del luogo);
  • ZADDRESS: 17801 International Blvd (indirizzo);
  • ZNEIGHBORHOOD: null (vicinato/quartiere);
  • ZCOUNTRY: United States (paese);
  • ZSTATE: WA (stato);
  • ZCITY: SeaTac (città);
  • ZCROSSSTREET: at S 188th St (traversa/incrocio);
  • ZPOSTALCODE: 98158 (codice postale);
  • ZPHONE: 2067875388 (numero di telefono);
  • ZFACEBOOKID: 126440017409096 (identificativo dell'account Facebook);
  • ZTWITTER: flysea (identificativo dell'account Twitter);
  • ZINSTAGRAM: flysea (identificativo dell'account Instagram);
  • ZCANONICALURL: https://foursquare.com/v/seattletacoma-international-airport-sea/45f555cef964a5200e441fe3 (URL del luogo);
  • ZURL: www.portseattle.org/sea-tac (sito web).

La tabella ZFSUSER (U_PK=1):

  • Z_PK: 1 (chiave primaria);
  • ZRELATIONSHIP: Owner (tipo di relazione);
  • ZFIRSTNAME: Abe (nome);
  • ZLASTNAME: Rudder (cognome).

Consigli

I consigli (tips) sono un modo di condividere approfondimenti con la comunità, offrire raccomandazioni o mettere in guardia contro determinate esperienze. La tabella in questione è ZFSTIP.

SELECT
    T.Z_PK AS "T_PK",
    U.Z_PK AS "U_PK",
    V.Z_PK AS "V_PK",
    datetime(T.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
    CASE U.ZRELATIONSHIP
        WHEN 'self' THEN 'Owner'
        WHEN 'friend' THEN 'Friend'
        WHEN 'pendingMe' THEN 'Follower'
        WHEN 'pendingThem' THEN 'Follower'
        WHEN 'followingThem' THEN 'Following'
        ELSE 'Other'
    END AS 'relationship',
    IIF(U.ZLASTNAME IS NULL OR length(U.ZLASTNAME) = 0, coalesce(U.ZFIRSTNAME, ''), IIF(U.ZFIRSTNAME IS NULL OR length(U.ZFIRSTNAME) = 0, coalesce(U.ZLASTNAME, ''), U.ZFIRSTNAME || ' ' || coalesce(U.ZLASTNAME, ''))) AS "full_name",
    T.ZTEXT AS "tip",
    V.ZNAME AS "venue_name",
    V.ZDISTANCE AS "distance (m)",
    V.ZGEOLAT AS "latitude",
    V.ZGEOLONG AS "longitude",
    T.ZURL AS "url_tip" ,
    T.ZMONGOID AS "uid"
FROM ZFSTIP AS "T"
LEFT JOIN ZFSUSER AS "U" ON (T.ZUSER = U.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (T.ZVENUE = V.Z_PK)
ORDER BY T.ZCREATEDAT DESC

La tabella ZFSTIP (esempio T_PK=46):

  • Z_PK: 46 (chiave primaria);
  • ZCREATEDAT: 2023-06-12 11:32:03 (data di creazione nel formato MAC Absolute Time 12  giugno 2023 11:32:03);
  • ZTEXT: On he mooove (testo del tip);
  • ZURL: null (URL del tip);
  • ZMONGOID: 64870233df69a377fc1f6c30 (identificatore univoco);
  • ZUSER: 1 (chiave esterna del contatto, Z_PK della tabella ZFSUSER);
  • ZVENUE: 2 (chiave esterna dei luoghi, Z_PK della tabella ZFSVENUE).

Adesivi

Gli adesivi (stickers) sono ricompense visive che possono essere aggiunte a messaggi, foto, nei successivi check-in per guadagnare monete, bonus e altro.

SELECT
    S.Z_PK AS "S_PK",
    S.ZNAME AS "title",
    S.ZCATEGORYNAME AS "category",
    S.ZTEASETEXT AS "preview_text",
    IIF(S.ZUNLOCKED = 0, '', 'Yes') AS "unlocked",
    S.ZUNLOCKTEXT,
    S.ZBONUSSTATUS,
    S.ZBONUSTEXT,
    IIF(S.ZIMAGEPREFIX NOT NULL AND S.ZIMAGENAME NOT NULL, S.ZIMAGEPREFIX || 'original' || S.ZIMAGENAME, '') AS "original_image",
    S.ZMONGOID AS "uid"
FROM ZFSSTICKER AS "S"

La tabella ZFSSTICKER (esempio S_PK=37):

  • Z_PK: 37 (chiave primaria);
  • ZNAME: Baggs (nome/titolo);
  • ZCATEGORYNAME: Food & Drink Shop (categoria);
  • ZTEASETEXT: Check in at food & drink shops to unlock this sticker. (anteprima del testo);
  • ZUNLOCKED: Yes (adesivo sbloccato? 0=No, 1=Yes);
  • ZUNLOCKTEXT: They're out of milk, your cart's wheel is busted, and that lady has way more than 10 items! Here's hoping Baggs doesn't dump your eggs on the ground. (testo quando è sbloccato);
  • ZBONUSSTATUS: Use once per week. Recharges Sunday at midnight. (stato del bonus);
  • ZBONUSTEXT: Use at Food & Drink Shops for a bonus. (testo del bonus);
  • ZIMAGEPREFIX: https://fastly.4sqi.net/img/sticker/ (parte inziale dell'URL dell'immagine);
  • ZSUFFIX: /groceries_2a2425.png (parte finale dell'URL dell'immagine);
  • ZMONGOID: 55563bd52beaa0fbc4d1dc3f (identificatore univoco).

La cronologia dei luoghi

La tabella ZFSVENUE contiene tutti i luoghi visitati mentre la tabella ZFSCATEGORY contiene le categorie associate ai luoghi, ad es. Airport, Medical Center, Men's Store, etc. 

SELECT
    V.Z_PK AS "V_PK",
    C.Z_PK AS "C_PK",
    U.Z_PK AS "U_PK",
    C.ZNAME AS "category_name",
    V.ZNAME AS "venue_name",
    V.ZDISTANCE AS "distance (m)",
    V.ZGEOLAT AS "latitude",
    V.ZGEOLONG AS "longitude",
    V.ZADDRESS AS "address",
    V.ZNEIGHBORHOOD AS "neighborhood",
    V.ZCOUNTRY AS "country",
    V.ZSTATE AS "state",
    V.ZCITY AS "city",
    V.ZCROSSSTREET,
    V.ZPOSTALCODE,
    V.ZPHONE,
    V.ZFACEBOOKID,
    V.ZTWITTER,
    V.ZINSTAGRAM,
    V.ZCANONICALURL AS "foursquare_url",
    V.ZURL AS "website",
    V.ZDESCRIPTIONTEXT,
    U.ZMONGOID AS "mayor_uid",
    IIF(U.ZLASTNAME IS NULL OR length(U.ZLASTNAME) = 0, coalesce(U.ZFIRSTNAME, ''), IIF(U.ZFIRSTNAME IS NULL OR length(U.ZFIRSTNAME) = 0, coalesce(U.ZLASTNAME, ''), U.ZFIRSTNAME || ' ' || coalesce(U.ZLASTNAME, ''))) AS "full_name",
    V.ZMAYORSUMMARY,
    V.ZFRIENDVISITSSUMMARY,
    V.ZEVENTSSUMMARY,
    V.ZREASONSUMMARY,
    V.ZLIKESCOUNT,
    V.ZUSERSCOUNT AS "visiters_count",
    V.ZCHECKINSCOUNT,
    V.ZPHOTOSCOUNT,
    V.ZTIPSCOUNT,
    V.ZEVENTSCOUNT
FROM ZFSVENUE AS "V"
LEFT JOIN ZFSCATEGORY AS "C" ON (V.ZPRIMARYCATEGORY = C.Z_PK)
LEFT JOIN ZFSUSER AS "U" ON (V.ZMAYOR = U.Z_PK)
ORDER BY V.ZNAME ASC

La tabella ZFSVENUE (esempio V_PK=3):

  • Z_PK: 3 (chiave primaria);
  • ZPRIMARYCATEGORY: 336 (chiave esterna della categoria, Z_PK della tabella ZFSCATEGORY);
  • ZNAME: Seattle-Tacoma International Airport (SEA) (nome del luogo);
  • ZDISTANCE: 26486.0 (distanza in metri dal luogo);
  • ZGEOLAT: 47.4435885341923 (latitudine del luogo);
  • ZGEOLONG: -122.302508354187 (longitudine del luogo);
  • ZADDRESS: 17801 International Blvd (indirizzo);
  • ZNEIGHBORHOOD: null (vicinato/quartiere);
  • ZCOUNTRY: United States (paese);
  • ZSTATE: WA (stato);
  • ZCITY: SeaTac (città);
  • ZCROSSSTREET: at S 188th St (traversa/incrocio);
  • ZPOSTALCODE: 98158 (codice postale);
  • ZPHONE: 2067875388 (numero di telefono);
  • ZFACEBOOKID: 126440017409096 (identificativo dell'account Facebook);
  • ZTWITTER: flysea (identificativo dell'account Twitter);
  • ZINSTAGRAM: flysea (identificativo dell'account Instagram);
  • ZCANONICALURL: https://foursquare.com/v/seattletacoma-international-airport-sea/45f555cef964a5200e441fe3 (URL del luogo);
  • ZURL: www.portseattle.org/sea-tac (sito web).
  • ZDESCRIPTIONTEXT: The 8th busiest airport in U.S., owned and operated by Port of Seattle. (descrizione del luogo);
  • ZMAYOR: 244 (chiave esterna dell'utente sindaco, Z_PK della tabella ZFSUSER);
  • ZMAYORSUMMARY: Andz M. is the Mayor with 31 check-ins in the last 30 days (riepilogo sul mayor);
  • ZFRIENDVISITSSUMMARY: You've been here (riepilogo delle visite degli amici);
  • ZEVENTSSUMMARY:  (riepilogo dell'evento);
  • ZREASONSUMMARY: You're here! (motivo per cui la sede è stata consigliata);
  • ZLIKESCOUNT: 5506 (numero totale di mi piace);
  • ZUSERSCOUNT: 254175 (numero totale di visitatori);
  • ZCHECKINSCOUNT: 1409015 (numero totale di checkin);
  • ZPHOTOSCOUNT: 21706 (numero totale di foto);
  • ZTIPSCOUNT: 2031 (numero totale di consigli);
  • ZEVENTCOUNT: null (numero totale di eventi).
La tabella ZFSCATEGORY (C_PK=336):
  • Z_PK: 336 (chiave primaria);
  • ZNAME: International Airport (nome della categoria, ad es. Airport, Office, Hospital, etc.).

Le foto dei luoghi

La tabella ZFSPHOTO contiene tutte le foto, sia quelle dei luoghi che dei suggerimenti, etc. Per le foto dei luoghi occorre solo filtrare il campo ZPHOTOTYPE che rappresenta il tipo di foto con il valore venue.

SELECT
    P.Z_PK AS "P_PK",
    V.Z_PK AS "V_PK",
    U.Z_PK AS "U_PK",
    datetime(P.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
    IIF(U.ZLASTNAME IS NULL OR length(U.ZLASTNAME) = 0, coalesce(U.ZFIRSTNAME, ''), IIF(U.ZFIRSTNAME IS NULL OR length(U.ZFIRSTNAME) = 0, coalesce(U.ZLASTNAME, ''), U.ZFIRSTNAME || ' ' || coalesce(U.ZLASTNAME, ''))) AS "full_name",
    U.ZMONGOID AS "user_uid",
    V.ZNAME AS "venue_name",
    V.ZDISTANCE AS "distance (m)",
    V.ZGEOLAT AS "latitude",
    V.ZGEOLONG AS "longitude",
    V.ZADDRESS AS "address",
    V.ZNEIGHBORHOOD AS "neighborhood",
    V.ZCOUNTRY AS "country",
    V.ZSTATE AS "state",
    V.ZCITY AS "city",
    V.ZCROSSSTREET,
    V.ZPOSTALCODE,
    V.ZPHONE,
    V.ZFACEBOOKID AS "facebook",
    V.ZTWITTER,
    V.ZINSTAGRAM,
    V.ZCANONICALURL AS "foursquare_url",
    V.ZURL AS "website",
    V.ZDESCRIPTIONTEXT,
    IIF(P.ZPREFIX NOT NULL AND P.ZSUFFIX NOT NULL, P.ZPREFIX || 'original' || P.ZSUFFIX, '') AS "original_photo",
    (P.ZWIDTH || 'x' || P.ZHEIGHT) AS "photo_size",
    P.ZSWARMPRIVACYSETTING,
    P.ZSOURCENAME
FROM ZFSPHOTO AS "P"
LEFT JOIN ZFSVENUE AS "V" ON (P.ZPREVIEWVENUE = V.Z_PK)
LEFT JOIN ZFSUSER AS "U" ON (P.ZUSER = U.Z_PK)
WHERE P.ZPHOTOTYPE = 'venue'

La tabella ZFSPHOTO (esempio P_PK=161):

  • Z_PK: 161 (chiave primaria);
  • ZCREATEDAT: 2018-01-16 03:06:52 (data di creazione nel formato MAC Absolute Time 16  gennaio 2018 03:06:52);
  • ZPHOTOTYPE: venue (tipo di foto, ad es. venue, tip, checkin, etc.);
  • ZPREFIX: https://fastly.4sqi.net/img/general/ (parte inziale dell'URL della foto);
  • ZSUFFIX: /17614142_64fOoDi-kXxehbuaFAdfOJS1bEaGHLgau0Sbb_z_Gdk.jpg (parte finale dell'URL della foto);
  • ZWIDTH: 1440 (larghezza in pixel);
  • ZHEIGHT: 1920 (altezza in pixel);
  • ZSWARMPRIVACYSETTING: public (privacy, ad es. public o private);
  • ZSOURCENAME: Swarm for iOS (sorgente/provenienza della foto, ad es. Instagram, Swarm for Android, Swarm for iOS, Foursquare for iOS, etc.);
  • ZPREVIEWVENUE: 2 (chiave esterna del luogo, Z_PK della tabella ZFSVENUE).

Commenti sui check-in

La tabella ZFSCOMMENT contiene tutti i commenti, sia quelli dei check-in, dei piani, dei suggerimenti, degli adesivi etc.

SELECT
    C.Z_PK AS "C_PK",
    U.Z_PK AS "U_PK",
    CI.Z_PK AS "CI_PK",
    V.Z_PK AS "V_PK",
    datetime(C.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
    CASE U.ZRELATIONSHIP
        WHEN 'self' THEN 'Owner'
        WHEN 'friend' THEN 'Friend'
        WHEN 'pendingMe' THEN 'Follower'
        WHEN 'pendingThem' THEN 'Follower'
        WHEN 'followingThem' THEN 'Following'
        ELSE 'Other'
    END AS "relationship",
    IIF(U.ZLASTNAME IS NULL OR length(U.ZLASTNAME) = 0, coalesce(U.ZFIRSTNAME, ''), IIF(U.ZFIRSTNAME IS NULL OR length(U.ZFIRSTNAME) = 0, coalesce(U.ZLASTNAME, ''), U.ZFIRSTNAME || ' ' || coalesce(U.ZLASTNAME, ''))) AS "full_name",
    C.ZTEXT AS "text",
    C.ZLAT AS "latitude",
    C.ZLNG AS "longitude",
    V.ZNAME AS "venue_name",
    V.ZGEOLAT AS "venue_latitude",
    V.ZGEOLONG AS "venue_longitude",
    C.ZMONGOID AS "uid"
FROM ZFSCOMMENT AS "C"
LEFT JOIN ZFSUSER AS "U" ON (C.ZUSER = U.Z_PK)
LEFT JOIN ZFSCHECKIN AS "CI" ON (C.ZCHECKIN = CI.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (CI.ZVENUE = V.Z_PK)

La tabella ZFSCOMMENT:

  • Z_PK: (chiave primaria);
  • ZCREATEDAT: (data di creazione nel formato MAC Absolute Time);
  • ZTEXT: (testo);
  • ZLAT: (latitudine del commento?);
  • ZLNG: (latitudine del commento?);
  • ZMONGOID: (identificatore univoco);
  • ZUSER: (chiave esterna dell'utente, Z_PK della tabella ZFSUSER);
  • ZCHECKIN: (chiave esterna del check-in, Z_PK della tabella ZFSCHECKIN).

Come si nota, non ho inserito volutamente i dati di esempio come nei casi precedenti in quanto non sono nella disponibilità di una banca dati completa. La query va quindi considerata come sperimentale!

Da fare

Lo studio svolto finora è focalizzato principalmente sull'utilizzo tipico dell'app, ovvero quello di fare check-in nei luoghi. Un'analisi più completa dovrebbe comprendere anche le altre funzioni come gli Eventi, le Liste, l'elenco delle foto per gli altri tipi (ad es. suggerimenti,  liste, etc.), i commenti per gli altri tipi, etc.

Poco è meglio di niente 😂.

iLEAPP 💖

Come di consueto, al fine di aggiungere un nuova app del poster della SANS “iOS Third-Party Apps Forensics Reference Guide Poster” al progetto iLEAPP (iOS Logs, Events, And Plists Parser) di Alexis Brignoni ho creato il plugin foursquareSwarm.py che alla data odierna è in “pull request” e comunque disponibile sul mio GitHub.

Di seguito alcuni "screenshot" del report di Foursquare Swarm:

, , , , ,

0 comments:

Posta un commento

Iscriviti a: Commenti sul post (Atom)