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 



167
168
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
 
# File 'lib/query_helper/sql_parser.rb', line 167

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|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 



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

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

#from_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

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) 


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

def group_by_included?
  !group_by_index.nil?
end

#group_by_index(position = :start) ⇒ Object 



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

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



155
156
157
 
# File 'lib/query_helper/sql_parser.rb', line 155

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

#having_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def having_included?
  !having_index.nil?
end

#having_index(position = :start) ⇒ Object 



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

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

#insert_having_index ⇒ Object 



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

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 



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

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

#insert_limit_index ⇒ Object 



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

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 



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

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 



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

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

#insert_select_index ⇒ Object 



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

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

#insert_where_index ⇒ Object 



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

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

#limit_clause ⇒ Object 



163
164
165
 
# File 'lib/query_helper/sql_parser.rb', line 163

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

#limit_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def limit_included?
  !limit_index.nil?
end

#limit_index(position = :start) ⇒ Object 



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

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

#order_by_clause ⇒ Object 



159
160
161
 
# File 'lib/query_helper/sql_parser.rb', line 159

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

#order_by_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def order_by_included?
  !order_by_index.nil?
end

#order_by_index(position = :start) ⇒ Object 



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

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

#qualify_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

def qualify_included?
  !qualify_index.nil?
end

#qualify_index(position = :start) ⇒ Object 



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

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
 
# 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 



139
140
141
 
# File 'lib/query_helper/sql_parser.rb', line 139

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

#select_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

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 



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

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

#where_included? ⇒ Boolean

Returns:

  • (Boolean) 


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

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