Module: Sixty::Sql

Defined in:
lib/sixty/sql.rb

Overview

SQL and route normalization.

SECURITY BOUNDARY. Everything in this file runs inside the customer's process, before any byte leaves their network. Raw SQL text contains PII in literals ('alice@example.com', SSNs, tokens). We never transmit raw SQL — only the literal-free shape. If you are tempted to add a "send the original for debugging" option: don't.

Normalization also bounds cardinality, which is the other way systems like this die. where id = 1 and where id = 99 must collapse to one operation or the operations table grows with traffic instead of with the number of queries the application contains.

Ported from packages/core/src/sql.js and kept deliberately close to it. A Rails app and a Node service in the same organization must reduce the same statement to the same shape, because the shape is what the collector hashes into an operation's identity — two spellings of the same query would split one operation's history in half at the exact moment somebody rewrites a service in the other language.

Constant Summary collapse

MAX_CACHED =

ActiveRecord issues the same statement text thousands of times per minute. Normalization is a per-character lexer, so the result is memoized against the raw text — bounded, because a caller with unbounded distinct SQL is exactly the case that would otherwise grow this hash forever.

1000
SQL_VERB = 
/\A\s*(select|insert|update|delete|with|begin|commit|rollback|create|alter|drop|truncate|copy|explain|set|show|savepoint|release|listen|notify)\b/i.freeze
BY_VERB =

Which keyword introduces the relation that names the statement. INSERT is named by its target, not by the SELECT that feeds it.

{
  'insert' => [/\binto\s+/i],
  'update' => [/\bupdate\s+/i],
  'delete' => [/\bdelete\s+from\s+/i, /\bfrom\s+/i],
  'select' => [/\bfrom\s+/i, /\bjoin\s+/i],
  'with' => [/\bfrom\s+/i, /\bjoin\s+/i]
}.freeze
IDENT =

The quote is optional and may be either dialect's. A MySQL statement reaches here as insert into `orders` (a) values(?), and a pattern that only knows the Postgres quote finds no relation in it — so every backticked statement collapses to the bare verb insert, and the feed becomes a list of verbs. The captured name excludes the quotes, so orders and `orders` produce one label rather than two spellings.

/(["`]?[A-Za-z_][\w$]*["`]?\.)?["`]?([A-Za-z_][\w$]*)["`]?/.freeze
RELATION_PATTERNS =

Compiled once. Regexp.new(lead.source + IDENT.source) per call meant building and discarding a regular expression on every query in the application.

BY_VERB.transform_values do |leads|
  leads.map { |lead| Regexp.new(lead.source + IDENT.source, Regexp::IGNORECASE) }
end.freeze
UUID = 
/\A[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\z/i.freeze

Class Method Summary collapse

Class Method Details

.analyze(sql, dialect) ⇒ Array(String, Integer)

Returns the normalized text and how many literals were taken out of it.

Returns:

  • (Array(String, Integer)) —

    the normalized text and how many literals were taken out of it



63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/sixty/sql.rb', line 63

def analyze(sql, dialect)
  key = dialect == :mysql ? "mysql\x00#{sql}" : sql
  cached = @cache[key]
  return cached if cached

  result = lex_with_stats(sql, dialect)
  @cache_mutex.synchronize do
    @cache.clear if @cache.size >= MAX_CACHED
    @cache[key] = result
  end
  result
end

.compute_operation_name(normalized) ⇒ Object 



316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/sixty/sql.rb', line 316

def compute_operation_name(normalized)
  body, names = split_ctes(normalized)
  verb_match = SQL_VERB.match(body) || SQL_VERB.match(normalized)
  verb = verb_match ? verb_match[1].downcase : 'query'

  # Resolved against the *main* statement, not the first common table
  # expression: otherwise every `with d as (select ... from unnest(...))`
  # collapses to the same label, several unrelated statements all reading
  # `with:unnest` and indistinguishable in a list.
  table = relation_for(verb, body, names)
  table ||= relation_for(verb, normalized, names) || first_relation(normalized, names)

  table ? "#{verb}:#{table}" : verb
end

.first_relation(sql, cte_names) ⇒ Object

Any real relation the statement touches, CTE aliases excluded.



367
368
369
370
371
372
373
 
# File 'lib/sixty/sql.rb', line 367

def first_relation(sql, cte_names)
  sql.scan(/\b(?:from|into|update|join)\s+(["`]?[A-Za-z_][\w$]*["`]?\.)?["`]?([A-Za-z_][\w$]*)["`]?/i) do
    name = Regexp.last_match(2)
    return name if name && !cte_names.include?(name.downcase)
  end
  nil
end

.lex(sql, dialect = :postgres) ⇒ Object 



76
77
78
 
# File 'lib/sixty/sql.rb', line 76

def lex(sql, dialect = :postgres)
  lex_with_stats(sql, dialect).first
end

.lex_with_stats(sql, dialect = :postgres) ⇒ Object

Strip literals, collapse whitespace, fold IN-lists.

Deliberately a lexer, not a parser: it must never raise on a dialect quirk, must be fast enough to run on every query, and its only job is removing things — an unparseable statement still gets its literals stripped, which is the property that matters.

── Why the dialect is a parameter and not a union of both rule sets ──────

The two dialects disagree about a character rather than merely differing. "alice@example.com" is a quoted identifier in Postgres — schema, safe to keep — and in MySQL's default sql_mode the same bytes are a string literal, which is exactly the PII this file exists to remove. Reading MySQL with the Postgres rules would transmit it. Backticks are the mirror image: MySQL's identifier quote, and not a quote at all in Postgres.

ActiveRecord knows which one it is talking to, so the adapter name decides (see instrument/active_record.rb) rather than a guess from the text.



98
99
100
101
102
103
104
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
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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
 
# File 'lib/sixty/sql.rb', line 98

def lex_with_stats(sql, dialect = :postgres)
  mysql = dialect == :mysql
  src = sql.chars
  n = src.length
  out = +''
  i = 0
  # Every literal removed, counted. A bind parameter is not a literal: it
  # was already a placeholder when it arrived.
  literals = 0

  while i < n
    c = src[i]

    # --- line comment
    if c == '-' && src[i + 1] == '-'
      i += 1 while i < n && src[i] != "\n"
      next
    end

    # --- MySQL line comment. `#` is not a comment introducer in Postgres.
    if mysql && c == '#'
      i += 1 while i < n && src[i] != "\n"
      next
    end

    # --- block comment
    if c == '/' && src[i + 1] == '*'
      i += 2
      i += 1 while i < n && !(src[i] == '*' && src[i + 1] == '/')
      i += 2
      next
    end

    # --- single-quoted string (SQL escape is '')
    if c == "'"
      i += 1
      while i < n
        # MySQL also honours backslash escapes by default, so `'it\'s'` does
        # not end where a Postgres lexer thinks it does. Mis-finding the
        # closing quote resumes lexing *inside* a literal, and the tail of
        # somebody's data is then emitted as if it were SQL.
        if mysql && src[i] == '\\'
          i += 2
          next
        end
        if src[i] == "'" && src[i + 1] == "'"
          i += 2
          next
        end
        if src[i] == "'"
          i += 1
          break
        end
        i += 1
      end
      out << '?'
      literals += 1
      next
    end

    # --- MySQL double-quoted string. Postgres reads these as identifiers
    #     and keeps them; here they are data and must not survive. Checked
    #     before the identifier branch below, which is the Postgres reading.
    if mysql && c == '"'
      i += 1
      while i < n
        if src[i] == '\\'
          i += 2
          next
        end
        if src[i] == '"' && src[i + 1] == '"'
          i += 2
          next
        end
        if src[i] == '"'
          i += 1
          break
        end
        i += 1
      end
      out << '?'
      literals += 1
      next
    end

    # --- MySQL backtick identifier: preserved, it is schema, not data
    if mysql && c == '`'
      out << c
      i += 1
      while i < n
        out << src[i]
        if src[i] == '`' && src[i + 1] != '`'
          i += 1
          break
        end
        if src[i] == '`' && src[i + 1] == '`'
          out << src[i + 1]
          i += 2
          next
        end
        i += 1
      end
      next
    end

    # --- dollar-quoted string ($tag$ ... $tag$). Postgres only: `$` is a
    #     legal identifier character in MySQL, where `a$b$c` is one name and
    #     reading it as a quoted string would swallow the rest of the
    #     statement.
    if !mysql && c == '$'
      rest = sql[i..]
      if (m = /\A\$([A-Za-z_]\w*)?\$/.match(rest))
        tag = m[0]
        found = sql.index(tag, i + tag.length)
        i = found.nil? ? n : found + tag.length
        out << '?'
        literals += 1
        next
      end
      # $1, $2 — already placeholders. Normalized to one symbol so a
      # difference in parameter *count* does not fragment the identity.
      if (p = /\A\$\d+/.match(rest))
        out << '?'
        i += p[0].length
        next
      end
    end

    # --- double-quoted identifier: preserved, it is schema, not data
    if c == '"'
      out << c
      i += 1
      while i < n
        out << src[i]
        if src[i] == '"' && src[i + 1] != '"'
          i += 1
          break
        end
        if src[i] == '"' && src[i + 1] == '"'
          out << src[i + 1]
          i += 2
          next
        end
        i += 1
      end
      next
    end

    # --- numeric literal (not part of an identifier like col2)
    #
    # `previous` is spelled out rather than written as `src[i - 1]`: at i = 0
    # Ruby's negative index would hand back the *last* character of the
    # statement, so a query starting with a digit would be judged by its own
    # final byte.
    previous = i.zero? ? ' ' : (src[i - 1] || ' ')
    if c =~ /[0-9]/ && previous !~ /[A-Za-z_$."]/
      while i < n && src[i] =~ /[0-9.eE+\-xa-fA-F]/
        # stop at an operator that merely follows the number
        break if src[i] =~ /[+\-]/ && src[i - 1] !~ /[eE]/

        i += 1
      end
      out << '?'
      literals += 1
      next
    end

    # --- whitespace run
    if c =~ /\s/
      out << ' '
      i += 1 while i < n && src[i] =~ /\s/
      next
    end

    out << c
    i += 1
  end

  normalized = out
               # fold IN (?, ?, ?) -> IN (?) so batch size does not
               # fragment identity
               .gsub(/\b(?:in|IN|In)\s*\(\s*\?(?:\s*,\s*\?)+\s*\)/) { |m| m[0, m.index('(')] + '(?)' }
               # fold multi-row VALUES (?),(?) -> VALUES (?)
               .gsub(/\bvalues\s*(\(\s*\?(?:\s*,\s*\?)*\s*\))(?:\s*,\s*\(\s*\?(?:\s*,\s*\?)*\s*\))+/i, 'values \1')
               .gsub(/\s+/, ' ')
               .gsub(/\s*([(),;])\s*/, '\1')
               .strip

  [normalized, literals]
end

.normalize_sql(sql, dialect = :postgres) ⇒ Object

Parameters:

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

    :postgres or :mysql



38
39
40
41
42
 
# File 'lib/sixty/sql.rb', line 38

def normalize_sql(sql, dialect = :postgres)
  return '' unless sql.is_a?(String)

  analyze(sql, dialect).first
end

.relation_for(verb, sql, cte_names) ⇒ Object 



355
356
357
358
359
360
361
362
363
364
 
# File 'lib/sixty/sql.rb', line 355

def relation_for(verb, sql, cte_names)
  (RELATION_PATTERNS[verb] || RELATION_PATTERNS['select']).each do |re|
    sql.scan(re) do
      name = Regexp.last_match(2)
      # A CTE alias names nothing the reader can go and look at.
      return name if name && !cte_names.include?(name.downcase)
    end
  end
  nil
end

.split_ctes(sql) ⇒ Object

Split a statement into its CTE names and the statement that follows them. Paren-aware: a CTE body contains commas and parentheses, and a regex that ignores nesting stops in the wrong place.



378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
 
# File 'lib/sixty/sql.rb', line 378

def split_ctes(sql)
  names = Set.new
  return [sql, names] unless sql =~ /\A\s*with\b/i

  i = (sql =~ /\bwith\b/i) + 4
  loop do
    name_match = /\A\s*(?:recursive\s+)?["`]?([A-Za-z_][\w$]*)["`]?/i.match(sql[i..] || '')
    open = sql.index('(', i)
    return [sql, names] if open.nil?

    names << name_match[1].downcase if name_match

    depth = 0
    j = open
    while j < sql.length
      if sql[j] == '('
        depth += 1
      elsif sql[j] == ')'
        depth -= 1
        if depth.zero?
          j += 1
          break
        end
      end
      j += 1
    end
    return [sql, names] unless depth.zero?

    rest = sql[j..] || ''
    comma = /\A\s*,/.match(rest)
    return [rest.sub(/\A\s+/, ''), names] unless comma

    i = j + comma[0].length
  end
end

.sql_operation_name(normalized) ⇒ Object

Short display name for a SQL operation: "select:orders", "insert:users". The collector derives its own from the normalized text — this is what the span carries so a trace is readable before it ever leaves the process.

Memoized against the normalized statement, and that is not a micro-optimization: naming walks the statement looking for the relation it touches, which measured at fifteen microseconds per query — four times the cost of everything else the agent does per query put together. A name is a pure function of the text, and the same few hundred statements repeat forever, so it is computed once each.



304
305
306
307
308
309
310
311
312
313
314
 
# File 'lib/sixty/sql.rb', line 304

def sql_operation_name(normalized)
  cached = @names[normalized]
  return cached if cached

  name = compute_operation_name(normalized)
  @names_mutex.synchronize do
    @names.clear if @names.size >= MAX_CACHED
    @names[normalized] = name
  end
  name
end

.template_path(pathname) ⇒ Object

Template an HTTP path so /users/42 and /users/43 are one operation. Used only when the framework does not hand us a route pattern; a real Rails route always wins over this heuristic.



419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
 
# File 'lib/sixty/sql.rb', line 419

def template_path(pathname)
  return '/' unless pathname.is_a?(String)

  pathname = pathname.split('?', 2).first.to_s

  templated = pathname.split('/', -1).map do |seg|
    next seg if seg.empty?
    next ':id' if seg =~ /\A\d+\z/
    next ':uuid' if seg =~ UUID
    next ':hash' if seg =~ /\A[0-9a-f]{24,}\z/i
    # A colon inside a path segment is almost never part of a route: routes
    # are named with words. It is, reliably, a delimiter inside an id.
    next ':id' if seg.include?(':') && !seg.start_with?(':')
    # long, high-entropy, mixed-case segments are almost always ids/slugs
    next ':id' if seg.length > 24 && seg =~ /\d/ && seg =~ /[A-Za-z]/

    seg
  end.join('/')

  templated.empty? ? '/' : templated
end

.value_free?(sql, dialect = :postgres) ⇒ Boolean

Did this statement arrive with its values already out of it?

A statement written with bind parameters (where id = $1) carries no data; one written with literals (where id = 42) carries all of it. Only the first kind may be handed back to the database in an EXPLAIN — see plans.rb — and this is what decides which it is.

It is answered by the lexer rather than by a second pattern, because the question is exactly "would normalization have removed anything", and the only code that can answer that without disagreeing with itself is the code that does the removing.

Returns:

  • (Boolean) 


55
56
57
58
59
 
# File 'lib/sixty/sql.rb', line 55

def value_free?(sql, dialect = :postgres)
  return false unless sql.is_a?(String)

  analyze(sql, dialect).last.zero?
end