SQL Injection
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.