SQL Injection - Code - Elixir

SQL Injection - Code - Elixir

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