Phase 9 — Security (SQL Injection & Least Privilege)

Overview

This section explains how security is enforced in the Wedding Planner database system, with a focus on SQL injection prevention and the principle of least privilege. Security is implemented through a combination of database-level controls, safe query practices, and role-based access management in PostgreSQL.

What we cover in this phase

• SQL injection prevention through parameterized queries
• Database-level protection using constraints
• Role-based access control (RBAC)
• Least privilege principle for database users

SQL Injection Prevention

Description

SQL injection occurs when user input is directly concatenated into SQL queries, allowing attackers to manipulate query logic and access or modify unauthorized data.

Examples include:

• bypassing authentication
• reading private wedding or user data
• deleting bookings or entire tables

Example: Unsafe vs Safe Query

Unsafe (vulnerable to SQL injection)

SELECT * FROM "user"
WHERE email = 'user_input';

If user_input = 'x' OR '1'='1', all users are returned.

Safe (parameterized query)

SELECT * FROM "user"
WHERE email = $1;

Reason for use

Parameterized queries ensure that malicious input is always treated as data, not executable SQL code.

Input Validation and Domain Constraints

While application-level validation is applied, the database also enforces security-related constraints:

• NOT NULL on mandatory fields (email, names)
• UNIQUE constraints to prevent duplicate accounts
• CHECK constraints to prevent invalid values

These rules ensure that even if malicious input reaches the database, it cannot violate domain rules.

Principle of Least Privilege

Concept

The principle of least privilege states that each user or service should have only the minimum permissions required to perform its tasks.

In the Wedding Planner system, different database roles are created for different responsibilities.

Role-Based Access Control (RBAC)

Roles used in our application:

• wp_app_user – used by the application backend
• wp_admin – used only for schema migrations and maintenance
• wp_readonly – used for reporting or analytics

Example

CREATE ROLE wp_app_user LOGIN PASSWORD 'strong_password';

GRANT CONNECT ON DATABASE wedding_planner TO wp_app_user;
GRANT USAGE ON SCHEMA public TO wp_app_user;

GRANT SELECT, INSERT, UPDATE, DELETE
ON "user", wedding, venue, venue_booking
TO wp_app_user;

Explanation

This role represents the database user used by the application backend. It is allowed to connect to the database and use the public schema, but its privileges are strictly limited to data manipulation on specific application tables.

Reason for use

This approach enforces the principle of least privilege and reduces the impact of:

• application bugs
• SQL injection vulnerabilities
• compromised application credentials

Even if the application role is abused, attackers cannot DROP tables, ALTER constraints/triggers, or access data outside the permitted scope.

Protecting Sensitive Data

Example 1: Unauthorized Data Access Prevention

In the Wedding Planner system, sensitive data protection is enforced by ensuring that queries are always scoped to the authenticated user and never rely on client-supplied identifiers alone.

Example endpoint:

GET /weddings/{id}/guests

Potential attack scenario: An attacker could attempt to access another user’s wedding by guessing or manipulating the wedding ID.

To prevent this, the database query always filters by both wedding ID and the authenticated user ID.

SQL code

SELECT g.*
FROM guest g
JOIN wedding w ON w.wedding_id = g.wedding_id
WHERE g.wedding_id = $1
  AND w.user_id = $2;

Explanation $1 represents the wedding ID from the request, while $2 represents the authenticated user’s ID.

Reason for use If the wedding does not belong to the user, the query returns no results, keeping data secure.

Example 2: Availability Query Without Sensitive Data Exposure

Example endpoint:

GET /availability/venue?date=2026-06-10

Potential attack scenario: An attacker could try to infer booking details for venues by repeatedly