Course contentsShow
← Security
Lesson 1239 of 3,05225. Application Security - Secure CodingFree lesson

Raw Queries and Dynamic SQL in ORMs

Identify unsafe ORM patterns: raw queries, string concatenation in filters, and when to use parameterized raw queries safely.

Raw Queries and Dynamic SQL in ORMs

You'll learn how even ORMs can become vulnerable when you bypass their safe defaults with raw SQL or dynamic string building.

The Escape Hatch Problem

ORMs like SQLAlchemy, Django ORM, or Entity Framework protect you by generating parameterized queries automatically. But they all offer "escape hatches" for complex queries—raw SQL execution. When you use these features carelessly, you reintroduce the very SQL injection vulnerabilities the ORM was designed to prevent.

Unsafe ORM Patterns

Pattern 1: Raw queries with string concatenation

# UNSAFE - Django ORM raw query
User.objects.raw(f"SELECT * FROM users WHERE email = '{user_input}'")

Pattern 2: String concatenation in filters

# UNSAFE - SQLAlchemy filter
db.query(User).filter(f"username = '{user_input}'")

Both examples look innocuous but allow injection attacks because user_input flows directly into SQL strings.

The Safe Approach

When you must use raw queries, use parameterization:

# SAFE - parameterized raw query
User.objects.raw("SELECT * FROM users WHERE email = %s", [user_input])

The ORM still handles parameter binding securely, even though you're writing SQL yourself.

When to Use Raw Queries Safely

Legitimate cases include:

  • Database-specific functions not supported by the ORM
  • Performance-critical queries with complex joins
  • Bulk operations requiring native SQL

Always ask: "Can the ORM's query builder do this?" If yes, use it. If no, use parameterized raw queries.

Key Takeaway: ORMs protect you by default, but raw query features bypass that safety—always use parameterized placeholders (like %s or ?) instead of string concatenation or f-strings when executing raw SQL through an ORM.