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()
