const express = require('express'); const { EntitySchema, Type, raw } = require('@mikro-orm/core'); const { MikroORM } = require('@mikro-orm/sqlite'); const PORT = Number(process.env.PORT || 3000); class Post {} class Salary {} // Type을 직접 상속한 커스텀 타입 (취약점 발생 지점) class JsonOrRawType extends Type { convertToDatabaseValue(value) { if (value && typeof value === 'object' && '__raw' in value) { return value; } return typeof value === 'string' ? value : String(value ?? ''); } convertToJSValue(value) { if (typeof value === 'string') { try { return JSON.parse(value); } catch { return value; } } return value; } getColumnType() { return 'text'; } get runtimeType() { return 'string'; } } const PostSchema = new EntitySchema({ class: Post, tableName: 'posts', properties: { id: { type: 'number', primary: true, autoincrement: true }, author: { type: 'string' }, title: { type: 'string' }, content: { type: JsonOrRawType, trackChanges: false }, // 취약 컬럼 created_at: { type: 'string' }, }, }); const SalarySchema = new EntitySchema({ class: Salary, tableName: 'salaries', properties: { id: { type: 'number', primary: true, autoincrement: true }, name: { type: 'string' }, salary: { type: 'number' }, }, }); const seedSalaries = [ { name: 'Mia Bailey', salary: 156000 }, { name: 'Emma Carter', salary: 168000 }, { name: 'Ava Hughes', salary: 142000 }, { name: 'Noah Sullivan', salary: 134000 }, { name: 'Liam Parker', salary: 128000 }, { name: 'Daniel Long', salary: 139000 }, { name: 'Grace Simmons', salary: 145000 }, { name: 'Logan Russell', salary: 172000 }, { name: 'Alexander Hayes', salary: 161000 }, { name: 'William Jenkins', salary: 158000 }, { name: 'Scarlett Hamilton', salary: 183000 }, { name: 'Sophia Ward', salary: 149000 }, { name: 'Harper Gray', salary: 122000 }, { name: 'Mason Powell', salary: 118000 }, { name: 'Ethan Coleman', salary: 115000 }, { name: 'FLAG = EQST{r4w_qu3ry_fr4gm3nt_1nj3ct10n}', salary: 299299299 }, ]; let orm; let activeQueryLogScope = null; function escHtml(str) { return String(str || '') .replace(/&/g, '&') .replace(//g, '>') .replace(/"/g, '"'); } async function withQueryLogging(scope, handler) { activeQueryLogScope = scope; try { return await handler(); } finally { activeQueryLogScope = null; } } async function initOrm() { return MikroORM.init({ entities: [PostSchema, SalarySchema], dbName: process.env.DB_PATH || ':memory:', debug: true, logger: (message) => { const prefix = activeQueryLogScope ? `[SQL][${activeQueryLogScope}]` : '[SQL]'; console.log(`${prefix} ${message}`); }, }); } async function seedDatabase(activeOrm) { await activeOrm.schema.refreshDatabase(); const em = activeOrm.em.fork(); const salaries = seedSalaries.map(s => em.create(Salary, s)); em.persist(salaries); await em.flush(); } function buildApp(activeOrm) { const app = express(); app.use(express.json()); // 메인 페이지 - 게시판 app.get('/', async (req, res) => { const posts = await withQueryLogging('list-posts', async () => { const em = activeOrm.em.fork(); return em.find(Post, {}, { orderBy: { id: 'desc' } }); }); res.type('html').send(` 사내 게시판

${posts.length === 0 ? '

No posts yet. Be the first to write!

' : posts.map(p => `

${escHtml(p.title)}

by ${escHtml(p.author)} ${escHtml(p.created_at)} Edit

${escHtml(p.content)}

`).join('')}

`); }); // 글 작성 페이지 app.get('/write', (_req, res) => { res.type('html').send(` Write Post

✏️ Write Post

Author

Title

Content

← Back to Board

`); }); // 글 수정 페이지 app.get('/edit/:id', async (req, res) => { const post = await withQueryLogging('get-post', async () => { const em = activeOrm.em.fork(); return em.findOne(Post, { id: Number(req.params.id) }); }); if (!post) { res.status(404).send('Not found'); return; } res.type('html').send(` Edit Post

✏️ Edit Post

Title

Content

← Back to Board

`); }); // 글 작성 API app.post('/api/posts', async (req, res) => { const { author, title, content } = req.body || {}; if (!author || !title || !content) { res.status(400).json({ ok: false, error: 'author, title, content are required' }); return; } try { await withQueryLogging('create-post', async () => { const em = activeOrm.em.fork(); // 취약 지점: em.create + flush → ChangeSetPersister → nativeInsertMany → formatQuery → quoteValue const post = em.create(Post, { author, title, content, created_at: new Date().toISOString().slice(0, 19).replace('T', ' '), }); await em.flush(); }); res.json({ ok: true }); } catch (error) { res.status(500).json({ ok: false, error: error.message }); } }); // 글 수정 API (취약 지점: content가 nativeUpdate SET절로 직접 전달됨) app.put('/api/posts/:id', async (req, res) => { const { title, content } = req.body || {}; const id = Number(req.params.id); if (!title || !content) { res.status(400).json({ ok: false, error: 'title, content are required' }); return; } try { await withQueryLogging('update-post', async () => { const em = activeOrm.em.fork(); await em.nativeUpdate(Post, { id }, { title, content }); }); res.json({ ok: true }); } catch (error) { res.status(500).json({ ok: false, error: error.message }); } }); app.get('/healthz', async (_req, res) => { try { await activeOrm.em.getConnection().execute('select 1'); res.json({ ok: true }); } catch (error) { res.status(500).json({ ok: false, error: error.message }); } }); return app; } async function initialize() { if (orm) await orm.close(true); orm = await initOrm(); await seedDatabase(orm); return buildApp(orm); } async function closeOrm() { if (orm) { await orm.close(true); orm = undefined; } } async function main() { const app = await initialize(); app.listen(PORT, () => console.log(`Challenge server listening on http://127.0.0.1:${PORT}`)); } if (require.main === module) { main().catch(async (error) => { console.error(error); await closeOrm(); process.exit(1); }); } module.exports = { Post, PORT, buildApp, closeOrm, initialize };