Class: QueryHelper::SqlParser

Inherits:
Object
  • Object
show all
Defined in:
lib/query_helper/sql_parser.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql) ⇒ SqlParser

Returns a new instance of SqlParser.



9
10
11
 
# File 'lib/query_helper/sql_parser.rb', line 9

def initialize(sql)
  update(sql)
end

Instance Attribute Details

#sql ⇒ Object

Returns the value of attribute sql.



7
8
9
 
# File 'lib/query_helper/sql_parser.rb', line 7

def sql
  @sql
end

Instance Method Details

#find_aliases ⇒ Object 



154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/query_helper/sql_parser.rb', line 154

def find_aliases
  # Determine alias expression combos.  White out sql used in case there
  # are any custom strings or subqueries in the select clause
  white_out_selects = @white_out_sql[select_index(:end)..from_index()]
  selects = @sql[select_index(:end)..from_index()]
  comma_split_points = white_out_selects.each_char.with_index.map{|char, i| i if char == ','}.compact
  comma_split_points.unshift(-1) # We need the first select clause to start out with a 'split'
  column_maps = white_out_selects.split(",").each_with_index.map do |x,i|
    sql_alias = x.squish.split(" as ")[1] || x.squish.split(" AS ")[1] || x.squish.split(".")[1] # look for custom defined aliases or table.column notation
    # sql_alias = nil unless /^[a-zA-Z_]+$/.match?(sql_alias) # only allow aliases with letters and underscores
    sql_expression = if x.split(" as ")[1]
      expression_length = x.split(" as ")[0].length
      selects[comma_split_points[i] + 1, expression_length]
    elsif x.squish.split(" AS ")[1]
      expression_length = x.split(" AS ")[0].length
      selects[comma_split_points[i] + 1, expression_length]
    elsif x.squish.split(".")[1]
      selects[comma_split_points[i] + 1, x.length]
    end
    ColumnMap.new(
      alias_name: sql_alias,
      sql_expression: sql_expression.squish,
      aggregate: /(array_agg|avg|bit_and|bit_or|bool_and|bool_or|count|every|json_agg|jsonb_agg|json_object_agg|jsonb_object_agg|max|min|string_agg|sum|xmlagg)\((.*)\)/.match?(sql_expression)
    ) if sql_alias
  end
  column_maps.compact
end

#from_clause ⇒ Object 



130
131
132
 
# File 'lib/query_helper/sql_parser.rb', line 130

def from_clause
  @sql[from_index()..insert_join_index()].strip if from_included?
end

#from_included? ⇒ Boolean

Returns:

  • (Boolean) 


75
76
77
 
# File 'lib/query_helper/sql_parser.rb', line 75

def from_included?
  !from_index.nil?
end

#from_index(position = :start) ⇒ Object 



41
42
43
44
 
# File 'lib/query_helper/sql_parser.rb', line 41

def from_index(position=:start)
  regex = / [Ff][Rr][Oo][Mm] /
  find_index(regex, position)
end

#group_by_included? ⇒ Boolean

Returns:

  • (Boolean) 


83
84
85
 
# File 'lib/query_helper/sql_parser.rb', line 83

def group_by_included?
  !group_by_index.nil?
end

#group_by_index(position = :start) ⇒ Object 



51
52
53
54
 
# File 'lib/query_helper/sql_parser.rb', line 51

def group_by_index(position=:start)
  regex = / [Gg][Rr][Oo][Uu][Pp] [Bb][Yy] /
  find_index(regex, position)
end

#having_clause ⇒ Object

def group_by_clause

@sql[group_by_index()..insert_group_by_index()] if group_by_included?

end



142
143
144
 
# File 'lib/query_helper/sql_parser.rb', line 142

def having_clause
  @sql[having_index()..insert_having_index()].strip if having_included?
end

#having_included? ⇒ Boolean

Returns:

  • (Boolean) 


87
88
89
 
# File 'lib/query_helper/sql_parser.rb', line 87

def having_included?
  !having_index.nil?
end

#having_index(position = :start) ⇒ Object 



56
57
58
59
 
# File 'lib/query_helper/sql_parser.rb', line 56

def having_index(position=:start)
  regex = / [Hh][Aa][Vv][Ii][Nn][Gg] /
  find_index(regex, position)
end

#insert_having_index ⇒ Object 



111
112
113
114
 
# File 'lib/query_helper/sql_parser.rb', line 111

def insert_having_index
  # raise InvalidQueryError.new("Cannot calculate insert_having_index because the query has no group by clause") unless group_by_included?
  order_by_index() || limit_index() || @sql.length
end

#insert_join_index ⇒ Object 



103
104
105
 
# File 'lib/query_helper/sql_parser.rb', line 103

def insert_join_index
  where_index() || group_by_index() || order_by_index() || limit_index() || @sql.length
end

#insert_limit_index ⇒ Object 



121
122
123
124
 
# File 'lib/query_helper/sql_parser.rb', line 121

def insert_limit_index
  # raise InvalidQueryError.new("This query already includes a limit clause") if limit_included?
  @sql.length
end

#insert_order_by_index ⇒ Object 



116
117
118
119
 
# File 'lib/query_helper/sql_parser.rb', line 116

def insert_order_by_index
  # raise InvalidQueryError.new("This query already includes an order by clause") if order_by_included?
  limit_index() || @sql.length
end

#insert_select_index ⇒ Object 



99
100
101
 
# File 'lib/query_helper/sql_parser.rb', line 99

def insert_select_index
  from_index() || where_index() || group_by_index() || order_by_index() || limit_index() || @sql.length
end

#insert_where_index ⇒ Object 



107
108
109
 
# File 'lib/query_helper/sql_parser.rb', line 107

def insert_where_index
  group_by_index() || order_by_index() || limit_index() || @sql.length
end

#limit_clause ⇒ Object 



150
151
152
 
# File 'lib/query_helper/sql_parser.rb', line 150

def limit_clause
  @sql[limit_index()..insert_limit_index()].strip if limit_included?
end

#limit_included? ⇒ Boolean

Returns:

  • (Boolean) 


95
96
97
 
# File 'lib/query_helper/sql_parser.rb', line 95

def limit_included?
  !limit_index.nil?
end

#limit_index(position = :start) ⇒ Object 



66
67
68
69
 
# File 'lib/query_helper/sql_parser.rb', line 66

def limit_index(position=:start)
  regex = / [Ll][Ii][Mm][Ii][Tt] /
  find_index(regex, position)
end

#order_by_clause ⇒ Object 



146
147
148
 
# File 'lib/query_helper/sql_parser.rb', line 146

def order_by_clause
  @sql[order_by_index()..insert_order_by_index()].strip if order_by_included?
end

#order_by_included? ⇒ Boolean

Returns:

  • (Boolean) 


91
92
93
 
# File 'lib/query_helper/sql_parser.rb', line 91

def order_by_included?
  !order_by_index.nil?
end

#order_by_index(position = :start) ⇒ Object 



61
62
63
64
 
# File 'lib/query_helper/sql_parser.rb', line 61

def order_by_index(position=:start)
  regex = / [Oo][Rr][Dd][Ee][Rr] [Bb][Yy] /
  find_index(regex, position)
end

#remove_comments ⇒ Object 



19
20
21
22
23
 
# File 'lib/query_helper/sql_parser.rb', line 19

def remove_comments
  # Remove SQL inline comments (/* */) and line comments (--)
  @sql = @sql.gsub(/\/\*(.*?)\*\//, '').gsub(/--(.*)$/, '')
  @sql.squish!
end

#select_clause ⇒ Object 



126
127
128
 
# File 'lib/query_helper/sql_parser.rb', line 126

def select_clause
  @sql[select_index()..insert_select_index()].strip if select_included?
end

#select_included? ⇒ Boolean

Returns:

  • (Boolean) 


71
72
73
 
# File 'lib/query_helper/sql_parser.rb', line 71

def select_included?
  !select_index.nil?
end

#select_index(position = :start) ⇒ Object 



36
37
38
39
 
# File 'lib/query_helper/sql_parser.rb', line 36

def select_index(position=:start)
  regex = /( |^)[Ss][Ee][Ll][Ee][Cc][Tt] / # space or new line at beginning of select
  find_index(regex, position)
end

#update(sql) ⇒ Object 



13
14
15
16
17
 
# File 'lib/query_helper/sql_parser.rb', line 13

def update(sql)
  @sql = sql
  remove_comments()
  white_out()
end

#where_clause ⇒ Object 



134
135
136
 
# File 'lib/query_helper/sql_parser.rb', line 134

def where_clause
  @sql[where_index()..insert_where_index()].strip if where_included?
end

#where_included? ⇒ Boolean

Returns:

  • (Boolean) 


79
80
81
 
# File 'lib/query_helper/sql_parser.rb', line 79

def where_included?
  !where_index.nil?
end

#where_index(position = :start) ⇒ Object 



46
47
48
49
 
# File 'lib/query_helper/sql_parser.rb', line 46

def where_index(position=:start)
  regex = / [Ww][Hh][Ee][Rr][Ee] /
  find_index(regex, position)
end

#white_out ⇒ Object 



25
26
27
28
29
30
31
32
33
34
 
# File 'lib/query_helper/sql_parser.rb', line 25

def white_out
  # Replace everything between () and '' and ""
  # This will allow us to ignore subqueries, common table expressions,
  # regex, custom strings, etc. when determining injection points
  # and performing other manipulations
  @white_out_sql = @sql.dup
  while @white_out_sql.scan(/\"[^""]*\"|\'[^'']*\'|\([^()]*\)/).length > 0 do
    @white_out_sql.scan(/\"[^""]*\"|\'[^'']*\'|\([^()]*\)/).each { |s| @white_out_sql.gsub!(s,s.gsub(/./, '*')) }
  end
end