# -*- coding:utf-8 -*-
"""SQLite parser plugin for Tango on Android database files."""
import codecs
from base64 import b64decode as base64_decode
from plaso.containers import events
from plaso.parsers import sqlite
from plaso.parsers.sqlite_plugins import interface
[docs]
class AndroidTangoConversationEventData(events.EventData):
"""Tango on Android conversation event data.
Attributes:
conversation_identifier (int): conversation identifier.
"""
DATA_TYPE = "android:tango:conversation"
[docs]
def __init__(self):
"""Initializes event data."""
super().__init__(data_type=self.DATA_TYPE)
self.conversation_identifier = None
[docs]
class AndroidTangoMessageEventData(events.EventData):
"""Tango on Android message event data.
Attributes:
creation_time (dfdatetime.DateTimeValues): date and time the message
was created.
direction (int): flag indicating direction of the message.
message_identifier (int): message identifier.
sent_time (dfdatetime.DateTimeValues): date and time the message was sent.
"""
DATA_TYPE = "android:tango:message"
[docs]
def __init__(self):
"""Initializes event data."""
super().__init__(data_type=self.DATA_TYPE)
self.creation_time = None
self.direction = None
self.message_identifier = None
self.sent_time = None
[docs]
class AndroidTangoProfilePlugin(interface.SQLitePlugin):
"""SQLite parser plugin for Tango on Android profile database files."""
NAME = "tango_android_profile"
DATA_FORMAT = "Tango on Android profile SQLite database file"
REQUIRED_STRUCTURE = {
"profiletable": frozenset(
[
"itemLastActiveTime",
"itemLastLocalAccessTime",
"itemFriendRequestTime",
"itemFirstName",
"itemLastName",
"itemBirthday",
"itemGender",
"itemStatus",
"itemDistance",
"itemIsFriend",
"itemFriendRequestType",
"itemFriendRequestMessage",
]
)
}
QUERIES = [
(
(
"SELECT itemLastActiveTime AS last_active_time, "
"itemLastLocalAccessTime AS last_access_time, "
"itemFriendRequestTime AS friend_request_time, "
"itemFirstName AS first_name, itemLastName AS last_name, itemBirthday "
"AS birthday, itemGender AS gender, itemStatus AS status, itemDistance "
"AS distance, itemIsFriend AS friend, itemFriendRequestType AS "
"friend_request_type, itemFriendRequestMessage AS "
"friend_request_message FROM profiletable"
),
"ParseContactRow",
)
]
SCHEMAS = [
{
"profiles": (
"CREATE TABLE `profiles` (`key` TEXT PRIMARY KEY, `value` TEXT)"
),
"profiletable": (
"CREATE TABLE `profiletable` (`itemUserId` TEXT PRIMARY KEY, "
"`itemFirstName` TEXT NOT NULL, `itemLastName` TEXT NOT NULL, "
"`itemBirthday` TEXT NOT NULL, `itemGender` TEXT NOT NULL, "
"`itemStatus` TEXT NOT NULL, `itemLastActiveTime` BIGINT NOT NULL, "
"`itemDistance` DOUBLE NOT NULL, `itemCity` TEXT NOT NULL, "
"`itemGeoCountryCode` TEXT NOT NULL, `itemAvatarUrl` TEXT NOT NULL, "
"`itemThumbnailUrl` TEXT NOT NULL, `itemVideoUrl` TEXT NOT NULL, "
"`itemVideoThumbnailUrl` TEXT NOT NULL, `itemBackgroundUrl` TEXT "
"NOT NULL, `itemIsFriend` INTEGER NOT NULL, `itemIsBlocked` INTEGER "
"NOT NULL, `itemFriendRequestType` TEXT NOT NULL, "
"`itemReverseRelationships` TEXT NOT NULL, `itemFavoriterCount` "
"INTEGER NOT NULL, `itemFavoritingCount` INTEGER NOT NULL, "
"`itemFeedCount` INTEGER NOT NULL, `itemRefereneCount` INTEGER NOT "
"NULL, `itemLevel1DataSyncTime` BIGINT NOT NULL, "
"`itemLevel2DataSyncTime` BIGINT NOT NULL, `itemLevel3DataSyncTime` "
"BIGINT NOT NULL, `itemLevel4DataSyncTime` BIGINT NOT NULL, "
"`itemLevel5DataSyncTime` BIGINT NOT NULL, "
"`itemLastLocalAccessTime` BIGINT NOT NULL, `itemFriendRequestId` "
"TEXT NOT NULL, `itemFriendRequestMessage` TEXT NOT NULL, "
"`itemFriendRequestTime` BIGINT NOT NULL, `itemIsNewFriendRequest` "
"INTEGER NOT NULL, `itemFriendRequestTCMessageId` INTEGER NOT NULL, "
"`itemFriendRequestContext` TEXT NOT NULL, "
"`itemFriendRequestAttachedPostType` INTEGER NOT NULL, "
"`itemFriendRequestAttachedPostContent` TEXT NOT NULL, "
"`itemFriendRequestHasBeenForwardedToTc` INTEGER NOT NULL, "
"`itemProfileType` TEXT NOT NULL, `itemDatingAge` INTEGER NOT NULL, "
"`itemDatingLocationString` TEXT NOT NULL, "
"`itemDatingSeekingString` TEXT NOT NULL, `itemDatingEssayText` "
"TEXT NOT NULL, `itemDatingBodyType` TEXT NOT NULL, "
"`itemDatingLastActive` TEXT NOT NULL, `itemDatingProfileUrl` TEXT "
"NOT NULL, `itemLastTimeOfLikeProfile` BIGINT NOT NULL, "
"`itemIsHidden` INTEGER NOT NULL, `itemPrivacy` INTEGER NOT NULL, "
"`itemCanSeeMyPost` INTEGER NOT NULL, `itemCanShareMyPost` INTEGER "
"NOT NULL, `itemCanContactMe` INTEGER NOT NULL)"
),
}
]
def _GetBase64RowValue(self, parser_mediator, query_hash, row, value_name):
"""Retrieves a base64 encoded value from the row.
Args:
parser_mediator (ParserMediator): mediates interactions between parsers
and other components, such as storage and dfVFS.
query_hash (int): hash of the query, that uniquely identifies the query
that produced the row.
row (sqlite3.Row): row.
value_name (str): name of the value.
Returns:
object: value or None if not available.
"""
encoded_value = self._GetRowValue(query_hash, row, value_name)
try:
value = base64_decode(encoded_value)
return codecs.decode(value, "utf-8")
except ValueError as exception:
parser_mediator.ProduceExtractionWarning(
f"unable to decode: {value_name:s} with error: {exception!s}"
)
return None
[docs]
class AndroidTangoTCPlugin(interface.SQLitePlugin):
"""SQLite parser plugin for Tango on Android TC database files."""
NAME = "tango_android_tc"
DATA_FORMAT = "Tango on Android TC SQLite database file"
REQUIRED_STRUCTURE = {
"conversations": frozenset(["conv_id", "payload"]),
"messages": frozenset(
["create_time", "send_time", "msg_id", "payload", "direction"]
),
"likes": frozenset(["msg_id"]),
}
QUERIES = [
(
(
"SELECT conversations.conv_id AS conv_id, conversations.payload AS "
"payload FROM conversations"
),
"ParseConversationRow",
),
(
(
"SELECT messages.create_time AS create_time, messages.send_time AS "
"send_time, messages.msg_id AS msg_id, messages.payload AS payload, "
"messages.direction AS direction FROM messages LEFT JOIN likes ON "
"messages.msg_id = likes.msg_id"
),
"ParseMessageRow",
),
]
SCHEMAS = [
{
"conversations": (
"CREATE TABLE `conversations` (`conv_id` TEXT PRIMARY KEY, "
"`conv_type` INTEGER DEFAULT 0, `payload` BLOB, `last_msg_id` "
"INTEGER, `unread_count` INTEGER, `last_read_sent_msg_id` INTEGER, "
"`conv_del_status` INTEGER DEFAULT 0, `deleting_ts` BIGINT DEFAULT "
"0, `conv_restore_status` INTEGER DEFAULT 0, `peers_read` TEXT, "
"`total_received_msg_count` INTEGER DEFAULT -1, "
"`communication_context` INTEGER DEFAULT 0)"
),
"games": (
"CREATE TABLE `games` (`game_session_id` TEXT PRIMARY KEY, "
"`message_id` INTEGER, `conversation_id` TEXT, `game_id` TEXT, "
"`game_state` INTEGER, `action_timestamp` BIGINT, "
"`current_player_account_id` TEXT)"
),
"likes": (
"CREATE TABLE `likes` (`msg_id` INTEGER PRIMARY KEY, "
"`global_msg_id` TEXT, `conv_id` TEXT, `liker_aid` TEXT, `act_type` "
"INTEGER, `status` INTEGER, `act_ts` BIGINT, `payload` BLOB)"
),
"messages": (
"CREATE TABLE `messages` (`msg_id` INTEGER PRIMARY KEY, `conv_id` "
"TEXT, `type` INTEGER, `media_id` TEXT, `share_id` TEXT, "
"`create_time` BIGINT, `send_time` BIGINT, `direction` INTEGER, "
"`status` INTEGER, `payload` BLOB, `del_status` INTEGER)"
),
"profiles": (
"CREATE TABLE `profiles` (`key` TEXT PRIMARY KEY, `value` TEXT)"
),
"receipts": (
"CREATE TABLE `receipts` (`conv_id` TEXT PRIMARY KEY, `msg_id` "
"INTEGER, `sender_msg_id` INTEGER, `sender_aids` TEXT, `type` "
"INTEGER, `create_time` BIGINT, `status` INTEGER, `payload` BLOB)"
),
"sms": (
"CREATE TABLE `sms` (`msg_id` INTEGER PRIMARY KEY, `phonenumber` "
"TEXT, `text` TEXT)"
),
}
]
[docs]
def ParseConversationRow(self, parser_mediator, query, row, **unused_kwargs):
"""Parses a conversation 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 = AndroidTangoConversationEventData()
event_data.conversation_identifier = self._GetRowValue(
query_hash, row, "conv_id"
)
# TODO: payload is a base64 encoded binary blob, we need to find the
# structure to extract the relevant bits.
# event_data.payload = self._GetRowValue(query_hash, row, 'payload')
parser_mediator.ProduceEventData(event_data)
[docs]
def ParseMessageRow(self, parser_mediator, query, row, **unused_kwargs):
"""Parses a message 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)
# TODO: payload is a base64 encoded binary blob, we need to find the
# structure to extract the relevant bits.
# payload = self._GetRowValue(query_hash, row, 'payload')
event_data = AndroidTangoMessageEventData()
event_data.creation_time = self._GetJavaTimeRowValue(
query_hash, row, "create_time"
)
event_data.direction = self._GetRowValue(query_hash, row, "direction")
event_data.message_identifier = self._GetRowValue(query_hash, row, "msg_id")
event_data.sent_time = self._GetJavaTimeRowValue(query_hash, row, "send_time")
parser_mediator.ProduceEventData(event_data)
sqlite.SQLiteParser.RegisterPlugins([AndroidTangoProfilePlugin, AndroidTangoTCPlugin])