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

Practice

1. Which of the following is the best way to prevent SQL injection attacks in a web application?

easy

A. Escape HTML tags in user input

B. Use parameterized queries or prepared statements

C. Disable JavaScript in the browser

D. Use client-side validation only

Solution

1. Step 1: Understand SQL injection cause

   SQL injection happens when user input is directly included in SQL queries without safe handling.

2. Step 2: Identify prevention method

   Parameterized queries separate code from data, preventing attackers from injecting SQL commands.

3. Final Answer:

   Use parameterized queries or prepared statements -> Option B

4. Quick Check:

   SQL injection prevention = parameterized queries [OK]

Hint: Always separate code and data with parameterized queries [OK]

Common Mistakes:

• Relying only on client-side validation
• Escaping HTML instead of SQL input
• Disabling JavaScript does not stop SQL injection

2. Which syntax correctly uses a parameterized query to prevent SQL injection in a typical SQL library?

easy

A. cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,))

B. cursor.execute('SELECT * FROM users WHERE name = ' + user_input)

C. cursor.execute(f'SELECT * FROM users WHERE name = {user_input}')

D. cursor.execute('SELECT * FROM users WHERE name = ' + str(user_input))

Solution

1. Step 1: Identify parameterized query syntax

   Parameterized queries use placeholders like '?' and pass parameters separately to avoid injection.

2. Step 2: Check each option

   cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,)) uses '?' placeholder and passes parameters as a tuple, which is correct syntax.

3. Final Answer:

   cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,)) -> Option A

4. Quick Check:

   Parameterized query syntax = cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,)) [OK]

Hint: Look for placeholders and separate parameter tuple [OK]

Common Mistakes:

• Concatenating strings directly with user input
• Using f-strings without sanitization
• Passing parameters inside the query string

3. Given the following code snippet, what will be the output if user_input = "" and the output is rendered in HTML without escaping?

def render_comment(user_input):
    return f"<p>{user_input}</p>"

print(render_comment(user_input))

medium

A. SyntaxError due to unescaped quotes

B. <p>&lt;script&gt;alert('XSS')&lt;/script&gt;</p> (script shown as text)

C. <p><script>alert('XSS')</script></p> (script runs in browser)

D. Empty output because script tags are removed

Solution

1. Step 1: Understand output without escaping

   The function inserts raw user input inside paragraph tags without escaping HTML special characters.

2. Step 2: Predict browser behavior

   Browser interprets <script> tags and runs the alert, causing an XSS attack.

3. Final Answer:

   <p><script>alert('XSS')</script></p> (script runs in browser) -> Option C

4. Quick Check:

   Unescaped HTML input = script runs [OK]

Hint: Unescaped HTML input allows scripts to run [OK]

Common Mistakes:

• Assuming script tags are automatically escaped
• Confusing HTML escaping with SQL escaping
• Expecting syntax errors from user input

4. A developer wrote this code to prevent XSS:

def safe_render(user_input):
    return f"<p>{user_input.replace('<', '<').replace('>', '>')}</p>"

But users report that some scripts still run. What is the likely cause?

medium

A. The replace method does not work on strings

B. The function should remove all HTML tags instead of replacing

C. The code should use double quotes instead of single quotes

D. Only replacing < and > is insufficient; other characters like quotes must be escaped

Solution

1. Step 1: Analyze current escaping

   The code replaces only < and > but leaves quotes and other characters unescaped.

2. Step 2: Understand XSS vectors

   Attackers can use quotes or other entities to break out of attributes or inject scripts.

3. Final Answer:

   Only replacing < and > is insufficient; other characters like quotes must be escaped -> Option D

4. Quick Check:

   Partial escaping = incomplete XSS prevention [OK]

Hint: Escape all special HTML chars, not just < and > [OK]

Common Mistakes:

• Thinking replacing < and > is enough
• Believing string replace fails silently
• Ignoring quotes and ampersands in input

5. You are designing a web app that stores user comments and displays them safely. Which combination of techniques best prevents both SQL injection and XSS attacks?

hard

A. Use parameterized queries for database and escape HTML output with a Content Security Policy (CSP)

B. Sanitize user input by removing all special characters before storing

C. Use client-side JavaScript to validate input and disable scripts in browser settings

D. Store raw input and rely on HTTPS to secure data transmission

Solution

1. Step 1: Prevent SQL injection

   Use parameterized queries to safely handle user input in database commands.

2. Step 2: Prevent XSS attacks

   Escape HTML output when rendering user content and enforce a Content Security Policy to restrict script execution.

3. Final Answer:

   Use parameterized queries for database and escape HTML output with a Content Security Policy (CSP) -> Option A

4. Quick Check:

   SQL injection + XSS prevention = parameterized queries + escaping + CSP [OK]

Hint: Combine safe DB queries with output escaping and CSP [OK]

Common Mistakes:

• Removing all special characters breaks user input
• Relying only on client-side validation
• Ignoring output escaping and security headers