SQL Injection Prevention for APIs — Complete Defense Guide

In this tutorial, you will learn about SQL Injection Prevention for APIs. We cover key concepts, practical examples, and best practices to help you master this topic.

SQL injection (SQLi) occurs when an attacker inserts malicious SQL statements into API query parameters or body fields. It remains one of the most dangerous and prevalent web vulnerabilities.

What You'll Learn

You'll learn how SQL injection works, how to prevent it with parameterized queries, and how ORMs protect against injection.

Why It Matters

SQL injection can lead to complete database compromise, including data theft, data destruction, and in some cases, remote code execution. The average cost of a data breach involving SQLi exceeds 4 million dollars.

Real-World Use

A healthcare API that parameterizes all queries prevented an attack where an email parameter containing "OR '1'='1'" would have returned every patient record in the database.

flowchart TD
    A[API Request] --> B{Query Type}
    B -->|Parameterized Query| C[Safe Execution]
    B -->|String Concatenation| D{Contains SQL?}
    D -->|Yes| E[SQL Injection!]
    D -->|No| F[Accidentally Safe]
    C --> G[Database]
    F --> G
    E --> H[Data Breach]

Teacher's Mindset

SQL injection is like tricking a bank teller by writing "withdraw all money" in the memo field of a check. Parameterized queries are like special checks where the teller only reads the amount field, ignoring other text.

Preventing SQL Injection

# VULNERABLE: String concatenation
import sqlite3

def get_user_vulnerable(user_id):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    query = f"SELECT * FROM users WHERE id = {user_id}"
    cursor.execute(query)
    return cursor.fetchall()

# SAFE: Parameterized query
def get_user_safe(user_id):
    conn = sqlite3.connect("users.db")
    cursor = conn.cursor()
    query = "SELECT * FROM users WHERE id = ?"
    cursor.execute(query, (user_id,))
    return cursor.fetchall()

# SQLAlchemy ORM protection
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine("postgresql://user:pass@localhost/db")

def get_users_safe(user_id: int):
    with Session(engine) as session:
        query = text("SELECT * FROM users WHERE id = :user_id")
        result = session.execute(query, {"user_id": user_id})
        return result.fetchall()

def get_users_orm(user_id: int):
    with Session(engine) as session:
        from sqlalchemy import select
        from models import User
        stmt = select(User).where(User.id == user_id)
        result = session.execute(stmt)
        return result.scalars().all()

# Input type enforcement
from pydantic import BaseModel, Field

class UserQuery(BaseModel):
    user_id: int = Field(..., ge=1)

class SearchQuery(BaseModel):
    username: str = Field(..., min_length=1, max_length=50, pattern=r"^[a-zA-Z0-9_]+$")

query = UserQuery(user_id=42)
safe_id = query.user_id

search = SearchQuery(username="john")
safe_username = search.username

Common Mistakes

Practice Questions

1. How does a parameterized query prevent SQL injection?
2. Why is escaping quotes not sufficient for SQLi prevention?
3. What is a second-order SQL injection?
4. How do ORMs protect against SQL injection?
5. Why can't table names be parameterized?

Challenge

Create a Flask API with SQLite that is vulnerable to SQL injection on a search endpoint. Then fix it using parameterized queries. Demonstrate both the attack and the fix.

FAQ

Mini Project

Build an API with a vulnerable endpoint that accepts user input directly in SQL queries. Demonstrate extracting database schema using UNION injection. Then fix all queries to use parameterized statements.

What's Next

Learn about XSS protection to prevent client-side script injection through your API.