ruby.rails.security.audit.sqli.ruby-pg-sqli.ruby-pg-sqli
semgrep
Author
649
Download Count*
License
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
Short Link: https://sg.run/kL0o