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 alfred2(): try: start_time = time.time() mysql = FreifunkMySQL() banned = mysql.fetchall(""" SELECT mac FROM banned """, (), "mac") hoodsv2 = mysql.fetchall(""" SELECT name FROM hoodsv2 """, (), "name") statstime = utcnow() netifdict = mysql.fetchdict("SELECT id, name FROM netifs", (), "name", "id") hoodsdict = mysql.fetchdict("SELECT id, name FROM hoods", (), "name", "id") r = make_response(json.dumps({})) r.mimetype = 'application/json' if request.method == 'POST': try: alfred_data = request.get_json() except Exception as e: writelog( CONFIG["debug_dir"] + "/fail_alfred2.txt", "{} - {}".format(request.environ['REMOTE_ADDR'], 'JSON parsing failed')) writefulllog( "Warning: Error converting ALFRED2 data to JSON:\n__%s" % (request.get_data(True, True).replace("\n", "\n__"))) r.headers['X-API-STATUS'] = "JSON parsing failed" return r if alfred_data: # load router status xml data i = 1 for mac, xml in alfred_data.items(): import_nodewatcher_xml(mysql, mac, xml, banned, hoodsv2, netifdict, hoodsdict, statstime) if (i % 500 == 0): mysql.commit() i += 1 mysql.commit() r.headers['X-API-STATUS'] = "ALFRED2 data imported" mysql.close() writelog( CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds (alfred2)" % (request.environ['REMOTE_ADDR'], time.time() - start_time)) return r except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'], str(e))) writefulllog("Warning: Error while processing ALFRED2 data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__"))) r.headers['X-API-STATUS'] = "ERROR processing ALFRED2 data" return r
def alfred(): try: start_time = time.time() mysql = FreifunkMySQL() #cur = mysql.cursor() #set_alfred_data = {65: "hallo", 66: "welt"} set_alfred_data = {} r = make_response(json.dumps(set_alfred_data)) #import cProfile, pstats, io #pr = cProfile.Profile() #pr.enable() if request.method == 'POST': alfred_data = request.get_json() if alfred_data: # load router status xml data i = 1 for mac, xml in alfred_data.get("64", {}).items(): import_nodewatcher_xml(mysql, mac, xml) if (i % 500 == 0): mysql.commit() i += 1 mysql.commit() r.headers['X-API-STATUS'] = "ALFRED data imported" #detect_offline_routers(mysql) #detect_orphaned_routers(mysql) #delete_orphaned_routers(mysql) #delete_old_stats(mysql) #record_global_stats(mysql) #record_hood_stats(mysql) #update_mapnik_csv(mysql) mysql.close() #pr.disable() #s = io.StringIO() #sortby = 'cumulative' #ps = pstats.Stats(pr, stream=s).sort_stats(sortby) #ps.print_stats() #print(s.getvalue()) writelog(CONFIG["debug_dir"] + "/apitime.txt", "%.3f seconds" % (time.time() - start_time)) r.mimetype = 'application/json' return r except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", str(e))
def alfred(): try: start_time = time.time() mysql = FreifunkMySQL() #set_alfred_data = {65: "hallo", 66: "welt"} set_alfred_data = {} r = make_response(json.dumps(set_alfred_data)) #import cProfile, pstats, io #pr = cProfile.Profile() #pr.enable() banned = mysql.fetchall(""" SELECT mac FROM banned """,(),"mac") statstime = utcnow() netifdict = mysql.fetchdict("SELECT id, name FROM netifs",(),"name","id") if request.method == 'POST': alfred_data = request.get_json() if alfred_data: # load router status xml data i = 1 for mac, xml in alfred_data.get("64", {}).items(): import_nodewatcher_xml(mysql, mac, xml, banned, netifdict, statstime) if (i%500 == 0): mysql.commit() i += 1 mysql.commit() r.headers['X-API-STATUS'] = "ALFRED data imported" mysql.close() #pr.disable() #s = io.StringIO() #sortby = 'cumulative' #ps = pstats.Stats(pr, stream=s).sort_stats(sortby) #ps.print_stats() #print(s.getvalue()) writelog(CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'],time.time() - start_time)) r.mimetype = 'application/json' return r except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e))) import traceback writefulllog("Warning: Error while processing ALFRED data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def gwinfo(): try: start_time = time.time() mysql = FreifunkMySQL() #set_data = {65: "hallo", 66: "welt"} set_data = {} r = make_response(json.dumps(set_data)) if request.method == 'POST': try: gw_data = request.get_json() except Exception as e: writelog( CONFIG["debug_dir"] + "/fail_gwinfo.txt", "{} - {}".format(request.environ['REMOTE_ADDR'], 'JSON parsing failed')) writefulllog( "Warning: Error converting GWINFO data to JSON:\n__%s" % (request.get_data(True, True).replace("\n", "\n__"))) return if gw_data: import_gw_data(mysql, gw_data) mysql.commit() r.headers['X-API-STATUS'] = "GW data imported" mysql.close() writelog( CONFIG["debug_dir"] + "/gwtime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'], time.time() - start_time)) r.mimetype = 'application/json' return r except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_gwinfo.txt", "{} - {}".format(request.environ['REMOTE_ADDR'], str(e))) writefulllog("Warning: Error while processing GWINFO data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
def user_info(nickname): mysql = FreifunkMySQL() user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) if not user: mysql.close() return "User not found" if request.method == 'POST': if request.form.get("action") == "changepw": if is_authorized(user["nickname"], session): if request.form["password"] != request.form["password_rep"]: flash("<b>Passwords did not match!</b>", "danger") elif request.form["password"] == "": flash("<b>Password must not be empty!</b>", "danger") else: set_user_password(mysql, user["nickname"], request.form["password"]) flash("<b>Password changed!</b>", "success") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changemail": if is_authorized(user["nickname"], session): if request.form["email"] != request.form["email_rep"]: flash("<b>E-Mail addresses do not match!</b>", "danger") elif not "@" in request.form["email"]: flash("<b>Invalid E-Mail addresse!</b>", "danger") else: try: set_user_email(mysql, user["nickname"], request.form["email"]) flash("<b>E-Mail changed!</b>", "success") if not session.get('admin'): password = base64.b32encode(os.urandom(10)).decode() set_user_password(mysql, user["nickname"], password) send_email( recipient = request.form['email'], subject = "Password for %s" % user['nickname'], content = "Hello %s,\n\n" % user["nickname"] + "You changed your email address on https://monitoring.freifunk-franken.de/\n" + "To verify your new email address your password was changed to %s\n" % password + "... and sent to your new address. Please log in and change it.\n\n" + "Regards,\nFreifunk Franken Monitoring System" ) mysql.close() return logout() else: # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) except AccountWithEmailExists: flash("<b>There is already an account with this E-Mail Address!</b>", "danger") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changeadmin": if session.get('admin'): set_user_admin(mysql, nickname, request.form.get("admin") == "true") # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changeabuse": if session.get('admin'): set_user_abuse(mysql, nickname, request.form.get("abuse") == "true") # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "deleteaccount": if is_authorized(user["nickname"], session): mysql.execute("DELETE FROM users WHERE nickname = %s LIMIT 1",(nickname,)) mysql.commit() flash("<b>User <i>%s</i> deleted!</b>" % nickname, "success") mysql.close() if user["nickname"] == session.get("user"): session.pop('user', None) return redirect(url_for("user_list")) else: flash("<b>You are not authorized to perform this action!</b>", "danger") routers = mysql.fetchall(""" SELECT router.id, hostname, status, hoods.id AS hoodid, hoods.name AS hood, firmware, hardware, created, sys_uptime, clients, router.lat, router.lng, reset, blocked, v2, local FROM router INNER JOIN hoods ON router.hood = hoods.id LEFT JOIN ( SELECT router, blocked.mac AS blocked FROM router_netif INNER JOIN blocked ON router_netif.mac = blocked.mac WHERE netif = 'br-mesh' ) AS b ON router.id = b.router WHERE contact = %s ORDER BY hostname ASC """,(user["email"],)) mysql.close() routers = mysql.utcawaretuple(routers,"created") return render_template("user.html", user=user, routers=routers, routers_count=len(routers), authuser = is_authorized(user["nickname"], session), authadmin = session.get('admin') )
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__")))
mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `users` ( `id` int(11) NOT NULL, `nickname` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `password` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `token` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `admin` tinyint(1) NOT NULL DEFAULT '0', `abuse` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `nickname` (`nickname`), ADD UNIQUE KEY `email` (`email`) """) mysql.execute(""" ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT """) mysql.commit() mysql.close()