🛡️ SQL Security

Amazon & Microsoft Interview Deep Dive

🏢 Amazon🏢 Microsoft⚡ Difficulty: Medium-Hard⏱️ 35 min

📋 Interview Question

ℹ️🔴 Amazon/Microsoft Interview Question

"Demonstrate SQL injection vulnerabilities and prevention techniques. Implement: 1) Parameterized queries, 2) Input validation, 3) Role-based access control (RBAC), 4) Audit logging for sensitive operations."

Companies: Amazon, Microsoft | Difficulty: Medium-Hard | Time: 35 minutes

📊 Setup: Users and Products

-- Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE,
    password_hash VARCHAR(200),
    email VARCHAR(200),
    role VARCHAR(20) DEFAULT 'user',  -- admin, user, readonly
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200),
    price DECIMAL(10, 2),
    stock INT DEFAULT 0,
    created_by INT REFERENCES users(user_id)
);

-- Audit log
CREATE TABLE audit_log (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    table_name VARCHAR(100),
    record_id INT,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Permissions table
CREATE TABLE permissions (
    permission_id SERIAL PRIMARY KEY,
    role VARCHAR(20),
    table_name VARCHAR(100),
    can_select BOOLEAN DEFAULT false,
    can_insert BOOLEAN DEFAULT false,
    can_update BOOLEAN DEFAULT false,
    can_delete BOOLEAN DEFAULT false
);

-- Insert sample data
INSERT INTO users (username, password_hash, email, role) VALUES
('admin', '$2b$12$hash...', 'admin@example.com', 'admin'),
('alice', '$2b$12$hash...', 'alice@example.com', 'user'),
('bob', '$2b$12$hash...', 'bob@example.com', 'readonly');

INSERT INTO permissions (role, table_name, can_select, can_insert, can_update, can_delete) VALUES
('admin', 'products', true, true, true, true),
('user', 'products', true, true, false, false),
('readonly', 'products', true, false, false, false),
('admin', 'users', true, true, true, true),
('user', 'users', true, false, false, false);

⚠️ Part 1: SQL Injection Vulnerabilities

⚠️⚠️ SQL Injection

SQL injection occurs when user input is directly concatenated into SQL queries. Attackers can manipulate queries to:

Bypass authentication
Access unauthorized data
Modify or delete data
Execute administrative operations

Vulnerable Code Examples

-- VULNERABLE: String concatenation
-- User input: username = 'admin' OR '1'='1'
-- Query becomes: SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'anything'

-- This ALWAYS returns a row, bypassing authentication!

-- VULNERABLE: Dynamic SQL
CREATE OR REPLACE FUNCTION unsafe_search(p_search TEXT)
RETURNS TABLE (product_name VARCHAR, price DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT product_name, price FROM products WHERE product_name LIKE ''%' || p_search || '%''';
END;
$$;

-- Attack: p_search = '; DROP TABLE products; --
-- Query becomes: SELECT ... WHERE product_name LIKE '%'; DROP TABLE products; --%'

-- VULNERABLE: ORDER BY injection
CREATE OR REPLACE FUNCTION unsafe_sort(p_sort_column TEXT)
RETURNS TABLE (product_name VARCHAR, price DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT product_name, price FROM products ORDER BY ' || p_sort_column;
END;
$$;

-- Attack: p_sort_column = 'product_name; DROP TABLE products; --'

🔒 Part 2: Parameterized Queries

💡✅ Parameterized Queries

Parameterized queries separate SQL code from data, preventing injection by treating user input as values, not executable code.

PostgreSQL Parameterized Queries

-- SAFE: Using parameters
PREPARE safe_search AS
SELECT product_name, price
FROM products
WHERE product_name LIKE $1;

EXECUTE safe_search('%laptop%');

-- SAFE: Dynamic SQL with proper escaping
CREATE OR REPLACE FUNCTION safe_search_function(p_search TEXT)
RETURNS TABLE (product_name VARCHAR, price DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE
        'SELECT product_name, price FROM products WHERE product_name LIKE $1'
        USING '%' || p_search || '%';
END;
$$;

-- SAFE: Parameterized ORDER BY (whitelist approach)
CREATE OR REPLACE FUNCTION safe_sort(p_sort_column TEXT)
RETURNS TABLE (product_name VARCHAR, price DECIMAL)
LANGUAGE plpgsql
AS $$
DECLARE
    v_valid_columns TEXT[] := ARRAY['product_name', 'price', 'stock'];
BEGIN
    -- Whitelist validation
    IF p_sort_column != ALL(v_valid_columns) THEN
        RAISE EXCEPTION 'Invalid sort column: %', p_sort_column;
    END IF;

    RETURN QUERY EXECUTE
        format('SELECT product_name, price FROM products ORDER BY %I', p_sort_column);
END;
$$;

Application-Level Examples

# Python example with psycopg2 (SAFE)
import psycopg2

def get_user_safe(username, password):
    conn = psycopg2.connect("dbname=mydb")
    cur = conn.cursor()

    # Parameterized query - SAFE
    cur.execute(
        "SELECT * FROM users WHERE username = %s AND password_hash = %s",
        (username, password)
    )
    return cur.fetchone()

# Node.js example with pg (SAFE)
const { Pool } = require('pg');
const pool = new Pool();

async function getUserSafe(username, password) {
    const result = await pool.query(
        'SELECT * FROM users WHERE username = $1 AND password_hash = $2',
        [username, password]
    );
    return result.rows[0];
}

🛡️ Part 3: Input Validation

-- Input validation function
CREATE OR REPLACE FUNCTION validate_input(
    p_input TEXT,
    p_max_length INT DEFAULT 255,
    p_allow_special BOOLEAN DEFAULT false
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_clean TEXT;
BEGIN
    -- Check for NULL
    IF p_input IS NULL THEN
        RETURN NULL;
    END IF;

    -- Trim whitespace
    v_clean := trim(p_input);

    -- Check length
    IF length(v_clean) > p_max_length THEN
        RAISE EXCEPTION 'Input exceeds maximum length of %', p_max_length;
    END IF;

    -- Block common SQL injection patterns
    IF v_clean ~* '(;|''|\"|--|/\*|\*/|union|select|insert|update|delete|drop|exec|execute)' THEN
        RAISE EXCEPTION 'Invalid characters detected in input';
    END IF;

    -- Allow only alphanumeric if special chars not allowed
    IF NOT p_allow_special AND v_clean ~ '[^a-zA-Z0-9\s@.\-_]' THEN
        RAISE EXCEPTION 'Special characters not allowed';
    END IF;

    RETURN v_clean;
END;
$$;

-- Email validation
CREATE OR REPLACE FUNCTION validate_email(p_email TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$;

-- Usage
SELECT validate_input('laptop'' OR ''1''=''1');  -- Raises exception
SELECT validate_input('laptop');  -- Returns 'laptop'
SELECT validate_email('user@example.com');  -- Returns true

👥 Part 4: Role-Based Access Control (RBAC)

-- Check permissions function
CREATE OR REPLACE FUNCTION check_permission(
    p_role VARCHAR,
    p_table VARCHAR,
    p_operation VARCHAR
)
RETURNS BOOLEAN
LANGUAGE sql
AS $$
    SELECT CASE p_operation
        WHEN 'SELECT' THEN can_select
        WHEN 'INSERT' THEN can_insert
        WHEN 'UPDATE' THEN can_update
        WHEN 'DELETE' THEN can_delete
        ELSE false
    END
    FROM permissions
    WHERE role = p_role AND table_name = p_table;
$$;

-- Secure view using RBAC
CREATE OR REPLACE FUNCTION get_products(p_user_id INT)
RETURNS TABLE (
    product_id INT,
    product_name VARCHAR,
    price DECIMAL,
    stock INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_role VARCHAR;
BEGIN
    -- Get user's role
    SELECT role INTO v_role
    FROM users WHERE user_id = p_user_id AND is_active = true;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User not found or inactive';
    END IF;

    -- Check permission
    IF NOT check_permission(v_role, 'products', 'SELECT') THEN
        RAISE EXCEPTION 'Insufficient permissions';
    END IF;

    -- Return data based on role
    RETURN QUERY
    SELECT
        p.product_id,
        p.product_name,
        p.price,
        CASE
            WHEN v_role = 'admin' THEN p.stock
            ELSE NULL  -- Non-admins don't see stock
        END AS stock
    FROM products p;
END;
$$;

-- Usage
SELECT * FROM get_products(1);  -- Admin sees all
SELECT * FROM get_products(2);  -- User sees limited

📝 Part 5: Audit Logging

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (user_id, action, table_name, record_id, new_values)
        VALUES (
            current_setting('app.current_user_id', true)::INT,
            'INSERT',
            TG_TABLE_NAME,
            NEW.product_id,
            to_jsonb(NEW)
        );
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (user_id, action, table_name, record_id, old_values, new_values)
        VALUES (
            current_setting('app.current_user_id', true)::INT,
            'UPDATE',
            TG_TABLE_NAME,
            NEW.product_id,
            to_jsonb(OLD),
            to_jsonb(NEW)
        );
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (user_id, action, table_name, record_id, old_values)
        VALUES (
            current_setting('app.current_user_id', true)::INT,
            'DELETE',
            TG_TABLE_NAME,
            OLD.product_id,
            to_jsonb(OLD)
        );
        RETURN OLD;
    END IF;
END;
$$;

-- Create audit triggers
CREATE TRIGGER trg_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_func();

-- Set current user for audit
SET app.current_user_id = '1';

🔐 Part 6: Password Security

-- Password hashing (using pgcrypto)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash password
INSERT INTO users (username, password_hash, email, role)
VALUES (
    'newuser',
    crypt('mypassword', gen_salt('bf', 12)),  -- bcrypt with 12 rounds
    'newuser@example.com',
    'user'
);

-- Verify password
SELECT *
FROM users
WHERE username = 'newuser'
AND password_hash = crypt('mypassword', password_hash);

-- Password complexity check
CREATE OR REPLACE FUNCTION check_password_strength(p_password TEXT)
RETURNS TABLE (
    is_valid BOOLEAN,
    strength TEXT,
    issues TEXT[]
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_issues TEXT[] := ARRAY[]::TEXT[];
BEGIN
    IF length(p_password) < 8 THEN
        v_issues := array_append(v_issues, 'Minimum 8 characters');
    END IF;

    IF p_password !~ '[A-Z]' THEN
        v_issues := array_append(v_issues, 'Need uppercase letter');
    END IF;

    IF p_password !~ '[a-z]' THEN
        v_issues := array_append(v_issues, 'Need lowercase letter');
    END IF;

    IF p_password !~ '[0-9]' THEN
        v_issues := array_append(v_issues, 'Need digit');
    END IF;

    IF p_password !~ '[^a-zA-Z0-9]' THEN
        v_issues := array_append(v_issues, 'Need special character');
    END IF;

    RETURN QUERY SELECT
        array_length(v_issues, 1) IS NULL,
        CASE
            WHEN array_length(v_issues, 1) IS NULL THEN 'Strong'
            WHEN array_length(v_issues, 1) <= 2 THEN 'Medium'
            ELSE 'Weak'
        END,
        v_issues;
END;
$$;

SELECT * FROM check_password_strength('P@ssw0rd123');

🎯 Quiz Section

🏆 Best Practices for Interviews

💡✅ Security Best Practices

1. Always Use Parameterized Queries:

-- NEVER: String concatenation
query = "SELECT * FROM users WHERE name = '" + username + "'"

-- ALWAYS: Parameters
query = "SELECT * FROM users WHERE name = $1"
execute(query, [username])

2. Validate All Input:

-- Whitelist valid characters
-- Check length limits
-- Validate data types
-- Use regular expressions for patterns

3. Apply Least Privilege:

-- Create role-specific users
CREATE ROLE app_readonly;
GRANT SELECT ON products TO app_readonly;

CREATE ROLE app_writer;
GRANT SELECT, INSERT, UPDATE ON products TO app_writer;

4. Hash Passwords Properly:

-- Use bcrypt with sufficient rounds
crypt(password, gen_salt('bf', 12))

-- Never store plain text passwords
-- Never use MD5 or SHA1 for passwords

5. Enable Audit Logging:

-- Log all sensitive operations
-- Include user ID, timestamp, action, and data changes
-- Review logs regularly

6. Encrypt Sensitive Data:

-- Use column-level encryption for PII
-- Use TLS for data in transit
-- Use encryption at rest

7. Keep Software Updated:

-- Apply security patches promptly
-- Monitor for vulnerabilities
-- Follow security advisories

⚠️⚠️ Security Checklist

All queries use parameterized statements
Input validation on all user-facing fields
Least privilege for database users
Passwords hashed with bcrypt/scrypt
Audit logging enabled for sensitive tables
Sensitive data encrypted at rest
TLS enabled for connections
Regular security audits scheduled

Topic: SQL Security and Injection Prevention for FAANG Interviews