logo

Database

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

1. 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.

2. 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.

3. 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.

On this page