Skip to main content

Insecure Use of SQL Queries

Why is this important?

SQL injections are dangerous because they can be easily identified by attackers. Hackers can use SQL injections to read from and sometimes even write to your database. SQL injection occurs when untrusted input is interpolated directly into a SQL query. In a typical Phoenix application, this would mean using the Ecto.Adapters.SQL.query method and not using the parameterization feature. SQL injections are very common and have been the cause of many high-profile breaches.

Check out this video for a high-level explanation:

SQL Injection Explanation Video

Fixing Insecure Use of SQL Queries

Option A: Use Ecto Securely

  1. Go through the issues that GuardRails identified in the PR

  2. Look for insecure patterns like these:

    def query(%{"sql" => sql}) do
    Repo.query(sql)
    end

    or:

    def query(%{"sql" => sql}) do
    SQL.query(Repo, sql, [])
    end

    or:

    def query(%{"sql" => sql}) do
    SQL.stream(Repo, sql, [])
    end
  3. Replace it with the following:

    sql = """
    select * from users where name = $1;
    """
    def query(%{"sql" => sql}, name) do
    SQL.stream(Repo, sql, [name])
    end
  4. Test it

  5. Ship it 🚢 and relax 🌴

More information: