def tryLogin(self, username, passwd):
global db_instance
username = username
with Database('users.db') as db:
rows = db.getUserInfo(username)
if rows is not None:
salt = rows[1]
cipher = rows[2]
encrypted = rows[3]
hash = argon2.low_level.hash_secret(
str.encode(passwd),
salt,
time_cost=500,
memory_cost=1024,
parallelism=2,
hash_len=64,
type=argon2.low_level.Type.ID)
derivated_key = hash.split(b'$')[-1][:32]
if DEBUG:
print(f"DEBUG: Clave derivada es {derivated_key}")
db_instance = cryptoHandler.db_wrapper(username, derivated_key,
cipher, encrypted)
if db_instance.valid:
return True
return False
def createUser(self, name, username, passwd, cipher):
global db_instance
username = username
if self.existUser(username):
return 1
if not self.isValidUsername(username):
return 2
if not self.isStrongPasswd(passwd):
return 3
# Generate argon2 hash
salt = os.urandom(16)
hash = argon2.low_level.hash_secret(str.encode(passwd),
salt,
time_cost=500,
memory_cost=1024,
parallelism=2,
hash_len=64,
type=argon2.low_level.Type.ID)
derivated_key = hash.split(b'$')[-1][:32]
if DEBUG:
print(f"DEBUG: Clave derivada es {derivated_key}")
with Database('users.db') as db:
db.addUser(name, username, salt, cipher, "")
db_instance = cryptoHandler.db_wrapper(username, derivated_key, cipher,
"")
return 0
def existUser(self, user_name):
user_name = user_name
with Database('users.db') as db:
rows = db.getUserInfo(user_name)
if rows:
return True
return False
def getUserName(self, user_name):
user_name = user_name
with Database('users.db') as db:
rows = db.getUserInfo(user_name)
if rows:
return rows[0]
return None
def debug_info(self):
with Database('users.db') as db:
rows = db.getUserInfo(self.username)
encrypted = rows[3]
length = len(encrypted)
if self.cipher == 0:
ciphertext = encrypted[:length - 28]
# tag es de 12 bytes
iv = encrypted[length - 28:length - 16]
# tag es de 16 bytes
tag = encrypted[length - 16:]
print(
f"\nArchivo encriptado:\n{encrypted}\n\nBase de dato encriptada:\n{ciphertext}\n\niv:\n{iv}\n\ntag:\n{tag}\n\n"
)
else:
ciphertext = encrypted[:length - 12]
nonce = encrypted[length - 12:]
print(
f"\nArchivo encriptado:\n{encrypted}\n\nBase de dato encriptada:\n{ciphertext}\n\nNonce:\n{nonce}\n\n"
)
def encript_and_save(self):
database = "\n".join(self.conn.iterdump())
database = str.encode(database)
# 0 = AES, 1= ChaCha20
if self.cipher == 0:
iv, ciphertext, tag = self.encryptAES(database,
str.encode(self.username))
# Store encrypted db and append IV + tag
with Database('users.db') as db:
db.updateEncrypted(self.username, ciphertext + iv + tag)
else:
nonce, ciphertext = self.encryptChaCha20(database,
str.encode(self.username))
# Store encrypted db and append nonce
with Database('users.db') as db:
db.updateEncrypted(self.username, ciphertext + nonce)
def register_votes(self, votes):
"""
Insert votes into VOTES table
:param votes: list of voted_snack
"""
query_string = "INSERT INTO tb_votes(vote_date, user_email, voted_snack) VALUES(?,?,?)"
# Dates saved are in year-mm-dd format
vote_date = date.today().strftime("%Y-%m-%d")
with Database(Models.DB_FILE) as db:
for v in votes:
db.cursor.execute(query_string, (vote_date, self.user_email, v))
def suggest(self, snack):
"""
Suggest a snack to be voted on, and record the date of suggestion
:param snack: string snack name
"""
query_string = "INSERT INTO tb_suggestion(suggestion_date, user_email, suggested_snack) VALUES(?,?,?)"
# Dates saved are in year-mm-dd format
suggest_date = date.today().strftime("%Y-%m-%d")
with Database(Models.DB_FILE) as db:
db.cursor.execute(query_string, (suggest_date, self.user_email, snack))
def create_tables():
create_tb_votes = '''
CREATE TABLE IF NOT EXISTS tb_votes (
vote_id INTEGER PRIMARY KEY,
vote_date TEXT,
user_email TEXT,
voted_snack TEXT
);'''
create_tb_suggestion = '''
CREATE TABLE IF NOT EXISTS tb_suggestion (
suggestion_id INTEGER PRIMARY KEY,
suggestion_date TEXT,
user_email TEXT,
suggested_snack TEXT
);'''
with Database(Models.DB_FILE) as db:
db.cursor.execute(create_tb_votes)
db.cursor.execute(create_tb_suggestion)
def get_tally(year, month):
"""
get tally of votes for a specific month
:param year: integer year eg. 2018
:param month: integer month eg. 2
:returns: dictionary for the snacks vote tally {"snack name": 4, ...} or {}
"""
# Convert the saved dates to month format year-mm
query_string = '''
SELECT voted_snack, COUNT(vote_id) as tally
FROM tb_votes
WHERE strftime('%Y-%m', vote_date) = :month
GROUP BY (voted_snack);
'''
vote_month = "{}-{:02d}".format(year, month)
with Database(Models.DB_FILE) as db:
db.cursor.execute(query_string, {"month": vote_month})
rows = db.cursor.fetchall()
return dict(rows)
def get_last_suggest_date(self):
"""
Get the last date a user made a suggestion to web service
:returns: datetime string (Year-mm) when this user last made suggestion or None
"""
query_string = '''
SELECT strftime('%Y-%m', suggestion_date)
FROM tb_suggestion
WHERE user_email = :email
ORDER BY suggestion_date DESC
LIMIT 1;
'''
with Database(Models.DB_FILE) as db:
db.cursor.execute(query_string, {"email": self.user_email})
row = db.cursor.fetchone()
if row:
return row[0]
else:
return None
def get_suggestion(self, year, month):
"""
Get a list of snacks suggested for a month
:param year: integer year eg. 2018
:param month: integer month eg. 2
:returns: list of snacks [name,..] or []
"""
query_string = '''
SELECT DISTINCT(suggested_snack)
FROM tb_suggestion
WHERE strftime('%Y-%m', suggestion_date) = :month
'''
# year-mm format
suggest_month = "{}-{:02d}".format(year, month)
with Database(Models.DB_FILE) as db:
db.cursor.execute(query_string, {"month": suggest_month})
rows = db.cursor.fetchall()
if rows:
return [s[0] for s in rows]
else:
return rows
def get_allowed_votes(self):
"""
Get allowed votes for a specific user for the current month
:returns: int allowed votes left for this user or 0
"""
max_vote_per_month = 3
# Month format year-mm
query_string = '''
SELECT COUNT(voted_snack) as voted_times
FROM tb_votes
WHERE strftime('%Y-%m', vote_date) = :month AND user_email = :email
'''
current_month = date.today().strftime("%Y-%m")
with Database(Models.DB_FILE) as db:
db.cursor.execute(query_string, {"month": current_month, "email": self.user_email})
row = db.cursor.fetchone()
if row:
voted_times = row[0]
allowed_votes = max(0, max_vote_per_month - voted_times)
return allowed_votes
else:
return 0
from flask import Flask, render_template,current_app
from flask import request
import pymysql
from dbConnection import Database
app = Flask(__name__)
db = Database()
# @app.route('/')
# def home():
# return app.send_static_file('templates/Covax/about.html')
def db_query(sql):
rs = db.get_from_table(sql)
return rs
#home page
@app.route('/')
def home():
return render_template('index.html')
@app.route('/home')
def home1():
return render_template('index.html')
#about-us page
@app.route('/about')
def about():
return render_template('about1.html')