ruby.aws-lambda.security.tainted-sql-string.tainted-sql-string

profile photo of semgrepsemgrep
Author
unknown
Download Count*
LGPL Commons Clause
License

Detected user input used to manually construct a SQL string. This is usually bad practice because manual construction could accidentally result in a SQL injection. An attacker could use a SQL injection to steal or modify contents of the database. Instead, use a parameterized query which is available by default in most database engines. Alternatively, consider using an object-relational mapper (ORM) such as Sequelize which will protect your queries.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: tainted-sql-string
    languages:
      - ruby
    severity: ERROR
    message: Detected user input used to manually construct a SQL string. This is
      usually bad practice because manual construction could accidentally result
      in a SQL injection. An attacker could use a SQL injection to steal or
      modify contents of the database. Instead, use a parameterized query which
      is available by default in most database engines. Alternatively, consider
      using an object-relational mapper (ORM) such as Sequelize which will
      protect your queries.
    metadata:
      references:
        - https://rorsecurity.info/portfolio/ruby-on-rails-sql-injection-cheat-sheet
      category: security
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      technology:
        - aws-lambda
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: MEDIUM
      impact: HIGH
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    mode: taint
    pattern-sources:
      - patterns:
          - pattern: event
          - pattern-inside: |
              def $HANDLER(event, context)
                ...
              end
    pattern-sinks:
      - patterns:
          - pattern-either:
              - patterns:
                  - pattern: |
                      "...#{...}..."
                  - pattern-regex: (?i)(select|delete|insert|create|update|alter|drop)\b|\w+\s*!?[<>=].*
              - patterns:
                  - pattern-either:
                      - pattern: Kernel::sprintf("$SQLSTR", ...)
                      - pattern: |
                          "$SQLSTR" + $EXPR
                      - pattern: |
                          "$SQLSTR" % $EXPR
                  - metavariable-regex:
                      metavariable: $SQLSTR
                      regex: (?i)(select|delete|insert|create|update|alter|drop)\b|\w+\s*!?[<>=].*
          - pattern-not-inside: |
              puts(...)

Examples

tainted-sql-string.rb

require 'pg'

def show(event:, context:)
  conn = PG::Connection.open(:dbname => 'test')

  # ok: tainted-sql-string
  res = conn.exec_params('SELECT $1 AS a, $2 AS b, $3 AS c', [event['id'], 2, nil])

  # ok: tainted-sql-string
  res3 = conn.exec_params('SELECT * FROM foobar WHERE id = %{id}' % {id: "something"})
  
  # ok: tainted-sql-string
  query = 'SELECT * FROM foobar WHERE id = ' + "something"
  res4 = conn.exec_params(query)

  # ruleid: tainted-sql-string
  res2 = conn.exec_params('SELECT * FROM foobar WHERE id = %{id}' % {id: event['id']})

  # ruleid: tainted-sql-string
  res5 = conn.exec_params("SELECT * FROM foobar WHERE id = #{event['id']}")

  # ok: tainted-sql-string
  puts("SELECT * FROM foobar WHERE id = #{event['id']}")

  {
    body: res2
  }
end