1
2
3
4
5
6
7
8
9
10
11
12
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
|
# frozen-string-literal: true
#
# The sql_log_normalizer extension normalizes the SQL that is logged,
# removing the literal strings and numbers in the SQL, and removing the
# logging of any bound variables:
#
# ds = DB[:table].first(a: 1, b: 'something')
# # Without sql_log_normalizer extension
# # SELECT * FROM "table" WHERE (("a" = 1) AND ("b" = 'something')) LIMIT 1
#
# # With sql_log_normalizer_extension
# # SELECT * FROM "table" WHERE (("a" = ?) AND ("b" = ?)) LIMIT ?
#
# The normalization is done by scanning the SQL string being executed
# for literal strings and numbers, and replacing them with question
# marks. While this should work for all or almost all production queries,
# there are pathlogical queries that will not be handled correctly, such as
# the use of apostrophes in identifiers:
#
# DB[:"asf'bar"].where(a: 1, b: 'something').first
# # Logged as:
# # SELECT * FROM "asf?something')) LIMIT ?
#
# The expected use case for this extension is when you want to normalize
# logs to group similar queries, or when you want to protect sensitive
# data from being stored in the logs.
#
# Related module: Sequel::SQLLogNormalizer
#
module Sequel
module SQLLogNormalizer
def self.extended(db)
type = case db.literal("'")
when "''''"
:standard
when "'\\''"
:backslash
when "N''''"
:n_standard
else
raise Error, "SQL log normalization is not supported on this database (' literalized as #{db.literal("'").inspect})"
end
db.instance_variable_set(:@sql_string_escape_type, type)
end
# Normalize the SQL before calling super.
def log_connection_yield(sql, conn, args=nil)
unless skip_logging?
sql = normalize_logged_sql(sql)
args = nil
end
super
end
# Replace literal strings and numbers in SQL with question mark placeholders.
def normalize_logged_sql(sql)
sql = sql.dup
sql.force_encoding('BINARY')
start_index = 0
check_n = @sql_string_escape_type == :n_standard
outside_string = true
if @sql_string_escape_type == :backslash
search_char = /[\\']/
escape_char_offset = 0
escape_char_value = 92 # backslash
else
search_char = "'"
escape_char_offset = 1
escape_char_value = 39 # apostrophe
end
# The approach used here goes against Sequel's philosophy of never attempting
# to parse SQL. However, parsing the SQL is basically the only way to implement
# this support with Sequel's design, and it's better to be pragmatic and accept
# this than not be able to support this.
# Replace literal strings
while outside_string && (index = start_index = sql.index("'", start_index))
if check_n && index != 0 && sql.getbyte(index-1) == 78 # N' start
start_index -= 1
end
index += 1
outside_string = false
while (index = sql.index(search_char, index)) && (sql.getbyte(index + escape_char_offset) == escape_char_value)
# skip escaped characters inside string literal
index += 2
end
if index
# Found end of string
sql[start_index..index] = '?'
start_index += 1
outside_string = true
end
end
# Replace integer and decimal floating point numbers
sql.gsub!(/\b-?\d+(?:\.\d+)?\b/, '?')
sql
end
end
Database.register_extension(:sql_log_normalizer, SQLLogNormalizer)
end
|
