def load(name, skip_auth=False):
"""Loads the paste text for given name
:param name: name associated with the paste text
:type name: str
:param skip_auth: a flag to skip the authentication for the user;
``False`` by default
:type skip_auth: bool
:return: a string representing the paste text for this user
"""
out = None
with connect_db() as db:
data = db(
"SELECT data FROM pastes WHERE name=%s AND private=FALSE",
[name],
).fetchone()
if data:
out = data[0]
if out is None:
if not skip_auth and not is_staff("cs61a"):
return login()
with connect_db() as db:
data = db(
"SELECT data FROM pastes WHERE name=%s",
[name],
).fetchone()
if data:
out = data[0]
if out is None:
abort(404)
elif isinstance(out, bytes):
return out.decode("utf-8")
else:
return out
def db_lock(active_db, username):
try:
with connect_db() as db:
locked = db(f"SELECT locked FROM {active_db} WHERE username=%s",
[username]).fetchone()
if locked is None:
# environment does not exist
db(
f"INSERT INTO {active_db} (username, initialized, locked) VALUES (%s, FALSE, TRUE)",
[username],
)
yield
else:
[locked] = locked
if locked:
# TODO: Some way to force an unlock from the CLI
raise BlockingIOError(
f"Another operation is currently taking place on {active_db}"
)
else:
db(
f"UPDATE {active_db} SET locked=TRUE WHERE username=%s",
[username],
)
yield
finally:
with connect_db() as db:
db(f"UPDATE {active_db} SET locked=FALSE WHERE username=%s",
[username])
def validate(data, timeout):
for participation in data["participations"]:
if participation["course"]["offering"] == get_endpoint("cs61a"):
break
else:
abort(
401,
"You are not enrolled in CS 61A, and so are not authorized to submit."
)
email = data["email"]
with connect_db() as db:
ret = db("SELECT last_access FROM accesses WHERE email=(%s)",
[email]).fetchone()
now = int(time.time())
if ret and now - ret[0] < timeout:
abort(
429,
"You have made many requests in a short amount of time. Please wait a bit and try again.",
)
with connect_db() as db:
db("DELETE FROM accesses WHERE email=(%s)", [email])
db("INSERT INTO accesses VALUES (%s, %s)", [email, now])
def submitRegradeRequest():
if not is_logged_in():
return dict(success=False)
if request.method == "GET":
return dict(success=False)
email = request.get_json().get("email")
assignment = request.get_json().get("assignment")
with connect_db() as db:
status = db(
"SELECT status FROM regrade_requests WHERE courseCode=%s AND email=%s AND assignment=%s",
[get_course(), email, assignment],
).fetchone()
if status:
status = status[0]
if status and status not in ("needs followup"):
return dict(success=False)
backup_id = request.get_json().get("backup_id")
description = request.get_json().get("description")
ta = request.form.get("ta")
status = "requested"
with connect_db() as db:
db(
"""INSERT INTO regrade_requests (
courseCode, email, assignment, backup_id, description, assigned_to, status
) VALUES (%s, %s, %s, %s, %s, %s, %s)""",
[
get_course(), email, assignment, backup_id, description,
ta, status
],
)
return dict(success=True)
def load(name, skip_auth=False):
out = None
with connect_db() as db:
data = db(
"SELECT data FROM pastes WHERE name=%s AND private=FALSE",
[name],
).fetchone()
if data:
out = data[0]
if out is None:
if not skip_auth and not is_staff("cs61a"):
return login()
with connect_db() as db:
data = db(
"SELECT data FROM pastes WHERE name=%s",
[name],
).fetchone()
if data:
out = data[0]
if out is None:
abort(404)
elif isinstance(out, bytes):
return out.decode("utf-8")
else:
return out
def refresh():
with connect_db() as db:
db("DELETE FROM shortlinks WHERE course=%s", [get_course()])
sheets = db(
"SELECT url, sheet, secure FROM sources WHERE course=(%s)", [get_course()]
).fetchall()
data = []
for url, sheet, secure in sheets:
try:
csvr = read_spreadsheet(url=url, sheet_name=sheet)
except:
return error(f"Failed to read spreadsheet {url} (Sheet: {sheet})")
headers = [x.lower() for x in csvr[0]]
for row in csvr[1:]:
row = row + [""] * 5
shortlink = row[headers.index("shortlink")]
url = row[headers.index("url")]
creator = row[headers.index("creator")]
data.append([shortlink, url, creator, secure, get_course()])
with connect_db() as db:
db(
"INSERT INTO shortlinks (shortlink, url, creator, secure, course) VALUES (%s, %s, %s, %s, %s)",
data,
)
return html("Links updated")
def add_admin(course):
email = request.form["email"]
with connect_db() as db:
check = db(
"SELECT * FROM course_admins WHERE email=(%s) AND course=(%s)",
[email, course],
).fetchone()
if check:
return error("User is already an admin"), 409
with connect_db() as db:
db(
"INSERT INTO course_admins VALUES (%s, %s, %s, %s)",
[email, "Unknown", course,
get_name()],
)
# make sure that you can't accidentally lock yourself out
with connect_db() as db:
check = db(
"SELECT * FROM course_admins WHERE email=(%s) AND course=(%s)",
[get_email(), course],
).fetchone()
if not check:
db(
"INSERT INTO course_admins VALUES (%s, %s, %s, %s)",
[get_email(), get_name(), course,
get_name()],
)
return redirect(url_for("index"))
def query():
try:
if is_logged_in():
user = get_user()
email = user["email"]
target = request.args.get("target", None)
if is_staff(get_course()):
if target:
email = target
else:
all_students = []
with connect_db() as db:
lookup = db(
"SELECT shortData FROM students WHERE courseCode=%s",
[get_course()],
).fetchall()
for row in lookup:
parsed = json.loads(row[0])
all_students.append(parsed)
return jsonify({
"success": True,
"isStaff": True,
"allStudents": all_students,
"email": user["email"],
"name": user["name"],
"lastUpdated": last_updated(),
})
with connect_db() as db:
[short_data, data] = db(
"SELECT shortData, data FROM students WHERE courseCode=%s AND email=%s",
[get_course(), email],
).fetchone()
[header
] = db("SELECT header FROM headers WHERE courseCode=%s",
[get_course()]).fetchone()
short_data = json.loads(short_data)
data = json.loads(data)
header = json.loads(header)
return jsonify({
"success": True,
"header": header,
"data": data,
"email": short_data["Email"],
"name": short_data["Name"],
"SID": short_data["SID"],
"lastUpdated": last_updated(),
})
else:
return jsonify({"success": False, "retry": True})
except Exception:
pass
return jsonify({"success": False, "retry": False})
def add_domain(app, is_staging, course, domain):
try:
if app != "auth":
abort(401)
app = APP_LOOKUP[domain.split(".")[0]]
with connect_db() as db:
status = db("SELECT status FROM hosted_apps WHERE domain=(%s)",
[domain]).fetchone()
if status is not None and status[0] == Status.SUCCESS:
return "" # domain already provisioned
db("DELETE FROM hosted_apps WHERE domain=(%s)", [domain])
with connect_db() as db:
db(
"INSERT INTO hosted_apps (domain, course, app, status) VALUES (%s, %s, %s, %s)",
[domain, course, app, Status.VALIDATING.value],
)
try:
ip = socket.gethostbyname(domain)
except socket.gaierror:
ip = None
if ip != socket.gethostbyname("proxy.cs61a.org"):
set_status(domain, Status.DNS_INVALID)
return
set_status(domain, Status.PROVISIONING)
try:
requests.post(
"https://proxy.cs61a.org/create_domain",
json=dict(
app=app,
domain=domain,
target=get_base_hostname(target_app=app),
secret=get_secret(secret_name="DOMAIN_WEBHOOK_SECRET"),
),
).raise_for_status()
except requests.exceptions.ConnectionError:
pass # nginx restarts so the connection crashes
sleep(5)
if not requests.get(f"https://{domain}/").ok:
set_status(domain, Status.PROVISIONING_FAILED)
return
set_status(domain, Status.UPDATING_OAUTH)
# TODO
set_status(domain, Status.SUCCESS)
return
except:
set_status(domain, Status.INTERNAL_ERROR)
raise
def oauth():
if not request.args["code"]:
return jsonify({"Error": "sadcat"}), 500
resp = requests.post(
"https://slack.com/api/oauth.v2.access",
{
"code": request.args["code"],
"client_id": get_secret(secret_name="CLIENT_ID"),
"client_secret": get_secret(secret_name="CLIENT_SECRET"),
},
)
if resp.status_code == 200:
data = resp.json()
bot_token = data["access_token"]
workspace_data = requests.post(
"https://slack.com/api/auth.test",
headers={"Authorization": "Bearer {}".format(bot_token)},
).json()
workspace_url = workspace_data["url"]
workspace = re.match(
r"https://([a-zA-Z\-0-9]+)\.slack\.com", workspace_url
).group(1)
store_bot_token(get_course(workspace), data["team"]["id"], bot_token)
store_user_token(
data["authed_user"]["id"], data["authed_user"]["access_token"]
)
with connect_db() as db:
db(
"DELETE FROM silenced_users WHERE user = (%s)",
[data["authed_user"]["id"]],
)
return redirect(workspace_url)
return jsonify({"Error": "sadcat"}), 500
def handler():
payload = json.loads(request.form["payload"])
if "actions" not in payload or "value" not in payload["actions"][0]:
return ""
action = payload["actions"][0]["value"]
user_id = payload["user"]["id"]
if action == "activate":
requests.post(
payload["response_url"],
json={
"text": ":robot_face: Activated! While we can't update your previous message (:crying_cat_face:), all your future messages will be made awesome!",
"replace_original": "true",
},
)
elif action == "maybe_later":
requests.post(
payload["response_url"],
json={
"text": "Alright, I'll ask you again later. Or visit slack.apps.cs61a.org to activate this bot manually!!",
"replace_original": "true",
},
)
elif action == "never_ask_again":
with connect_db() as db:
db("INSERT INTO silenced_users VALUES (%s)", (user_id,))
requests.post(
payload["response_url"],
json={
"text": "Understood. If you ever change your mind, visit slack.apps.cs61a.org to activate this bot!",
"replace_original": "true",
},
)
return ""
def index():
if not is_staff("cs61a"):
return login()
with connect_db() as db:
secrets: List[Tuple[str, str, str, str]] = db(
"SELECT app, name, public_value, staging_value FROM secrets"
).fetchall()
return """
<h1>Secrets Tool</h1>
<p>
Add a secret:
<form action="/create_secret" method="POST">
<input name="app" placeholder="App name" />
<input name="name" placeholder="Secret name" />
<input name="public" placeholder="Public value" />
<input name="staging" placeholder="Staging value" />
<button type="submit">Submit</button>
</form>
</p>
<p>
You should assume that the staging value is visible to any member of 61A staff.
For instance, for Auth keys, provide a 61A-specific key for the staging value,
and a super key only for the public value, to avoid leaking information. That said,
staging values are not directly exposed and access will be logged in deploy logs,
so don't worry about it too much, just be careful.
</p>
""" + "".join(f"""<p>
<form
style="display: inline"
action="{url_for("delete_secret", app_name=app, secret_name=name)}"
method="post"
>
{app}/{name} - {display_hash(public_value)} (staging: {display_hash(staging_value)})
<input type="submit" value="Remove">
</form>""" for app, name, public_value, staging_value in secrets)
def wrapped(**kwargs):
kwargs.pop("client_name", None) # legacy argument
secret = kwargs.pop("secret")
with connect_db() as db:
ret_regular = db(
"SELECT client_name, course FROM auth_keys WHERE auth_key = (%s)",
[secret],
).fetchone()
ret_super = db(
"SELECT client_name FROM super_auth_keys WHERE auth_key = (%s)",
[secret],
).fetchone()
if ret_regular:
client_name = ret_regular[0]
course = ret_regular[1]
db(
"UPDATE auth_keys SET unused = FALSE WHERE client_name=(%s)",
[client_name],
)
# the course might still be passed in, but should be ignored
kwargs.pop("course", None)
elif ret_super:
client_name = ret_super[0]
db(
"UPDATE super_auth_keys SET unused = FALSE WHERE client_name=(%s)",
[client_name],
)
course = kwargs.pop("course")
else:
abort(401)
return route(**kwargs, course=course)
def lookup(path):
with connect_db() as db:
target = db(
"SELECT url, creator, secure FROM shortlinks WHERE shortlink=%s AND course=%s",
[path, get_course()],
).fetchone()
return list(target) if target else (None, None, None)
def super_clients():
if not is_staff(MASTER_COURSE):
return ""
with connect_db() as db:
ret = db("SELECT client_name, creator, unused FROM super_auth_keys"
).fetchall()
super_client_names = [
make_row(
f'{client_name}, created by {creator} {"(unused)" if unused else ""} ',
url_for("revoke_super_key", client_name=client_name),
) for client_name, creator, unused in ret
]
return f"""
<h3>Super-Clients</h3>
<p>
Warning - the API keys for these clients are extremely sensitive,
as they can access <i>any</i> course's data. Only use them for 61A-hosted apps,
and reset them whenever a head TA leaves course staff.
</p>
Create new super-client and obtain secret key:
<form action="{url_for("create_super_key")}" method="post">
<input name="client_name" type="text" placeholder="client_name">
<input type="submit">
</form>
""" + "<p>".join(super_client_names)
def workspace_name(course):
with connect_db() as db:
workspace = db(
"SELECT workspace FROM slack_config WHERE course=(%s)",
[course]).fetchone()
if workspace:
return workspace[0]
def teardown():
conn = connect_db()
cursor = conn.cursor()
cursor.execute("DELETE FROM `users` WHERE `username` LIKE 'test%'")
conn.commit()
conn.close()
def index():
if not is_staff("cs61a"):
return login()
email = get_user()["email"]
if not is_admin(course="cs61a", email=email):
abort(401)
with connect_db() as db:
apps = db("SELECT app FROM services WHERE pr_number=0", []).fetchall()
pr_apps = db(
"SELECT app, pr_number FROM services WHERE pr_number>0 ORDER BY pr_number DESC",
[],
).fetchall()
return html(
f"""
This service manages the deployment of the 61A website and various apps.
{"".join(f'''
<form action="/deploy_prod_app">
<input type="submit" name="app" value="{app}" />
</form>
''' for [app] in apps)}
{"".join(f'''
<form action="/trigger_build">
<input type="hidden" name="app" value="{app}" />
<input type="hidden" name="pr_number" value="{pr_number}" />
<input type="submit" value="{app + "-pr" + str(pr_number)}" />
</form>
''' for [app, pr_number] in pr_apps)}
<form action="/delete_unused_services" method="post">
<input type="submit" value="Delete unused services" />
</form>
"""
)
def load_stored_file(file_name):
with connect_db() as db:
out = db("SELECT * FROM stored_files WHERE file_name=%s;",
[file_name]).fetchone()
if out:
return out[1]
abort(404)
def attempt_generated_shortlink(path, app):
with connect_db() as db:
try:
ret = db("SELECT * FROM staffLinks WHERE link=%s;", [path]).fetchone()
if ret is not None:
return ServerFile(
ret["link"],
ret["fileName"],
"",
ret["fileContent"].decode(),
ret["shareRef"],
False,
)._asdict()
ret = db("SELECT * FROM studentLinks WHERE link=%s;", [path]).fetchone()
if ret is None:
return NOT_FOUND
if check_auth(app):
return ServerFile(
ret["link"],
ret["fileName"],
"",
ret["fileContent"].decode(),
ret["shareRef"],
False,
)._asdict()
else:
return NOT_AUTHORIZED
except Exception:
return NOT_LOGGED_IN
def init_db():
with connect_db() as db:
db("""CREATE TABLE IF NOT EXISTS auth_keys (
client_name varchar(128),
auth_key varchar(128),
creator varchar(128),
course varchar(128),
service varchar(128),
unused BOOLEAN
)""")
db("""CREATE TABLE IF NOT EXISTS super_auth_keys (
client_name varchar(128),
auth_key varchar(128),
creator varchar(128),
unused BOOLEAN
)""")
db("""CREATE TABLE IF NOT EXISTS courses (
course varchar(128),
endpoint varchar(128),
endpoint_id INTEGER
)""")
ret = db("SELECT * FROM courses WHERE course=(%s)",
[MASTER_COURSE]).fetchone()
if not ret:
db(
"INSERT INTO courses (course, endpoint, endpoint_id) VALUES (%s, %s, %s)",
["cs61a", "cal/cs61a/sp20", 151],
)
def handle_get_endpoint_id(course):
with connect_db() as db:
endpoint = db(
"SELECT endpoint_id FROM courses WHERE course = (%s)",
[course]).fetchone()
if endpoint:
return endpoint[0]
def course_config(course):
with connect_db() as db:
endpoint, endpoint_id = db(
"SELECT endpoint, endpoint_id FROM courses WHERE course=(%s)",
[course]).fetchone()
user_data = get_user()
for participation in user_data["participations"]:
if participation["course"]["offering"] == endpoint:
endpoint_id = participation["course_id"]
db(
"UPDATE courses SET endpoint_id=(%s) WHERE endpoint=(%s)",
[[endpoint_id, endpoint]],
)
return """
<h3>Config</h3>
<p>
Current endpoint: {} (id: {})
</p>
Set new endpoint:
<form action="/api/{}/set_endpoint" method="post">
<input name="endpoint" type="text" placeholder="OKPy endpoint">
<input type="submit">
</form>
""".format(endpoint, endpoint_id, course)
def delete_secret(app_name, secret_name):
if not is_admin(get_user()["email"], "cs61a"):
return login()
with connect_db() as db:
db("DELETE FROM secrets WHERE app=%s AND name=%s",
[app_name, secret_name])
return redirect(url_for("index"))
def handle_get_course(domain, **_kwargs):
# note: deliberately not secured, not sensitive data
with connect_db() as db:
[course
] = db("SELECT course FROM domains_config WHERE domain = (%s)",
[domain]).fetchone()
return course
def register_channel(course):
purpose = request.form["purpose"]
channel_name = request.form["channel"]
channel_data = list_channels(course=course)
for channel in channel_data:
if channel["name"] == channel_name:
channel_id = channel["id"]
break
else:
return "Channel not found.", 404
with connect_db() as db:
ret = db(
"SELECT * FROM slack_channels WHERE course = (%s) AND purpose = (%s)",
[course, purpose],
).fetchone()
if ret:
return "Channel with same purpose already registered", 409
db(
"INSERT INTO slack_channels VALUES (%s, %s, %s, %s)",
[course, purpose, channel_name, channel_id],
)
return redirect("/")
def record_strat(name, group, received_strat):
if not isinstance(name, str):
abort(400, "Name is not a string!")
name = base64.encodebytes(bytes(name, "utf-8"))
if len(name) >= 1024:
abort(400, "Strategy name is too long!")
name = name.decode("utf-8")
extracted_strat = validate_strat(received_strat)
# extracted_strat probably OK
email = group[0]
encoded_strat = json.dumps(extracted_strat)
hashed = hashlib.md5(bytes(encoded_strat, "utf-8")).hexdigest()
# time to load it into the database
with connect_db() as db:
dupes = db(
"SELECT email FROM cached_strategies WHERE name = (%s)", [name]
).fetchall()
for dupe in dupes:
if dupe["email"] not in group:
abort(
409,
"Another strategy has already been submitted with the same name.",
)
for member in group:
db("DELETE FROM cached_strategies WHERE email=(%s)", [member])
db(
"INSERT INTO cached_strategies VALUES (%s, %s, %s, %s)",
[email, name, hashed, encoded_strat],
)
return hashed
def perform_action(action,
course,
as_staff=False,
is_test=None,
kwargs=None):
with connect_db() as db:
if as_staff:
user, pw = db(
"SELECT staff_user, staff_pw FROM piazza_config WHERE course = (%s)",
[course],
).fetchone()
else:
user, pw = db(
"SELECT student_user, student_pw FROM piazza_config WHERE course = (%s)",
[course],
).fetchone()
if is_test:
(course_id, ) = db(
"SELECT test_course_id FROM piazza_config WHERE course = (%s)",
[course],
).fetchone()
else:
(course_id, ) = db(
"SELECT course_id FROM piazza_config WHERE course = (%s)",
[course]).fetchone()
p = Piazza()
p.user_login(user, pw)
course = p.network(course_id)
if kwargs is None:
kwargs = {}
try:
return getattr(course, action)(**kwargs)
except Exception as e:
return str(e), 400
def clear():
email = session.get("email")
if not email:
abort(401)
with connect_db() as db:
db("DELETE FROM saves WHERE email=%s", [email])
return dict(success=True)
def handle_list_admins(course):
with connect_db() as db:
return [
list(x) for x in db(
"SELECT email, name FROM course_admins WHERE course=(%s)",
[course]).fetchall()
]
def handle_list_courses(**_kwargs):
# note: deliberately not secured, not sensitive data
with connect_db() as db:
return [
list(x)
for x in db("SELECT course, endpoint FROM courses").fetchall()
]
def setUp():
conn = connect_db()
cursor = conn.cursor()
salt = bcrypt.gensalt(8)
pwhash = bcrypt.hashpw('test', salt)
cursor.executemany("""INSERT INTO `users`
(`username`, `salt`, `pwhash`) VALUES (%s, %s, %s)""",
[('test', salt, pwhash), ('test2', salt, pwhash)])
conn.commit()
conn.close()
app.testing = True
def init_db():
g.conn = connect_db()
g.cursor = g.conn.cursor()
