def gen_complete_user(name, password, mail, first_name, last_name, adress,
role):
"""
Generiere einen User in der Datenbank mit allen einfügbaren Daten die es gibt
:param name:
:param password:
:param mail:
:param first_name:
:param last_name:
:param adress:
:param role:
:return: id des generierten Nutzers
"""
chars = string.ascii_letters + string.digits
size = 16
salt = ''.join((random.choice(chars)) for x in range(size))
pw_hash = sha256_crypt.encrypt(password + salt)
pwmd5 = get_md5_bytes(password)
cursor = get_cursor()
cursor.execute(
'INSERT INTO user (name, password, salt, secure_id, pw_md5, role, mail, first_name, last_name, adress) '
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [
name.lower(), pw_hash, salt, salt, pwmd5, role, mail, first_name,
last_name, adress
])
return cursor.lastrowid
def create_cart_table(dictcart):
"""
Generiere die Einkaufswagentabelle und füge eine "Gesamt" Zeile am ende ein
:param dictcart:
:return: cart_array
"""
cursor = get_cursor()
result = []
i = 1
gesamtpreis = 0
for key, value in dictcart.items():
cursor.execute("SELECT name, price FROM items WHERE id = ?", [key])
row = cursor.fetchall()
row[0] = list(row[0])
row[0].insert(0, i)
row[0].insert(2, value)
row[0].insert(4, value * row[0][3])
result.extend(row)
i = i + 1
gesamtpreis += row[0][4]
row = []
row.insert(0, [])
row[0].insert(0, i)
row[0].insert(1, "GESAMT")
row[0].insert(2, 1)
row[0].insert(3, gesamtpreis)
row[0].insert(4, gesamtpreis)
result.extend(row)
return result
def save_profile(form, id):
"""
Speichere Nutzerprofil in Datenbank ab
:param form:
:param id:
:return: None
"""
if app.config['user_id_handling'] == 'insecure':
columname = 'insecure_id'
else:
columname = 'secure_id'
cursor = get_cursor()
cursor.execute(
"UPDATE user SET "
"name = ?,"
"mail = ?,"
"first_name = ?,"
"last_name = ?,"
"adress = ?"
f"WHERE {columname} = ?", [
form.username.data, form.mail.data, form.first_name.data,
form.last_name.data, form.adress.data, id
])
def insecure__check_pw_secure_id(id, pw):
"""
Unsicherer md5-byte passwort vergleich aus der DB anhand sicherer ID mit unsicherem Passwort
Sicherheitsstufe (security) erhöht sich wenn ein passwordcheck geschafft wird und bleibt gleich sich wenn nicht geschafft werden
security 0 = kein check geschafft --> falsches passwort
security 1 = nur weiter check geschafft --> sql injection
security 2 = beide checks geschafft --> richtiges passwort
:param id:
:param pw:
:return: 0 || 1 || 2
"""
security = 0
cursor = get_cursor()
compare = get_md5_bytes(pw)
sqlstring = """SELECT insecure_id from user WHERE secure_id = '""" + id + """' AND pw_md5 = '""" + compare + """'"""
cursor.execute(sqlstring)
try:
cursor.fetchall()[0]
except IndexError as e:
security = -1
security = security + 1
cursor.execute(
'SELECT insecure_id from user where secure_id = ? AND pw_md5 = ?',
[id, compare])
try:
cursor.fetchall()[0]
except IndexError as e:
security = security - 1
return security + 1
def ctf_admin_delete_user(secure_id):
"""
Lösche user für gegebener sicheren ID (admin delete)
:param secure_id:
:return: redirect referrer
"""
cursor = get_cursor()
cursor.execute('DELETE FROM user WHERE secure_id = ?', [secure_id])
return redirect(request.referrer)
def remove_itemtype_flag():
"""
Entferne Versteckte Flagge aus shoptabelle
:return: None
"""
admincursor = get_admin_cursor()
admincursor.execute('SELECT flag FROM flag WHERE id = 1')
hideflag = admincursor.fetchall()[0][0]
cursor = get_cursor()
cursor.execute('DELETE FROM flag WHERE flag = ?', [hideflag])
def hide_itemtype_flag():
"""
Verstecke Flagge in shoptabelle um sie für UNION SELECT sichtbar zu machen
:return: None
"""
admincursor = get_admin_cursor()
admincursor.execute('SELECT flag FROM flag WHERE id = 1')
hideflag = admincursor.fetchall()[0][0]
cursor = get_cursor()
cursor.execute('INSERT INTO flag (flag) VALUES (?)', [hideflag])
def insecure__get_item_by_type(itemtype):
"""
Hole Waren für übergebenen Typen von Datenbank (unsicher)
:param itemtype:
:return: [i][id, name, filename, price]
"""
cursor = get_cursor()
cursor.execute(
"SELECT id, name, filename, price FROM items where type like '" +
itemtype + "';")
result = cursor.fetchall()
return result
def secure__get_item_by_type(itemtype):
"""
Hole Waren für übergebenen Typen von Datenbank (sicher)
:param itemtype:
:return: [i][id, name, filename, price]
"""
cursor = get_cursor()
cursor.execute(
"SELECT id, name, filename, price FROM items WHERE type = ?;",
[itemtype])
result = cursor.fetchall()
return result
def get_user_instance(cls, id):
"""
Fülle User Konstruktor aus DB auf anhand gegebener unsicherer oder sicherer ID
:param id:
:return: Initialisierter Nutzer
"""
if app.config['user_id_handling'] == 'insecure':
cursor = get_cursor()
cursor.execute(
'SELECT name, first_name, last_name, adress, mail, role, insecure_id, secure_id FROM user WHERE insecure_id = ?',
[id])
else:
cursor = get_cursor()
cursor.execute(
'SELECT name, first_name, last_name, adress, mail, role, insecure_id, secure_id FROM user WHERE secure_id = ?',
[id])
result = []
try:
result = cursor.fetchall()[0]
except IndexError as e:
return None
name = result[0]
firstname = result[1]
lastname = result[2]
adress = result[3]
mail = result[4]
role = result[5]
insecure_id = result[6]
secure_id = result[7]
return User(id=id,
name=name,
firstname=firstname,
lastname=lastname,
adress=adress,
mail=mail,
role=role,
insecure_id=insecure_id,
secure_id=secure_id)
def secure__get_id_for_name(name):
"""
Hole sichere random ID für gegebenen namen aus db
:param name:
:return: secure_id
"""
cursor = get_cursor()
cursor.execute('SELECT secure_id FROM user WHERE name = ?', [name.lower()])
try:
secureid = cursor.fetchall()[0][0]
except IndexError as e:
print(e.__str__())
return -1
return secureid
def get_secure_id_for_insecure_id(id):
"""
Hole für gegebene unsichere User-ID die dazu gehörige sichere User-ID aus DB
:param id:
:return: secure_id
"""
cursor = get_cursor()
cursor.execute('SELECT secure_id FROM user WHERE insecure_id = ?', [id])
result = cursor.fetchall()
try:
return result[0][0]
except IndexError as e:
print(e.with_traceback())
return -1
def save_pw(pw, id):
"""
Speichere übergebenes Passwort in allen benötigten Formaten in Datenbank ab
:param pw:
:param id:
:return: None
"""
chars = string.ascii_letters + string.digits
size = 16
salt = ''.join((random.choice(chars)) for x in range(size))
pw_hash = sha256_crypt.encrypt(pw + salt)
pwmd5 = get_md5_bytes(pw)
cursor = get_cursor()
cursor.execute(
'UPDATE user SET password = ?, salt = ?, pw_md5 = ? WHERE secure_id = ?',
[pw_hash, salt, pwmd5, id])
def secure__check_pw_secure_id(id, pw):
"""
Sicherer SHA256 passwort vergleich aus der DB anhand sicherer ID
:param id:
:param pw:
:return: True || False
"""
cursor = get_cursor()
cursor.execute('SELECT password, salt FROM user WHERE secure_id = ?', [id])
result = cursor.fetchall()
try:
pw_hash = result[0][0]
pw_salt = result[0][1]
except IndexError as e:
return False
return sha256_crypt.verify(pw + pw_salt, pw_hash)
def gen_user(name, passwd):
"""
Generiere einen User in der Datenbank nur mit Name und Passwort
:param name:
:param passwd:
:return: id des generierten nutzers
"""
chars = string.ascii_letters + string.digits
size = 16
salt = ''.join((random.choice(chars)) for x in range(size))
pw_hash = sha256_crypt.encrypt(passwd + salt)
pwmd5 = get_md5_bytes(passwd)
cursor = get_cursor()
cursor.execute(
'INSERT INTO user (name, password, salt, secure_id, pw_md5, role) VALUES (?, ?, ?, ?, ?, ?)',
[name.lower(), pw_hash, salt, salt, pwmd5, 'user'])
return cursor.lastrowid
def ctf_admin_panel():
"""
Falls GET, Zeige Adminpanel für Modi und Nutzerverwaltung
Falls POST, Erstelle neuen User für gegebene Informationen (Rolle anpassbar, nicht wie bei Register)
:return: redirect ctf/admin || redirect index
"""
if current_user.role == 'admin':
form = CompleteUserForm(request.form)
if request.method == 'GET':
data = get_cursor().execute(
"select insecure_id, name, mail, first_name, last_name, adress, secure_id, role from user"
).fetchall()
return render_template('ctf/admin.html', form=form, data=data)
if request.method == 'POST':
gen_complete_user(form.username.data, form.password.data,
form.mail.data, form.first_name.data,
form.last_name.data, form.adress.data,
form.role.data)
return redirect('/ctf/admin')
else:
return redirect(url_for('index'))
