def __init__(self):
self.conn = CryptoDatabase()
class UserDAO:
def __init__(self):
self.conn = CryptoDatabase()
def insert(self, user_data: tuple):
query = "INSERT INTO chat_user (nickname,pass_hash,salt,public_key,is_active) VALUES (%s,%s,%s,%s,%s)"
self.conn.execute(query, query_data=user_data)
result = self.conn.query("SELECT * FROM chat_user WHERE nickname = %s",
(user_data[0], ))
self.conn.commit()
return len(result)
def update(self, new_data: tuple):
#tupla tem que ser (nick,hash,salt,public,active,nick)
query = "UPDATE chat_user SET nickname = %s, pass_hash = %s, salt = %s, public_key = %s, is_active = %s WHERE nickname = %s"
self.conn.execute(query, query_data=new_data)
result = self.conn.query(
"SELECT * FROM chat_user WHERE nickname = %s and pass_hash = %s and salt = %s and public_key = %s and is_active = %s",
tuple([x for x in new_data][:-1]))
self.conn.commit()
return len(result)
def delete(self, user_id):
query = "DELETE FROM chat_user WHERE user_id = %s"
self.conn.execute(query, (user_id, ))
self.conn.commit()
def select_by_id(self, user_id):
query = "SELECT * FROM chat_user WHERE user_id = %s"
res = self.conn.query(query, query_data=(user_id, ))
if (len(res) > 0):
return res[0]
else:
return None
def try_to_login(self, nickname, password, publickey):
from modules.crypto import CryptoEngine
salt_pos = 3
hash_pos = 2
nick = nickname.decode("utf8")
select_result = self.select_by_nickname(nickname=nick)
engine = CryptoEngine()
engine.init_HASH_mode()
if select_result == None:
from Crypto.Random import get_random_bytes
salt = get_random_bytes(16)
password_salt = password + salt
hash_pass = engine.hash_byte_string(password_salt)
self.insert(user_data=(nick, hash_pass, salt, publickey, '0'))
return True
else:
password_salt = password + bytes(select_result[salt_pos])
password_hash = engine.hash_byte_string(password_salt)
if password_hash == select_result[hash_pos].tobytes():
self.update(
(nick, password_hash, select_result[salt_pos].tobytes(),
publickey, '1', nick))
print("Usuario cadastrado: Login com sucesso!")
return True
else:
print("Usuario cadastrado: Login sem sucesso!")
return False
def select_by_nickname(self, nickname: str):
query = "SELECT * FROM chat_user WHERE nickname = %s"
res = self.conn.query(query, query_data=(nickname, ))
if (len(res) > 0):
return res[0]
else:
return None
class KeySetDAO:
def __init__(self):
self.conn = CryptoDatabase()
def insert(self, key_set_data: tuple):
#tupla tem que ser (usuario1,key_user1,usuario2,key_user2,conversation_id)
query = "INSERT INTO key_set (private_owner,key,conversation_id) VALUES (%s,%s,%s)"
rows = 0
for i in range(0, 3, 2):
data = tuple([key_set_data[0 + i], key_set_data[1 + i]] +
[key_set_data[-1]])
self.conn.execute(query, query_data=data)
result = self.conn.query(
"SELECT * FROM key_set WHERE private_owner = %s and conversation_id = %s",
(data[0], data[2]))
rows += len(result)
self.conn.commit()
return rows
def update(self, new_data: tuple):
#tupla tem que ser (usuario1,key_user1,usuario2,key_user2,conversation_id)
rows = 0
for i in range(2):
query = "UPDATE key_set SET key = %s WHERE private_owner = %s AND conversation_id = %s"
data = tuple([new_data[1 + i], new_data[0 + i], new_data[-1]])
self.conn.execute(query, query_data=data)
result = self.conn.query(
"SELECT * FROM key_set WHERE private_owner = %s AND conversation_id = %s",
(data[1], data[2]))
rows += len(result)
self.conn.commit()
return rows
def delete(self, conversation_id):
query = "DELETE FROM key_set WHERE conversation_id = %s"
self.conn.execute(query, (conversation_id, ))
self.conn.commit()
def select_by_owner_conversation(self, data):
query = "SELECT * FROM key_set WHERE private_owner = %s AND conversation_id = %s"
res = self.conn.query(query, query_data=data)
if (len(res) > 0):
return res[0]
else:
return None
class UserRelationDAO:
def __init__(self):
self.conn = CryptoDatabase()
def insert(self, userR_data: tuple):
query = "INSERT INTO user_relation (first_user,second_user,relation_type) VALUES (%s,%s,%s)"
data = tuple(sorted([userR_data[0], userR_data[1]]) + [userR_data[2]])
self.conn.execute(query, query_data=data)
result = self.conn.query(
"SELECT * FROM user_relation WHERE first_user = %s AND second_user = %s",
(data[0], data[1]))
self.conn.commit()
return len(result)
def update(self, new_data: tuple):
query = "UPDATE chat_user SET relation_type = %s WHERE first_user = %s AND second_user = %s"
data = tuple([userR_data[2]] + sorted([new_data[0], new_data[1]]))
self.conn.execute(query, query_data=data)
result = self.conn.query(
"SELECT * FROM user_relation WHERE first_user = %s AND second_user = %s AND relation_type = %s",
tuple([data[1], data[2], data[0]]))
self.conn.commit()
return len(result)
def delete(self, users: tuple):
query = "DELETE FROM user_relation WHERE first_user = %s AND second_user = %s"
data = tuple(sorted(users[0], users[1]))
self.conn.execute(query, data)
self.conn.commit()
def select_by_users(self, users):
query = "SELECT * FROM user_relation WHERE first_user = %s AND second_user = %s"
data = tuple(sorted([users[0], users[1]]))
res = self.conn.query(query, query_data=data)
if (len(res) > 0):
return res[0]
else:
return None
def select_by_single_user(self, user_id):
query = "SELECT * FROM user_relation WHERE first_user = %s OR second_user = %s"
return self.conn.query(query, query_data=tuple([user_id] * 2))
def select_relation_of_users(self, users):
query = "SELECT relation_type FROM user_relation WHERE first_user = %s AND second_user = %s"
data = tuple(sorted([users[0], users[1]]))
return self.conn.query(query, query_data=data)
def select_friends_of_user(self, nickname):
uDAO = UserDAO()
current_user = uDAO.select_by_nickname(nickname)
if (len(current_user) < 1):
return False
query = "SELECT user_id, nickname, public_key FROM chat_user WHERE user_id IN (SELECT first_user FROM user_relation WHERE second_user = %s AND relation_type = 'friends' UNION SELECT second_user FROM user_relation WHERE first_user = %s AND relation_type = 'friends')"
return self.conn.query(query, query_data=tuple([current_user[0]] * 2))
def invite_friend(self, nickname, friend_nickname):
uDAO = UserDAO()
friend_found = uDAO.select_by_nickname(friend_nickname)
current_user = uDAO.select_by_nickname(nickname)
relation_exists = self.select_relation_of_users(
(friend_found[0], current_user[0]))
if (len(relation_exists) > 0):
return False
if (friend_found != None and current_user != None):
res = self.insert((friend_found[0], current_user[0], 'friends'))
if (res == 1):
return (friend_found[0], friend_found[1], friend_found[4])
else:
return False
else:
return False
class ConversationDAO:
def __init__(self):
self.conn = CryptoDatabase()
def insert(self,conversation_data:tuple):
query = "INSERT INTO conversation (user_one,user_two) VALUES (%s,%s)"
data = tuple(sorted([conversation_data[0],conversation_data[1]]))
self.conn.execute(query,query_data=data)
result = self.conn.query("SELECT * FROM conversation WHERE user_one = %s AND user_two = %s",(data[0],data[1]))
self.conn.commit()
return len(result)
def delete(self,users:tuple):
query = "DELETE FROM conversation WHERE user_one = %s AND user_two = %s"
data = tuple(sorted(users[0],users[1]))
self.conn.execute(query,data)
self.conn.commit()
def select_by_users(self,users):
query = "SELECT * FROM conversation WHERE user_one = %s AND user_two = %s"
data = tuple(sorted([users[0],users[1]]))
res = self.conn.query(query,query_data=data)
if(len(res) > 0):
return res[0]
else:
return None
def select_by_single_user(self,user_id):
query = "SELECT * FROM conversation WHERE user_one = %s OR user_two = %s"
return self.conn.query(query,query_data=(user_id,user_id))
class MessageDAO:
def __init__(self):
self.conn = CryptoDatabase()
def insert(self, message_data: tuple):
query = "INSERT INTO message (message_data,date_sent,exp_date,receiving_user,was_received,conversation_id) VALUES (%s,%s,%s,%s,%s,%s)"
self.conn.execute(query, query_data=message_data)
result = self.conn.query(
"SELECT * FROM message WHERE message_data = %s AND date_sent = %s AND exp_date = %s AND receiving_user = %s AND was_received = %s AND conversation_id = %s",
message_data)
self.conn.commit()
return len(result)
def update(self, message_id):
query = "UPDATE message SET was_received = %s WHERE message_id = %s"
self.conn.execute(query, query_data=('1', message_id))
result = self.conn.query(
"SELECT * FROM message WHERE was_received = %s AND message_id = %s",
('1', message_id))
self.conn.commit()
return len(result)
def delete(self, message_id):
query = "DELETE FROM message WHERE message_id = %s"
self.conn.execute(query, (message_id, ))
self.conn.commit()
def select_by_id(self, message_id):
query = "SELECT * FROM message WHERE message_id = %s"
res = self.conn.query(query, query_data=(message_id, ))
if (len(res) > 0):
return res[0]
else:
return None
def check_unread_messages_by_user(self, user_id):
query = "SELECT user_one FROM conversation WHERE user_two = %s AND conversation_id IN (SELECT conversation_id FROM message WHERE receiving_user = %s AND was_received = '0') UNION SELECT user_two FROM conversation WHERE user_one = %s AND conversation_id IN (SELECT conversation_id FROM message WHERE receiving_user = %s AND was_received = '0')"
data = tuple([user_id] * 4)
return self.conn.query(query, query_data=data)
def fetch_unread_messages_by_users(self, receiving_user, other_user):
uDAO = UserDAO()
users_ids = []
res = uDAO.select_by_nickname(other_user)
if (res != None):
users_ids.append(res[0])
else:
return False
res = uDAO.select_by_nickname(receiving_user)
if (res != None):
users_ids.append(res[0])
else:
return False
query = "SELECT * FROM message WHERE conversation_id IN (SELECT conversation_id FROM conversation WHERE user_one = %s AND user_two = %s) AND was_received = '0' AND receiving_user = %s"
data = tuple(sorted([users_ids[0], users_ids[1]]) + [users_ids[1]])
return self.conn.query(query, query_data=data)