class QueryHelper::SqlParser

Attributes

sql[RW]

Public Class Methods

new(sql) click to toggle source
# File lib/query_helper/sql_parser.rb, line 9
def initialize(sql)
  update(sql)
end

Public Instance Methods

find_aliases() click to toggle source
# 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() click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 75
def from_included?
  !from_index.nil?
end
from_index(position=:start) click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 83
def group_by_included?
  !group_by_index.nil?
end
group_by_index(position=:start) click to toggle source
# 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() click to toggle source

def group_by_clause

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

end

# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 87
def having_included?
  !having_index.nil?
end
having_index(position=:start) click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 95
def limit_included?
  !limit_index.nil?
end
limit_index(position=:start) click to toggle source
# 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() click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 91
def order_by_included?
  !order_by_index.nil?
end
order_by_index(position=:start) click to toggle source
# 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() click to toggle source
# 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() click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 71
def select_included?
  !select_index.nil?
end
select_index(position=:start) click to toggle source
# 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) click to toggle source
# File lib/query_helper/sql_parser.rb, line 13
def update(sql)
  @sql = sql
  remove_comments()
  white_out()
end
where_clause() click to toggle source
# 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?() click to toggle source
# File lib/query_helper/sql_parser.rb, line 79
def where_included?
  !where_index.nil?
end
where_index(position=:start) click to toggle source
# 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() click to toggle source
# 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

Private Instance Methods

find_index(regex, position=:start) click to toggle source
# File lib/query_helper/sql_parser.rb, line 184
def find_index(regex, position=:start)
  start_position = @white_out_sql.rindex(regex)
  return position == :start ? start_position : start_position + @white_out_sql[regex].size()
end