SQL Injection - Elixir

SQL Injection - Elixir

Need

Prevent unauthorized extraction or manipulation of data in the database

Context

  • Usage of Elixir (v1.10+) for building scalable and fault-tolerant applications
  • Usage of PostgreSQL database for data storage and retrieval
  • Usage of Ecto for database handling

Description

Non compliant code

        def get_user_data(conn, %{"id" => id}) do
 Repo.query("SELECT * FROM users WHERE id = " <> id)
end

The code above is vulnerable to SQL Injection because it directly concatenates a user-provided string (id) into a SQL query. An attacker could provide a malicious string that alters the SQL query to expose sensitive data or execute unauthorized SQL commands.

Steps

  • Utilize Ecto.Query API to create SQL queries instead of string concatenation.
  • Always validate and sanitize user inputs before using them in SQL queries.
  • Limit the permissions of the database user used by the application.

Compliant code

        def get_user_data(conn, %{"id" => id}) do
  id = String.to_integer(id)
  query = from u in "users", where: u.id == ^id
  Repo.one(query)
end

The code above is secure because it uses Ecto's query API to construct the SQL query, which automatically escapes variables to prevent SQL Injection. It also converts the user input to an integer, providing an extra layer of security.

References