SQL Obfuscation While Exempting Comments Proof of Concept

obfus.rb

#!/usr/bin/env ruby
# frozen_string_literal: true

statements = [{ input: 'SELECT this FROM there WHERE userId = 123 AND roleId = 456 LIMIT 11',
                expected: 'SELECT this FROM there WHERE userId = ? AND roleId = ? LIMIT ?' },
              { input: "SELECT this FROM there WHERE userId = 123 AND roleId = 456 LIMIT 11\n-- Linking id: 867",
                expected: "SELECT this FROM there WHERE userId = ? AND roleId = ? LIMIT ?\n-- Linking id: 867" },
              { input: "SELECT this FROM there WHERE userId = 123 /* linking id:\n867 */ AND roleId = 456 LIMIT 11",
                expected: "SELECT this FROM there WHERE userId = ? /* linking id:\n867 */ AND roleId = ? LIMIT ?" }]

COMPONENTS_REGEX_MAP = {
  single_quotes: /'(?:[^']|'')*?(?:\\'.*|'(?!'))/,
  double_quotes: /"(?:[^"]|"")*?(?:\\".*|"(?!"))/,
  dollar_quotes: /(\$(?!\d)[^$]*?\$).*?(?:\1|$)/,
  uuids: /\{?(?:[0-9a-fA-F]-*){32}\}?/,
  numeric_literals: /-?\b(?:[0-9]+\.)?[0-9]+([eE][+-]?[0-9]+)?\b/,
  boolean_literals: /\b(?:true|false|null)\b/i,
  hexadecimal_literals: /0x[0-9a-fA-F]+/,
  comments: /(?:#|--).*?(?=\r|\n|$)/i,
  multi_line_comments: %r{/\*.*?\*/}m,
  oracle_quoted_strings: /q'\[.*?(?:\]'|$)|q'\{.*?(?:\}'|$)|q'<.*?(?:>'|$)|q'\(.*?(?:\)'|$)/
}.freeze

NON_COMMENTS = Regexp.union(COMPONENTS_REGEX_MAP.reject { |k, _v| k.match?('comments') }.values)
COMMENTS = Regexp.union(COMPONENTS_REGEX_MAP.select { |k, _v| k.match?('comments') }.values)
PLACEHOLDER = '__OBFUSCATION_PLACEHOLDER__'

def filter(string)
  string.gsub(NON_COMMENTS, '?')
end

def obfuscate(statement)
  comments = statement.match(COMMENTS).to_a.compact
  return filter(statement) if comments.empty?

  obfuscated = filter(statement.gsub(/#{comments.map { |c| Regexp.escape(c) }.join('|')}/, PLACEHOLDER))
  comments.each { |c| obfuscated.sub!(PLACEHOLDER, c) }
  obfuscated
end

statements.each_with_index do |statement, idx|
  actual = obfuscate(statement[:input])
  status = actual == statement[:expected] ? 'PASS' : 'FAIL'
  puts "Test #{idx + 1}: #{status}"
  if status == 'FAIL'
    puts  "expected: >>#{statement[:expected]}<<"
    puts  "  actual: >>#{actual}<<"
  end
end