Avoid SQL injection

Docs > Datadog Security > Code Security > Static Code Analysis (SAST) > SAST Rules > Avoid SQL injection

Metadata

ID: ruby-security/sql-injection

Language: Ruby

Severity: Error

Category: Security

CWE: 89

Description

This rule pertains to avoiding SQL Injection, which is a serious security vulnerability that can allow attackers to manipulate or control your database. SQL Injection occurs when untrusted data is inserted into a database query without proper sanitization or parameterization.

In the provided non-compliant code, the SQL queries are constructed by string concatenation with user-provided input. This is a dangerous practice as it allows an attacker to inject arbitrary SQL code into the query. For instance, if an attacker provides an ‘id’ value of “1; DROP TABLE analysis_results;”, it could lead to the deletion of an entire table.

To avoid SQL Injection, always use parameterized queries or prepared statements, which ensure that user-provided input is always treated as literal values, not executable code. In Ruby, you can use the ‘quote’ and ‘sanitize’ methods provided by ActiveRecord, or use ‘?’ placeholders in your SQL queries to safely include user-provided input. For example, you could write: ActiveRecord::Base.connection.execute("UPDATE analysis_results SET running_time_sec = ? WHERE id = ?", time, id). This ensures that the ’time’ and ‘id’ values are properly escaped, preventing SQL Injection.

Non-Compliant Code Examples

class NonCompliantController < ApplicationController
  def show(event:, context:)
    conn = PG::Connection.open(:dbname => 'datadog')
    res2 = conn.exec_params('SELECT $1 AS a FROM B where C like %{id}' % {id: event['id']})
    res5 = conn.exec_params("SELECT * AS a FROM B where C like #{event['id']}")
  end


  def update2
    user = User.where("user_id = '#{params[:user][:id]}'")[0]

    if user
      user.update_attributes(user_params_without_password)
    else
      flash[:error] = "Could not update user!"
      redirect_to "app.datadoghq.com/security/appsec/vm/library"
    end
  end

  def test3
    records = ActiveRecord::Base.connection.execute("INSERT INTO events (name) VALUES ('%s')" % params[:user])
    redirect_to "app.datadoghq.com/security/appsec/vm/library"
  end

  def test4
    records = ActiveRecord::Base.connection.execute(Kernel::sprintf("SELECT FROM event WHERE name='%s'", params[:user]))
    redirect_to "app.datadoghq.com/security/appsec/vm/library"
  end

  def test5
    records = ActiveRecord::Base.connection.execute("SELECT FROM event WHERE name='" + params[:user] + "'")
    redirect_to "app.datadoghq.com/security/appsec/vm/library"
  end
  
  def text_bio
    user = User.find_by "name = '#{params[:user_name]}'"
  end

end

Compliant Code Examples

class CompliantController < ApplicationController
  def show(event:, context:)
    conn = PG::Connection.open(:dbname => 'datadog')
    res = conn.exec_params('SELECT $1 AS a FROM B where C like %D%', [event['id'], 1, nil])

    res3 = conn.exec_params('SELECT $1 AS a FROM B where C like {id}' % {id: "something"})

    query = 'SELECT $1 AS a FROM B where C like ' + "something"
    res4 = conn.exec_params(query)

    puts("SELECT * FROM foobar WHERE id = #{event['id']}")
  end

  def ok_test1
    message = "Compliant example %s" % params[:user]
    redirect_to message
  end

  def ok_test2
    message = Kernel::sprintf("Compliant example %s", params[:user])
    redirect_to message
  end

  def ok_test3
    records = "this is ok!" + params[:user] + "'"
    redirect_to records
  end

  def ok_test4
    user = User.where("id = ?", "#{params[:id]}")[0]
  end

  def ok_test5
    redirect_url = params[:redirect]
    redirect_to "app.datadoghq.com/security/appsec/vm/library/{redirect_url}"
  end
end