def register_user(nickname, email, password): if not nickname or not email: raise AccountWithEmptyField() mysql = FreifunkMySQL() user_with_nick = mysql.findone("SELECT id, email FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user_with_email = mysql.findone("SELECT id FROM users WHERE email = %s LIMIT 1",(email,),"id") pw = generate_password_hash(password) if user_with_email: mysql.close() raise AccountWithEmailExists() elif user_with_nick and user_with_nick["email"]: mysql.close() raise AccountWithNicknameExists() else: time = mysql.utcnow() if user_with_nick: mysql.execute(""" UPDATE users SET password = %s, email = %s, created = %s, token = NULL WHERE id = %s LIMIT 1 """,(pw,email,time,user_with_nick["id"],)) mysql.commit() mysql.close() return user_with_nick["id"] else: mysql.execute(""" INSERT INTO users (nickname, password, email, created, token) VALUES (%s, %s, %s, %s, NULL) """,(nickname,pw,email,time,)) userid = mysql.cursor().lastrowid mysql.commit() mysql.close() return userid
def router_info(dbid): try: mysql = FreifunkMySQL() router = mysql.findone(""" SELECT router.*, hoods.id AS hoodid, hoods.name AS hoodname FROM router INNER JOIN hoods ON router.hood = hoods.id WHERE router.id = %s LIMIT 1 """,(dbid,)) mac = None if router: if request.args.get('fffconfig', None) != None: mysql.close() s = "\nconfig fff 'system'\n" s += " option hostname '{}'\n".format(router["hostname"]) s += " option description '{}'\n".format(router["description"]) s += " option latitude '{}'\n".format(router["lat"] if router["lat"] else "") s += " option longitude '{}'\n".format(router["lng"] if router["lng"] else "") s += " option position_comment '{}'\n".format(router["position_comment"]) s += " option contact '{}'\n".format(router["contact"]) return Response(s,mimetype='text/plain') router = mysql.utcaware(router,["created","last_contact"]) router["user"] = mysql.findone("SELECT nickname FROM users WHERE email = %s",(router["contact"],),"nickname") router["netifs"] = mysql.fetchall("""SELECT * FROM router_netif WHERE router = %s""",(dbid,)) netifs = [] for n in router["netifs"]: n["ipv6_addrs"] = mysql.fetchall("""SELECT ipv6 FROM router_ipv6 WHERE router = %s AND netif = %s""",(dbid,n["netif"],),"ipv6") if n["netif"]=="br-mesh": mac = n["mac"] netifs.append(n["netif"]) router["neighbours"] = mysql.fetchall(""" SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id FROM router_neighbor AS nb LEFT JOIN ( SELECT router, mac FROM router_netif GROUP BY mac, router ) AS net ON nb.mac = net.mac LEFT JOIN router as r ON net.router = r.id WHERE nb.router = %s ORDER BY nb.quality DESC """,(dbid,)) # FIX SQL: only one from router_netif router["gws"] = mysql.fetchall(""" SELECT router_gw.mac AS mac, quality, router_gw.netif AS netif, gw_class, selected, gw.name AS gw, n1.netif AS gwif, n2.netif AS batif, n2.mac AS batmac FROM router_gw LEFT JOIN ( gw_netif AS n1 INNER JOIN gw ON n1.gw = gw.id LEFT JOIN gw_netif AS n2 ON n1.mac = n2.vpnmac AND n1.gw = n2.gw ) ON router_gw.mac = n1.mac WHERE router = %s """,(dbid,)) for gw in router["gws"]: gw["label"] = gw_name(gw) gw["batX"] = gw_bat(gw) router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,)) router["events"] = mysql.utcawaretuple(router["events"],"time") ## Create json with all data except stats if request.args.get('json', None) != None: mysql.close() return Response(bson2json(router, sort_keys=True, indent=4), mimetype='application/json') cwan = "blue" cclient = "orange" cbatman = "#29c329" cvpn = "red" chidden = "gray" ## Label netifs AFTER json if clause for n in router["netifs"]: netif = n["netif"]; desc = None color = None if netif == 'br-mesh': desc = "Bridge" elif netif.endswith('.1'): desc = "Clients via Ethernet" color = cclient elif netif.endswith('.2'): desc = "WAN" color = cwan elif netif.endswith('.3'): desc = "Mesh via Ethernet" color = cbatman elif netif == "w2ap": desc = "Clients @ 2.4 GHz" color = cclient elif netif == "w2mesh" or netif == "w2ibss": desc = "Mesh @ 2.4 GHz" color = cbatman elif netif == "w2configap": desc = "Config @ 2.4 GHz" color = chidden elif netif == "w5ap": desc = "Clients @ 5 GHz" color = cclient elif netif == "w5mesh" or netif == "w5ibss": desc = "Mesh @ 5 GHz" color = cbatman elif netif == "w5configap": desc = "Config @ 5 GHz" color = chidden elif netif == "fffVPN": desc = "Fastd VPN Tunnel" color = cvpn elif netif.startswith("l2tp"): desc = "L2TP VPN Tunnel" color = cvpn elif netif.startswith("bat"): desc = "Batman Interface" elif netif.startswith("eth") and any(item.startswith("{}.".format(netif)) for item in netifs): desc = "Switch" elif netif == "eth1": # already known from above: no switch; no one-port, as there must be eth0 if not "eth0" in netifs or any(item.startswith("eth0.") for item in netifs): desc = "WAN" color = cwan else: # Second port of Nanostation M2 desc = "Ethernet Multi-Port" elif netif == "eth0": if any(item.startswith("eth1.") for item in netifs): # already known from above: no switch desc = "WAN" color = cwan else: # First port of Nanostation M2 or ONE-Port desc = "Ethernet Multi-Port" n["description"] = desc n["color"] = color ## Set color for neighbors AFTER json if clause for n in router["neighbours"]: n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"]) router["stats"] = mysql.fetchall("""SELECT * FROM router_stats WHERE router = %s""",(dbid,)) for s in router["stats"]: s["time"] = mysql.utcawareint(s["time"]) threshold_neighstats = (utcnow() - datetime.timedelta(hours=24)).timestamp() neighfetch = mysql.fetchall(""" SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s AND time > %s """,(dbid,threshold_neighstats,)) neighdata = {} for ns in neighfetch: ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)} if not ns["mac"] in neighdata: neighdata[ns["mac"]] = [] neighdata[ns["mac"]].append(ns) neighident = mysql.fetchall(""" SELECT snb.mac, r.hostname, n.netif FROM router_stats_neighbor AS snb INNER JOIN router_netif AS n ON snb.mac = n.mac INNER JOIN router AS r ON n.router = r.id WHERE snb.router = %s AND n.netif <> 'w2ap' AND n.netif <> 'w5ap' GROUP BY snb.mac, r.hostname, n.netif """,(dbid,)) neighlabel = {} for ni in neighident: label = ni["hostname"] # add network interface when there are multiple links to same node for ni2 in neighident: if label == ni2["hostname"] and ni["mac"] != ni2["mac"]: # This shows the NEIGHBOR'S interface name label += "@" + ni["netif"] append = " (old)" for nnn in router["neighbours"]: if nnn["mac"] == ni["mac"]: append = "" neighlabel[ni["mac"]] = label + append gwfetch = mysql.fetchall(""" SELECT quality, mac, time FROM router_stats_gw WHERE router = %s """,(dbid,)) for ns in gwfetch: ns["time"] = mysql.utcawareint(ns["time"]) if request.method == 'POST': if request.form.get("act") == "delete": # a router may not have a owner, but admin users still can delete it if is_authorized(router["user"], session): delete_router(mysql,dbid) flash("<b>Router <i>%s</i> deleted!</b>" % router["hostname"], "success") mysql.close() return redirect(url_for("index")) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "ban": if session.get('admin'): if mac: ban_router(mysql,dbid) delete_router(mysql,dbid) flash("<b>Router <i>%s</i> banned!</b>" % router["hostname"], "success") mysql.close() return redirect(url_for("index")) else: flash("<b>Router has no br-mesh and thus cannot be banned!</b>", "danger") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "changeblocked" and mac: if session.get('admin'): if request.form.get("blocked") == "true": added = mysql.utcnow() mysql.execute("INSERT INTO blocked (mac, added) VALUES (%s, %s)",(mac,added,)) mysql.execute(""" INSERT INTO router_events (router, time, type, comment) VALUES (%s, %s, %s, %s) """,(dbid,mysql.utcnow(),"admin","Marked as blocked",)) mysql.commit() else: mysql.execute("DELETE FROM blocked WHERE mac = %s",(mac,)) mysql.execute(""" INSERT INTO router_events (router, time, type, comment) VALUES (%s, %s, %s, %s) """,(dbid,mysql.utcnow(),"admin","Removed blocked status",)) mysql.commit() router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,)) router["events"] = mysql.utcawaretuple(router["events"],"time") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "report": abusemails = mysql.fetchall("SELECT email FROM users WHERE abuse = 1") for a in abusemails: send_email( recipient = a["email"], subject = "Monitoring: Router %s reported" % router["hostname"], content = "Hello Admin,\n\n" + "The router with hostname %s has been reported as abusive by a user.\n" % router["hostname"] + "Please take care:\n" + "%s\n\n" % url_for("router_info", dbid=dbid, _external=True) + "Regards,\nFreifunk Franken Monitoring System" ) flash("<b>Router reported to administrators!</b>", "success") else: mysql.close() return "Router not found" router["blocked"] = mysql.findone(""" SELECT blocked.mac FROM router_netif AS n LEFT JOIN blocked ON n.mac = blocked.mac WHERE n.router = %s AND n.netif = 'br-mesh' """,(dbid,),"mac") mysql.close() return render_template("router.html", router = router, mac = mac, tileurls = tileurls, neighstats = neighdata, neighlabel = neighlabel, gwstats = gwfetch, authuser = is_authorized(router["user"], session), authadmin = session.get('admin') ) except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_router.txt", str(e)) import traceback writefulllog("Warning: Failed to display router details page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))