SQL injection and XSS prevention in HLD - System Design Guide

Problem Statement

When user input is directly included in database queries or web pages without proper checks, attackers can inject malicious SQL commands or scripts. This leads to data theft, unauthorized access, or defacement of websites, causing severe security breaches and loss of user trust.

Solution

To prevent these attacks, user inputs must be treated as data, not code. For SQL injection, use parameterized queries or prepared statements that separate code from data. For XSS, sanitize and encode user inputs before displaying them on web pages, ensuring scripts cannot execute in the browser.

Architecture

User Input

→Input Validation

↓

Parameterized SQL

Parameterized SQL

↓

Database

Database

↓

User Input

→Input Sanitizer

↓

Output Encoding

Output Encoding

↓

Browser

Browser

The diagram shows two flows: one for SQL injection prevention using parameterized queries, and one for XSS prevention using input sanitization and output encoding before rendering in the browser.

Trade-offs

✓ Pros

→

Protects sensitive data and prevents unauthorized database access.

→

Prevents malicious scripts from running in users' browsers, safeguarding user experience.

→

Improves overall system security posture and compliance with security standards.

→

Reduces risk of costly data breaches and reputation damage.

✗ Cons

→

Requires careful implementation and consistent use across all input points.

→

May introduce performance overhead due to input validation and encoding.

→

Complexity increases when handling diverse input types and legacy codebases.

Always use in any system accepting user input, especially web applications with database interactions and dynamic content rendering. Critical when handling sensitive data or operating at scale with many users.

Not applicable to systems that do not process external user input or do not interact with databases or web content, such as internal batch processing without user interfaces.

Real World Examples

Netflix

Uses parameterized queries to prevent SQL injection in their user data management systems, ensuring secure handling of subscription and viewing data.

Twitter/X

Implements strict input sanitization and output encoding to prevent XSS attacks in tweets and user profiles, protecting users from malicious scripts.

Amazon

Employs prepared statements and content security policies to prevent injection attacks and XSS in their e-commerce platform, securing transactions and user reviews.

Code Example

The before code directly inserts user input into SQL queries and HTML, allowing attackers to inject malicious code. The after code uses parameterized queries to separate data from code in SQL, and escapes HTML special characters to prevent script execution in the browser.

HLD

Before (vulnerable to SQL injection):

user_id = input("Enter user ID:")
query = f"SELECT * FROM users WHERE id = {user_id}"
cursor.execute(query)

After (using parameterized queries):

user_id = input("Enter user ID:")
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))


Before (vulnerable to XSS):

comment = request.GET.get('comment')
html = f"<div>{comment}</div>"
return html

After (using sanitization and encoding):

import html
comment = request.GET.get('comment')
safe_comment = html.escape(comment)
html = f"<div>{safe_comment}</div>"
return html

Alternatives

Stored Procedures

Encapsulates SQL logic in the database, reducing direct query construction in application code.

Use when: When database supports stored procedures and you want centralized query logic with controlled input parameters.

Content Security Policy (CSP)

Browser-level security that restricts sources of executable scripts to prevent XSS.

Use when: When you want an additional layer of defense against XSS beyond input sanitization.

ORM (Object-Relational Mapping)

Abstracts database queries into object methods, often using parameterized queries internally.

Use when: When using high-level frameworks that support ORM to reduce manual query construction.

Summary

SQL injection and XSS attacks exploit improper handling of user input to execute malicious code.

Using parameterized queries and output encoding effectively separates code from data, preventing these attacks.

Consistent implementation of these practices is critical for secure, trustworthy web applications.

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