ruby.rails.security.audit.sqli.ruby-pg-sqli.ruby-pg-sqli

profile photo of semgrepsemgrep
Author
649
Download Count*

Detected string concatenation with a non-literal variable in a pg Ruby SQL statement. This could lead to SQL injection if the variable is user-controlled and not properly sanitized. In order to prevent SQL injection, use parameterized queries or prepared statements instead. You can use parameterized queries like so: conn.exec_params('SELECT $1 AS a, $2 AS b, $3 AS c', [1, 2, nil]) And you can use prepared statements with exec_prepared.

Run Locally

Run in CI

Defintion

rules:
  - id: ruby-pg-sqli
    mode: taint
    pattern-propagators:
      - pattern: $X << $Y
        from: $Y
        to: $X
    pattern-sources:
      - pattern-either:
          - pattern: |
              params
          - pattern: |
              cookies
    pattern-sinks:
      - patterns:
          - pattern-either:
              - pattern-inside: |
                  $CON = PG.connect(...)
                  ...
              - pattern-inside: |
                  $CON = PG::Connection.open(...)
                  ...
              - pattern-inside: |
                  $CON = PG::Connection.new(...)
                  ...
          - pattern-either:
              - pattern: |
                  $CON.$METHOD($X,...)
              - pattern: |
                  $CON.$METHOD $X, ...
          - focus-metavariable: $X
          - metavariable-regex:
              metavariable: $METHOD
              regex: ^(exec|exec_params)$
    languages:
      - ruby
    message: "Detected string concatenation with a non-literal variable in a pg Ruby
      SQL statement. This could lead to SQL injection if the variable is
      user-controlled and not properly sanitized. In order to prevent SQL
      injection, use parameterized queries or prepared statements instead. You
      can use parameterized queries like so: `conn.exec_params('SELECT $1 AS a,
      $2 AS b, $3 AS c', [1, 2, nil])` And you can use prepared statements with
      `exec_prepared`."
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://www.rubydoc.info/gems/pg/PG/Connection
      category: security
      technology:
        - rails
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: HIGH
      impact: MEDIUM
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    severity: WARNING

Examples

ruby-pg-sqli.rb

require 'pg'

def bad1()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age=" + params['age']
    # ruleid: ruby-pg-sqli
    con.exec query
end

def bad2(user_input)
    age = params[user_input]
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query += age
    # ruleid: ruby-pg-sqli
    con.exec query
end

def bad3(userinput)
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query.concat(cookies[userinput])
    # ruleid: ruby-pg-sqli
    con.exec query
end

def bad4(userinput)
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query << params[userinput]
    # passes on 0.111.0 and higher
    # ruleid: ruby-pg-sqli
    con.exec(query)
end

def bad5()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ruleid: ruby-pg-sqli
    con.exec_params("SELECT name FROM users WHERE age=" + params['age'])
end

def bad6()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ruleid: ruby-pg-sqli
    con.exec_params("SELECT name FROM users WHERE age=".concat(cookies['age']),some_params)
end

def bad7(userinput)
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ruleid: ruby-pg-sqli
    con.exec_params("SELECT name FROM users WHERE age=" << params[userinput])
end

def ok1()
    conn = PG.connect(:dbname => 'db1')
    conn.prepare('statement1', 'insert into table1 (id, name, profile) values ($1, $2, $3)')
    # ok: ruby-pg-sqli
    conn.exec_prepared('statement1', [ 11, 'J.R. "Bob" Dobbs', 'Too much is always better than not enough.' ])
end

def ok2()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age=" + "3"
    # ok: ruby-pg-sqli
    con.exec query
end

def ok3()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query += "3"
    # ok: ruby-pg-sqli
    con.exec query
end

def ok4(userinput)
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query.concat("hello")
    # ok: ruby-pg-sqli
    con.exec query
end

def ok5(userinput)
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    query = "SELECT name FROM users WHERE age="
    query << "hello"
    # ok: ruby-pg-sqli
    con.exec query
end

def ok6()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"
    # ok: ruby-pg-sqli
    con.exec_params(stm, [1, 2, 3])
end

def ok7()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ok: ruby-pg-sqli
    con.exec("SELECT name FROM users WHERE age=" + "3")
end

def ok8()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ok: ruby-pg-sqli
    con.exec("SELECT * FROM users WHERE email=hello;".concat("hello"))
end

def ok9()
    con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
    # ok: ruby-pg-sqli
    con.exec("SELECT * FROM users WHERE email=hello;" << "hello")
end