1#[derive(Clone, Copy, Debug, PartialEq, Eq)]
11enum Kind {
12 Word,
14 QuotedIdent,
16 Literal,
18 Placeholder,
20 Comment,
22 Space,
24 Punct,
26}
27
28#[derive(Clone, Copy, Debug)]
29struct Token<'a> {
30 kind: Kind,
31 text: &'a str,
32}
33
34fn is_ident_start(c: char) -> bool {
35 c.is_alphabetic() || c == '_'
36}
37
38fn is_ident_char(c: char) -> bool {
39 c.is_alphanumeric() || c == '_' || c == '$'
40}
41
42fn quoted_end(s: &str, start: usize, close: char) -> usize {
45 let mut chars = s[start + 1..].char_indices().peekable();
46 while let Some((i, c)) = chars.next() {
47 if c == close {
48 if chars.peek().map(|&(_, n)| n) == Some(close) && close != ']' {
49 chars.next();
50 continue;
51 }
52 return start + 1 + i + c.len_utf8();
53 }
54 }
55 s.len()
56}
57
58fn lex(sql: &str) -> Vec<Token<'_>> {
59 let mut tokens = Vec::new();
60 let bytes = sql.as_bytes();
61 let mut i = 0;
62 while i < sql.len() {
63 let rest = &sql[i..];
64 let c = rest.chars().next().unwrap_or(' ');
65 let (kind, len) = if c.is_whitespace() {
66 let len = rest
67 .char_indices()
68 .find(|&(_, c)| !c.is_whitespace())
69 .map_or(rest.len(), |(j, _)| j);
70 (Kind::Space, len)
71 } else if rest.starts_with("--") {
72 (Kind::Comment, rest.find('\n').unwrap_or(rest.len()))
73 } else if rest.starts_with("/*") {
74 let mut depth = 0usize;
76 let mut j = 0;
77 let mut end = rest.len();
78 while j < rest.len() {
79 if rest[j..].starts_with("/*") {
80 depth += 1;
81 j += 2;
82 } else if rest[j..].starts_with("*/") {
83 depth -= 1;
84 j += 2;
85 if depth == 0 {
86 end = j;
87 break;
88 }
89 } else {
90 j += rest[j..].chars().next().map_or(1, char::len_utf8);
91 }
92 }
93 (Kind::Comment, end)
94 } else if c == '\'' {
95 (Kind::Literal, quoted_end(sql, i, '\'') - i)
96 } else if matches!(c, 'E' | 'e' | 'B' | 'b' | 'X' | 'x' | 'N' | 'n' | 'U' | 'u')
97 && rest[c.len_utf8()..].starts_with('\'')
98 {
99 let open = i + c.len_utf8();
101 (Kind::Literal, quoted_end(sql, open, '\'') - i)
102 } else if c == '"' {
103 (Kind::QuotedIdent, quoted_end(sql, i, '"') - i)
104 } else if c == '`' {
105 (Kind::QuotedIdent, quoted_end(sql, i, '`') - i)
106 } else if c == '[' {
107 (Kind::QuotedIdent, quoted_end(sql, i, ']') - i)
108 } else if c == '$' {
109 let after = &rest[1..];
110 let digits = after.bytes().take_while(u8::is_ascii_digit).count();
111 if digits > 0 {
112 (Kind::Placeholder, 1 + digits)
113 } else {
114 let tag_len = after
116 .char_indices()
117 .find(|&(_, c)| !is_ident_char(c) || c == '$')
118 .map_or(after.len(), |(j, _)| j);
119 if after[tag_len..].starts_with('$') {
120 let delim = &rest[..tag_len + 2];
121 let body = &rest[delim.len()..];
122 let end = body
123 .find(delim)
124 .map_or(rest.len(), |j| delim.len() + j + delim.len());
125 (Kind::Literal, end)
126 } else if tag_len > 0 {
127 (Kind::Placeholder, 1 + tag_len)
128 } else {
129 (Kind::Punct, 1)
130 }
131 }
132 } else if c == '?' {
133 let digits = rest[1..].bytes().take_while(u8::is_ascii_digit).count();
134 (Kind::Placeholder, 1 + digits)
135 } else if (c == ':' || c == '@')
136 && rest[1..].chars().next().is_some_and(is_ident_start)
137 && !(c == ':' && i > 0 && bytes[i - 1] == b':')
138 {
139 let len = rest[1..]
140 .char_indices()
141 .find(|&(_, c)| !is_ident_char(c))
142 .map_or(rest.len() - 1, |(j, _)| j);
143 (Kind::Placeholder, 1 + len)
144 } else if c.is_ascii_digit()
145 || (c == '.' && rest[1..].bytes().next().is_some_and(|b| b.is_ascii_digit()))
146 {
147 let b = rest.as_bytes();
148 let mut j = 0;
149 if rest.starts_with("0x") || rest.starts_with("0X") {
150 j = 2;
151 while j < b.len() && b[j].is_ascii_hexdigit() {
152 j += 1;
153 }
154 } else {
155 while j < b.len() && (b[j].is_ascii_digit() || b[j] == b'.' || b[j] == b'_') {
156 j += 1;
157 }
158 if j < b.len() && (b[j] == b'e' || b[j] == b'E') {
159 let mut k = j + 1;
160 if k < b.len() && (b[k] == b'+' || b[k] == b'-') {
161 k += 1;
162 }
163 if k < b.len() && b[k].is_ascii_digit() {
164 j = k;
165 while j < b.len() && b[j].is_ascii_digit() {
166 j += 1;
167 }
168 }
169 }
170 }
171 (Kind::Literal, j.max(1))
172 } else if is_ident_start(c) {
173 let len = rest
174 .char_indices()
175 .find(|&(_, c)| !is_ident_char(c))
176 .map_or(rest.len(), |(j, _)| j);
177 let word = &rest[..len];
178 if word.eq_ignore_ascii_case("true") || word.eq_ignore_ascii_case("false") {
179 (Kind::Literal, len)
180 } else {
181 (Kind::Word, len)
182 }
183 } else {
184 (Kind::Punct, c.len_utf8())
185 };
186 tokens.push(Token {
187 kind,
188 text: &sql[i..i + len],
189 });
190 i += len;
191 }
192 tokens
193}
194
195pub fn sanitize(sql: &str) -> String {
200 let tokens = lex(sql);
201 let mut out = String::with_capacity(sql.len());
202 let mut i = 0;
203 while i < tokens.len() {
204 let t = tokens[i];
205 match t.kind {
206 Kind::Literal => out.push('?'),
207 Kind::Comment | Kind::Space => {
209 if !out.is_empty() && !out.ends_with(' ') {
210 out.push(' ');
211 }
212 }
213 Kind::Word if t.text.eq_ignore_ascii_case("in") => {
214 out.push_str(t.text);
215 if let Some(end) = literal_list(&tokens, i + 1) {
216 if tokens[i + 1].kind == Kind::Space {
217 out.push(' ');
218 }
219 out.push_str("(?)");
220 i = end;
221 continue;
222 }
223 }
224 _ => out.push_str(t.text),
225 }
226 i += 1;
227 }
228 out.trim().to_string()
229}
230
231fn literal_list(tokens: &[Token<'_>], start: usize) -> Option<usize> {
234 let mut i = start;
235 while tokens.get(i)?.kind == Kind::Space {
236 i += 1;
237 }
238 if tokens[i].text != "(" {
239 return None;
240 }
241 i += 1;
242 let mut items = 0;
243 loop {
244 let t = tokens.get(i)?;
245 match t.kind {
246 Kind::Space | Kind::Comment => {}
247 Kind::Literal => items += 1,
248 Kind::Punct if t.text == "," => {}
249 Kind::Punct if t.text == ")" => return (items > 0).then_some(i + 1),
250 _ => return None,
251 }
252 i += 1;
253 }
254}
255
256const OPERATIONS: &[&str] = &[
257 "SELECT",
258 "INSERT",
259 "UPDATE",
260 "DELETE",
261 "MERGE",
262 "REPLACE",
263 "UPSERT",
264 "CALL",
265 "EXEC",
266 "EXECUTE",
267 "CREATE",
268 "ALTER",
269 "DROP",
270 "TRUNCATE",
271 "BEGIN",
272 "START",
273 "COMMIT",
274 "END",
275 "ROLLBACK",
276 "SAVEPOINT",
277 "RELEASE",
278 "PRAGMA",
279 "VACUUM",
280 "ANALYZE",
281 "REINDEX",
282 "ATTACH",
283 "DETACH",
284 "COPY",
285 "LISTEN",
286 "NOTIFY",
287 "UNLISTEN",
288 "SET",
289 "SHOW",
290 "GRANT",
291 "REVOKE",
292 "LOCK",
293 "REFRESH",
294 "VALUES",
295];
296
297const LIST_END: &[&str] = &[
299 "WHERE",
300 "GROUP",
301 "ORDER",
302 "LIMIT",
303 "OFFSET",
304 "HAVING",
305 "WINDOW",
306 "UNION",
307 "EXCEPT",
308 "INTERSECT",
309 "RETURNING",
310 "SET",
311 "VALUES",
312 "ON",
313 "USING",
314 "JOIN",
315 "INNER",
316 "LEFT",
317 "RIGHT",
318 "FULL",
319 "CROSS",
320 "NATURAL",
321 "OUTER",
322 "FETCH",
323 "FOR",
324 "INTO",
325 "SELECT",
326 "LATERAL",
327];
328
329const OBJECTS: &[&str] = &[
331 "TABLE",
332 "INDEX",
333 "VIEW",
334 "TRIGGER",
335 "SCHEMA",
336 "DATABASE",
337 "SEQUENCE",
338 "FUNCTION",
339 "PROCEDURE",
340 "EXTENSION",
341 "TYPE",
342 "ROLE",
343 "USER",
344 "DOMAIN",
345 "POLICY",
346];
347
348const OBJECT_MODIFIERS: &[&str] = &[
350 "OR",
351 "REPLACE",
352 "TEMP",
353 "TEMPORARY",
354 "UNIQUE",
355 "VIRTUAL",
356 "MATERIALIZED",
357 "UNLOGGED",
358 "GLOBAL",
359 "LOCAL",
360 "CONCURRENTLY",
361];
362
363const MAX_SUMMARY: usize = 255;
364
365fn is(word: &str, set: &[&str]) -> bool {
366 set.iter().any(|w| word.eq_ignore_ascii_case(w))
367}
368
369pub fn summarize(sql: &str) -> Option<String> {
375 let tokens: Vec<Token<'_>> = lex(sql)
376 .into_iter()
377 .filter(|t| !matches!(t.kind, Kind::Space | Kind::Comment))
378 .collect();
379 let mut parts: Vec<String> = Vec::new();
380 let mut i = 0;
381 while i < tokens.len() {
382 let t = tokens[i];
383 if t.kind != Kind::Word {
384 i += 1;
385 continue;
386 }
387 let w = t.text;
388 if is(w, &["CREATE", "ALTER", "DROP"]) {
389 let mut j = i + 1;
391 while j < tokens.len()
392 && tokens[j].kind == Kind::Word
393 && is(tokens[j].text, OBJECT_MODIFIERS)
394 {
395 j += 1;
396 }
397 match tokens.get(j) {
398 Some(o) if o.kind == Kind::Word && is(o.text, OBJECTS) => {
399 parts.push(format!("{w} {}", o.text));
400 j += 1;
401 while j < tokens.len()
402 && tokens[j].kind == Kind::Word
403 && is(tokens[j].text, &["IF", "NOT", "EXISTS"])
404 {
405 j += 1;
406 }
407 if let Some((name, next)) = name_at(&tokens, j) {
408 parts.push(name);
409 j = next;
410 }
411 }
412 _ => parts.push(w.to_string()),
413 }
414 i = j;
415 continue;
416 }
417 if is(w, OPERATIONS) {
418 if is(w, &["END", "START", "SET", "VALUES"]) && i != 0 {
420 i += 1;
421 continue;
422 }
423 if w.eq_ignore_ascii_case("REPLACE") && i != 0 {
425 i += 1;
426 continue;
427 }
428 parts.push(w.to_string());
429 i += 1;
430 if is(w, &["UPDATE"]) {
431 if tokens
433 .get(i)
434 .is_some_and(|t| t.text.eq_ignore_ascii_case("OR"))
435 {
436 i += 2;
437 }
438 if tokens
439 .get(i)
440 .is_some_and(|t| t.text.eq_ignore_ascii_case("ONLY"))
441 {
442 i += 1;
443 }
444 if let Some((name, next)) = name_at(&tokens, i) {
445 parts.push(name);
446 i = next;
447 }
448 } else if is(
449 w,
450 &[
451 "TRUNCATE", "LOCK", "VACUUM", "ANALYZE", "REINDEX", "PRAGMA", "CALL", "EXEC",
452 "EXECUTE",
453 ],
454 ) {
455 if tokens
456 .get(i)
457 .is_some_and(|t| t.text.eq_ignore_ascii_case("TABLE"))
458 {
459 i += 1;
460 }
461 if let Some((name, next)) = name_at(&tokens, i) {
462 parts.push(name);
463 i = next;
464 }
465 }
466 continue;
467 }
468 if is(w, &["FROM", "JOIN", "INTO", "USING"]) {
469 if w.eq_ignore_ascii_case("USING")
470 && !parts.first().is_some_and(|p| is(p, &["MERGE", "DELETE"]))
471 {
472 i += 1;
474 continue;
475 }
476 let from = w.eq_ignore_ascii_case("FROM");
477 i += 1;
478 if tokens
479 .get(i)
480 .is_some_and(|t| t.text.eq_ignore_ascii_case("ONLY"))
481 {
482 i += 1;
483 }
484 loop {
485 if tokens.get(i).is_some_and(|t| t.text == "(") {
486 break;
488 }
489 let Some((name, next)) = name_at(&tokens, i) else {
490 break;
491 };
492 i = next;
493 if !w.eq_ignore_ascii_case("INTO") && tokens.get(i).is_some_and(|t| t.text == "(") {
496 break;
497 }
498 parts.push(name);
499 if tokens
501 .get(i)
502 .is_some_and(|t| t.text.eq_ignore_ascii_case("AS"))
503 {
504 i += 1;
505 if name_at(&tokens, i).is_some() {
506 i += 1;
507 }
508 } else if tokens.get(i).is_some_and(|t| {
509 matches!(t.kind, Kind::Word | Kind::QuotedIdent) && !is(t.text, LIST_END)
510 }) {
511 i += 1;
512 }
513 if from && tokens.get(i).is_some_and(|t| t.text == ",") {
514 i += 1;
515 continue;
516 }
517 break;
518 }
519 continue;
520 }
521 i += 1;
522 }
523 if parts.is_empty() {
524 return None;
525 }
526 let mut summary = String::new();
527 for part in parts {
528 let extra = usize::from(!summary.is_empty()) + part.len();
529 if summary.len() + extra > MAX_SUMMARY {
530 break;
531 }
532 if !summary.is_empty() {
533 summary.push(' ');
534 }
535 summary.push_str(&part);
536 }
537 (!summary.is_empty()).then_some(summary)
538}
539
540fn name_at(tokens: &[Token<'_>], mut i: usize) -> Option<(String, usize)> {
544 let first = tokens.get(i)?;
545 let ok = |t: &Token<'_>| {
546 matches!(t.kind, Kind::QuotedIdent)
547 || (t.kind == Kind::Word && !is(t.text, LIST_END) && !is(t.text, OPERATIONS))
548 || (t.kind == Kind::Literal && t.text.starts_with('\''))
549 };
550 if !ok(first) {
551 return None;
552 }
553 let mut name = first.text.to_string();
554 i += 1;
555 while tokens.get(i).is_some_and(|t| t.text == ".") && tokens.get(i + 1).is_some_and(ok) {
556 name.push('.');
557 name.push_str(tokens[i + 1].text);
558 i += 2;
559 }
560 Some((name, i))
561}
562
563#[cfg(test)]
564mod tests {
565 use super::*;
566
567 #[test]
568 fn summaries_follow_the_conventions_examples() {
569 for (sql, summary) in [
570 (
571 "SELECT * FROM wuser_table WHERE username = ?",
572 "SELECT wuser_table",
573 ),
574 (
575 "INSERT INTO shipping_details (order_id, address) SELECT order_id, address FROM orders WHERE order_id = ?",
576 "INSERT shipping_details SELECT orders",
577 ),
578 (
579 "SELECT * FROM songs, artists WHERE songs.artist_id == artists.id",
580 "SELECT songs artists",
581 ),
582 (
583 "SELECT order_date FROM (SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id)",
584 "SELECT SELECT orders customers",
585 ),
586 (
587 r#"SELECT * FROM "song list", 'artists'"#,
588 r#"SELECT "song list" 'artists'"#,
589 ),
590 ] {
591 assert_eq!(summarize(sql).as_deref(), Some(summary), "{sql}");
592 }
593 }
594
595 #[test]
596 fn summaries_of_everyday_statements() {
597 for (sql, summary) in [
598 ("select id from users where id = $1", "select users"),
599 (
600 "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
601 "UPDATE accounts",
602 ),
603 ("UPDATE OR REPLACE kv SET v = ?1", "UPDATE kv"),
604 (
605 "DELETE FROM sessions WHERE expires < now()",
606 "DELETE sessions",
607 ),
608 (
609 "INSERT OR REPLACE INTO kv (k, v) VALUES (?, ?)",
610 "INSERT kv",
611 ),
612 ("REPLACE INTO kv VALUES (1, 2)", "REPLACE kv"),
613 (
614 "CREATE TABLE IF NOT EXISTS objects (id BLOB PRIMARY KEY)",
615 "CREATE TABLE objects",
616 ),
617 (
618 "CREATE UNIQUE INDEX idx_refs ON refs (name)",
619 "CREATE INDEX idx_refs",
620 ),
621 ("DROP VIEW public.recent", "DROP VIEW public.recent"),
622 ("BEGIN IMMEDIATE", "BEGIN"),
623 ("COMMIT", "COMMIT"),
624 ("PRAGMA journal_mode = WAL", "PRAGMA journal_mode"),
625 ("SELECT 1", "SELECT"),
626 (
627 "SELECT * FROM a LEFT JOIN b ON a.id = b.id JOIN c USING (id)",
628 "SELECT a b c",
629 ),
630 (
631 "SELECT * FROM public.users AS u WHERE u.id = 1",
632 "SELECT public.users",
633 ),
634 ("SELECT * FROM generate_series(1, 10)", "SELECT"),
635 (
636 "WITH recent AS (SELECT * FROM commits) SELECT * FROM recent",
637 "SELECT commits SELECT recent",
638 ),
639 ("SELECT CASE WHEN x THEN 1 END FROM t", "SELECT t"),
640 ("-- note\nSELECT /* hint */ id FROM t", "SELECT t"),
641 ("TRUNCATE TABLE logs", "TRUNCATE logs"),
642 ("SELECT replace(name, 'a', 'b') FROM t", "SELECT t"),
643 (
644 "MERGE INTO target USING source ON (target.id = source.id)",
645 "MERGE target source",
646 ),
647 ] {
648 assert_eq!(summarize(sql).as_deref(), Some(summary), "{sql}");
649 }
650 assert_eq!(summarize("not sql at all"), None);
651 assert_eq!(summarize(""), None);
652 }
653
654 #[test]
655 fn long_summaries_stop_at_a_whole_name() {
656 let tables: Vec<String> = (0..60).map(|i| format!("table_number_{i}")).collect();
657 let sql = format!("SELECT * FROM {}", tables.join(", "));
658 let summary = summarize(&sql).unwrap();
659 assert!(summary.len() <= MAX_SUMMARY);
660 assert!(
661 summary
662 .split(' ')
663 .skip(1)
664 .all(|t| tables.iter().any(|n| n == t)),
665 "{summary}"
666 );
667 }
668
669 #[test]
670 fn sanitizing_replaces_literals_and_keeps_placeholders() {
671 for (sql, clean) in [
672 (
673 "SELECT * FROM users WHERE name = 'Ann' AND age > 30",
674 "SELECT * FROM users WHERE name = ? AND age > ?",
675 ),
676 (
677 "SELECT * FROM t WHERE id = $1 AND x = ?2 AND y = :name AND z = @p",
678 "SELECT * FROM t WHERE id = $1 AND x = ?2 AND y = :name AND z = @p",
679 ),
680 (
681 "SELECT 'it''s', 1.5e10, .5, 0xFF, X'0102', E'a\\nb' FROM t",
682 "SELECT ?, ?, ?, ?, ?, ? FROM t",
683 ),
684 ("SELECT $$secret$$, $tag$also secret$tag$", "SELECT ?, ?"),
685 (
686 "SELECT * FROM t WHERE active = TRUE",
687 "SELECT * FROM t WHERE active = ?",
688 ),
689 (
690 "SELECT * FROM t WHERE id IN (1, 2, 3)",
691 "SELECT * FROM t WHERE id IN (?)",
692 ),
693 (
694 "SELECT * FROM t WHERE id IN ($1, $2)",
695 "SELECT * FROM t WHERE id IN ($1, $2)",
696 ),
697 (
698 "SELECT * FROM t -- tail 'secret'\nWHERE x = 1",
699 "SELECT * FROM t WHERE x = ?",
700 ),
701 ("SELECT /* user: bob */ col2 FROM t2", "SELECT col2 FROM t2"),
702 (
703 r#"SELECT "column 1", t1.col2 FROM "my table" t1"#,
704 r#"SELECT "column 1", t1.col2 FROM "my table" t1"#,
705 ),
706 ("SELECT id::text FROM t", "SELECT id::text FROM t"),
707 ] {
708 assert_eq!(sanitize(sql), clean, "{sql}");
709 }
710 }
711
712 #[test]
713 fn unterminated_input_is_still_safe() {
714 assert_eq!(sanitize("SELECT 'unterminated secret"), "SELECT ?");
715 assert_eq!(sanitize("SELECT $$ never closed"), "SELECT ?");
716 assert_eq!(sanitize("SELECT /* never closed"), "SELECT");
717 for sql in [
719 "'", "\"", "$", "$$", "?", ":", "@", "/*", "--", "[", "E'", "0x", ".", "IN (", "IN (1,",
720 ] {
721 let _ = sanitize(sql);
722 let _ = summarize(sql);
723 }
724 }
725}