Module: SqlGenius::Core::SqlValidator

Extended by:
SqlValidator
Included in:
SqlValidator
Defined in:
lib/sql_genius/core/sql_validator.rb

Constant Summary collapse

FORBIDDEN_KEYWORDS = 
["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "TRUNCATE", "GRANT", "REVOKE"].freeze
MYSQL_SYSTEM_SCHEMAS = 
["information_schema", "mysql", "performance_schema", "sys"].freeze
POSTGRESQL_SYSTEM_SCHEMAS = 
["information_schema", "pg_catalog", "pg_toast", "pg_temp"].freeze

Instance Method Summary collapse

Instance Method Details

#apply_row_limit(sql, limit) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/sql_genius/core/sql_validator.rb', line 50

def apply_row_limit(sql, limit)
  if sql.match?(/\bLIMIT\s+\d+\s*,\s*\d+/i)
    sql.gsub(/\bLIMIT\s+(\d+)\s*,\s*(\d+)/i) do
      "LIMIT #{::Regexp.last_match(1).to_i}, #{[::Regexp.last_match(2).to_i, limit].min}"
    end
  elsif sql.match?(/\bLIMIT\s+\d+/i)
    sql.gsub(/\bLIMIT\s+(\d+)/i) { "LIMIT #{[::Regexp.last_match(1).to_i, limit].min}" }
  else
    "#{sql.gsub(/;\s*\z/, "")} LIMIT #{limit}"
  end
end

#extract_table_references(sql, connection) ⇒ Object 



40
41
42
43
44
45
46
47
48
 
# File 'lib/sql_genius/core/sql_validator.rb', line 40

def extract_table_references(sql, connection)
  tables = []
  sql.scan(/\bFROM\s+((?:["`]?\w+["`]?(?:\s*,\s*["`]?\w+["`]?)*)+)/i) do |m|
    m[0].scan(/["`]?(\w+)["`]?/) { |t| tables << t[0] }
  end
  sql.scan(/\bJOIN\s+["`]?(\w+)["`]?/i) { |m| tables << m[0] }
  sql.scan(/\b(?:INTO|UPDATE)\s+["`]?(\w+)["`]?/i) { |m| tables << m[0] }
  tables.uniq.map(&:downcase) & connection.tables
end

#masked_column?(column_name, patterns) ⇒ Boolean

Returns:

  • (Boolean) 


69
70
71
 
# File 'lib/sql_genius/core/sql_validator.rb', line 69

def masked_column?(column_name, patterns)
  patterns.any? { |pattern| column_name.downcase.include?(pattern) }
end

#normalize_identifier_quotes(sql, connection) ⇒ Object 



62
63
64
65
66
67
 
# File 'lib/sql_genius/core/sql_validator.rb', line 62

def normalize_identifier_quotes(sql, connection)
  quote = connection.quote_table_name("sql_genius_identifier_probe")[0]
  return sql if quote == "`" || !sql.include?("`")

  rewrite_backtick_identifiers(sql, connection)
end

#read_backtick_identifier(sql, index) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/sql_genius/core/sql_validator.rb', line 125

def read_backtick_identifier(sql, index)
  output = +""
  i = index + 1

  while i < sql.length
    if sql[i] == "`"
      if sql[i + 1] == "`"
        output << "`"
        i += 2
        next
      end

      i += 1
      break
    end

    output << sql[i]
    i += 1
  end

  [output, i]
end

#read_single_quoted_literal(sql, index) ⇒ Object 



103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/sql_genius/core/sql_validator.rb', line 103

def read_single_quoted_literal(sql, index)
  output = +"'"
  i = index + 1

  while i < sql.length
    output << sql[i]
    if sql[i] == "'"
      if sql[i + 1] == "'"
        output << sql[i + 1]
        i += 2
        next
      end

      i += 1
      break
    end
    i += 1
  end

  [output, i]
end

#rewrite_backtick_identifiers(sql, connection) ⇒ Object 



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'lib/sql_genius/core/sql_validator.rb', line 81

def rewrite_backtick_identifiers(sql, connection)
  output = +""
  i = 0

  while i < sql.length
    char = sql[i]

    if char == "'"
      literal, i = read_single_quoted_literal(sql, i)
      output << literal
    elsif char == "`"
      identifier, i = read_backtick_identifier(sql, i)
      output << connection.quote_table_name(identifier)
    else
      output << char
      i += 1
    end
  end

  output
end

#system_schemas_for(connection) ⇒ Object 



73
74
75
76
77
78
79
 
# File 'lib/sql_genius/core/sql_validator.rb', line 73

def system_schemas_for(connection)
  return MYSQL_SYSTEM_SCHEMAS unless connection.respond_to?(:server_version)

  connection.server_version.postgresql? ? POSTGRESQL_SYSTEM_SCHEMAS : MYSQL_SYSTEM_SCHEMAS
rescue StandardError
  MYSQL_SYSTEM_SCHEMAS
end

#validate(sql, blocked_tables:, connection:) ⇒ Object 



13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/sql_genius/core/sql_validator.rb', line 13

def validate(sql, blocked_tables:, connection:)
  return "Please enter a query." if sql.nil? || sql.strip.empty?

  normalized = sql.gsub(/--.*$/, "").gsub(%r{/\*.*?\*/}m, "").strip

  unless normalized.match?(/\ASELECT\b/i) || normalized.match?(/\AWITH\b/i)
    return "Only SELECT queries are allowed."
  end

  system_schemas = system_schemas_for(connection)
  if normalized.match?(/\b(#{system_schemas.join("|")})\b/i)
    return "Access to system schemas is not allowed."
  end

  FORBIDDEN_KEYWORDS.each do |keyword|
    return "#{keyword} statements are not allowed." if normalized.match?(/\b#{keyword}\b/i)
  end

  tables_in_query = extract_table_references(normalized, connection)
  blocked = tables_in_query & blocked_tables
  if blocked.any?
    return "Access denied for table(s): #{blocked.join(", ")}."
  end

  nil
end