def change_parent(node, new_parent):
with AbstractDatabaseConnection('amazingco.db') as conn:
# archiving the old parent-descendant pair
sql_statement = "UPDATE node SET is_current = 0 WHERE name = '{}' AND is_current = 1;".format(
node)
cursor = conn.cursor()
cursor.execute(sql_statement)
# gather info about new parent node (root node, height)
new_parent_statement = "SELECT root_name, height FROM node WHERE name = '{}'".format(
new_parent)
cursor = conn.cursor()
qresult = cursor.execute(new_parent_statement)
if (qresult):
for r in cursor.fetchall():
root_node = r[0]
new_height = int(r[1]) + 1
# insert new parent-descentdant node pair
insert_statement = "INSERT INTO node(name, parent_name, root_name, height) VALUES ('{}', '{}', '{}', {})".format(
node, new_parent, root_node, new_height)
cursor = conn.cursor()
cursor.execute(insert_statement)
conn.commit()
return True
else:
return False
def session():
"""
Checks for a valid session.
If the session is valid, a flag is returned.
Otherwise, return a 404.
Parameters: None
Expects a session token set as a cookie.
"""
with AbstractDatabaseConnection('login.db') as conn:
cursor = conn.cursor()
session = request.cookies.get('session')
if (session == None):
return build_result([], 403)
# Checks to see if this is a valid session
cursor.execute("""
SELECT *
FROM session as s
WHERE s.session = '%s'
""" % (session))
# If the session is valid, then keep going.
# Otherwise, exit.
if (len(cursor.fetchall()) == 0):
return build_result("False :(", 400)
return build_result("flg{sqli_is_lit!}", 200)
def delete_tables():
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
cursor.execute("""DROP TABLE IF EXISTS author""")
cursor.execute("""DROP TABLE IF EXISTS books""")
cursor.execute("""DROP TABLE IF EXISTS library""")
cursor.execute("""DROP TABLE IF EXISTS secret""")
conn.commit()
def seed():
"""
Insert sample data to tables in the database.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
for ins in insert_statements:
cursor.execute(insert_statements[ins])
conn.commit()
def create_tables():
"""
Creates all tables in the database.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
for cs in create_statements:
cursor.execute(create_statements[cs])
conn.commit()
def get_library():
"""
Gets all library information and further information
Returns:
Response message with HTTP code.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM library where book_id = 1")
result = cursor.fetchall()
return build_result(result, 200)
def get_books():
"""
Gets all books and further information
Returns:
Response message with HTTP code.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM books")
# Build into a comma delimited string.
result = cursor.fetchall()
return build_result(result, 200)
def login_call():
"""
Does the login process
If valid login, a cookie is set for the session
Otherwise,
Parameters: Username, Password
Returns:
Response message with HTTP code.
"""
with AbstractDatabaseConnection('login.db') as conn:
cursor = conn.cursor()
username = request.args.get('username')
password = request.args.get('password')
# Check for the login in a secure way :)
# This is where the vulnerability is at.
query = """
SELECT *
FROM login
WHERE username = '******'
AND password = '******';
""" % (username, password)
# Catches all errors and prints the query out.
try:
cursor.execute(query)
except:
return build_result("Error...Query" + query.replace('\n', ''),
400), 400
# Handles the authorization; correct versus incorrect.
result = cursor.fetchall()
if (len(result) == 0):
return build_result("Wrong!...Query: " + query, 403), 403
else:
# Get a safe and secure token for the session
session = secrets.token_urlsafe(120)
# Set the session
cursor.execute("""
INSERT INTO session(session)
VALUES ('%s')
""" % session)
conn.commit()
return build_result("flg{sqli_is_super_fun!}", 200), 200
def books_by_author():
"""
Gets all the books by a given author.
Takes a parameter: "name" as input
Returns:
Response message with HTTP code.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT b.title
FROM author as a, books as b
WHERE a.author_id = b.author_id AND a.name = '%s'
""" % request.args.get('name'))
result = cursor.fetchall()
return build_result(result, 200)
def get_descendant(parent_node):
"""
Get a row from the specified table with given row id.
Args:
table_name: a valid table in the database.
row_id: a valid row of said table.
Returns:
A row from the database.
"""
with AbstractDatabaseConnection('amazingco.db') as conn:
column = "parent_name"
sql_statement = "SELECT name FROM node WHERE {} = '{}' AND is_current = 1;".format(
column, parent_node)
cursor = conn.cursor()
rows_count = cursor.execute(sql_statement)
if (rows_count):
result = ', '.join([r[0] for r in cursor.fetchall()])
return result
else:
return None
def checkin_book():
"""
Given a books serial number, check out the book.
Takes a parameter: "book_id" as input
Returns:
Response message with HTTP code.
"""
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
cursor.execute("""
UPDATE library
SET
checked_out = 0
WHERE
book_id = %s
""" % request.args.get('book_id'))
# Build into a comma delimited string.
conn.commit()
result = cursor.fetchall()
return build_result(result, 200)
def test():
with AbstractDatabaseConnection('library.db') as conn:
cursor = conn.cursor()
conn.commit()
cursor.execute("""UPDATE library SET checked_out = 1 WHERE book_id = 1;""")
def delete_tables():
with AbstractDatabaseConnection('login.db') as conn:
cursor = conn.cursor()
cursor.execute("""DROP TABLE login""")
conn.commit()
