SQL injection and XSS prevention in HLD - System Design Exercise

Design: Web Application Security: SQL Injection and XSS Prevention

Focus on design of input validation, sanitization, and secure coding practices to prevent SQL injection and XSS. Out of scope: detailed cryptography, network security layers, or physical security.

Functional Requirements

FR1: Prevent attackers from injecting malicious SQL commands through user inputs

FR2: Prevent attackers from injecting malicious scripts (XSS) that run in users' browsers

FR3: Ensure all user inputs are validated and sanitized before processing

FR4: Provide secure authentication and authorization to protect sensitive data

FR5: Log and monitor suspicious activities related to injection attempts

Non-Functional Requirements

NFR1: Handle up to 10,000 concurrent users securely

NFR2: API response latency p99 under 200ms including security checks

NFR3: Availability target of 99.9% uptime

NFR4: Support modern browsers and mobile clients

NFR5: Compliance with OWASP Top 10 security standards

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Input validation and sanitization module

Parameterized queries or prepared statements

Web application firewall (WAF)

Content Security Policy (CSP) headers

Security logging and monitoring system

Design Patterns

Parameterized queries to prevent SQL injection

Output encoding to prevent XSS

Whitelist input validation

Use of security headers (CSP, X-Content-Type-Options)

Defense in depth with layered security controls

Reference Architecture

Client Browser
   |
   | HTTP Requests with user inputs
   v
Web Application Server
   |-- Input Validation & Sanitization Module
   |-- Parameterized Query Executor
   |-- Output Encoding Module
   |-- Security Headers Middleware (CSP, etc.)
   |
   v
Database Server

Security Logging & Monitoring System (receives logs from Web App Server)

Web Application Firewall (WAF) sits in front of Web Application Server

Components

Input Validation & Sanitization Module

Custom code or security libraries

Check and clean user inputs to remove or reject malicious content

Parameterized Query Executor

Database drivers supporting prepared statements (e.g., JDBC, PDO)

Execute database queries safely by separating code from data

Output Encoding Module

Template engines or encoding libraries

Encode data before rendering in HTML to prevent script execution

Security Headers Middleware

Web framework middleware

Add HTTP headers like Content Security Policy to restrict script sources

Web Application Firewall (WAF)

Cloudflare, AWS WAF, or open-source WAF

Filter and block malicious HTTP requests before reaching the app

Security Logging & Monitoring System

SIEM tools like Splunk or ELK stack

Collect and analyze logs to detect injection attempts

Request Flow

1. User submits input via browser form or API call

2. Web Application Server receives input and passes it to Input Validation & Sanitization Module

3. If input is invalid or suspicious, reject request and log event

4. If valid, use Parameterized Query Executor to safely query or update the database

5. Encode any data sent back to client using Output Encoding Module

6. Add security headers via middleware before sending response

7. WAF monitors incoming requests and blocks known attack patterns

8. Security Logging & Monitoring System collects logs from app and WAF for analysis

Database Schema

No special schema changes required. Use existing relational schema. Ensure all queries use parameterized statements to separate data from commands.

Scaling Discussion

Bottlenecks

Input validation and sanitization can add latency under high load

Logging and monitoring systems may become overwhelmed with large volumes of security events

WAF throughput limits may restrict request handling capacity

Database connection pool saturation due to increased safe query executions

Solutions

Optimize validation logic and use asynchronous processing where possible

Implement log sampling and aggregation to reduce volume sent to monitoring

Scale WAF horizontally or use cloud-managed scalable WAF services

Increase database connection pool size and use read replicas for load distribution

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying scope, 20 minutes designing the architecture and data flow, 10 minutes discussing scaling and security trade-offs, 5 minutes summarizing.

Emphasize importance of separating code and data with parameterized queries

Highlight layered defense: validation, encoding, headers, WAF

Discuss trade-offs between strict validation and user experience

Mention monitoring and alerting as critical for ongoing security

Show awareness of performance impact and scaling strategies