Spaces:
Sleeping
Sleeping
File size: 3,869 Bytes
cb16781 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | -- 014_recreate_web_exploitation.sql
-- Re-creates the `web_exploitation_challenges` table that was dropped in
-- migration 013. This version is Red-Team-only (team_role = 'red') and
-- stores a complete web exploitation scenario that includes:
-- - A realistic HTTP request/response pair (raw strings)
-- - The vulnerability type / class / description
-- - The expected payload (flag or exploit string)
-- - A hint of what to look for in the intercepted traffic
--
-- The AI generator must produce diverse scenarios across XSS, SQLi, CSRF,
-- SSRF, IDOR, LFI, XXE, Command Injection, Auth Bypass, and File Upload.
BEGIN;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS public.web_exploitation_challenges (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
team_role text NOT NULL DEFAULT 'red'
CHECK (team_role IN ('red')),
module text NOT NULL DEFAULT 'web-exploitation',
topic text NOT NULL,
title text NOT NULL,
story text NOT NULL,
task_outline text NOT NULL,
-- Describes the vulnerable endpoint / scenario
vulnerability_type text NOT NULL,
vulnerability_class text NOT NULL,
vulnerability_description text NOT NULL,
-- The raw HTTP exchange (simulated)
http_request text NOT NULL,
http_response text NOT NULL,
-- The expected answer (flag or exploit payload)
flag_preview text NOT NULL,
flag_hash text NOT NULL,
-- Hints (3 levels, jsonb)
hints jsonb DEFAULT '[]'::jsonb,
difficulty text NOT NULL
CHECK (difficulty IN ('مبتدئ','سهل','متوسط','صعب','خبير')),
xp_reward integer DEFAULT 200,
created_at timestamptz DEFAULT now()
);
-- Fast pool / dashboard queries
CREATE INDEX IF NOT EXISTS idx_web_exploit_topic ON public.web_exploitation_challenges(topic);
CREATE INDEX IF NOT EXISTS idx_web_exploit_vuln ON public.web_exploitation_challenges(vulnerability_type);
CREATE INDEX IF NOT EXISTS idx_web_exploit_diff ON public.web_exploitation_challenges(difficulty);
-- RLS — open read for anon, insert/delete for service role
ALTER TABLE public.web_exploitation_challenges ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Open read web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Open read web_exp" ON public.web_exploitation_challenges
FOR SELECT USING (true);
DROP POLICY IF EXISTS "Service role insert web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Service role insert web_exp" ON public.web_exploitation_challenges
FOR INSERT WITH CHECK (true);
DROP POLICY IF EXISTS "Service role delete web_exp" ON public.web_exploitation_challenges;
CREATE POLICY "Service role delete web_exp" ON public.web_exploitation_challenges
FOR DELETE USING (true);
-- Update the v_challenge_type_consistency view to include web_exploitation
DROP VIEW IF EXISTS v_challenge_type_consistency;
CREATE VIEW v_challenge_type_consistency AS
SELECT 'encryption_challenges' AS table_name, COUNT(*) AS bad_rows
FROM encryption_challenges WHERE module <> 'crypto'
UNION ALL
SELECT 'code_fixing_challenges', COUNT(*)
FROM code_fixing_challenges WHERE module <> 'code-fixing'
UNION ALL
SELECT 'log_analysis_challenges', COUNT(*)
FROM log_analysis_challenges WHERE module <> 'log-analysis'
UNION ALL
SELECT 'vulnerability_hunter_challenges', COUNT(*)
FROM vulnerability_hunter_challenges WHERE module <> 'vulnerability-hunter'
UNION ALL
SELECT 'web_exploitation_challenges', COUNT(*)
FROM web_exploitation_challenges WHERE module <> 'web-exploitation';
COMMIT;
|