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 



169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/query_helper/sql_parser.rb', line 169

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: /\b(array_agg|avg|bit_and|bit_or|bool_and|bool_or|boolor_agg|booland_agg|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 



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

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

#from_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def from_included?
  !from_index.nil?
end

#from_index(position = :start) ⇒ Object 



43
44
45
46
 
# File 'lib/query_helper/sql_parser.rb', line 43

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

#group_by_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def group_by_included?
  !group_by_index.nil?
end

#group_by_index(position = :start) ⇒ Object 



58
59
60
61
 
# File 'lib/query_helper/sql_parser.rb', line 58

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



157
158
159
 
# File 'lib/query_helper/sql_parser.rb', line 157

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

#having_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def having_included?
  !having_index.nil?
end

#having_index(position = :start) ⇒ Object 



63
64
65
66
 
# File 'lib/query_helper/sql_parser.rb', line 63

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

#insert_having_index ⇒ Object 



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

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 



114
115
116
 
# File 'lib/query_helper/sql_parser.rb', line 114

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

#insert_limit_index ⇒ Object 



136
137
138
139
 
# File 'lib/query_helper/sql_parser.rb', line 136

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 



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

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_qualify_index ⇒ Object 



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

def insert_qualify_index
  order_by_index() || limit_index() || @sql.length
end

#insert_select_index ⇒ Object 



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

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

#insert_where_index ⇒ Object 



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

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

#limit_clause ⇒ Object 



165
166
167
 
# File 'lib/query_helper/sql_parser.rb', line 165

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

#limit_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def limit_included?
  !limit_index.nil?
end

#limit_index(position = :start) ⇒ Object 



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

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

#order_by_clause ⇒ Object 



161
162
163
 
# File 'lib/query_helper/sql_parser.rb', line 161

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

#order_by_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def order_by_included?
  !order_by_index.nil?
end

#order_by_index(position = :start) ⇒ Object 



68
69
70
71
 
# File 'lib/query_helper/sql_parser.rb', line 68

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

#qualify_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def qualify_included?
  !qualify_index.nil?
end

#qualify_index(position = :start) ⇒ Object 



53
54
55
56
 
# File 'lib/query_helper/sql_parser.rb', line 53

def qualify_index(position=:start)
  regex = / [Qq][Uu][Aa][Ll][Ii][Ff][Yy] /
  find_index(regex, position)
end

#remove_comments ⇒ Object 



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

def remove_comments
  # Remove SQL inline comments (/* */) and line comments (--)
  @sql = @sql.gsub(%r{/\*[^/]*?\*/}m, '')  # Removes multi-line comments (/* ... */)
          .gsub(/--[^\n]*/, '')            # Removes single-line comments (--)

  @sql.squish!
end

#select_clause ⇒ Object 



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

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

#select_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def select_included?
  !select_index.nil?
end

#select_index(position = :start) ⇒ Object 



38
39
40
41
 
# File 'lib/query_helper/sql_parser.rb', line 38

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 



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

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

#where_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def where_included?
  !where_index.nil?
end

#where_index(position = :start) ⇒ Object 



48
49
50
51
 
# File 'lib/query_helper/sql_parser.rb', line 48

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

#white_out ⇒ Object 



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

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