Class: Sneaql::Core::ExpressionHandler

Inherits:
Object
  • Object
show all
Defined in:
lib/sneaql_lib/expressions.rb

Overview

Handles variables, expression evaluation, and comparisons. A single ExpressionHandler is created per transform. This object will get passed around to the various commands as well as other manager objects attached to the transform class.

Instance Method Summary collapse

Constructor Details

#initialize(logger = nil) ⇒ ExpressionHandler

Returns a new instance of ExpressionHandler.

Parameters:

  • environment_variables (Hash) —

    pass in a set of ENV

  • logger (Logger) (defaults to: nil) —

    object otherwise will default to new Logger



13
14
15
16
17
18
19
 
# File 'lib/sneaql_lib/expressions.rb', line 13

def initialize(logger = nil)
  @logger = logger ? logger : Logger.new(STDOUT)
  @environment_variables = filtered_environment_variables
  validate_environment_variables unless ENV['SNEAQL_DISABLE_SQL_INJECTION_CHECK']
  @environment_variables.freeze
  @session_variables = {}
end

Instance Method Details

#array_has_boolean_value?(arr) ⇒ Boolean

checks to see if any element of the array has a boolean value

Parameters:

  • arr (Array<Object>) —

    array of objects

Returns:

  • (Boolean) 


254
255
256
257
258
 
# File 'lib/sneaql_lib/expressions.rb', line 254

def array_has_boolean_value?(arr)
  tmp = arr.map { |e| e.class }
  # [exp1.class, exp2.class].include?(FalseClass) || [exp1.class, exp2.class].include?(TrueClass)
  tmp.include?(FalseClass) || tmp.include?(TrueClass)
end

#coerce_boolean(value) ⇒ Object

evaluates string or fixnum values to coerce into boolean

Parameters:

  • value (Object)

Returns:

  • (Object) 


263
264
265
266
267
268
269
270
271
272
273
274
 
# File 'lib/sneaql_lib/expressions.rb', line 263

def coerce_boolean(value)
  retval = nil
  if array_has_boolean_value?([value])
    retval = value
  elsif value.class == Fixnum
    retval = true if value == 1
    retval = false if value.zero?
  elsif value.class == String
    retval = text_to_boolean(value)
  end
  retval
end

#coerce_data_types(exp1, exp2) ⇒ Array<Float, Fixnum, String>

coerces the data types for both expressions to match for valid comparison

Parameters:

  • exp1 (String, Float, Fixnum) —

    expression for left operand

  • exp2 (String, Float, Fixnum) —

    expression for right operand

Returns:

  • (Array<Float, Fixnum, String>) —

    returns array with both input expressions coerced to the same data type



203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
 
# File 'lib/sneaql_lib/expressions.rb', line 203

def coerce_data_types(exp1, exp2)
  # coerce data types to make for a good comparison
  @logger.debug("coercing types #{[exp1.class, exp2.class]}")
  if exp1.class == exp2.class
    nil # nothing to do... continue with comparison
  elsif array_has_boolean_value?([exp1, exp2])
    # if one of the values is an actual Boolean object...
    unless [coerce_boolean(exp1), coerce_boolean(exp2)].include?(nil)
      exp1 = coerce_boolean(exp1)
      exp2 = coerce_boolean(exp2)
    end
  elsif exp1.class == Time
    tmp = coerce_datetime(exp2)
    if tmp
      exp2 = tmp
    else
      exp1, exp2 = exp1.to_s, exp2.to_s
    end
  elsif exp2.class == Time
    tmp = coerce_datetime(exp1)
    if tmp
      exp1 = tmp
    else
      exp1, exp2 = exp1.to_s, exp2.to_s
    end
  elsif [exp1.class, exp2.class].include?(Float)
    # if either is a float then make sure they are both floats
    exp1 = exp1.to_f
    exp2 = exp2.to_f
  elsif [exp1.class, exp2.class].include?(Fixnum)
    # otherwise... if one is an integer make them both integers
    exp1 = exp1.to_i
    exp2 = exp2.to_i
  end
  @logger.debug("coerced types #{[exp1.class, exp2.class]}")
  [exp1, exp2]
end

#coerce_datetime(obj) ⇒ Object 



241
242
243
244
245
246
247
248
249
 
# File 'lib/sneaql_lib/expressions.rb', line 241

def coerce_datetime(obj)
  retobj = nil
  case
    when obj.class == Fixnum then retobj = Time.at(obj)
    when obj.class == String then retobj = Time.parse(obj)
    when obj.class == Time then retobj = obj
  end
  retobj
end

#compare_expressions(operator, exp1, exp2) ⇒ Object

provides a standardized method of comparing two expressions. note that this only works for variables and constants. current version supports float, integer, and contigious strings. as of 0.0.13 expressions need to be evaluated before being passed

Parameters:

  • operator (String) —

    comparison operator @see valid_operators

  • exp1 (String) —

    expression for left operand

  • exp2 (String) —

    expression for right operand



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/sneaql_lib/expressions.rb', line 183

def compare_expressions(operator, exp1, exp2)
  unless valid_operators.include?(operator)
    raise Sneaql::Exceptions::InvalidComparisonOperator
  end

  @logger.debug "evaluating #{exp1} #{operator} #{exp2}"

  # evaluate exps and coerce data types
  coerced = coerce_data_types(
    exp1,
    exp2
  )

  compare_values(operator, coerced[0], coerced[1])
end

#compare_values(operator, exp1, exp2) ⇒ Boolean

performs the actual comparison between two values

Parameters:

  • operator (String) —

    comparison operator @see valid_operators

  • exp1 (String) —

    expression for left operand

  • exp2 (String) —

    expression for right operand

Returns:

  • (Boolean) 


294
295
296
297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/sneaql_lib/expressions.rb', line 294

def compare_values(operator, exp1, exp2)
  # below are all the valid comparison operators
  @logger.debug("comparing #{exp1} #{operator} #{exp2}")
  case operator
  when '=' then return exp1 == exp2
  when '!=' then return exp1 != exp2
  when '>=' then return exp1 >= exp2
  when '<=' then return exp1 <= exp2
  when '>' then return exp1 > exp2
  when '<' then return exp1 < exp2
  when 'like' then return like_operator(exp1, exp2)
  when 'notlike' then return !like_operator(exp1, exp2)
  end
end

#evaluate_all_expressions(statement) ⇒ String

evaluates all expressions in a given SQL statement.

replaces...
  environment variables in the form :env_HOSTNAME
  session variables in the form :variable_name
  session variables in the deprecated form {variable_name}

Parameters:

  • statement (String) —

    SQL statement to have all expressions evaluated

Returns:

  • (String) —

    SQL statement with all variable references resolved



111
112
113
114
115
116
117
118
119
120
 
# File 'lib/sneaql_lib/expressions.rb', line 111

def evaluate_all_expressions(statement)
  evaluate_session_variables(statement)
  evaluate_environment_variables(statement)
  evaluate_session_variables_braces(statement)
  return statement
rescue => e
  @logger.error "evaluation error #{e.message}"
  e.backtrace.each { |b| logger.error b.to_s }
  raise Sneaql::Exceptions::ExpressionEvaluationError
end

#evaluate_environment_variables(statement) ⇒ String

evaluates all session variables in a given SQL statement.

replaces...
  session variables in the form :variable_name

variables are sorted and reversed before performing substitutions this prevents :var from partially overwriting :var_foo

Parameters:

  • statement (String) —

    SQL statement to have all session variables evaluated

Returns:

  • (String) —

    SQL statement with all variable references resolved



143
144
145
146
147
148
 
# File 'lib/sneaql_lib/expressions.rb', line 143

def evaluate_environment_variables(statement)
  # replace env vars in the form :env_HOSTNAME
  @environment_variables.keys.sort.reverse.each do |e|
    statement.gsub!(/\:env\_#{e}/i, @environment_variables[e])
  end
end

#evaluate_expression(expression) ⇒ String, ...

or reference to session or environment variable

Parameters:

  • expression (String) —

    either a numeric constant, string constant in ”,

Returns:

  • (String, Fixnum, Float) 


58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/sneaql_lib/expressions.rb', line 58

def evaluate_expression(expression)
  return expression unless expression.class == String

  # first handle empty string
  if expression == "''"
    return ''

  # reference to an environment variable
  # :env_var_name or :ENV_var_name
  # env variable references are case insensitive in this case
  elsif expression =~ /^\:env\_\w+/i
    return @environment_variables[expression.gsub(/\:env\_/i, '').strip]

  # reference to a variable
  # ANSI dynamic SQL :var_name
  # variable names are case sensitive
  elsif expression =~ /^\:\w+/
    return @session_variables[expression.gsub(/\:/, '').strip]

  # deprecated
  elsif expression =~ /^\{.*\}$/
    @logger.warn('{var_name} deprecated. use dynamic SQL syntax :var_name')
    return @session_variables[expression.gsub(/\{|\}/, '').strip]

  # boolean
  elsif ['true', 'false'].include?(expression.downcase)
    @logger.debug "handling #{expression} as boolean"
    return true if expression.downcase == 'true'
    return false if expression.downcase == 'false'

  # string literal enclosed in single quotes

  elsif expression.match(/^\s*\'.*\'\s*$/)
    return expression.strip[1..expression.strip.length - 2]

  # else assume it is a numeric literal
  # need some better thinking here
  else
    return expression.strip
  end
rescue => e
  @logger.error("error evaluating expression: #{e.message}")
  e.backtrace.each { |b| @logger.error(b.to_s) }
  raise Sneaql::Exceptions::ExpressionEvaluationError
end

#evaluate_session_variables(statement) ⇒ String

evaluates all environment variables in a given SQL statement.

replaces...
  environment variables in the form :env_HOSTNAME

variables are sorted and reversed before performing substitutions this prevents :var from partially overwriting :var_foo

Parameters:

  • statement (String) —

    SQL statement to have all environment variables evaluated

Returns:

  • (String) —

    SQL statement with all variable references resolved



129
130
131
132
133
134
 
# File 'lib/sneaql_lib/expressions.rb', line 129

def evaluate_session_variables(statement)
  # replaces :var_name in provided statement
  @session_variables.keys.sort.reverse.each do |k|
    statement.gsub!(/\:#{k}/, @session_variables[k].to_s)
  end
end

#evaluate_session_variables_braces(statement) ⇒ String

Deprecated.

evaluates all session variables in a given SQL statement.

replaces...
  session variables in the deprecated form {variable_name}

Parameters:

  • statement (String) —

    SQL statement to have all deprecated form variable references evaluated

Returns:

  • (String) —

    SQL statement with all variable references resolved



156
157
158
159
160
161
 
# File 'lib/sneaql_lib/expressions.rb', line 156

def evaluate_session_variables_braces(statement)
  # deprecated
  @session_variables.keys.each do |k|
    statement.gsub!(/\{#{k}\}/, @session_variables[k].to_s)
  end
end

#filtered_environment_variables ⇒ Object

create a hash built from supplied environment variables. if SNEAQL_AVAILABLE_ENV_VARS is provided (as a comma delimited list) only the listed values are included. return <Hash>



329
330
331
332
333
334
335
336
337
338
339
340
 
# File 'lib/sneaql_lib/expressions.rb', line 329

def filtered_environment_variables
  env_vars = {}
  if ENV['SNEAQL_AVAILABLE_ENV_VARS']
    @logger.debug('filtering environment variables')
    available = ENV['SNEAQL_AVAILABLE_ENV_VARS'].split(',')
    ENV.keys.each { |k| env_vars[k] = ENV[k] if available.include?(k) }
  else
    @logger.debug('setting environment variables')
    ENV.keys.each { |k| env_vars[k] = ENV[k] }
  end
  env_vars
end

#get_environment_variable(var_name) ⇒ String

Parameters:

  • var_name (String) —

    identifier for environment variable as defined in ENV

Returns:

  • (String) 


51
52
53
 
# File 'lib/sneaql_lib/expressions.rb', line 51

def get_environment_variable(var_name)
  @environment_variables[var_name]
end

#get_session_variable(var_name) ⇒ String, ...

Parameters:

  • var_name (String) —

    identifier for variable

Returns:

  • (String, Fixnum, Float) 


40
41
42
 
# File 'lib/sneaql_lib/expressions.rb', line 40

def get_session_variable(var_name)
  @session_variables[var_name]
end

#like_operator(left_operand, like_right_operand) ⇒ Boolean

performs SQL style LIKE comparison between inputs

Parameters:

  • left_operand (String)
  • like_right_operand (String) —

    this will be the like expression

Returns:

  • (Boolean) 


313
314
315
316
 
# File 'lib/sneaql_lib/expressions.rb', line 313

def like_operator(left_operand, like_right_operand)
  # converts to string before comparison
  left_operand.to_s.match(wildcard_to_regex(like_right_operand.to_s)) ? true : false
end

#output_all_session_variables ⇒ Object

convenience method, outputs all session variables to the logger



45
46
47
 
# File 'lib/sneaql_lib/expressions.rb', line 45

def output_all_session_variables
  @logger.debug("current session variables: #{@session_variables}")
end

#set_session_variable(var_name, var_value) ⇒ Object

Parameters:

  • var_name (String) —

    identifier for variable

  • var_value (String, Fixnum, Float) —

    value to store, expressions here will not be evaluated



23
24
25
26
27
 
# File 'lib/sneaql_lib/expressions.rb', line 23

def set_session_variable(var_name, var_value)
  @logger.info("setting session var #{var_name} to #{var_value}")
  raise "can't set variable #{var_name}" unless valid_session_variable_name?(var_name)
  @session_variables[var_name] = var_value
end

#sql_injection_filter(value) ⇒ Boolean

basic regex filtering to help combat sql injection by way of environment variables

Parameters:

  • value (String) —

    value for validation

Returns:

  • (Boolean) —

    returns true if value is safe



346
347
348
349
 
# File 'lib/sneaql_lib/expressions.rb', line 346

def sql_injection_filter(value)
  return false if value.to_s.match(/(\'|\;|(drop|alter).*(table|user|view|column|database|schema|function|sequence|procedure))/i)
  true
end

#text_to_boolean(value) ⇒ Boolean

converts text representation of boolean to boolean

Parameters:

  • value (String)

Returns:

  • (Boolean) 


279
280
281
282
283
284
285
286
287
 
# File 'lib/sneaql_lib/expressions.rb', line 279

def text_to_boolean(value)
  case value.downcase.strip
  when 'f', 'false', '0'
    retval = false
  when 't', 'true', '1'
    retval = true
  end
  retval
end

#valid_expression_reference?(expr) ⇒ Boolean

validates that this would make a suitable reference at run time. checks to see this is single quoted string, :variable_name, {var_name) or number (1, 1.031, etc.)

Parameters:

  • expr (String) —

    value to check

Returns:

  • (Boolean) 


166
167
168
 
# File 'lib/sneaql_lib/expressions.rb', line 166

def valid_expression_reference?(expr)
  expr.to_s.match(/(^\s*\'.+\'\s*$|^\:\w+$|^\{\w+\}$|^\d+$|^\d+\.\d*$|true|false)/i) ? true : false
end

#valid_operators ⇒ Array<String>

Operators valid for expression comparison

Returns:

  • (Array<String>) 


172
173
174
 
# File 'lib/sneaql_lib/expressions.rb', line 172

def valid_operators
  ['=', '!=', '>', '<', '>=', '<=', 'like', 'notlike']
end

#valid_session_variable_name?(var_name) ⇒ Boolean

validates that this would make a suitable variable name

Parameters:

  • var_name (String)

Returns:

  • (Boolean) 


32
33
34
35
36
 
# File 'lib/sneaql_lib/expressions.rb', line 32

def valid_session_variable_name?(var_name)
  r = var_name.to_s.match(/^\w+$/) && !var_name.to_s.match(/env\_\w*/) && !var_name.to_s.match(/^\d+/) ? true : false
  @logger.debug("validating #{var_name} as valid variable identifier indicates #{r}")
  r
end

#validate_environment_variables ⇒ Object

insures that all environment variables pass SQL injection test



352
353
354
355
356
357
358
 
# File 'lib/sneaql_lib/expressions.rb', line 352

def validate_environment_variables
  @environment_variables.keys.each do |k|
    unless sql_injection_filter(@environment_variables[k])
      raise 'SQL Injection Filter Error'
    end
  end
end

#wildcard_to_regex(wildcard) ⇒ Regexp

converts a SQL LIKE wildcard expression to a Regexp

Parameters:

  • wildcard (String) —

    like expression

Returns:

  • (Regexp) —

    returns regexp object for use in match comparison



321
322
323
 
# File 'lib/sneaql_lib/expressions.rb', line 321

def wildcard_to_regex(wildcard)
  Regexp.new("^#{wildcard}$".gsub('%', '.*').gsub('_', '.'))
end