---
title: Avoid SQL injection
description: Datadog, the leading service for cloud-scale monitoring.
breadcrumbs: >-
  Docs > Datadog Security > Code Security > Static Code Analysis (SAST) > SAST
  Rules > Avoid SQL injection
---

# Avoid SQL injection

{% callout %}
# Important note for users on the following Datadog sites: app.ddog-gov.com

{% alert level="danger" %}
This product is not supported for your selected [Datadog site](https://docs.datadoghq.com/getting_started/site). ().
{% /alert %}

{% /callout %}

## Metadata{% #metadata %}

**ID:** `ruby-security/sql-injection`

**Language:** Ruby

**Severity:** Error

**Category:** Security

**CWE**: [89](https://cwe.mitre.org/data/definitions/89.html)

## Description{% #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{% #non-compliant-code-examples %}

```ruby
# controller
# params[:q] = "'; DROP TABLE users; --"
@users = User.where("email LIKE '%#{params[:q]}%'")
```

```ruby
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{% #compliant-code-examples %}

```ruby
class Something < BaseController
  def text
    @text ||= Table.find(params[:id].to_i)
  end
end
```

```ruby
class ClientsController < BaseController
  def index
    foo = Model.find(params.get(:id))
  end

  def create
    if @client.save
      foo = Model.find(params.get(:id))
    else
      render "new"
    end
  end
end
```

```ruby
class ClientsController < ApplicationController
  def index
    foo = Model.find(params.get(:id))
  end

  def create
    if @client.save
      foo = Model.find(params.get(:id))
    else
      render "new"
    end
  end
end
```

```ruby
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
```
  Seamless integrations. Try Datadog Code SecurityDatadog Code Security 
{% icon name="icon-external-link" /%}