SQL injection and XSS prevention in HLD - Architecture Diagram

System Overview - SQL injection and XSS prevention

This system protects a web application from SQL injection and Cross-Site Scripting (XSS) attacks. It ensures that user inputs are safely handled before reaching the database or being displayed on web pages. The key requirements are to validate and sanitize inputs, use secure query methods, and encode outputs to prevent malicious code execution.

Architecture Diagram

User
  |
  v
Web Application Firewall (WAF)
  |
  v
Load Balancer
  |
  v
API Gateway
  |
  v
Input Validation & Sanitization Service
  |
  +---------------------+
  |                     |
  v                     v
Parameterized Query Service  Output Encoding Service
  |                     |
  v                     v
Database               Web Server
  |                     |
  +----------+----------+
             |
             v
           Cache

Components

User

user

Sends requests with inputs to the web application

Web Application Firewall (WAF)

firewall

Filters malicious traffic and blocks common attack patterns

Load Balancer

load_balancer

Distributes incoming requests evenly to backend services

API Gateway

api_gateway

Routes requests to appropriate backend services and enforces security policies

Input Validation & Sanitization Service

service

Checks and cleans user inputs to remove harmful content

Parameterized Query Service

service

Executes database queries using safe parameterized statements to prevent SQL injection

Output Encoding Service

service

Encodes data before sending to the web server to prevent XSS attacks

Database

database

Stores application data securely

Web Server

service

Delivers web pages to users with encoded safe content

Cache

cache

Stores frequently accessed data to improve response time

Request Flow - 12 Hops

User → Web Application Firewall (WAF)

Web Application Firewall (WAF) → Load Balancer

Load Balancer → API Gateway

API Gateway → Input Validation & Sanitization Service

Input Validation & Sanitization Service → Parameterized Query Service

Parameterized Query Service → Cache

Cache → Parameterized Query Service

Parameterized Query Service → Database

Database → Parameterized Query Service

Parameterized Query Service → Output Encoding Service

Output Encoding Service → Web Server

Web Server → User

Failure Scenario

Component Fails:Input Validation & Sanitization Service

Impact:Malicious inputs may reach the database or web server, causing SQL injection or XSS attacks.

Mitigation:Use Web Application Firewall (WAF) as a secondary filter and implement parameterized queries and output encoding as defense-in-depth.

Architecture Quiz - 3 Questions

Which component ensures that user inputs do not contain harmful SQL code?

A Input Validation & Sanitization Service

B Output Encoding Service

C Cache

D Web Server

Design Principle

This architecture uses multiple layers of defense including input validation, parameterized queries, output encoding, and caching to prevent SQL injection and XSS attacks while maintaining performance and security.

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