SQL Injection - Code

Need

To prevent unauthorized database access and manipulation via SQL Injection.

Context

• Usage of Elixir (1.12.0 and above) for building scalable and fault-tolerant applications
• Usage of Phoenix Framework for building web applications
• Usage of Ecto for data persistence

Description

Non compliant code

defmodule MyAppWeb.UserController do
  use MyAppWeb, :controller

  def show(conn, %{"id" => id}) do
    user = MyApp.Repo.one("SELECT * FROM users WHERE id = " <> id)
    render(conn, "show.html", user: user)
  end
end

This code is vulnerable because it's constructing a SQL query by directly appending user-supplied input (id) to the SQL statement. An attacker can manipulate the id input to execute arbitrary SQL queries against the database, leading to unauthorized access or data manipulation.

Steps

• Avoid creating SQL queries by string concatenation with user-supplied input.
• Use Ecto's query API or parameterized queries to safely interact with the database.
• Always validate and sanitize user-supplied inputs.

Compliant code

defmodule MyAppWeb.UserController do
  use MyAppWeb, :controller

  def show(conn, %{"id" => id}) do
    user = MyApp.Repo.one(from u in User, where: u.id == ^id)
    render(conn, "show.html", user: user)
  end
end

This code is safe because it's using Ecto's query API to create a SQL query. The ^ operator is used to safely interpolate values in a query, which prevents SQL Injection attacks.

References

• 297.SQL injection - Code