tRework database to allow random flag submissions - scoreboard - Interactive scoreboard for CTF-like games
HTML git clone git://git.z3bra.org/scoreboard.git
DIR Log
DIR Files
DIR Refs
---
DIR commit f2372a7f800172e4b59c983b08daff889b05274b
DIR parent 18522b155c6c058696e5699d7d321ddebb4302b1
HTML Author: Willy Goiffon <contact@z3bra.org>
Date: Wed, 25 Sep 2024 22:59:01 +0200
Rework database to allow random flag submissions
Diffstat:
M db.go | 103 +++++++++----------------------
M html.go | 3 +++
M main.go | 107 +++++++++++++------------------
M player.go | 116 +++++++++++++++----------------
M playerbox.go | 15 +++++++--------
M ui.go | 7 ++++---
6 files changed, 143 insertions(+), 208 deletions(-)
---
DIR diff --git a/db.go b/db.go
t@@ -23,17 +23,23 @@ const (
// DB queries
DB_CREATE string = `
CREATE TABLE IF NOT EXISTS
- score(
- hash TEXT,
- name TEXT,
- flag INT,
+ user(
+ name TEXT PRIMARY KEY,
+ hash TEXT NOT NULL UNIQUE,
score INT,
+ flag INT,
ts INT
);
CREATE TABLE IF NOT EXISTS
flag(
- chapter INT,
- value TEXT
+ value TEXT PRIMARY KEY,
+ badge TEXT,
+ score INT
+ );
+ CREATE TABLE IF NOT EXISTS
+ score(
+ name TEXT,
+ flag TEXT
);
`
)
t@@ -57,61 +63,31 @@ func db_init(file string) (*sql.DB, error) {
return db, nil
}
-func db_count(db *sql.DB) int {
- var count int
- query := `SELECT count(*) FROM score;`
- row := db.QueryRow(query)
- row.Scan(&count)
- return count
-}
-
-func db_score_count(db *sql.DB, score, ts int) int {
- var count int
- query := `SELECT
- count(id)
- FROM score
- WHERE
- score >= ? AND
- ts => ?
- ;`
-
- row := db.QueryRow(query, score, ts)
- row.Scan(&count)
- return count
-}
-
-func db_flag_count(db *sql.DB, flag int) int {
- var count int
- query := `SELECT
- count(*)
- FROM score
- WHERE
- flag >= ?
- ;`
+func db_get_flags(db *sql.DB) ([]Flag, error) {
+ query := `SELECT rowid,value,badge,score FROM flag ORDER BY score;`
- row := db.QueryRow(query, flag)
- row.Scan(&count)
- return count
-}
+ rows, err := db.Query(query)
+ if err != nil {
+ return nil, err
+ }
-func db_id(db *sql.DB, nick string) bool {
- var count int
- query := `SELECT
- count(*)
- FROM score
- WHERE
- name = ?
- ;`
+ flags := make([]Flag, 0)
+ for rows.Next() {
+ var flag Flag
+ err := rows.Scan(&flag.id, &flag.value, &flag.badge, &flag.score)
+ if err != nil {
+ return nil, err
+ }
+ flags = append(flags, flag)
+ }
- row := db.QueryRow(query, nick)
- row.Scan(&count)
- return (count > 0)
+ return flags, nil
}
func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
query := `SELECT
name,flag,score,ts
- FROM score
+ FROM user
ORDER BY
score DESC,
flag DESC,
t@@ -137,24 +113,3 @@ func db_ranked_players(db *sql.DB, offset, limit int) ([]Player, error) {
return players, nil
}
-
-func db_flags(db *sql.DB) ([]Flag, error) {
- query := `SELECT chapter,value FROM flag ORDER BY chapter;`
-
- rows, err := db.Query(query)
- if err != nil {
- return nil, err
- }
-
- flags := make([]Flag, 0)
- for rows.Next() {
- var flag Flag
- err := rows.Scan(&flag.chapter, &flag.value)
- if err != nil {
- return nil, err
- }
- flags = append(flags, flag)
- }
-
- return flags, nil
-}
DIR diff --git a/html.go b/html.go
t@@ -53,10 +53,13 @@ var html string = `
`
func (a *Application) GenerateHTML() {
+ players := make([]Player, 0)
+ /*
players, err := db_ranked_players(a.db, 0, -1)
if err != nil {
panic(err)
}
+ */
data := Template{}
for i:=0; i<len(players); i++ {
DIR diff --git a/main.go b/main.go
t@@ -42,8 +42,10 @@ Save it carefully, do not share it.
)
type Flag struct {
- chapter int
+ id int
value string
+ badge string
+ score int
}
type Application struct {
t@@ -58,7 +60,7 @@ type Application struct {
player *Player
}
-var cyboard Application
+var scoreboard Application
func usage() {
fmt.Println("ssh -t board@cyb.farm [FLAG]")
t@@ -67,25 +69,14 @@ func usage() {
func flagid(hash string) int {
- for i := 0; i<len(cyboard.flag_ref); i++ {
- if strings.ToUpper(hash) == cyboard.flag_ref[i].value {
- return cyboard.flag_ref[i].chapter
+ for i := 0; i<len(scoreboard.flag_ref); i++ {
+ if strings.ToUpper(hash) == scoreboard.flag_ref[i].value {
+ return scoreboard.flag_ref[i].id
}
}
return -1
}
-func scoreDump() {
- players, err := db_ranked_players(cyboard.db, 0, -1)
- if err != nil {
- panic(err)
- }
-
- for i:=0; i<len(players); i++ {
- fmt.Printf("%s\t%d\t%d\n", players[i].name, players[i].flag, players[i].score)
- }
-}
-
func pageToken() tview.Primitive {
input := tview.NewInputField().
SetLabel("TOKEN ").
t@@ -111,34 +102,34 @@ func pageToken() tview.Primitive {
}
if len(input.GetText()) != 24 {
- cyboard.Popup("ERROR", "Invalid token format")
+ scoreboard.Popup("ERROR", "Invalid token format")
return
}
- err := cyboard.player.FromToken(input.GetText())
+ err := scoreboard.player.FromToken(input.GetText())
if err != nil {
- cyboard.Fatal(err)
+ scoreboard.Fatal(err)
return
}
- err = cyboard.player.Submit(cyboard.flag)
+ err = scoreboard.player.Submit(scoreboard.flag)
if err != nil {
- cyboard.Fatal(err)
+ scoreboard.Fatal(err)
return
}
- cyboard.HighlightBoard(cyboard.player.ScoreRank())
- cyboard.pages.SwitchToPage("board")
- cyboard.GenerateHTML()
+ scoreboard.HighlightBoard(scoreboard.player.ScoreRank())
+ scoreboard.pages.SwitchToPage("board")
+ scoreboard.GenerateHTML()
})
return center(40, 1, input)
}
func pageBoard() tview.Primitive {
- cyboard.SetupFrame()
- cyboard.DrawBoard()
+ scoreboard.SetupFrame()
+ scoreboard.DrawBoard()
// center frame on screen, counting borders + header + footer
- return center(BOARD_WIDTH + 2, BOARD_HEIGHT + 7, cyboard.frame)
+ return center(BOARD_WIDTH + 2, BOARD_HEIGHT + 7, scoreboard.frame)
}
func main() {
t@@ -164,26 +155,25 @@ func main() {
tview.Styles.GraphicsColor = tcell.ColorDefault
tview.Styles.PrimaryTextColor = tcell.ColorDefault
- cyboard.db, err = db_init(*db)
+ scoreboard.db, err = db_init(*db)
if err != nil {
panic(err)
}
- defer cyboard.db.Close()
+ defer scoreboard.db.Close()
- cyboard.flag_ref, err = db_flags(cyboard.db)
+ scoreboard.flag_ref, err = db_get_flags(scoreboard.db)
- cyboard.flag = 0
- cyboard.html = *html
- cyboard.app = tview.NewApplication()
- cyboard.pages = tview.NewPages()
- //cyboard.frame = tview.NewFrame(tview.NewGrid())
- cyboard.board = tview.NewFlex()
- cyboard.player = &Player{ db: cyboard.db }
+ scoreboard.flag = 0
+ scoreboard.html = *html
+ scoreboard.app = tview.NewApplication()
+ scoreboard.pages = tview.NewPages()
+ scoreboard.board = tview.NewFlex()
+ scoreboard.player = &Player{ db: scoreboard.db }
- cyboard.pages.SetBackgroundColor(tcell.ColorDefault)
+ scoreboard.pages.SetBackgroundColor(tcell.ColorDefault)
- cyboard.pages.AddPage("token", pageToken(), true, false)
- cyboard.pages.AddPage("board", pageBoard(), true, false)
+ scoreboard.pages.AddPage("token", pageToken(), true, false)
+ scoreboard.pages.AddPage("board", pageBoard(), true, false)
args := flag.Args()
t@@ -193,46 +183,37 @@ func main() {
if args[0] == "help" {
usage()
}
- if args[0] == "dump" {
- scoreDump()
- os.Exit(0)
- }
if args[0] == "refresh" {
- cyboard.GenerateHTML()
+ scoreboard.GenerateHTML()
os.Exit(0)
}
- switch cyboard.flag = flagid(args[0]) + 1; cyboard.flag {
+ switch scoreboard.flag = flagid(args[0]) + 1; scoreboard.flag {
case 0:
fmt.Println("Incorrect flag")
return
case 1:
- cyboard.player.flag = cyboard.flag
- cyboard.player.score = 100
- cyboard.player.ts = time.Now().Unix()
-
- // Bonus points for the first player to submit a flag
- if cyboard.player.FlagRank() == 0 {
- cyboard.player.score += cyboard.flag * 10
- }
+ scoreboard.player.flag = scoreboard.flag
+ scoreboard.player.score = 100
+ scoreboard.player.ts = time.Now().Unix()
- rank := cyboard.player.ScoreRank() + 1
+ rank := scoreboard.player.ScoreRank() + 1
- cyboard.NewPlayer(rank)
- cyboard.pages.SwitchToPage("board")
+ scoreboard.NewPlayer(rank)
+ scoreboard.pages.SwitchToPage("board")
default:
- cyboard.pages.SwitchToPage("token")
+ scoreboard.pages.SwitchToPage("token")
}
} else {
- cyboard.pages.SwitchToPage("board")
- cyboard.DrawBoard()
+ scoreboard.pages.SwitchToPage("board")
+ scoreboard.DrawBoard()
}
- if err := cyboard.app.SetRoot(cyboard.pages, true).EnableMouse(true).Run(); err != nil {
+ if err := scoreboard.app.SetRoot(scoreboard.pages, true).EnableMouse(true).Run(); err != nil {
fmt.Println(err)
os.Exit(1)
}
- if cyboard.player.token != "" && cyboard.flag < 7 {
- fmt.Printf(TOKEN_REMINDER, cyboard.player.name, humanize.Ordinal(cyboard.flag + 1), cyboard.player.token)
+ if scoreboard.player.token != "" && scoreboard.flag < 7 {
+ fmt.Printf(TOKEN_REMINDER, scoreboard.player.name, humanize.Ordinal(scoreboard.flag + 1), scoreboard.player.token)
}
}
DIR diff --git a/player.go b/player.go
t@@ -29,6 +29,7 @@ import (
type Player struct {
db *sql.DB
+ id int
token string
name string
flag int
t@@ -36,6 +37,7 @@ type Player struct {
ts int64
}
+/* Randomize a buffer of a given length */
func randbuf(length int64) []byte {
b := make([]byte, length)
_, err := rand.Read(b)
t@@ -45,14 +47,14 @@ func randbuf(length int64) []byte {
return b
}
-func tokenize(name string) (string, string, error) {
+/* Generate a random base32 token using the provided input as a salt */
+func mktoken(input string) (string, string, error) {
key := randbuf(12)
- salt := base32.StdEncoding.EncodeToString([]byte(name))
+ salt := base32.StdEncoding.EncodeToString([]byte(input))
token := key
- token = append(token, []byte(name)...)
+ token = append(token, []byte(input)...)
- // use name as salt
dk, err := scrypt.Key(key, []byte(salt), 1<<15, 8, 1, 32)
if err != nil {
return "", "", err
t@@ -63,16 +65,19 @@ func tokenize(name string) (string, string, error) {
return token32, hash32, nil
}
+/* Register a user in the database */
func (p *Player) Register() error {
var hash string
var err error
- p.token, hash, err = tokenize(p.name)
+
+ p.ts = time.Now().Unix()
+ p.token, hash, err = mktoken(p.name)
if err != nil {
return err
}
- query := `INSERT INTO score(name,hash,flag,score,ts) VALUES(?,?,?,?,?);`
- _, err = p.db.Exec(query, p.name, hash, p.flag, p.score, p.ts)
+ query := `INSERT INTO score(name,hash,ts) VALUES(?,?,?);`
+ _, err = p.db.Exec(query, p.name, hash, p.ts)
if err != nil {
return err
}
t@@ -80,19 +85,36 @@ func (p *Player) Register() error {
return nil
}
-func (p *Player) Update() error {
- var hash string
+func (p *Player) Fetch() error {
+ /* Fill player struct with basic info */
+ query := `SELECT rowid,ts FROM user WHERE name = ?;`
+ row := p.db.QueryRow(query, p.name)
+ row.Scan(&p.id, &p.ts)
+
+ /* Calculate score based on submitted flags */
+ query = `SELECT
+ COUNT(flag.score), SUM(flag.score)
+ FROM flag
+ INNER JOIN score ON score.flag = flag.value
+ WHERE score.name = ?;`
+
+ row = p.db.QueryRow(query, p.name)
+ row.Scan(&p.flag, &p.score)
+
+ return nil
+}
+
+func (p *Player) Refresh(score int, flag int, ts int64) error {
var err error
- p.token, hash, err = tokenize(p.name)
- if err != nil {
- return err
- }
- query := `UPDATE score SET hash = ?, flag = ?, score = ?, ts = ? WHERE name = ?;`
- _, err = p.db.Exec(query, hash, p.flag, p.score, p.ts, p.name)
+
+ query := `UPDATE user SET score = ?, flag = ?, ts = ? WHERE name = ?;`
+ _, err = p.db.Exec(query, score, flag, ts, p.name)
if err != nil {
return err
}
+ p.ts = ts
+
return nil
}
t@@ -100,7 +122,7 @@ func (p *Player) ScoreRank() int {
var count int
query := `SELECT
count(*)
- FROM score
+ FROM user
WHERE
name != ? AND (score > ? OR (score == ? AND ts <= ?))
;`
t@@ -110,31 +132,8 @@ func (p *Player) ScoreRank() int {
return count
}
-func (p *Player) FlagRank() int {
- var count int
- query := `SELECT
- count(*)
- FROM score
- WHERE
- flag >= ?
- ;`
-
- row := p.db.QueryRow(query, p.flag)
- row.Scan(&count)
- return count
-}
-
func (p *Player) FlagStr() string {
- var str [7]byte
- for i:=0; i <len(str); i++ {
- if i < p.flag {
- str[i] = 'X'
- } else {
- str[i] = '.'
- }
- }
-
- return fmt.Sprintf("%s", str)
+ return fmt.Sprintf("%2d/%d", p.flag, len(scoreboard.flag_ref))
}
func (p *Player) RankStr() string {
t@@ -163,23 +162,19 @@ func (p *Player) Exists() bool {
}
func (p *Player) Submit(flag int) error {
- if flag <= p.flag {
- return errors.New(fmt.Sprintf("Flag already set for %s", p.name))
- }
-
- if flag != p.flag + 1 {
- return errors.New(fmt.Sprintf("Missing %s flag for %s", humanize.Ordinal(p.flag + 1), p.name))
- }
-
- p.ts = time.Now().Unix()
- p.flag = flag
- p.score += 100
-
- if p.FlagRank() == 0 {
- p.score += 10 * flag
- }
-
- err := p.Update()
+ var ts int64
+ var score int
+ var flags int
+
+ // TODO: check flag existence
+ // TODO: check flag already submitted
+ // TODO: retrieve flag score
+ ts = time.Now().Unix()
+ score = p.score // + flag_score
+ flags = p.flag + 1
+
+ // update user status in database
+ err := p.Refresh(score, flags, ts)
if err != nil {
return err
}
t@@ -187,6 +182,7 @@ func (p *Player) Submit(flag int) error {
return nil
}
+/* Retrieve username from given token */
func (p *Player) FromToken(token string) error {
var err error
blob, err := base32.StdEncoding.DecodeString(token)
t@@ -205,12 +201,12 @@ func (p *Player) FromToken(token string) error {
return err
}
hash := base32.StdEncoding.EncodeToString(dk)
- query := `SELECT name,flag,score,ts FROM score WHERE name = ? AND hash = ?`
+ query := `SELECT name,flag,score,ts FROM score WHERE hash = ?`
- row := p.db.QueryRow(query, p.name, hash)
+ row := p.db.QueryRow(query, hash)
err = row.Scan(&p.name, &p.flag, &p.score, &p.ts)
if err == sql.ErrNoRows {
- return errors.New("Invalid token")
+ return errors.New("Unknown token")
}
return nil
DIR diff --git a/playerbox.go b/playerbox.go
t@@ -2,7 +2,6 @@ package main
import (
"fmt"
- //"time"
"github.com/gdamore/tcell/v2"
"github.com/rivo/tview"
"github.com/dustin/go-humanize"
t@@ -82,7 +81,7 @@ func PlayerBoxName(p *Player) *tview.TextView {
SetTextAlign(tview.AlignRight).
SetText(boxtext(playerbox)).
SetChangedFunc(func() {
- cyboard.app.Draw()
+ scoreboard.app.Draw()
}).
SetDoneFunc(func(key tcell.Key) {
if key == tcell.KeyEnter {
t@@ -90,19 +89,19 @@ func PlayerBoxName(p *Player) *tview.TextView {
if ! p.Exists() {
err := p.Register()
if err != nil {
- cyboard.Fatal(err)
+ scoreboard.Fatal(err)
}
- cyboard.HighlightBoard(p.ScoreRank())
- cyboard.GenerateHTML()
- cyboard.Popup("CONGRATULATIONS", fmt.Sprintf(TOKEN_WELCOME, p.name, p.token));
+ scoreboard.HighlightBoard(p.ScoreRank())
+ scoreboard.GenerateHTML()
+ scoreboard.Popup("CONGRATULATIONS", fmt.Sprintf(TOKEN_WELCOME, p.name, p.token));
} else {
- cyboard.Popup("NOPE", "Player name unavailable\nPlease pick another one")
+ scoreboard.Popup("NOPE", "Player name unavailable\nPlease pick another one")
}
}
})
v.Focus(func(p tview.Primitive) {
- v.SetText(fmt.Sprintf("%4d ", cyboard.player.score))
+ v.SetText(fmt.Sprintf("%4d ", scoreboard.player.score))
})
v.SetInputCapture(manipulatebox)
DIR diff --git a/ui.go b/ui.go
t@@ -30,7 +30,8 @@ func BoardHeader() *tview.TextView {
// Optionally padded with "placeholder" lines
func RankTable(offset, limit, rank int, fill bool) *tview.Table {
t := tview.NewTable()
- players, err := db_ranked_players(cyboard.db, offset, limit)
+
+ players, err := db_ranked_players(scoreboard.db, offset, limit)
if err != nil {
panic(err)
}
t@@ -60,10 +61,10 @@ func RankTable(offset, limit, rank int, fill bool) *tview.Table {
bsize := int(math.Max(float64(BOARD_HEIGHT), float64(limit)))
for i:=t.GetRowCount(); i<bsize; i++ {
rankstr := fmt.Sprintf("%4s", humanize.Ordinal(rank + i + 1))
- scorestr := fmt.Sprintf("%4d", 0)
+ scorestr := fmt.Sprintf("%5d", 0)
t.SetCell(i, 0, newcell(rankstr).SetTextColor(tcell.ColorGray))
t.SetCell(i, 1, newcell("AAA").SetTextColor(tcell.ColorGray))
- t.SetCell(i, 2, newcell(".......").SetTextColor(tcell.ColorGray))
+ t.SetCell(i, 2, newcell(".....").SetTextColor(tcell.ColorGray))
t.SetCell(i, 3, newcell(scorestr).SetTextColor(tcell.ColorGray))
}
}