Fluid Attacks Database

Need

Prevention of SQL injection by passing user input as query parameters

Context

• Usage of Elixir 1.15+ for building application services

• Usage of Ecto SQL with PostgreSQL for database access

Description

1. Non compliant code

defmodule MyApp.Accounts do
  alias MyApp.Repo

  def find_by_email(email) do
    Repo.query("SELECT id, name FROM users WHERE email = '#{email}'")
  end
end

The `find_by_email/1` function below builds a SQL statement with string interpolation, placing the `email` argument between single quotes, and runs it with `Repo.query/1`. The database receives one string and cannot tell which part was written by the developer and which came from the caller. An email such as `' OR '1'='1` returns every user, and payloads with `UNION SELECT` read other tables, including password hashes. Depending on the driver and the database, stacked statements such as `'; DROP TABLE users; --` can also modify or delete data. The same flaw appears when interpolated strings are passed to `Ecto.Query.fragment/1`, `Postgrex.query/3` or `MyXQL.query/3`, or when the SQL is concatenated with `<>`.

2. Steps

• Write raw SQL passed to `Repo.query`, `Postgrex.query` and `MyXQL.query` as constant strings with placeholders (`$1` for PostgreSQL, `?` for MySQL) and send user input in the parameter list.

• Never build SQL with string interpolation (`#{...}`) or concatenation (`<>`) from user input.

• Prefer the Ecto query DSL, which binds every value pinned with `^`.

• Pass values to `fragment/1` only through `?` placeholders.

• Map dynamic identifiers such as column names and sort directions through an allowlist; placeholders cannot protect identifiers.

• Connect with a database role that has only the privileges the application needs.

3. Secure code example

defmodule MyApp.Accounts do
  alias MyApp.Repo

  def find_by_email(email) do
    Repo.query("SELECT id, name FROM users WHERE email = $1", [email])
  end
end

The corrected function keeps the SQL text constant, with a `$1` placeholder, and passes `email` in the parameter list of `Repo.query/2`. With a bound parameter, PostgreSQL receives the statement and the value separately, through the extended query protocol. The value is always treated as data, so quotes, comments or semicolons inside it cannot change the structure of the query. When the query can be written with the Ecto query DSL, `from u in User, where: u.email == ^email` gives the same guarantee, because the `^` operator always sends the value as a parameter. Inside `fragment/1`, values must be passed with `?` placeholders, such as `fragment("lower(?) = ?", u.email, ^email)`, never interpolated.

References

• 146. SQL injection