# -*- coding:utf-8 -*-
"""SQLite parser plugin for Twitter on Android database files."""
from plaso.containers import events
from plaso.parsers import sqlite
from plaso.parsers.sqlite_plugins import interface
[docs]
class AndroidTwitterSearchEventData(events.EventData):
"""Twitter on Android search event data.
Attributes:
creation_time (dfdatetime.DateTimeValues): date and time the search was
created.
name (str): twitter name handler.
query (str): SQL query that was used to obtain the event data.
search_query (str): search query.
"""
DATA_TYPE = "android:twitter:search"
[docs]
def __init__(self):
"""Initializes event data."""
super().__init__(data_type=self.DATA_TYPE)
self.creation_time = None
self.name = None
self.query = None
self.search_query = None
[docs]
class AndroidTwitterStatusEventData(events.EventData):
"""Twitter on Android status event data.
Attributes:
author_identifier (int): twitter account identifier.
content (str): status content.
creation_time (dfdatetime.DateTimeValues): date and time the status was
created.
favorited (int): favorited flag as 0/1 value.
identifier (int): status row identifier.
query (str): SQL query that was used to obtain the event data.
retweeted (int): retweeted flag as 0/1 value.
username (str): twitter account handler.
"""
DATA_TYPE = "android:twitter:status"
[docs]
def __init__(self):
"""Initializes event data."""
super().__init__(data_type=self.DATA_TYPE)
self.author_identifier = None
self.content = None
self.creation_time = None
self.favorited = None
self.identifier = None
self.query = None
self.retweeted = None
self.username = None
[docs]
class AndroidTwitterPlugin(interface.SQLitePlugin):
"""SQLite parser plugin for Twitter on Android database files."""
NAME = "twitter_android"
DATA_FORMAT = "Twitter on Android SQLite database file"
REQUIRED_STRUCTURE = {
"search_queries": frozenset(["name", "query", "time"]),
"statuses": frozenset(
["_id", "author_id", "content", "created", "favorited", "retweeted"]
),
"users": frozenset(
[
"username",
"user_id",
"_id",
"name",
"profile_created",
"description",
"web_url",
"location",
"followers",
"friends",
"statuses",
"image_url",
"updated",
"friendship_time",
]
),
}
QUERIES = [
("SELECT name, query, time FROM search_queries", "ParseSearchRow"),
(
(
"SELECT statuses._id AS _id, statuses.author_id AS author_id, "
"users.username AS username, statuses.content AS content, "
"statuses.created AS time, statuses.favorited AS favorited, "
"statuses.retweeted AS retweeted FROM statuses LEFT JOIN users ON "
"statuses.author_id = users.user_id"
),
"ParseStatusRow",
),
(
(
"SELECT _id, user_id, username, name, profile_created, description, "
"web_url, location, followers, friends, statuses, image_url, updated, "
"friendship_time FROM users"
),
"ParseContactRow",
),
]
SCHEMAS = [
{
"activities": (
"CREATE TABLE activities (_id INTEGER PRIMARY KEY,type INT,event "
"INT,created_at INT,hash INT,max_position INT,min_position "
"INT,sources_size INT,source_type INT,sources BLOB,targets_size "
"INT,target_type INT,targets BLOB,target_objects_size "
"INT,target_object_type INT,target_objects BLOB,is_last INT,tag "
"INT,magic_rec_id INT,UNIQUE (type, max_position) ON CONFLICT "
"REPLACE)"
),
"ads_account_permissions": (
"CREATE TABLE ads_account_permissions (_id INTEGER PRIMARY "
"KEY,promotable_users BLOB,last_synced INT NOT NULL)"
),
"android_metadata": ("CREATE TABLE android_metadata (locale TEXT)"),
"business_profiles": (
"CREATE TABLE business_profiles (_id INTEGER PRIMARY KEY,user_id "
"INT UNIQUE NOT NULL,business_profile BLOB,last_synced INT NOT "
"NULL)"
),
"card_state": (
"CREATE TABLE card_state (_id INTEGER PRIMARY KEY "
"AUTOINCREMENT,card_status_id INT,card_id INT, card_state BLOB)"
),
"category_timestamp": (
"CREATE TABLE category_timestamp (_id INTEGER PRIMARY "
"KEY,cat_status_id INT NOT NULL,cat_tag INT NOT NULL,cat_timestamp "
"INT NOT NULL)"
),
"clusters": (
"CREATE TABLE clusters (_id INTEGER PRIMARY KEY,cl_cluster_id TEXT "
"UNIQUE NOT NULL,cl_type INT,cl_title TEXT,cl_subtitle TEXT,cl_size "
"INT,cl_timestamp INT,cl_content BLOB)"
),
"conversation_entries": (
"CREATE TABLE conversation_entries (_id INTEGER PRIMARY "
"KEY,entry_id INT UNIQUE NOT NULL,sort_entry_id INT UNIQUE NOT "
"NULL,conversation_id TEXT,user_id INT,created INT,entry_type "
"INT,data BLOB,request_id TEXT)"
),
"conversation_participants": (
"CREATE TABLE conversation_participants (_id INTEGER PRIMARY "
"KEY,conversation_id TEXT NOT NULL,user_id TEXT NOT NULL,join_time "
"INT NOT NULL,participant_type INT NOT NULL)"
),
"conversations": (
"CREATE TABLE conversations (_id INTEGER PRIMARY "
"KEY,conversation_id TEXT UNIQUE NOT NULL,title TEXT,avatar_url "
"TEXT,type INT,sort_event_id BIGINT,last_readable_event_id "
"BIGINT,last_read_event_id BIGINT,sort_timestamp BIGINT,is_muted "
"INT,min_event_id BIGINT,is_hidden INT,has_more INT,read_only INT)"
),
"cursors": (
"CREATE TABLE cursors (_id INTEGER PRIMARY KEY,kind INT,type "
"INT,owner_id INT,ref_id TEXT,next TEXT)"
),
"dismiss_info": (
"CREATE TABLE dismiss_info(timeline_id INTEGER REFERENCES "
"timeline(_id),feedback_action_id INTEGER REFERENCES "
"feedback_action(_id),UNIQUE(timeline_id,feedback_action_id))"
),
"feedback_action": (
"CREATE TABLE feedback_action(_id INTEGER PRIMARY KEY "
"AUTOINCREMENT,feedback_type TEXT,prompt TEXT,confirmation "
"TEXT,UNIQUE(feedback_type,prompt,confirmation))"
),
"list_mapping": (
"CREATE TABLE list_mapping (_id INTEGER PRIMARY "
"KEY,list_mapping_list_id TEXT,list_mapping_type "
"INT,list_mapping_user_id INT,list_is_last INT)"
),
"locations": (
"CREATE TABLE locations (_id INTEGER PRIMARY KEY,name TEXT,woeid "
"INT,country TEXT,country_code TEXT)"
),
"moments": (
"CREATE TABLE moments (_id INTEGER PRIMARY KEY,title TEXT NOT "
"NULL,can_subscribe INT,is_live INT,is_sensitive "
"INT,subcategory_string TEXT,subcategory_favicon_url "
"TEXT,time_string TEXT,duration_string TEXT,is_subscribed "
"INT,description TEXT NOT NULL,moment_url TEXT,num_subscribers "
"INT,author_info BLOB,promoted_content BLOB)"
),
"moments_guide": (
"CREATE TABLE moments_guide (_id INTEGER PRIMARY KEY,moment_id INT "
"NOT NULL,section_id INT NOT NULL,tweet_id INT NOT NULL, crop_data "
"BLOB,media_id INT,media_url TEXT,media_size BLOB,FOREIGN "
"KEY(section_id) REFERENCES moments_sections(_id) ON DELETE "
"CASCADE)"
),
"moments_guide_categories": (
"CREATE TABLE moments_guide_categories (_id INTEGER PRIMARY "
"KEY,category_id TEXT NOT NULL,is_default_category INT NOT "
"NULL,category_name TEXT NOT NULL,fetch_timestamp INT NOT NULL)"
),
"moments_guide_user_states": (
"CREATE TABLE moments_guide_user_states (_id INTEGER PRIMARY "
"KEY,moment_id INT NOT NULL,is_read INT,is_updated INT,FOREIGN "
"KEY(moment_id) REFERENCES moments(_id) ON DELETE CASCADE)"
),
"moments_pages": (
"CREATE TABLE moments_pages (_id INTEGER PRIMARY KEY,moment_id INT "
"NOT NULL,page_id TEXT,type BLOB,tweet_id INT,display_mode "
"BLOB,page_number INT,crop_data BLOB,theme_data BLOB,media_id "
"INT,media_size BLOB,media_url TEXT,last_read_timestamp INT,FOREIGN "
"KEY(moment_id) REFERENCES moments(_id))"
),
"moments_sections": (
"CREATE TABLE moments_sections (_id INTEGER PRIMARY "
"KEY,section_title TEXT,section_type BLOB NOT NULL,section_group_id "
"TEXT,section_group_type INT NOT NULL)"
),
"moments_visit_badge": (
"CREATE TABLE moments_visit_badge (_id INTEGER PRIMARY "
"KEY,moment_id INT UNIQUE NOT NULL,is_new_since_visit "
"INT,is_updated_since_visit INT)"
),
"news": (
"CREATE TABLE news (_id INTEGER PRIMARY KEY AUTOINCREMENT,country "
"TEXT,language TEXT,topic_id INT,news_id TEXT,title TEXT,image_url "
"TEXT,author_name TEXT,article_description TEXT,article_url "
"TEXT,tweet_count INT,start_time INT,news_id_hash INT)"
),
"notifications": (
"CREATE TABLE notifications (_id INTEGER PRIMARY KEY,type "
"INT,notif_id INT,source_user_name TEXT,s_name TEXT,s_id "
"INT,notif_txt TEXT,aggregation_data TEXT,notif_extra_data BLOB)"
),
"one_click": (
"CREATE TABLE one_click (_id INTEGER PRIMARY KEY,topic "
"TEXT,filter_name TEXT,filter_location TEXT,filter_follow INT)"
),
"order_history": (
"CREATE TABLE order_history (_id INTEGER PRIMARY KEY,ordered_at INT "
",order_id INT ,data BLOB)"
),
"promoted_retry": (
"CREATE TABLE promoted_retry(impression_id TEXT,event INT NOT "
"NULL,is_earned INT NOT NULL,trend_id INT,num_retries INT NOT "
"NULL,url TEXT,video_playlist_url TEXT,video_content_uuid "
"TEXT,video_content_type TEXT,video_cta_url TEXT,video_cta_app_id "
"TEXT,video_cta_app_name TEXT,card_event TEXT,PRIMARY "
"KEY(impression_id,event,is_earned,trend_id))"
),
"prompts": (
"CREATE TABLE prompts (_id INTEGER PRIMARY KEY,p_id INT,p_format "
"TEXT,p_template TEXT,p_header TEXT,p_text TEXT,p_action_text "
"TEXT,p_action_url TEXT,p_icon TEXT,p_background_image_url "
"TEXT,p_persistence TEXT,p_entities BLOB,p_header_entities "
"BLOB,p_status_id LONG,p_insertion_index INT,p_trigger TEXT)"
),
"rankings": (
"CREATE TABLE rankings (_id INTEGER PRIMARY KEY "
"AUTOINCREMENT,country TEXT,language TEXT,granularity TEXT,category "
"TEXT,date INT)"
),
"search_queries": (
"CREATE TABLE search_queries (_id INTEGER PRIMARY KEY,type INT,name "
"TEXT NOT NULL,query TEXT NOT NULL,query_id INT,time INT,latitude "
"REAL,longitude REAL,radius REAL,location TEXT,pc "
"BLOB,cluster_titles BLOB)"
),
"search_results": (
"CREATE TABLE search_results (_id INTEGER PRIMARY KEY,search_id "
"INT,s_type INT,data_type INT,type_id INT,polled INT,data_id "
"INT,related_data BLOB,cluster_id INT)"
),
"search_suggestion_metadata": (
"CREATE TABLE search_suggestion_metadata (_id INTEGER PRIMARY "
"KEY,type INT,last_update LONG)"
),
"status_groups": (
"CREATE TABLE status_groups (_id INTEGER PRIMARY KEY,tweet_type INT "
"DEFAULT 0,type INT,sender_id INT,owner_id INT,ref_id INT,tag "
"INT,g_status_id INT,is_read INT,page INT,is_last INT,updated_at "
"INT,timeline INT,pc BLOB,g_flags INT,preview_draft_id "
"INT,preview_media BLOB,tweet_pivots BLOB)"
),
"status_metadata": (
"CREATE TABLE status_metadata (_id INTEGER PRIMARY KEY,owner_id INT "
"NOT NULL,status_id INT NOT NULL,status_group INT NOT "
"NULL,status_group_tag INT NOT NULL,soc_type INT,soc_name "
"TEXT,soc_second_name TEXT,soc_others_count INT,soc_fav_count "
"INT,soc_rt_count INT,reason_icon_type TEXT,reason_text "
"TEXT,scribe_component TEXT,scribe_data BLOB,highlights TEXT)"
),
"statuses": (
"CREATE TABLE statuses (_id INTEGER PRIMARY KEY,status_id INT "
"UNIQUE NOT NULL,author_id INT,content TEXT,source TEXT,created "
"INT,in_r_user_id INT,in_r_status_id INT,favorited INT,latitude "
"TEXT,longitude TEXT,place_data BLOB,entities TEXT,retweet_count "
"INT,r_content TEXT,cards BLOB,flags INT,favorite_count INT,lang "
"TEXT,supplemental_language TEXT,view_count INT,quoted_tweet_data "
"BLOB,quoted_tweet_id INT,retweeted INT)"
),
"stories": (
"CREATE TABLE stories ( _id INTEGER PRIMARY KEY,story_id "
"TEXT,story_order INT,story_type INT,story_proof_type "
"INT,story_proof_addl_count INT,data_type INT,data_id "
"INT,story_is_read INT,story_meta_title TEXT,story_meta_subtitle "
"TEXT,story_meta_query TEXT,story_meta_header_img_url "
"TEXT,story_source TEXT,story_impression_info TEXT,story_tag INT)"
),
"timeline": (
"CREATE TABLE timeline (_id INTEGER PRIMARY KEY "
"AUTOINCREMENT,owner_id INT,type INT,sort_index INT,entity_id "
"INT,entity_type INT,data_type INT,data_type_group "
"INT,data_type_tag INT,timeline_tag TEXT,timeline_group_id "
"INT,timeline_scribe_group_id INT,data_id INT,data BLOB,flags "
"INT,updated_at INT,data_origin_id TEXT,is_last INT,is_read "
"INT,scribe_content BLOB,timeline_moment_info BLOB,dismissed INT "
"NOT NULL DEFAULT 0,dismiss_actions INT NOT NULL DEFAULT 0)"
),
"tokens": (
"CREATE TABLE tokens (_id INTEGER PRIMARY KEY,text TEXT,weight "
"INT,type INT,ref_id INT)"
),
"topics": (
"CREATE TABLE topics (_id INTEGER PRIMARY KEY,ev_id TEXT UNIQUE NOT "
"NULL,ev_type INT,ev_query TEXT NOT NULL,ev_seed_hashtag "
"TEXT,ev_title STRING,ev_subtitle STRING,ev_view_url "
"STRING,ev_status STRING,ev_image_url TEXT,ev_explanation "
"TEXT,ev_tweet_count INT,ev_start_time INT,ev_owner_id INT,ev_pc "
"BLOB,ev_content BLOB,ev_hash INT)"
),
"user_groups": (
"CREATE TABLE user_groups (_id INTEGER PRIMARY KEY,type INT,tag "
"INT,rank INT,owner_id INT,user_id INT,is_last INT,pc BLOB,g_flags "
"INT)"
),
"user_metadata": (
"CREATE TABLE user_metadata (_id INTEGER PRIMARY KEY,owner_id INT "
"NOT NULL,user_id INT NOT NULL,user_group_type INT NOT "
"NULL,user_group_tag INT NOT NULL,soc_type INT,soc_name "
"TEXT,soc_follow_count INT,user_title TEXT,token TEXT)"
),
"users": (
"CREATE TABLE users (_id INTEGER PRIMARY KEY,user_id INT UNIQUE NOT "
"NULL,username TEXT,name TEXT,description TEXT,web_url "
"TEXT,bg_color INT,location TEXT,structured_location "
"BLOB,user_flags INT,followers INT,fast_followers INT DEFAULT "
"0,friends INT,statuses INT,profile_created INT,image_url TEXT,hash "
"INT,updated INT,friendship INT,friendship_time INT,favorites INT "
"DEFAULT 0,header_url TEXT,description_entities BLOB,url_entities "
"BLOB,media_count INT,extended_profile_fields BLOB,pinned_tweet_id "
"INT,link_color INT,advertiser_type TEXT,business_profile_state "
"TEXT)"
),
}
]
[docs]
def ParseSearchRow(self, parser_mediator, query, row, **unused_kwargs):
"""Parses a search row from the database.
Args:
parser_mediator (ParserMediator): mediates interactions between parsers
and other components, such as storage and dfVFS.
query (str): query that created the row.
row (sqlite3.Row): row resulting from query.
"""
query_hash = hash(query)
event_data = AndroidTwitterSearchEventData()
event_data.creation_time = self._GetJavaTimeRowValue(query_hash, row, "time")
event_data.query = query
event_data.name = self._GetRowValue(query_hash, row, "name")
event_data.search_query = self._GetRowValue(query_hash, row, "query")
parser_mediator.ProduceEventData(event_data)
[docs]
def ParseStatusRow(self, parser_mediator, query, row, **unused_kwargs):
"""Parses a status row from the database.
Args:
parser_mediator (ParserMediator): mediates interactions between parsers
and other components, such as storage and dfVFS.
query (str): query that created the row.
row (sqlite3.Row): row resulting from query.
"""
query_hash = hash(query)
event_data = AndroidTwitterStatusEventData()
event_data.author_identifier = self._GetRowValue(query_hash, row, "author_id")
event_data.content = self._GetRowValue(query_hash, row, "content")
event_data.creation_time = self._GetJavaTimeRowValue(query_hash, row, "time")
event_data.favorited = self._GetRowValue(query_hash, row, "favorited")
event_data.identifier = self._GetRowValue(query_hash, row, "_id")
event_data.query = query
event_data.retweeted = self._GetRowValue(query_hash, row, "retweeted")
event_data.username = self._GetRowValue(query_hash, row, "username")
parser_mediator.ProduceEventData(event_data)
sqlite.SQLiteParser.RegisterPlugin(AndroidTwitterPlugin)