def search():
query_string = flask.request.args.get('q')
if not query_string:
return render_template('search.tmpl', title="Search", query_string="")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT query.floor, query.post_id, query.thread_id, query.title, "
"pgroonga_snippet_html(query.content, pgroonga_query_extract_keywords(%s)) FROM "
"(SELECT post.post_id AS post_id, post.thread AS thread_id, post_content.content AS content, "
"rank() OVER (PARTITION BY post.thread ORDER BY post.datetime) AS floor, thread.title AS title "
"FROM post, post_content, thread WHERE post_content.post = post.post_id "
"AND post.thread = thread.thread_id "
"AND post.hidden = FALSE AND thread.hidden = FALSE "
"AND thread.draft = FALSE "
") AS query WHERE query.content &@~ %s",
(query_string, query_string))
ret = render_template("search.tmpl",
title=query_string,
cursor=cursor,
query_string=query_string)
finally:
cnx.close()
return ret
def sudo():
user = check_permission()
redis = get_redis_connection()
failed_attempts = redis.get('sudo_fail_{}'.format(user))
failed_attempts = int(failed_attempts) if failed_attempts != None else 0
if failed_attempts >= 5:
write_log("dashboard_access", user, {
"success": False,
"reason": "too many attemps"
})
return render_template("dashboard/sudo.tmpl", error=True, chances=0)
if flask.request.method == "GET":
return render_template("dashboard/sudo.tmpl")
password = flask.request.form.get("password")
if password == None:
flask.abort(400)
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute("SELECT salt FROM users WHERE user_id = %s", (user, ))
salt = cursor.fetchone()[0]
finally:
cnx.close()
hashed_password = hashlib.sha256(
(password + salt).encode()).hexdigest().upper()
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT * FROM users WHERE user_id = %s AND password = %s",
(user, hashed_password))
result = cursor.fetchone()
finally:
cnx.close()
if result == None:
write_log("dashboard_access", user, {"success": False})
redis = get_redis_connection()
failed_attemps = int(redis.incr('sudo_fail_{}'.format(user)))
redis.expire('sudo_fail_{}'.format(user), 300)
ret = render_template("dashboard/sudo.tmpl",
error=True,
chances=max(5 - failed_attemps, 0))
return ret
else:
ret = flask.make_response(flask.redirect("/dashboard/"))
ret.set_cookie("sudo_mode", create_cookie(user))
redis = get_redis_connection()
redis.delete('sudo_fail_{}'.format(user))
write_log("dashboard_access", user, {"success": True})
return ret
def registration():
if flask.request.method == "GET":
referrer = flask.request.referrer
return render_template("login.tmpl",
title="注册",
referrer=referrer,
type="registration")
try:
username = flask.request.form["username"]
password = flask.request.form["password"]
email = flask.request.form["email"]
referrer = flask.request.form["referrer"]
except KeyError:
flask.abort(400)
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute("SELECT * FROM users WHERE name = %s", (username, ))
if cursor.fetchone() != None:
write_log(
"registration", "", {
"success": False,
"username": username,
"reason": "user already exists"
})
return render_template("login.tmpl",
title="注册",
referrer=referrer,
error="用户 {} 已经存在".format(username))
user_id = str(uuid.uuid4()).replace('-', '').upper()
salt = crypt.mksalt(crypt.METHOD_SHA256)
hashed_password = hashlib.sha256(
(password + salt).encode()).hexdigest().upper()
cursor.execute(
"INSERT INTO users VALUES ( "
"%s, %s, %s, %s, %s, FALSE, FALSE, %s)",
(user_id, username, email, hashed_password, username, salt))
cnx.commit()
write_log("registration", user_id, {"success": True})
finally:
cnx.close()
cookie = create_cookie(user_id)
response = flask.make_response(flask.redirect(referrer))
response.set_cookie("session", cookie, expires=32503680000)
return response
def userinfo(userid):
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT name, nick, email, admin FROM users WHERE user_id = %s",
(userid, ))
userinfo = cursor.fetchone()
finally:
cnx.close()
if user == None:
flask.abort(404)
username, nick, email, admin = userinfo
user_avatar = hashlib.md5(
email.lower().strip().encode()).hexdigest().lower()
return render_template("dashboard/userinfo.tmpl",
username=username,
nick=nick,
email=email,
user_avatar=user_avatar,
user_is_admin=admin,
userid=userid)
def posts_view():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
return render_template("posts_view.tmpl")
def index():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT log.type, users.name, log.data FROM log, users "
"WHERE users.user_id = log.operator ORDER BY log.datetime DESC LIMIT 10"
)
recent_events = list(cursor)
cursor.execute(
"SELECT log.datetime, log.data FROM log, users "
"WHERE users.user_id = log.operator AND log.operator = %s "
"AND log.type = 'dashboard_access' ORDER by log.datetime DESC LIMIT 10",
(user, ))
recent_access = list(cursor)
finally:
cnx.close()
return render_template("dashboard/index.tmpl",
recent_events=recent_events,
recent_access=recent_access,
log_type_to_string=log_type_to_string,
log_data_simple=log_data_simple)
def new():
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
finally:
cnx.close()
return render_template("edit.tmpl", title="New Thread", type="new_thread")
def threads():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
query_string = flask.request.args.get("s")
if query_string == None:
return render_template("dashboard/threads.tmpl")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT title, hidden, thread_id FROM thread WHERE title &@~ %s",
(query_string, ))
threads = cursor.fetchall()
finally:
cnx.close()
return render_template("dashboard/threads.tmpl", threads=threads)
def users():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
query_string = flask.request.args.get("s")
if query_string == None:
return render_template("dashboard/user.tmpl")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT user_id, name, nick FROM users WHERE nick LIKE %s",
("%{}%".format(query_string), ))
users = cursor.fetchall()
finally:
cnx.close()
return render_template("dashboard/user.tmpl", users=users)
def categories():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute('SELECT category_id, name FROM category')
categories = cursor.fetchall()
finally:
cnx.close()
return render_template("dashboard/category.tmpl", categories=categories)
def log():
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT log.*, users.name FROM log, users WHERE users.user_id = log.operator ORDER BY log.datetime DESC"
)
logs = cursor.fetchall()
finally:
cnx.close()
return render_template("/dashboard/logs.tmpl",
logs=logs,
log_type_to_string=log_type_to_string,
log_data_simple=log_data_simple)
def deleted_post(post_id):
user = flask.request.cookies.get("session")
if user != None and read_cookie(user) != None:
user = read_cookie(user)
cnx = get_pg_connection()
try:
if user:
cursor = cnx.cursor()
cursor.execute("SELECT admin FROM users WHERE user_id = %s",
(user, ))
admin, = cursor.fetchone()
else:
admin = False
if not admin:
flask.abort(404)
cursor = cnx.cursor()
cursor.execute(
"select post.post_id, users.nick, post.author, "
"LOWER(MD5(TRIM(LOWER(users.email)))), "
"post_content.content, post_content.datetime, post_content.renderer FROM post, users, post_content "
"WHERE post.author = users.user_id "
"AND post.content = post_content.content_id "
"AND post.post_id = %s", (post_id, ))
posts = cursor.fetchall()
finally:
cnx.close()
return render_template("post.tmpl",
posts=posts,
page=1,
post_per_page=1,
total_pages=1,
render_content=render_content,
title="被删除的回复")
def log_detail(id):
user = check_permission()
sudo = check_sudo(user)
if not sudo:
return flask.redirect("/dashboard/sudo")
try:
id = int(id)
except:
flask.abort(400)
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT log.*, users.name FROM log, users WHERE users.user_id = log.operator AND log.log_id = %s",
(id, ))
details = cursor.fetchone()
finally:
cnx.close()
return render_template("/dashboard/log_details.tmpl",
details=details,
log_type_to_string=log_type_to_string)
def login():
if flask.request.method == "GET":
referrer = flask.request.referrer
else:
referrer = flask.request.form["referrer"]
try:
referrer.index("/login")
except ValueError:
pass
else:
referrer = "/"
if flask.request.method == "GET":
return render_template("login.tmpl",
title="登录",
referrer=flask.request.referrer,
error=None,
type="login")
else:
username = flask.request.form["username"]
password = flask.request.form["password"]
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute("SELECT salt FROM users WHERE name = %s",
(username, ))
salt = cursor.fetchone()
if salt:
salt = salt[0]
else:
error = "用户名或密码错误"
write_log(
"login", "", {
"success": False,
"username": username,
"reason": "user does not exist"
})
return render_template("login.tmpl",
referrer=referrer,
error=error,
title="Login",
type="login")
hashed_password = hashlib.sha256(
(password + salt).encode()).hexdigest().upper()
cursor.execute(
"SELECT user_id AS uid FROM users WHERE name = %s AND password = %s",
(username, hashed_password))
ret = cursor.fetchone()
if not ret:
cnx.close()
error = "用户名或密码错误"
write_log(
"login", "", {
"success": False,
"username": username,
"reason": "wrong password"
})
return render_template("login.tmpl",
referrer=referrer,
error=error,
title="Login",
type="login")
finally:
cnx.close()
userid = ret[0]
cookie = create_cookie(userid)
response = flask.make_response(flask.redirect(referrer))
response.set_cookie("session", cookie, expires=32503680000)
write_log("login", userid, {"success": True, "username": username})
return response
def userinfo():
try:
user = read_cookie(flask.request.cookies["session"])
except KeyError:
return flask.redirect("/")
if not user:
flask.redirect("/")
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
cursor.execute(
'SELECT name, email, nick, salt FROM users WHERE user_id = %s',
(user, ))
name, email, nick, salt = cursor.fetchone()
finally:
cnx.close()
if flask.request.method == "GET":
return render_template("userinfo.tmpl",
error=False,
name=name,
email=email,
nick=nick,
title="User Info")
else:
try:
new_email = flask.request.form["email"]
new_nick = flask.request.form["nick"]
old_password = flask.request.form["old_password"]
new_password = flask.request.form["new_password"]
except:
return flask.abort(400)
if new_password != "":
cnx = get_pg_connection()
cursor = cnx.cursor()
try:
hashed_old_password = hashlib.sha256(
(old_password + salt).encode()).hexdigest().upper()
cursor.execute(
"SELECT user_id FROM users WHERE user_id = %s AND password = %s",
(user, hashed_old_password))
if not cursor.fetchone():
cnx.close()
write_log("update_userinfo", user, {
"success": False,
"reason": "wrong password"
})
return render_template("userinfo.tmpl",
error=True,
name=name,
email=email,
nick=nick,
title="User Info")
except:
cnx.close()
raise
salt = crypt.mksalt(crypt.METHOD_SHA256)
hashed_password = hashlib.sha256(
(new_password + salt).encode()).hexdigest().upper()
else:
hashed_password = None
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
if hashed_password:
cursor.execute(
"UPDATE users SET email = %s, nick = %s, password = %s, salt = %s WHERE user_id = %s",
(new_email, new_nick, hashed_password, salt, user))
else:
cursor.execute(
"UPDATE users SET email = %s, nick = %s WHERE user_id = %s",
(new_email, new_nick, user))
cnx.commit()
finally:
cnx.close()
log_data = {
"success": True,
"nick": [nick, new_nick],
"email": [email, new_email],
"password_changed": new_password != ""
}
write_log("update_userinfo", user, log_data)
return flask.redirect("/userinfo")
def edit(edit_type, target_id):
try:
user = read_cookie(flask.request.cookies["session"])
except:
user = None
if user == None:
flask.abort(401)
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
if edit_type == 'thread':
cursor.execute(
"SELECT thread.title, thread.category, thread.draft, users.admin FROM thread, users "
"WHERE thread.thread_id = %s AND users.user_id = %s AND (thread.author = users.user_id OR users.admin = TRUE)",
(target_id, user))
title, category, is_draft, _ = cursor.fetchone()
cursor.execute(
"SELECT post.post_id, users.admin FROM post, users "
"WHERE post.thread = %s AND users.user_id = %s AND (post.author = users.user_id OR users.admin = TRUE) "
"ORDER BY datetime LIMIT 1 OFFSET 0", (target_id, user))
post_id = cursor.fetchone()
if not post_id:
flask.abort(404)
post_id = post_id[0]
else:
post_id = target_id
except:
cnx.close()
raise
try:
cursor = cnx.cursor()
cursor.execute(
"SELECT content, renderer FROM post_content WHERE post = %s ORDER BY datetime DESC LIMIT 1 OFFSET 0",
(post_id, ))
content, renderer = cursor.fetchone()
finally:
cnx.close()
kwargs = {
"renderer": renderer,
"referrer": flask.request.referrer,
"content": content,
"post_id": post_id
}
if edit_type == "thread":
print(title)
kwargs["type"] = "edit_thread"
kwargs["title"] = "编辑主题：" + title
kwargs["current_category_id"] = category
kwargs["draft"] = is_draft
kwargs["thread_id"] = target_id
else:
kwargs["type"] = "edit_post"
kwargs["title"] = "编辑回复"
return render_template("edit.tmpl", **kwargs)
def post(thread, page):
user = flask.request.cookies.get("session")
if user != None and read_cookie(user) != None:
user = read_cookie(user)
cnx = get_pg_connection()
try:
if user:
cursor = cnx.cursor()
cursor.execute("SELECT admin FROM users WHERE user_id = %s",
(user, ))
admin, = cursor.fetchone()
else:
admin = False
cursor = cnx.cursor()
cursor.execute("SELECT title FROM thread WHERE thread_id = %s",
(thread, ))
title = cursor.fetchone()[0]
cursor = cnx.cursor()
cursor.execute("SELECT COUNT(*) FROM post WHERE thread = %s",
(thread, ))
post_count = cursor.fetchone()[0]
cursor = cnx.cursor()
cursor.execute("SELECT hidden FROM thread WHERE thread_id = %s",
(thread, ))
hidden, = cursor.fetchone()
if hidden and not admin:
flask.abort(404)
query = (
"SELECT post.post_id, users.nick, post.author, "
"LOWER(MD5(TRIM(LOWER(users.email)))), "
"post_content.content, post_content.datetime, post_content.renderer FROM post, users, thread, post_content "
"WHERE post.thread = %s AND post.hidden = FALSE "
"AND post.thread = thread.thread_id "
"AND post.author = users.user_id "
"AND post.content = post_content.content_id "
"ORDER BY post.datetime LIMIT {} OFFSET %s".format(
config["paginator"]["post_per_page"]))
cursor = cnx.cursor()
cursor.execute(
query,
(thread, int(
(int(page) - 1) * config["paginator"]["post_per_page"])))
posts = list(cursor)
cursor = cnx.cursor()
cursor.execute("SELECT thread.author FROM thread WHERE thread_id = %s",
(thread, ))
thread_author_id = cursor.fetchone()[0]
finally:
cnx.close()
total_pages = int((post_count - 1) / config["paginator"]["post_per_page"] +
1)
return render_template("post.tmpl",
posts=posts,
thread_hidden=hidden,
page=int(page),
total_pages=total_pages,
thread_author_id=thread_author_id,
baseurl="/thread/view/{}".format(thread),
post_per_page=config["paginator"]["post_per_page"],
render_content=render_content,
title=title,
thread_id=thread)
def list_threads(order, page, category=None, author=None, draft=False):
order_sql = {
'publish': 'publish_datetime',
'reply': 'reply_count',
'last_modified': 'last_modified',
'random': 'RAND()'
}
category_sql = "AND thread.category = %s" if category else ''
author_sql = "AND thread.author = %s" if author else ''
try:
sudo_mode = read_cookie(flask.request.cookies["sudo_mode"])
except KeyError:
sudo_mode = None
hidden = bool(sudo_mode)
query = (
"SELECT thread.thread_id, thread.title, thread.datetime AS publish_datetime, "
"users.nick, MAX(post.last_modified) AS last_modified, "
"(SELECT COUNT(*) FROM post WHERE post.thread = thread.thread_id) AS reply_count FROM thread, post, users "
"WHERE thread.hidden = FALSE "
"AND post.thread = thread.thread_id "
"AND users.user_id = thread.author "
"AND thread.draft = %s {} {} "
"GROUP BY thread.thread_id, users.nick "
"ORDER BY {} DESC "
"LIMIT {} "
"OFFSET %s ").format(category_sql, author_sql, order_sql[order],
config["paginator"]["thread_per_page"])
page_query = "SELECT COUNT(*) FROM thread WHERE hidden = FALSE AND draft = %s {} {}".format(
category_sql, author_sql)
category_query = "SELECT name FROM category WHERE category_id = %s"
cnx = get_pg_connection()
try:
cursor = cnx.cursor()
page_cursor = cnx.cursor()
if category:
category_cursor = cnx.cursor()
if author:
cursor.execute(
query,
(draft, category, author,
(page - 1) * config["paginator"]["thread_per_page"]))
else:
cursor.execute(
query,
(draft, category,
(page - 1) * config["paginator"]["thread_per_page"]))
if author:
page_cursor.execute(page_query, (draft, category, author))
else:
page_cursor.execute(page_query, (
draft,
category,
))
category_cursor.execute(category_query, (category, ))
category_name, = category_cursor.fetchone()
title = "分类: " + category_name
else:
if author:
cursor.execute(
query,
(draft, author,
(page - 1) * config["paginator"]["thread_per_page"]))
else:
cursor.execute(
query,
(draft,
(page - 1) * config["paginator"]["thread_per_page"]))
if author:
page_cursor.execute(page_query, (draft, author))
else:
page_cursor.execute(page_query, (draft, ))
title = "主页"
total_pages, = page_cursor.fetchone()
total_pages = int((total_pages - 1) /
config["paginator"]["thread_per_page"] + 1)
if draft:
baseurl = "/drafts"
elif category:
baseurl = "/category/{}/{}".format(category, order)
else:
baseurl = "/index/" + order
ret = render_template('list.tmpl',
cursor=cursor,
title=title,
page=page,
total_pages=total_pages,
order=order,
baseurl=baseurl)
finally:
cnx.close()
return ret