lib/brakeman/checks/check_sql.rb
require 'brakeman/checks/base_check'
#This check tests for find calls which do not use Rails' auto SQL escaping
#
#For example:
# Project.find(:all, :conditions => "name = '" + params[:name] + "'")
#
# Project.find(:all, :conditions => "name = '#{params[:name]}'")
#
# User.find_by_sql("SELECT * FROM projects WHERE name = '#{params[:name]}'")
class Brakeman::CheckSQL < Brakeman::BaseCheck
Brakeman::Checks.add self
@description = "Check for SQL injection"
def run_check
# Avoid reporting `user_input` on silly values when generating warning.
# Note that we retroactively find `user_input` inside the "dangerous" value.
@safe_input_attributes.merge IGNORE_METHODS_IN_SQL
@sql_targets = [:average, :calculate, :count, :count_by_sql, :delete_all, :destroy_all,
:find_by_sql, :maximum, :minimum, :pluck, :sum, :update_all]
@sql_targets.concat [:from, :group, :having, :joins, :lock, :order, :reorder, :where] if tracker.options[:rails3]
@sql_targets.concat [:find_by, :find_by!, :find_or_create_by, :find_or_create_by!, :find_or_initialize_by, :not] if tracker.options[:rails4]
if tracker.options[:rails6]
@sql_targets.concat [:delete_by, :destroy_by, :rewhere, :reselect]
@sql_targets.delete :delete_all
@sql_targets.delete :destroy_all
end
if version_between?("6.1.0", "9.9.9")
@sql_targets.delete :order
@sql_targets.delete :reorder
@sql_targets.delete :pluck
end
if version_between?("2.0.0", "3.9.9") or tracker.config.rails_version.nil?
@sql_targets << :first << :last << :all
end
if version_between?("2.0.0", "4.0.99") or tracker.config.rails_version.nil?
@sql_targets << :find
end
@connection_calls = [:delete, :execute, :insert, :select_all, :select_one,
:select_rows, :select_value, :select_values]
if tracker.options[:rails3]
@connection_calls.concat [:exec_delete, :exec_insert, :exec_query, :exec_update]
else
@connection_calls.concat [:add_limit!, :add_offset_limit!, :add_lock!]
end
@expected_targets = active_record_models.keys + [:connection, :"ActiveRecord::Base", :Arel]
Brakeman.debug "Finding possible SQL calls on models"
calls = tracker.find_call(:methods => @sql_targets, :nested => true)
narrow_targets = [:exists?, :select]
calls.concat tracker.find_call(:targets => active_record_models.keys, :methods => narrow_targets, :chained => true)
Brakeman.debug "Finding possible SQL calls with no target"
calls.concat tracker.find_call(:target => nil, :methods => @sql_targets)
Brakeman.debug "Finding possible SQL calls using constantized()"
calls.concat tracker.find_call(:methods => @sql_targets).select { |result| constantize_call? result }
calls.concat tracker.find_call(:targets => @expected_targets, :methods => @connection_calls, :chained => true).select { |result| connect_call? result }
calls.concat tracker.find_call(:target => :Arel, :method => :sql)
Brakeman.debug "Finding calls to named_scope or scope"
calls.concat find_scope_calls
Brakeman.debug "Processing possible SQL calls"
calls.each { |call| process_result call }
end
#Find calls to named_scope() or scope() in models
#RP 3 TODO
def find_scope_calls
scope_calls = []
# Used in pre-3.1.0 versions of Rails
ar_scope_calls(:named_scope) do |model, args|
call = make_call(nil, :named_scope, args).line(args.line)
scope_calls << scope_call_hash(call, model, :named_scope)
end
# Use in 3.1.0 and later
ar_scope_calls(:scope) do |model, args|
second_arg = args[2]
next unless sexp? second_arg
if second_arg.node_type == :iter and node_type? second_arg.block, :block, :call, :safe_call
process_scope_with_block(model, args)
elsif call? second_arg
call = second_arg
scope_calls << scope_call_hash(call, model, call.method)
else
call = make_call(nil, :scope, args).line(args.line)
scope_calls << scope_call_hash(call, model, :scope)
end
end
scope_calls
end
def ar_scope_calls(symbol_name, &block)
active_record_models.each do |name, model|
model_args = model.options[symbol_name]
if model_args
model_args.each do |args|
yield model, args
end
end
end
end
def scope_call_hash(call, model, method)
{ :call => call, :location => { :type => :class, :class => model.name, :file => model.file }, :method => :named_scope }
end
def process_scope_with_block model, args
scope_name = args[1][1]
block = args[-1][-1]
# Search lambda for calls to query methods
if block.node_type == :block
find_calls = Brakeman::FindAllCalls.new(tracker)
find_calls.process_source(block, :class => model.name, :method => scope_name, :file => model.file)
find_calls.calls.each { |call| process_result(call) if @sql_targets.include?(call[:method]) }
elsif call? block
while call? block
process_result :target => block.target, :method => block.method, :call => block,
:location => { :type => :class, :class => model.name, :method => scope_name, :file => model.file }
block = block.target
end
end
end
#Process possible SQL injection sites:
#
# Model#find
#
# Model#(named_)scope
#
# Model#(find|count)_by_sql
#
# Model#all
#
### Rails 3
#
# Model#(where|having)
# Model#(order|group)
#
### Find Options Hash
#
# Dangerous keys that accept SQL:
#
# * conditions
# * order
# * having
# * joins
# * select
# * from
# * lock
#
def process_result result
return if duplicate?(result) or result[:call].original_line
call = result[:call]
method = call.method
dangerous_value = case method
when :find
check_find_arguments call.second_arg
when :exists?
check_exists call.first_arg
when :delete_all, :destroy_all
check_find_arguments call.first_arg
when :named_scope, :scope
check_scope_arguments call
when :find_by_sql, :count_by_sql
check_by_sql_arguments call.first_arg
when :calculate
check_find_arguments call.third_arg
when :last, :first, :all
check_find_arguments call.first_arg
when :average, :count, :maximum, :minimum, :sum
if call.length > 5
unsafe_sql?(call.first_arg) or check_find_arguments(call.last_arg)
else
check_find_arguments call.last_arg
end
when :where, :rewhere, :having, :find_by, :find_by!, :find_or_create_by, :find_or_create_by!, :find_or_initialize_by,:not, :delete_by, :destroy_by
check_query_arguments call.arglist
when :order, :group, :reorder
check_order_arguments call.arglist
when :joins
check_joins_arguments call.first_arg
when :from
unsafe_sql? call.first_arg
when :lock
check_lock_arguments call.first_arg
when :pluck
unsafe_sql? call.first_arg
when :sql
unsafe_sql? call.first_arg
when :update_all, :select, :reselect
check_update_all_arguments call.args
when *@connection_calls
check_by_sql_arguments call.first_arg
else
Brakeman.debug "Unhandled SQL method: #{method}"
end
if dangerous_value
add_result result
input = include_user_input? dangerous_value
if input
confidence = :high
user_input = input
else
confidence = :medium
user_input = dangerous_value
end
if result[:call].target and result[:chain] and not @expected_targets.include? result[:chain].first
confidence = case confidence
when :high
:medium
when :medium
:weak
else
confidence
end
end
warn :result => result,
:warning_type => "SQL Injection",
:warning_code => :sql_injection,
:message => "Possible SQL injection",
:user_input => user_input,
:confidence => confidence,
:cwe_id => [89]
end
if check_for_limit_or_offset_vulnerability call.last_arg
if include_user_input? call.last_arg
confidence = :high
else
confidence = :weak
end
warn :result => result,
:warning_type => "SQL Injection",
:warning_code => :sql_injection_limit_offset,
:message => msg("Upgrade to Rails >= 2.1.2 to escape ", msg_code(":limit"), " and ", msg_code("offset"), ". Possible SQL injection"),
:confidence => confidence,
:cwe_id => [89]
end
end
#The 'find' methods accept a number of different types of parameters:
#
# * The first argument might be :all, :first, or :last
# * The first argument might be an integer ID or an array of IDs
# * The second argument might be a hash of options, some of which are
# dangerous and some of which are not
# * The second argument might contain SQL fragments as values
# * The second argument might contain properly parameterized SQL fragments in arrays
# * The second argument might contain improperly parameterized SQL fragments in arrays
#
#This method should only be passed the second argument.
def check_find_arguments arg
return nil if not sexp? arg or node_type? arg, :lit, :string, :str, :true, :false, :nil
unsafe_sql? arg
end
def check_scope_arguments call
scope_arg = call.second_arg #first arg is name of scope
node_type?(scope_arg, :iter) ? unsafe_sql?(scope_arg.block) : unsafe_sql?(scope_arg)
end
def check_query_arguments arg
return unless sexp? arg
first_arg = arg[1]
if node_type? arg, :arglist
if arg.length > 2 and string_interp? first_arg
# Model.where("blah = ?", blah)
return check_string_interp first_arg
else
arg = first_arg
end
end
if request_value? arg
unless call? arg and params? arg.target and [:permit, :slice, :to_h, :to_hash, :symbolize_keys].include? arg.method
# Model.where(params[:where])
arg
end
elsif hash? arg and not kwsplat? arg
#This is generally going to be a hash of column names and values, which
#would escape the values. But the keys _could_ be user input.
check_hash_keys arg
elsif node_type? arg, :lit, :str
nil
else
#Hashes are safe...but we check above for hash, so...?
unsafe_sql? arg, :ignore_hash
end
end
#Checks each argument to order/reorder/group for possible SQL.
#Anything used with these methods is passed in verbatim.
def check_order_arguments args
return unless sexp? args
if node_type? args, :arglist
check_update_all_arguments(args)
else
unsafe_sql? args
end
end
#find_by_sql and count_by_sql can take either a straight SQL string
#or an array with values to bind.
def check_by_sql_arguments arg
return unless sexp? arg
#This is kind of unnecessary, because unsafe_sql? will handle an array
#correctly, but might be better to be explicit.
array?(arg) ? unsafe_sql?(arg[1]) : unsafe_sql?(arg)
end
#joins can take a string, hash of associations, or an array of both(?)
#We only care about the possible string values.
def check_joins_arguments arg
return unless sexp? arg and not node_type? arg, :hash, :string, :str
if array? arg
arg.each do |a|
unsafe_arg = check_joins_arguments a
return unsafe_arg if unsafe_arg
end
nil
else
unsafe_sql? arg
end
end
def check_update_all_arguments args
args.each do |arg|
unsafe_arg = unsafe_sql? arg
return unsafe_arg if unsafe_arg
end
nil
end
#Model#lock essentially only cares about strings. But those strings can be
#any SQL fragment. This does not apply to all databases. (For those who do not
#support it, the lock method does nothing).
def check_lock_arguments arg
return unless sexp? arg and not node_type? arg, :hash, :array, :string, :str
unsafe_sql?(arg, :ignore_hash)
end
#Check hash keys for user input.
#(Seems unlikely, but if a user can control the column names queried, that
#could be bad)
def check_hash_keys exp
hash_iterate(exp) do |key, _value|
unless symbol?(key)
unsafe_key = unsafe_sql? key
return unsafe_key if unsafe_key
end
end
false
end
#Check an interpolated string for dangerous values.
#
#This method assumes values interpolated into strings are unsafe by default,
#unless safe_value? explicitly returns true.
def check_string_interp arg
arg.each do |exp|
if dangerous = unsafe_string_interp?(exp)
return dangerous
end
end
nil
end
TO_STRING_METHODS = [:chomp, :chop, :lstrip, :rstrip, :scrub, :squish, :strip,
:strip_heredoc, :to_s, :tr]
#Returns value if interpolated value is not something safe
def unsafe_string_interp? exp
if node_type? exp, :evstr
value = exp.value
else
value = exp
end
if not sexp? value
nil
elsif call? value and TO_STRING_METHODS.include? value.method
unsafe_string_interp? value.target
elsif call? value and safe_literal_target? value
nil
else
case value.node_type
when :or
unsafe_string_interp?(value.lhs) || unsafe_string_interp?(value.rhs)
when :dstr
if dangerous = check_string_interp(value)
return dangerous
end
else
if safe_value? value
nil
elsif string_building? value
check_for_string_building value
else
value
end
end
end
end
#Checks the given expression for unsafe SQL values. If an unsafe value is
#found, returns that value (may be the given _exp_ or a subexpression).
#
#Otherwise, returns false/nil.
def unsafe_sql? exp, ignore_hash = false
return unless sexp?(exp)
dangerous_value = find_dangerous_value exp, ignore_hash
safe_value?(dangerous_value) ? false : dangerous_value
end
#Check _exp_ for dangerous values. Used by unsafe_sql?
def find_dangerous_value exp, ignore_hash
case exp.node_type
when :lit, :str, :const, :colon2, :true, :false, :nil
nil
when :array
#Assume this is an array like
#
# ["blah = ? AND thing = ?", ...]
#
#and check first value
unsafe_sql? exp[1]
when :dstr
check_string_interp exp
when :hash
if kwsplat? exp and has_immediate_user_input? exp
exp
elsif not ignore_hash
check_hash_values exp
else
nil
end
when :if
unsafe_sql? exp.then_clause or unsafe_sql? exp.else_clause
when :call
unless IGNORE_METHODS_IN_SQL.include? exp.method
if has_immediate_user_input? exp
exp
elsif TO_STRING_METHODS.include? exp.method
find_dangerous_value exp.target, ignore_hash
else
check_call exp
end
end
when :or
if unsafe = (unsafe_sql?(exp.lhs) || unsafe_sql?(exp.rhs))
unsafe
else
nil
end
when :block, :rlist
unsafe_sql? exp.last
else
if has_immediate_user_input? exp
exp
else
nil
end
end
end
#Checks hash values associated with these keys:
#
# * conditions
# * order
# * having
# * joins
# * select
# * from
# * lock
def check_hash_values exp
hash_iterate(exp) do |key, value|
if symbol? key
unsafe = case key.value
when :conditions, :having, :select
check_query_arguments value
when :order, :group
check_order_arguments value
when :joins
check_joins_arguments value
when :lock
check_lock_arguments value
when :from
unsafe_sql? value
else
nil
end
return unsafe if unsafe
end
end
false
end
def check_for_string_building exp
return unless call? exp
target = exp.target
method = exp.method
arg = exp.first_arg
if STRING_METHODS.include? method
check_str_target_or_arg(target, arg) or
check_interp_target_or_arg(target, arg) or
check_for_string_building(target) or
check_for_string_building(arg)
else
nil
end
end
def check_str_target_or_arg target, arg
if string? target
check_string_arg arg
elsif string? arg
check_string_arg target
end
end
def check_interp_target_or_arg target, arg
if string_interp? target or string_interp? arg
check_string_arg target and
check_string_arg arg
end
end
def check_string_arg exp
if safe_value? exp
nil
elsif string_building? exp
check_for_string_building exp
elsif string_interp? exp
check_string_interp exp
elsif call? exp and exp.method == :to_s
check_string_arg exp.target
else
exp
end
end
IGNORE_METHODS_IN_SQL = Set[:id, :merge_conditions, :table_name, :quoted_table_name,
:quoted_primary_key, :to_i, :to_f, :sanitize_sql, :sanitize_sql_array,
:sanitize_sql_for_assignment, :sanitize_sql_for_conditions, :sanitize_sql_hash,
:sanitize_sql_hash_for_assignment, :sanitize_sql_hash_for_conditions,
:to_sql, :sanitize, :primary_key, :table_name_prefix, :table_name_suffix,
:where_values_hash, :foreign_key, :uuid, :escape, :escape_string
]
def ignore_methods_in_sql
@ignore_methods_in_sql ||= IGNORE_METHODS_IN_SQL + (tracker.options[:sql_safe_methods] || [])
end
def safe_value? exp
return true unless sexp? exp
case exp.node_type
when :str, :lit, :const, :colon2, :nil, :true, :false
true
when :call
if exp.method == :to_s or exp.method == :to_sym
safe_value? exp.target
else
ignore_call? exp
end
when :if
safe_value? exp.then_clause and safe_value? exp.else_clause
when :block, :rlist
safe_value? exp.last
when :or
safe_value? exp.lhs and safe_value? exp.rhs
when :dstr
not unsafe_string_interp? exp
else
false
end
end
def ignore_call? exp
return unless call? exp
ignore_methods_in_sql.include? exp.method or
quote_call? exp or
arel? exp or
exp.method.to_s.end_with? "_id" or
number_target? exp or
date_target? exp or
locale_call? exp
end
QUOTE_METHODS = [:quote, :quote_column_name, :quoted_date, :quote_string, :quote_table_name]
def quote_call? exp
if call? exp.target
exp.target.method == :connection and QUOTE_METHODS.include? exp.method
elsif exp.target.nil?
exp.method == :quote_value
end
end
AREL_METHODS = [:all, :and, :arel_table, :as, :eq, :eq_any, :exists, :group,
:gt, :gteq, :having, :in, :join_sources, :limit, :lt, :lteq, :not,
:not_eq, :on, :or, :order, :project, :skip, :take, :where, :with]
def arel? exp
call? exp and (AREL_METHODS.include? exp.method or arel? exp.target)
end
#Check call for string building
def check_call exp
return unless call? exp
unsafe = check_for_string_building exp
if unsafe
unsafe
elsif call? exp.target
check_call exp.target
else
nil
end
end
def check_exists arg
if call? arg and arg.method == :to_s
false
else
check_find_arguments arg
end
end
#Prior to Rails 2.1.1, the :offset and :limit parameters were not
#escaping input properly.
#
#http://www.rorsecurity.info/2008/09/08/sql-injection-issue-in-limit-and-offset-parameter/
def check_for_limit_or_offset_vulnerability options
return false if rails_version.nil? or rails_version >= "2.1.1" or not hash?(options)
return true if hash_access(options, :limit) or hash_access(options, :offset)
false
end
#Look for something like this:
#
# params[:x].constantize.find('something')
#
# s(:call,
# s(:call,
# s(:call,
# s(:call, nil, :params, s(:arglist)),
# :[],
# s(:arglist, s(:lit, :x))),
# :constantize,
# s(:arglist)),
# :find,
# s(:arglist, s(:str, "something")))
def constantize_call? result
call = result[:call]
call? call.target and call.target.method == :constantize
end
SELF_CLASS = s(:call, s(:self), :class)
def connect_call? result
call = result[:call]
target = call.target
if call? target and target.method == :connection
target = target.target
klass = class_name(target)
target.nil? or
target == SELF_CLASS or
node_type? target, :self or
klass == :"ActiveRecord::Base" or
active_record_models.include? klass
end
end
def number_target? exp
return unless call? exp
if number? exp.target
true
elsif call? exp.target
number_target? exp.target
else
false
end
end
DATE_CLASS = s(:const, :Date)
def date_target? exp
return unless call? exp
if exp.target == DATE_CLASS
true
elsif call? exp.target
date_target? exp.target
else
false
end
end
end