class ScoutApm::Utils::SqlSanitizer

Constants

MAX_SQL_LENGTH
MULTIPLE_QUESTIONS
MULTIPLE_SPACES
MYSQL_IN_CLAUSE
MYSQL_REMOVE_DOUBLE_QUOTE_STRINGS
MYSQL_REMOVE_INTEGERS
MYSQL_REMOVE_SINGLE_QUOTE_STRINGS
MYSQL_VAR_INTERPOLATION
PSQL_AFTER_FROM
PSQL_AFTER_JOIN
PSQL_AFTER_SELECT
PSQL_AFTER_SET
PSQL_AFTER_WHERE
PSQL_IN_CLAUSE
PSQL_PLACEHOLDER
PSQL_REMOVE_INTEGERS
PSQL_REMOVE_STRINGS
PSQL_VAR_INTERPOLATION
SQLITE_REMOVE_INTEGERS
SQLITE_REMOVE_STRINGS
SQLITE_VAR_INTERPOLATION
SQLSERVER_EXECUTESQL

> “EXEC sp_executesql N'SELECT [users].* FROM [users] WHERE (age > 50) ORDER BY [users]. ASC OFFSET 0 ROWS FETCH NEXT @0 ROWS ONLY', N'@0 int', @0 = 10”

SQLSERVER_IN_CLAUSE
SQLSERVER_REMOVE_INTEGERS

Attributes

database_engine[RW]

Public Class Methods

new(sql) click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 41
def initialize(sql)
  @raw_sql = sql
  @database_engine = ScoutApm::Agent.instance.context.environment.database_engine
  @sanitized = false # only sanitize once.
end

Public Instance Methods

sql() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 47
def sql
  @sql ||= scrubbed(@raw_sql.dup) # don't do this in initialize as it is extra work that isn't needed unless we have a slow transaction.
end
to_s() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 51
def to_s
  if @sanitized
    sql
  else
    @sanitized = true
  end
  case database_engine
  when :postgres then to_s_postgres
  when :mysql    then to_s_mysql
  when :sqlite   then to_s_sqlite
  when :sqlserver then to_s_sqlserver
  end
end

Private Instance Methods

has_encodings?(encodings=['UTF-8', 'binary']) click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 108
def has_encodings?(encodings=['UTF-8', 'binary'])
  encodings.all?{|enc| Encoding.find(enc) rescue false}
end
scrubbed(str) click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 114
def scrubbed(str)
  return '' if !str.is_a?(String) || str.length > MAX_SQL_LENGTH # safeguard - don't sanitize or scrub large SQL statements
  return str if !str.respond_to?(:encode) # Ruby <= 1.8 doesn't have string encoding
  return str if str.valid_encoding? # Whatever encoding it is, it is valid and we can operate on it
  ScoutApm::Agent.instance.context.logger.debug "Scrubbing invalid sql encoding."
  if str.respond_to?(:scrub) # Prefer to scrub before we have to convert
    return str.scrub('_')
  elsif has_encodings?(['UTF-8', 'binary'])
    return str.encode('UTF-8', 'binary', :invalid => :replace, :undef => :replace, :replace => '_')
  end
  ScoutApm::Agent.instance.context.logger.debug "Unable to scrub invalid sql encoding."
  ''
end
to_s_mysql() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 88
def to_s_mysql
  sql.gsub!(MYSQL_VAR_INTERPOLATION, '')
  sql.gsub!(MYSQL_REMOVE_SINGLE_QUOTE_STRINGS, '?')
  sql.gsub!(MYSQL_REMOVE_DOUBLE_QUOTE_STRINGS, '?')
  sql.gsub!(MYSQL_REMOVE_INTEGERS, '?')
  sql.gsub!(MYSQL_IN_CLAUSE, 'IN (?)')
  sql.gsub!(MULTIPLE_QUESTIONS, '?')
  sql.strip!
  sql
end
to_s_postgres() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 74
def to_s_postgres
  sql.gsub!(PSQL_PLACEHOLDER, '?')
  sql.gsub!(PSQL_VAR_INTERPOLATION, '')
  # sql.gsub!(PSQL_REMOVE_STRINGS, '?')
  sql.gsub!(PSQL_AFTER_WHERE) {|c| c.gsub(PSQL_REMOVE_STRINGS, '?')}
  sql.gsub!(PSQL_AFTER_JOIN) {|c| c.gsub(PSQL_REMOVE_STRINGS, '?')}
  sql.gsub!(PSQL_AFTER_SET) {|c| c.gsub(PSQL_REMOVE_STRINGS, '?')}
  sql.gsub!(PSQL_REMOVE_INTEGERS, '?')
  sql.gsub!(PSQL_IN_CLAUSE, 'IN (?)')
  sql.gsub!(MULTIPLE_SPACES, ' ')
  sql.strip!
  sql
end
to_s_sqlite() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 99
def to_s_sqlite
  sql.gsub!(SQLITE_VAR_INTERPOLATION, '')
  sql.gsub!(SQLITE_REMOVE_STRINGS, '?')
  sql.gsub!(SQLITE_REMOVE_INTEGERS, '?')
  sql.gsub!(MULTIPLE_SPACES, ' ')
  sql.strip!
  sql
end
to_s_sqlserver() click to toggle source
# File lib/scout_apm/utils/sql_sanitizer.rb, line 67
def to_s_sqlserver
  sql.gsub!(SQLSERVER_EXECUTESQL, '\1')
  sql.gsub!(SQLSERVER_REMOVE_INTEGERS, '?')
  sql.gsub!(SQLSERVER_IN_CLAUSE, 'IN (?)')
  sql
end