Module: Woods::Console::SqlNoiseStripper

Defined in:
lib/woods/console/sql_noise_stripper.rb

Overview

Strips SQL comments and string literals from a SQL string so that downstream checks (keyword scanning, table scanning) are not confused by content embedded inside comments or literals.

This is a shared utility used by SqlValidator and TableGate to avoid duplicating comment- and literal-stripping logic. All methods are module-level and stateless — pass a SQL string in, receive a stripped string out.

Examples:

Strip comments only

SqlNoiseStripper.strip_comments("SELECT 1 -- pick one\nFROM t")
# => "SELECT 1 \nFROM t"

Strip literals (PostgreSQL dialect)

SqlNoiseStripper.strip_literals("SELECT 'it''s ok' FROM t")
# => "SELECT '' FROM t"

Strip literals (MySQL dialect — backslash escapes)

SqlNoiseStripper.strip_literals("SELECT 'it\\'s ok' FROM t", dialect: :mysql)
# => "SELECT '' FROM t"

Constant Summary collapse

LINE_COMMENT =

Strips SQL line comments (-- ...) and block comments (/* ... */). Line comments are stripped to (but not including) the newline so that newline-separated statement structure is preserved for callers that check for multiple statements.

Block comments are non-nested — real SQL engines do not support nested block comments, and neither does this stripper.

Returns:

  • (String) —

    a new string with all SQL comments removed

/--[^\n]*/
BLOCK_COMMENT = 
%r{/\*.*?\*/}m
DOLLAR_QUOTED =

Strips single-quoted string literals and (for the :postgres dialect) PostgreSQL dollar-quoted string literals from a SQL string, replacing each with an empty '' placeholder so that the structure of the SQL is maintained for subsequent checks.

Dollar-quoted strings are stripped before single-quoted strings so that stray apostrophes inside a dollar-quoted body do not confuse the single-quote scanner.

Returns:

  • (String) —

    a new string with all string literals replaced by ''

Raises:

  • (ArgumentError) —

    if an unsupported dialect is provided

/\$(\w*)\$.*?\$\1\$/m
SINGLE_QUOTED_POSTGRES = 
/'(?:''|[^'])*'/m
SINGLE_QUOTED_MYSQL = 
/'(?:\\.|''|[^'])*'/m

Class Method Summary collapse

Class Method Details

.strip_comments(sql) ⇒ Object 



41
42
43
44
 
# File 'lib/woods/console/sql_noise_stripper.rb', line 41

def self.strip_comments(sql)
  out = sql.gsub(LINE_COMMENT, '')
  out.gsub(BLOCK_COMMENT, '')
end

.strip_literals(sql, dialect: :postgres) ⇒ Object 



73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/woods/console/sql_noise_stripper.rb', line 73

def self.strip_literals(sql, dialect: :postgres)
  unless SUPPORTED_DIALECTS.include?(dialect)
    raise ArgumentError, "Unknown dialect #{dialect.inspect}. Supported: #{SUPPORTED_DIALECTS.inspect}"
  end

  # Strip dollar-quoted strings first so stray apostrophes inside them
  # do not interfere with the single-quote scanner.
  out = sql.gsub(DOLLAR_QUOTED, "''")

  pattern = dialect == :mysql ? SINGLE_QUOTED_MYSQL : SINGLE_QUOTED_POSTGRES
  out.gsub(pattern, "''")
end

.strip_noise(sql, dialect: :postgres) ⇒ String

Strip BOTH comments and string literals in a single left-to-right pass, so a comment marker inside a literal and a quote inside a comment are each protected by whichever construct opens first.

Running strip_comments then strip_literals (or vice versa) is unsafe: SELECT '-- ' FROM blocked has its real FROM blocked swallowed as a line comment (the -- sits inside a string literal), letting a blocked table slip past TableGate; the reverse order mis-handles an apostrophe inside a -- comment. Only a combined scan that tracks literal/comment state correctly resolves both. This scanner backs security checks (SqlValidator, TableGate) so it must never under-detect: an unterminated literal is treated as an ordinary character rather than swallowing the rest of the statement.

Parameters:

  • sql (String) —

    the SQL string to process

  • dialect (Symbol) (defaults to: :postgres) —

    :postgres (default) or :mysql — controls single-quote escape rules (see strip_literals).

Returns:

  • (String) —

    a new string with comments removed and every string literal replaced by ''

Raises:

  • (ArgumentError) —

    if an unsupported dialect is provided



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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/woods/console/sql_noise_stripper.rb', line 106

def self.strip_noise(sql, dialect: :postgres) # rubocop:disable Metrics/MethodLength,Metrics/CyclomaticComplexity,Metrics/PerceivedComplexity,Metrics/AbcSize
  unless SUPPORTED_DIALECTS.include?(dialect)
    raise ArgumentError, "Unknown dialect #{dialect.inspect}. Supported: #{SUPPORTED_DIALECTS.inspect}"
  end

  mysql = dialect == :mysql
  out = +''
  i = 0
  len = sql.length

  while i < len
    ch = sql[i]

    if ch == "'"
      close = single_quote_end(sql, i, mysql: mysql)
      if close
        out << "''"
        i = close
      else
        # Unterminated literal — never under-detect; keep the char.
        out << ch
        i += 1
      end
    elsif ch == '$' && (tag = dollar_tag_at(sql, i))
      close = sql.index(tag, i + tag.length)
      if close
        out << "''"
        i = close + tag.length
      else
        out << ch
        i += 1
      end
    elsif ch == '-' && sql[i + 1] == '-'
      nl = sql.index("\n", i)
      i = nl || len
    elsif ch == '/' && sql[i + 1] == '*'
      close = sql.index('*/', i + 2)
      if close
        i = close + 2
      else
        # Unterminated block comment: never under-detect. Leave it in
        # place (over-detection is safe; the old regex also required a
        # closing */ and left an unterminated /* untouched).
        out << ch
        i += 1
      end
    else
      out << ch
      i += 1
    end
  end

  out
end