SQL Injection - Code - Elixir

Need

To prevent unauthorized database access and manipulation via SQL Injection.

Context

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

Description

Insecure Code Example

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

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

Secure Code Example

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

Last updated

2023/09/18