GuardAPI Logo
GuardAPI
Automated Security Protocol

How to fix SQL Injection (Legacy & Modern)
in Phoenix

Executive Summary

Phoenix and Ecto provide robust protection out of the box, but 'clever' developers still find ways to introduce SQLi by bypassing the DSL. In legacy systems or complex reporting modules, raw SQL fragments and string interpolation are the primary attack vectors. As an AppSec researcher, I see these patterns when devs try to 'optimize' queries or bypass Ecto's type checking. The goal: never let user input touch the SQL string directly.

The Vulnerable Pattern

VULNERABLE CODE
def get_user_by_name(username) do
  # VULNERABLE: String interpolation in raw SQL
  query = "SELECT * FROM users WHERE username = '#{username}'"
  Ecto.Adapters.SQL.query!(Repo, query, [])
end

def search_posts(search_term) do


VULNERABLE: String interpolation inside a fragment


from(p in Post, where: fragment(“title ILIKE ’%#{search_term}%’”))
|> Repo.all()
end

The Secure Implementation

The vulnerability lies in treating data as code. When you use Elixir's '#{var}' interpolation, the string is evaluated before it reaches the database driver, allowing an attacker to break the SQL context with quotes or semicolons. The fix is 'parameterization'. In Ecto, the pin operator (^) tells the compiler to treat the value as a bound parameter. This ensures the database driver sends the SQL structure and the data in separate packets, rendering payload execution impossible. For raw SQL, use '$1, $2' placeholders and pass the values as a separate list.

SECURE CODE
def get_user_by_name(username) do
  # SECURE: Parameterized raw SQL
  query = "SELECT * FROM users WHERE username = $1"
  Ecto.Adapters.SQL.query!(Repo, query, [username])
end

def search_posts(search_term) do


SECURE: Using the pin operator (^) and proper fragment placeholders


like_term = ”%#{search_term}%”
from(p in Post, where: fragment(“title ILIKE ?”, ^like_term))
|> Repo.all()
end


def modern_search(username) do


BEST PRACTICE: Ecto Query DSL


from(u in User, where: u.username == ^username)
|> Repo.all()
end

System Alert • ID: 8335
Target: Phoenix API
Potential Vulnerability

Your Phoenix API might be exposed to SQL Injection (Legacy & Modern)

74% of Phoenix apps fail this check. Hackers use automated scanners to find this specific flaw. Check your codebase before they do.

RUN FREE SECURITY DIAGNOSTIC
GuardLabs Engine: ONLINE

Free Tier • No Credit Card • Instant Report

Verified by Ghost Labs Security Team

This content is continuously validated by our automated security engine and reviewed by our research team. Ghost Labs analyzes over 500+ vulnerability patterns across 40+ frameworks to provide up-to-date remediation strategies.

About GuardLabs → Follow us on X