def update_login_log(user_id, counter):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE login_logs SET counter = %s WHERE user_id = %s'
cursor.execute(sql, (counter, user_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def update_verification(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_otp SET verified = 1 WHERE user_id = %s'
cursor.execute(sql, (user_id,))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def update_file_key(key_id, user_id, file_id, key_path):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_key SET key_path = %s WHERE id = %s AND user_id = %s AND file_id = %s'
cursor.execute(sql, (key_path, key_id, user_id, file_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def set_is_send(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE login_logs SET is_send = 1 WHERE user_id = %s'
cursor.execute(sql, (user_id, ))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def delete(user_id, reset_case):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM user_tokens WHERE user_id = %s AND reset_case = %s'
cursor.execute(sql, (user_id, reset_case))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def set_is_active(user_id, device_id, activate_value):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_device SET device_is_active = %s WHERE user_id = %s AND id = %s'
cursor.execute(sql, (activate_value, user_id, device_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def set_second_factor_option(user_id, settings_id, setting_value):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_setting SET setting_value = %s WHERE user_id = %s AND settings_id = %s'
cursor.execute(sql, (setting_value, user_id, settings_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def delete_all_from_user(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM user_otp_used WHERE user_id = %s LIMIT 9900'
cursor.execute(sql, (user_id,))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def deactivate_all(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_device SET device_is_active = 0 WHERE user_id = %s'
cursor.execute(sql, (user_id, ))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def insert(user_id):
db = DBconnector.connect()
ts = int(time.time())
try:
with db.cursor() as cursor:
sql = 'INSERT INTO login_logs (user_id, counter, timestamp) VALUES (%s, %s, %s)'
cursor.execute(sql, (user_id, 0, ts))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def update_file(user_id, file_id, file_name, file_description):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE user_data SET file_name = %s, file_description = %s WHERE id = %s AND user_id = %s'
cursor.execute(sql,
(file_name, file_description, file_id, user_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def get_login_log(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT * FROM login_logs WHERE user_id = %s'
cursor.execute(sql, (user_id, ))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return result
def get_auth_token(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT token, session_id FROM auth_tokens WHERE user_id = %s'
cursor.execute(sql, (user_id,))
db.commit()
results = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return results
def all_auth_token():
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT token FROM auth_tokens'
cursor.execute(sql)
db.commit()
results = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return results
def get(user_id, reset_case):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT token FROM user_tokens WHERE user_id = %s AND reset_case = %s'
cursor.execute(sql, (user_id, reset_case))
db.commit()
results = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return results
def insert_auth_token(user_id, token, session_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM auth_tokens WHERE user_id = %s'
cursor.execute(sql, (user_id,))
sql = 'INSERT INTO auth_tokens (user_id, token, session_id) VALUES (%s, %s, %s)'
cursor.execute(sql, (user_id, token, session_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def check_verification(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT verified FROM user_otp WHERE user_id = %s'
cursor.execute(sql, (user_id,))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
db_otp_verified = result[0]['verified']
return db_otp_verified
def delete_file(file_id, user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM user_data WHERE id = %s AND user_id = %s'
cursor.execute(sql, (file_id, user_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
return 'Something went wrong'
else:
return 'Successful file deleted'
finally:
db.close()
def insert(user_id, token, reset_case):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
if reset_case == 1:
sql = 'DELETE FROM user_tokens WHERE user_id = %s AND reset_case = %s'
cursor.execute(sql, (user_id, reset_case))
sql = 'INSERT INTO user_tokens (user_id, token, reset_case) VALUES (%s, %s, %s)'
cursor.execute(sql, (user_id, token, reset_case))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def get_files(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT id, file_name, file_description, is_encrypted FROM user_data WHERE user_id = %s'
cursor.execute(sql, (user_id, ))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return result
def delete_login_log(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM login_logs WHERE user_id = %s'
cursor.execute(sql, (user_id, ))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
return 'Something went wrong'
else:
return 'Successful login_logs deleted'
finally:
db.close()
def get_file(file_id, user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT * FROM user_data WHERE id = %s AND user_id = %s'
cursor.execute(sql, (file_id, user_id))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return result
def update_email(user_id, email):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'UPDATE users SET email = %s WHERE id = %s'
cursor.execute(sql, (email, user_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
return 'There was an error while updating the email-address \n'
else:
return 'Successfully updated email \n'
finally:
db.close()
def get_user_id(email):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT id FROM users WHERE email = %s'
cursor.execute(sql, (email, ))
db.commit()
results = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return results
def get_active_devices_by_user_id(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT * FROM user_device WHERE user_id = %s AND device_is_active = 1'
cursor.execute(sql, (user_id, ))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return result
def delete(device_id, user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'DELETE FROM user_device WHERE id = %s AND user_id = %s'
cursor.execute(sql, (device_id, user_id))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
return result
def insert(user_id, device_id, device_name):
db = DBconnector.connect()
db_connection_state = 'pending'
try:
with db.cursor() as cursor:
sql = 'INSERT INTO user_device (user_id, device_id, device_name) VALUES (%s, %s, %s)'
cursor.execute(sql, (user_id, device_id, device_name))
db.commit()
db_connection_state = 'success'
except pymysql.MySQLError as e:
logging.error(e)
db_connection_state = 'failed'
finally:
db.close()
return db_connection_state
def update_password(user_id, password):
db = DBconnector.connect()
hashed_password = HashHandler.hash_string(password, 'sha512', 10000)
try:
with db.cursor() as cursor:
sql = 'UPDATE users SET password = %s WHERE id = %s'
cursor.execute(sql, (hashed_password, user_id))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
return 'There was an error while updating the password'
else:
return 'Successfully updated password'
finally:
db.close()
def insert(user_id, otp):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
ts = int(time.time())
sql = 'DELETE FROM user_otp WHERE user_id = %s'
cursor.execute(sql, (user_id,))
sql = 'INSERT INTO user_otp (user_id, current_otp, timestamp, verified) VALUES (%s, %s, %s, %s)'
cursor.execute(sql, (user_id, otp, ts, 0))
sql = 'INSERT INTO user_otp_used (user_id, used_otp, timestamp) VALUES (%s, %s, %s)'
cursor.execute(sql, (user_id, otp, ts))
db.commit()
except pymysql.MySQLError as e:
logging.error(e)
finally:
db.close()
def get_used(user_id):
db = DBconnector.connect()
try:
with db.cursor() as cursor:
sql = 'SELECT used_otp, timestamp FROM user_otp_used WHERE user_id = %s'
cursor.execute(sql, (user_id,))
db.commit()
result = cursor.fetchall()
except pymysql.MySQLError as e:
logging.error(e)
else:
if len(result) >= 10000:
DBotp.delete_all_from_user(user_id)
return result
finally:
db.close()
