Class: Woods::Console::SqlValidator

Inherits:
Object
  • Object
show all
Defined in:
lib/woods/console/sql_validator.rb

Overview

Validates SQL strings for read-only safety.

Allows only SELECT and WITH…SELECT statements. Rejects DML (INSERT, UPDATE, DELETE), DDL (CREATE, DROP, ALTER, TRUNCATE), and administrative commands (GRANT, REVOKE). Also rejects multiple statements (semicolons).

Uses pattern-based validation, not full SQL parsing.

Examples:

validator = SqlValidator.new
validator.validate!('SELECT * FROM users')         # passes
validator.validate!('DELETE FROM users')            # raises SqlValidationError
validator.valid?('SELECT 1')                       # => true

Constant Summary collapse

FORBIDDEN_KEYWORDS =

Forbidden statement prefixes (case-insensitive).

Expanded beyond DML/DDL to cover:

  • PG procedural (‘DO`, `CALL`) which can run arbitrary plpgsql.

  • Session-state mutation (‘SET`, `RESET`) — `SET ROLE`, `SET search_path` can swap out the effective permission set for the rest of the session even under rollback.

  • Admin/cluster ops (‘VACUUM`, `ANALYZE`, `CLUSTER`, `REINDEX`, `REFRESH`, `LOCK`) which are reads in the English-language sense but carry side effects or heavy locks.

  • Async signalling (‘LISTEN`, `NOTIFY`).

  • Prepared-statement lifecycle (‘PREPARE`, `EXECUTE`, `DEALLOCATE`).

  • Transaction control (‘BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE`, `START`) — SafeContext already owns the surrounding transaction; inner tx control would corrupt it.

  • File I/O vectors (‘LOAD`, `HANDLER`, `COPY`).

%w[
  INSERT UPDATE DELETE DROP ALTER TRUNCATE CREATE GRANT REVOKE
  DO CALL SET RESET LISTEN NOTIFY
  VACUUM ANALYZE CLUSTER REINDEX REFRESH LOCK
  PREPARE EXECUTE DEALLOCATE
  BEGIN COMMIT ROLLBACK SAVEPOINT RELEASE START
  LOAD HANDLER COPY
].freeze
BODY_FORBIDDEN_KEYWORDS =

Keywords that are forbidden anywhere in the SQL (not just at start).

UNION / INTERSECT / EXCEPT are SQL set operators — any of them can graft a second SELECT onto a validated one, which defeats the “single SELECT” posture even though TableGate still catches references to blocked tables. INTO / COPY are PostgreSQL write vectors that must not appear in read contexts.

%w[UNION INTERSECT EXCEPT INTO COPY].freeze
DANGEROUS_FUNCTIONS =

Dangerous functions that can be used for DoS or file access.

%w[
  pg_sleep lo_import lo_export pg_read_file pg_write_file
  load_file sleep benchmark
].freeze
ALLOWED_PREFIXES =

Allowed statement prefixes (case-insensitive).

‘EXPLAIN ANALYZE` actually executes the planned query on PostgreSQL (and the MySQL 8.0+ `EXPLAIN ANALYZE` does the same) — explicitly reject the `ANALYZE` variant. PostgreSQL also accepts an option-list form `EXPLAIN (ANALYZE, FORMAT JSON) SELECT …` where `ANALYZE` follows `(` rather than whitespace; the `(?!s*(?s*ANALYZE)` lookahead rejects both spellings so SafeContext doesn’t silently trust “we’re just planning, not running” for what is a side-effectful execution. ‘EXPLAIN (…)` without `ANALYZE` is still permitted (e.g. `EXPLAIN (FORMAT JSON) SELECT 1`).

/\A\s*(SELECT|WITH|EXPLAIN(?!\s+ANALYZE)(?!\s*\([^)]*\bANALYZE\b))\b/i
FORBIDDEN_PREFIX_REGEXES =

Frozen map of forbidden keyword => regex matching the keyword at statement start. Used by #check_forbidden_keywords! and #check_forbidden_keywords_in_body!.

FORBIDDEN_KEYWORDS.to_h do |kw|
  [kw, /\A\s*#{kw}\b/i]
end.freeze
BODY_FORBIDDEN_REGEXES =

Frozen map of forbidden body keyword => regex matching the keyword anywhere. Used by #check_body_forbidden_keywords!.

BODY_FORBIDDEN_KEYWORDS.to_h do |kw|
  [kw, /\b#{kw}\b/i]
end.freeze
FORBIDDEN_BODY_REGEXES =

Frozen map of forbidden keyword => regex matching the keyword anywhere in the body. Used by #check_forbidden_keywords_in_body! for the whole-body scan.

FORBIDDEN_KEYWORDS.to_h do |kw|
  [kw, /\b#{kw}\b/i]
end.freeze
DANGEROUS_FUNCTION_REGEXES =

Frozen map of dangerous function name => regex matching a call to that function. Used by #check_dangerous_functions!.

DANGEROUS_FUNCTIONS.to_h do |func|
  [func, /\b#{func}\s*\(/i]
end.freeze

Instance Method Summary collapse

Instance Method Details

#valid?(sql) ⇒ Boolean

Check if SQL is valid without raising.

Parameters:

  • sql (String) —

    SQL string to validate

Returns:

  • (Boolean) 


140
141
142
143
144
145
 
# File 'lib/woods/console/sql_validator.rb', line 140

def valid?(sql)
  validate!(sql)
  true
rescue SqlValidationError
  false
end

#validate!(sql) ⇒ Object

Raises:



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/woods/console/sql_validator.rb', line 105

def validate!(sql)
  raise SqlValidationError, 'SQL is empty' if sql.nil? || sql.strip.empty?

  normalized = sql.strip

  # Reject multiple statements (semicolons not inside string literals)
  if contains_multiple_statements?(normalized)
    raise SqlValidationError, 'Rejected: multiple statements are not allowed'
  end

  # Check for forbidden keywords at statement start
  check_forbidden_keywords!(normalized)

  # Check for writable CTEs (before body keywords to give better error messages)
  check_writable_ctes!(normalized)

  # Check for forbidden keywords anywhere in the SQL body
  check_body_forbidden_keywords!(normalized)

  # Check for dangerous functions
  check_dangerous_functions!(normalized)

  # After stripping comments, check again for forbidden keywords that might have been hidden
  check_forbidden_keywords_in_body!(normalized)

  # Must start with an allowed prefix
  return if normalized.match?(ALLOWED_PREFIXES)

  raise SqlValidationError, 'Rejected: SQL must start with SELECT, WITH, or EXPLAIN'
end