Detecting SQL Injection in Phoenix with Sobelow

SQL injection is a type of attack against a web application, where some malicious input is parsed by the underlying database, resulting in an unauthorized operation being performed. This can be the disclosure of sensitive data, modification of the database, or deletion of entire tables.

Most Phoenix applications use Ecto, a database wrapper and query generator for Elixir. When used correctly, Ecto prevents SQL injection due to the way queries are constructed. For example, the Ecto query

  from f in Fruit,
    where: f.quantity >= ^min_quantity
    and f.secret == false

where min_quantity is untrusted user input, is not vulnerable to SQL injection, because Ecto knows how to safely handle external data. However, Ecto also gives you the power to construct raw SQL queries yourself, a feature that may lead to an SQL injection vulnerability being introduced. This article will show secure and insecure Ecto code, how to perform an SQL injection attack, and how to use Sobelow to scan an Elixir codebase to detect this vulnerability.

1. The Phoenix application, basket

To demonstrate SQL injection, we use a simple Phoenix application. It has several types of fruit stored in the database, with a quantity integer and secret boolean value for each. There is one location for user input, the min_q URL parameter, which is used by the database (Postgres) to display fruits. Only fruit with secret set to false should be displayed. The full source is available on Github, https://github.com/paraxialio/basket

The function a_get_fruit/1 is used by the controller to execute the database query, where min_q is user supplied input.

lib/basket/goods.ex

defmodule Basket.Goods do
  import Ecto.Query

  alias Basket.Repo
  alias Basket.Fruit

  def a_get_fruit(min_q) do
    from(
      f in Fruit,
      where:
        f.quantity >= ^min_q and
          f.secret == false
    )
    |> Repo.all()
  end
end

The controller action gets the min_q parameter and passes it to a_get_fruit/1.

lib/basket_web/controllers/page_controller.ex

defmodule BasketWeb.PageController do
  use BasketWeb, :controller
  alias Basket.Goods

  def basket_a(conn, %{"min_q" => min_q}) do
    fruits = Goods.a_get_fruit(min_q)
    render(conn, "index.html", fruits: fruits)
  end
end

The SQL query executed on the basket_a route can be viewed by running the application and visiting http://localhost:4000/basket_a?min_q=0

[info] GET /basket_a
[debug] Processing with BasketWeb.PageController.basket_a/2
  Parameters: %{"min_q" => "0"}
  Pipelines: [:browser]
[debug] QUERY OK source="fruits" db=1.0ms idle=1143.3ms
SELECT f0."id", f0."name", f0."quantity", f0."secret", f0."inserted_at", 
f0."updated_at" FROM "fruits" AS f0 
WHERE ((f0."quantity" >= $1) AND (f0."secret" = FALSE)) [0]

Switching perspective to the attacker, we want to select all fruits in the table. Our goal is to alter the SQL query from

SELECT f0."id", f0."name", f0."quantity", f0."secret", f0."inserted_at", f0."updated_at" 
FROM "fruits" AS f0 
WHERE ((f0."quantity" >= $1) AND (f0."secret" = FALSE)) [0]

to

SELECT f0."id", f0."name", f0."quantity", f0."secret", f0."inserted_at", f0."updated_at" 
FROM "fruits" AS f0 
WHERE f0."quantity" >= 0 OR 1=1; -- AND f0."secret" = FALSE

The statement 1=1 evaluates to true, and the -- is a comment in Postgres. The meaning of this new query, constructed through malicious input, is to select all fruits, including the secret row. The attack is formatted with percent-encoding.

http://localhost:4000/basket_a?min_q=0%20OR%201=1;%20–

Ecto throws an error because the user input cannot be cast to an integer. Looking back to the debug output for this query, when the input is an integer, notice the formatting.

SELECT f0."id", f0."name", f0."quantity", f0."secret", f0."inserted_at", f0."updated_at" 
FROM "fruits" AS f0 
WHERE ((f0."quantity" >= $1) AND (f0."secret" = FALSE)) [0]

The parameter list, [0], was created from user input. This means that malicious input will be safely parameterized, and the function is not vulnerable. SQL injection happens when the query itself is constructed from user input, which will be shown soon.

2. Attempting and failing to introduce SQL injection

Create a new route in lib/basket_web/router.ex

  scope "/", BasketWeb do
    pipe_through :browser

    get "/", PageController, :index

    get "/basket_a", PageController, :basket_a
    get "/basket_b", PageController, :basket_b
  end

Then add the action basket_b/2 in lib/basket_web/controllers/page_controller.ex

  def basket_b(conn, %{"min_q" => min_q}) do
    fruits = Goods.b_get_fruit(min_q)
    render(conn, "index.html", fruits: fruits)
  end

Now with a new endpoint setup, we define b_get_fruit/1, using Ecto’s fragment, constructed from user input.

  def b_get_fruit(min_q) do
    from(
      f in Fruit,
      where: fragment("f0.quantity >= #{min_q} AND f0.secret = FALSE")
    )
    |> Repo.all()
  end

This pattern will be repeated for each new query function. The above should be vulnerable to SQL injection, but what happens when we recompile?

@ basket % mix phx.server
Compiling 1 file (.ex)

== Compilation error in file lib/basket/goods.ex ==
** (Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) 
does not allow strings to be interpolated as the first argument via the `^` 
operator, got: `"f0.quantity >= #{min_q} AND f0.secret = FALSE"`
    (ecto 3.9.0) expanding macro: Ecto.Query.where/3
    lib/basket/goods.ex:19: Basket.Goods.b_get_fruit/1
    (ecto 3.9.0) expanding macro: Ecto.Query.from/2
    lib/basket/goods.ex:19: Basket.Goods.b_get_fruit/1
    (elixir 1.13.0) expanding macro: Kernel.|>/2
    lib/basket/goods.ex:23: Basket.Goods.b_get_fruit/1

Ecto’s fragment will not allow you to introduce the vulnerability here, and compilation will fail. Let’s try a different way with fragment.

  def c_get_fruit(min_q) do
    min_q = String.to_integer(min_q)
    from(
      f in Fruit,
      where: fragment("f0.quantity >= ? AND f0.secret = FALSE", ^min_q)
    )
    |> Repo.all()
  end

This will also fail, because the user input, min_q, is being safely interpreted as a parameter.

The Ecto.Adapters.SQL.query/4 function runs a custom SQL query on the given repo. If you pass user input as parameters to your custom query, it will not be vulnerable to SQL injection.

  def d_get_fruit(min_q) do
    q = """
    SELECT f.id, f.name, f.quantity, f.secret
    FROM fruits AS f
    WHERE f.quantity > $1 AND f.secret = FALSE
    """
    {:ok, %{rows: rows}} =
      Ecto.Adapters.SQL.query(Repo, q, [String.to_integer(min_q)])
    Enum.map(rows, fn row ->
      [id, name, quantity, secret] = row
      %Fruit{id: id, name: name, quantity: quantity, secret: secret}
    end)
  end

3. A vulnerable query and successful attack

The previous examples demonstrate how Ecto helps avoid SQL injection, but it is still possible to introduce this vulnerability.

  def e_get_fruit(min_q) do
    q = """
    SELECT f.id, f.name, f.quantity, f.secret
    FROM fruits AS f
    WHERE f.quantity > #{min_q} AND f.secret = FALSE
    """
    {:ok, %{rows: rows}} =
      Ecto.Adapters.SQL.query(Repo, q)
    Enum.map(rows, fn row ->
      [id, name, quantity, secret] = row
      %Fruit{id: id, name: name, quantity: quantity, secret: secret}
    end)
  end

e_get_fruit/1 is vulnerable to SQL injection, the attack is performed by visting:

http://localhost:4000/basket_e?min_q=0%20OR%201=1;%20–

The secret row was printed, showing the attack was successful. Examine the query that was executed:

[info] GET /basket_e
[debug] Processing with BasketWeb.PageController.basket_e/2
  Parameters: %{"min_q" => "0 OR 1=1; --"}
  Pipelines: [:browser]
[debug] QUERY OK db=2.0ms queue=2.8ms idle=331.5ms
SELECT f.id, f.name, f.quantity, f.secret
FROM fruits AS f
WHERE f.quantity >= 0 OR 1=1; -- AND f.secret = FALSE
 []
[info] Sent 200 in 56ms

Notice that the user input was not placed into the query as a parameter, the entire query has been altered to print all fruits. This is the root cause of the vulnerability.

4. Sobelow, static analysis of Phoenix applications

Sobelow is a popular static analysis tool for Phoenix applications, and can be used to detect the vulnerability just introduced.

Install Sobelow in mix.exs

  defp deps do
    [
      {:phoenix, "~> 1.6.10"},
      ...
      {:sobelow, "~> 0.11.1"}
    ]
  end

basket % mix deps.get
...
basket % mix sobelow   
##############################################
#                                            #
#          Running Sobelow - v0.11.1         #
#  Created by Griffin Byatt - @griffinbyatt  #
#     NCC Group - https://nccgroup.trust     #
#                                            #
##############################################

Config.CSP: Missing Content-Security-Policy - High Confidence
File: lib/basket_web/router.ex
Pipeline: browser
Line: 10

-----------------------------------------------

Config.HTTPS: HTTPS Not Enabled - High Confidence

-----------------------------------------------

SQL.Query: SQL injection - Low Confidence
File: lib/basket/goods.ex
Line: 56
Function: e_get_fruit:49
Variable: q

-----------------------------------------------

SQL.Query: SQL injection - Low Confidence
File: lib/basket/goods.ex
Line: 42
Function: d_get_fruit:35
Variable: q

-----------------------------------------------

... SCAN COMPLETE ...

Two functions were reported vulnerable to SQL injection, but d_get_fruit/1 is not vulnerable. To prevent this function from showing up in findings, add the following comment in lib/basket/goods.ex.

  # sobelow_skip ["SQL"]
  def d_get_fruit(min_q) do

Then use the –skip flag to exclude it from the findings.

basket % mix sobelow --skip
##############################################
#                                            #
#          Running Sobelow - v0.11.1         #
#  Created by Griffin Byatt - @griffinbyatt  #
#     NCC Group - https://nccgroup.trust     #
#                                            #
##############################################

Config.CSP: Missing Content-Security-Policy - High Confidence
File: lib/basket_web/router.ex
Pipeline: browser
Line: 10

-----------------------------------------------

Config.HTTPS: HTTPS Not Enabled - High Confidence

-----------------------------------------------

SQL.Query: SQL injection - Low Confidence
File: lib/basket/goods.ex
Line: 56
Function: e_get_fruit:49
Variable: q

-----------------------------------------------

... SCAN COMPLETE ...

The function will no longer be included in findings. Sobelow can be run manually from the command line, or as part of a CI/CD pipeline, to monitor code changes for security problems.

Ecto strongly discourages you from writing vulnerable code. Using Ecto queries is safe, using fragment is safe, unless you are using macros to get around the SQL injection error shown in this article. Take care when using functions from Ecto.Adapters.SQL, they are vectors for SQL injection.

---

Paraxial.io stops data breaches by securing your Elixir and Phoenix apps. Detect and fix critical security issues today. Attending ElixirConf EU (April 17th) in Lisbon? Paraxial.io founder Michael Lubas is giving the training Elixir Application Security and will be speaking at the conference. Hope to see you there!