Coverage for src/asgi_profiler/fingerprint.py: 100%

1"""Stable identity for a SQL statement. 

2 

3Grouping statements by their literal text is right until it isn't. Two 

4executions of the same ORM query differ when the `IN` list is a different 

5length, when a savepoint is numbered, or when a `text()` query was written 

6with its values inline -- and the profiler then reports one problem as six, 

7which is the same as reporting nothing. 

8 

9:func:`sql_hash` answers "are these the same statement?" by normalising the 

10parts that vary per execution and hashing what is left. The normalisation is 

11deliberately conservative, in this order: 

12 

131. `IN (1, 2, 3)` and `IN (?, ?, ?)` collapse to `IN (%s)`. This is the one 

14 that matters most: a page of ten rows and a page of eleven issue the same 

15 query, and without this they are two statements forever. 

162. `SAVEPOINT sa_savepoint_3` collapses to `SAVEPOINT %s`. The counter is 

17 per-transaction, so every nested block would otherwise be unique. 

183. Single-quoted strings, numbers and booleans collapse to `%s`. 

19 

20What is deliberately *not* normalised: double-quoted strings, because 

21PostgreSQL uses them for identifiers and folding `"users"` into `%s` would 

22merge every table in the schema into one statement. Nor are bare placeholders 

23rewritten between dialects -- `?` and `%s` never appear in the same process, 

24so translating them buys nothing and risks merging statements that differ. 

25 

26Numbers are matched on word boundaries, so a table named `users_2024` or a 

27column alias `t1` survives: `_` and letters are word characters, which means 

28there is no boundary in front of those digits. 

29""" 

30 

31from __future__ import annotations 

32 

33import re 

34from functools import lru_cache 

35from hashlib import md5 

36 

37__all__ = ["is_truncated", "normalise_sql", "sql_hash"] 

38 

39#: Bump when the normalisation changes. It is part of the hashed input, so an 

40#: old capture and a new one never silently share a fingerprint -- they group 

41#: separately, which is visible, rather than merging, which is not. 

42SCHEME = "1" 

43 

44#: Bound parameters, in every dialect SQLAlchemy emits: `qmark`, `format`, 

45#: `numeric` and `named`. 

46_PLACEHOLDER = r"(?:%s|\?|\$\d+|:\w+)" 

47_NUMBER = r"-?\b(?:[0-9]+\.)?[0-9]+(?:[eE][+-]?[0-9]+)?\b" 

48#: `''` is SQL's escape for a literal quote, so it must not end the match. 

49_STRING = r"'(?:[^']|'')*'" 

50_BOOL = r"\b(?:TRUE|FALSE)\b" 

51 

52_IN_ITEM = f"(?:{_PLACEHOLDER}|{_NUMBER}|{_STRING}|{_BOOL})" 

53_IN_LIST = re.compile( 

54 rf"\bIN\s*\(\s*{_IN_ITEM}(?:\s*,\s*{_IN_ITEM})*\s*\)", 

55 re.IGNORECASE, 

56) 

57_SAVEPOINT = re.compile( 

58 r'\bSAVEPOINT\s+(?:"[^"]+"|`[^`]+`|\[[^\]]+\]|\w+)', 

59 re.IGNORECASE, 

60) 

61_LITERAL = re.compile(f"{_STRING}|{_NUMBER}|{_BOOL}", re.IGNORECASE) 

62 

63#: Appended by `instrument._normalise` when a statement is too long to keep. 

64#: A truncated statement cannot be compared honestly -- the part that differs 

65#: may be the part that was cut -- so detectors refuse to fingerprint one. 

66TRUNCATION_MARKER = "... [truncated," 

67 

68 

69def is_truncated(sql: str) -> bool: 

70 """Was this statement stored with its tail cut off?""" 

71 return sql.endswith("chars]") and TRUNCATION_MARKER in sql 

72 

73 

74def normalise_sql(sql: str) -> str: 

75 """The statement with per-execution values replaced by `%s`. 

76 

77 Readable on purpose: it is what the viewer shows when it needs to name a 

78 group of statements rather than one execution of it. 

79 """ 

80 text = " ".join(sql.split()) 

81 text = _IN_LIST.sub("IN (%s)", text) 

82 text = _SAVEPOINT.sub("SAVEPOINT %s", text) 

83 return _LITERAL.sub("%s", text) 

84 

85 

86@lru_cache(maxsize=4096) 

87def sql_hash(sql: str) -> str: 

88 """A short, stable identity for `sql`. 

89 

90 Cached because the whole point of the caller is repetition: an N+1 asks 

91 this the same question five hundred times in a row, and the cache turns 

92 all but the first into a dictionary lookup. 

93 

94 Not a security primitive -- md5 is here because it is fast and 64 bits of 

95 it is plenty to tell two statements apart within one application. 

96 """ 

97 digest = md5(normalise_sql(sql).encode("utf-8", "replace"), usedforsecurity=False) 

98 return f"{SCHEME}-{digest.hexdigest()[:16]}"