SQL Injection Prevention

SQL Injection Prevention

Never construct SQL queries with string concatenation or f-strings. Always use parameterized queries to prevent SQL injection attacks.

Why This Matters

SQL injection is one of the most common and dangerous web vulnerabilities. Attackers can:

• Access unauthorized data
• Modify or delete database records
• Execute admin operations on the database
• In some cases, issue commands to the OS

❌ Incorrect

Problem: User input directly interpolated into SQL query.

def get_user(user_id):
    query = f"SELECT * FROM users WHERE id = {user_id}"
    result = db.execute(query)
    return result

# Vulnerable to: get_user("1 OR 1=1")
# Executes: SELECT * FROM users WHERE id = 1 OR 1=1
# Returns all users!

Why it's dangerous:

• Attacker can inject arbitrary SQL
• Can bypass authentication
• Can extract entire database
• Simple user input becomes code execution

✅ Correct

Solution: Use parameterized queries (prepared statements).

def get_user(user_id: int) -> Optional[Dict[str, Any]]:
    """Safely retrieve user by ID.
    
    Args:
        user_id: User ID to look up
        
    Returns:
        User record or None if not found
    """
    query = "SELECT * FROM users WHERE id = ?"
    result = db.execute(query, (user_id,))
    return result.fetchone() if result else None

# Safe: user_id is treated as data, not code
# Even malicious input is harmless

Why it's safe:

• Parameters are escaped automatically
• Input treated as data, never as code
• Database driver handles sanitization
• No way to inject SQL syntax

Framework-Specific Solutions

SQLAlchemy (Python)

from sqlalchemy import select, text

# ✅ Using ORM
user = session.query(User).filter(User.id == user_id).first()

# ✅ Using Core with parameters
query = select(users).where(users.c.id == user_id)

# ✅ Using text() with bound parameters
query = text("SELECT * FROM users WHERE id = :id")
result = session.execute(query, {"id": user_id})

Django (Python)

# ✅ Using ORM
User.objects.get(id=user_id)

# ✅ Using raw SQL with parameters
User.objects.raw("SELECT * FROM users WHERE id = %s", [user_id])

Node.js (PostgreSQL)

// ✅ Using parameterized query
const result = await client.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

Node.js (MySQL)

// ✅ Using placeholder
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE id = ?',
  [userId]
);

Additional Best Practices

1. Validate input types

   def get_user(user_id: int) -> Optional[User]:
       if not isinstance(user_id, int):
           raise ValueError("user_id must be an integer")
       # ... query
   

2. Use ORMs when possible

   • ORMs handle parameterization automatically
   • Reduces risk of manual SQL errors
   • Provides abstraction and type safety

3. Principle of least privilege

   • Database users should have minimal permissions
   • Read-only accounts for SELECT operations
   • Limits damage from successful injection

4. Input validation as defense-in-depth

   • Parameterization is primary defense
   • Validation provides additional layer
   • Whitelist allowed characters/patterns

References

• OWASP SQL Injection
• CWE-89: SQL Injection
• SQLAlchemy SQL Injection Prevention