cratefield_core/
lint.rs

1//! The portable-SQL lint (ADR 0004): flags tokens that SQLite and Postgres
2//! disagree about, or that betray non-portable DDL. One canonical definition
3//! shared by `fz doctor` and the Postgres migration runner's set selection
4//! (issue #18) so the two can never drift.
5//!
6//! The lint reads **DDL**, not the whole file. Comments and string
7//! literals are stripped first, because neither is executed: a migration
8//! that explains itself in prose, or stores the word `blob` as data, is
9//! portable. Scanning them produced false positives that failed a module
10//! for a backtick inside a `--` comment.
11
12/// Returns `(token, explanation)` pairs found in `sql`.
13///
14/// This is the predicate behind both `fz doctor`'s migration lint and the
15/// `cratefield-adapter-postgres` runner's rule that a module's `sqlite`
16/// migration set may be applied to Postgres only when it passes.
17///
18/// SQL comments (`-- ...`, `/* ... */`) and single-quoted string literals
19/// are ignored: they are not DDL. Double-quoted identifiers are not
20/// ignored, because they are DDL and portable in both dialects.
21#[must_use]
22pub fn lint_portable_sql(sql: &str) -> Vec<(&'static str, &'static str)> {
23    const BANNED: &[(&str, &str)] = &[
24        (
25            "AUTOINCREMENT",
26            "SQLite-only; use plain INTEGER PRIMARY KEY (ULID ids instead of autoincrement)",
27        ),
28        (
29            "datetime(",
30            "dialect function; store ISO-8601 TEXT and compute in code",
31        ),
32        ("SERIAL", "Postgres-only; use TEXT ULID ids"),
33        (
34            "NOW()",
35            "dialect function; bind an ISO-8601 timestamp instead",
36        ),
37        (
38            "json_extract",
39            "SQLite-only JSON function; parse JSON in code",
40        ),
41        (
42            "`",
43            "backtick quoting is MySQL/SQLite; use double quotes or none",
44        ),
45        (
46            "BLOB",
47            "SQLite-only type; Postgres has BYTEA — ship a migrations/postgres override",
48        ),
49    ];
50
51    let haystack = strip_non_ddl(sql).to_ascii_lowercase();
52    BANNED
53        .iter()
54        .filter(|(token, _)| {
55            if *token == "`" {
56                haystack.contains('`')
57            } else {
58                haystack.contains(&token.to_ascii_lowercase())
59            }
60        })
61        .copied()
62        .collect()
63}
64
65/// Card-data fragments the harness never stores. With a normal Stripe
66/// integration the card details go straight to Stripe (Checkout, Elements, the
67/// SDKs) and never reach a backend, so a column or secret shaped like a card
68/// number, verification code or full expiry is a mistake. Deliberately the
69/// *specific* forms and not bare `pan`, `track` or `expiry`, which collide with
70/// legitimate columns — an audio `pan`, a music `track`, a `session_expiry`
71/// (issue #44).
72pub const CARD_DATA: &[&str] = &[
73    "card_number",
74    "cardnumber",
75    "card_no",
76    "cardno",
77    "primary_account_number",
78    "full_pan",
79    "cvv",
80    "cvc",
81    "cvv2",
82    "cvc2",
83    "card_cvv",
84    "card_cvc",
85    "exp_month",
86    "exp_year",
87    "card_expiry",
88    "expiry_date",
89    "expiration_date",
90    "track_data",
91    "track2",
92    "magstripe",
93    "magnetic_stripe",
94    // `tools/migration-guard.sh` carried these four and this list did
95    // not, so the same non-goal was two different rules: a `.sql`
96    // migration naming `expiry_month` failed the shell guard, and a
97    // declared table naming it passed everything, because a declared
98    // table's DDL is not a `.sql` file and only ever meets this list.
99    // `the_migration_guard_knows_the_same_card_data` keeps them one.
100    "cardholder",
101    "expiry_month",
102    "expiry_year",
103];
104
105/// The first card-data fragment `text` contains (case-insensitive), or `None`.
106/// Keeps card numbers, verification codes and full expiry out of migrations and
107/// secret names — with a normal Stripe integration none of them should exist
108/// (issue #44).
109#[must_use]
110pub fn card_data_hit(text: &str) -> Option<&'static str> {
111    let hay = text.to_ascii_lowercase();
112    CARD_DATA.iter().copied().find(|frag| hay.contains(frag))
113}
114
115/// Card-data column or table names found in `sql`, as `(fragment, why)` pairs.
116/// Comments and string literals are ignored, so documenting the rule does not
117/// trip it.
118#[must_use]
119pub fn lint_card_data(sql: &str) -> Vec<(&'static str, &'static str)> {
120    match card_data_hit(&strip_non_ddl(sql)) {
121        Some(frag) => vec![(
122            frag,
123            "looks like card data; with a normal Stripe integration the card never \
124             reaches a backend — store only Stripe's identifiers",
125        )],
126        None => Vec::new(),
127    }
128}
129
130/// Words a `CREATE` may carry before `TABLE`. Anything else after `CREATE`
131/// — `INDEX`, `UNIQUE`, `VIEW`, `TRIGGER` — ends the match, which is how
132/// `CREATE UNIQUE INDEX deletion_jobs_confirmation_code` stays out of the
133/// result.
134///
135/// `VIRTUAL` is here because a virtual table is a table: it holds rows, an
136/// export would read it, and leaving it out would be a hole shaped exactly
137/// like the one this scan exists to close.
138const CREATE_MODIFIERS: &[&str] = &[
139    "temp",
140    "temporary",
141    "unlogged",
142    "global",
143    "local",
144    "virtual",
145];
146
147/// Every table `sql` leaves behind, in the order it creates them.
148///
149/// This is the other half of the personal-data rule (issue #272).
150/// [`undeclared_tables`](crate::undeclared_tables) compares
151/// [`Module::tables`](crate::Module::tables) against
152/// [`Module::personal_data`](crate::Module::personal_data), so it can only see
153/// a table that is already in one of those two lists; a table the module never
154/// lists is invisible to it, to `fz data export` and to erasure at once. The
155/// migrations are where a table actually comes into being, so they are what
156/// [`unlisted_tables`](crate::unlisted_tables) compares against — and this is
157/// the scan it reads them with.
158///
159/// **Not a SQL parser, on purpose.** A parser is a dependency and a decision
160/// this workspace has not taken; what a `CREATE TABLE` names is recoverable
161/// from a token walk, and a narrow scan that is wrong loudly is worth more
162/// here than a general one nobody can audit. It reads the same stripped DDL
163/// [`lint_portable_sql`] does, so the four ways it could be wrong are handled
164/// the same way in both:
165///
166/// 1. **A `CREATE TABLE` in a comment or a string is not DDL.** Both are
167///    replaced with spaces before the walk, so a migration that explains its
168///    own schema in prose — as `deletion_jobs` and `cms_item` both do — does
169///    not report the tables it mentions.
170/// 2. **A temporary table is not the module's table.** `CREATE TEMP TABLE`
171///    and `CREATE TEMPORARY TABLE` are skipped: the table is gone with the
172///    connection, so no export could read it and no erasure could miss it.
173/// 3. **The rebuild pattern leaves one table, not two.** SQLite cannot relax
174///    a `NOT NULL`, so `auth-core 0003` and `waitlist 0005` both create
175///    `<table>_rebuild`, copy into it, drop the original and rename. Rather
176///    than special-casing the name, the walk tracks what each statement does:
177///    `CREATE` adds, `DROP TABLE` removes, `ALTER TABLE … RENAME TO` moves.
178///    The answer is therefore what exists when the last migration has run,
179///    which is the thing `tables()` is supposed to describe. `ALTER TABLE …
180///    RENAME COLUMN … TO …` is left alone — it renames a column, not a table.
181/// 4. **Case.** Names come back exactly as the SQL wrote them; duplicates are
182///    collapsed ASCII-case-insensitively, because an unquoted identifier is
183///    case-folded by both dialects.
184///
185/// A name that is not a plain identifier — schema-qualified, or quoted with
186/// something exotic inside — is returned verbatim rather than dropped. It will
187/// not match anything in `tables()` and the module will fail the check, which
188/// is the safe direction for a scan to be uncertain in: nothing in this
189/// workspace needs such a name, and silently ignoring one is how a table
190/// escapes.
191///
192/// Postgres dollar-quoted bodies (`$$ … $$`) are **not** stripped, because
193/// nothing in this workspace uses one; a `CREATE TABLE` inside a function body
194/// would be reported. That, too, fails loudly rather than quietly.
195///
196/// ```
197/// use cratefield_core::created_tables;
198///
199/// let sql = "\
200/// -- CREATE TABLE mentioned_in_prose (…)
201/// CREATE TABLE IF NOT EXISTS notes (id TEXT PRIMARY KEY);
202/// CREATE INDEX notes_by_id ON notes (id);
203/// CREATE TEMP TABLE scratch (id TEXT);
204/// CREATE TABLE notes_rebuild (id TEXT PRIMARY KEY, body TEXT);
205/// DROP TABLE notes;
206/// ALTER TABLE notes_rebuild RENAME TO notes;";
207/// assert_eq!(created_tables(sql), ["notes"]);
208/// ```
209#[must_use]
210pub fn created_tables(sql: &str) -> Vec<String> {
211    let ddl = strip_non_ddl(sql);
212    let words: Vec<&str> = ddl
213        .split(|c: char| !(c.is_ascii_alphanumeric() || c == '_' || c == '"' || c == '.'))
214        .filter(|word| !word.is_empty())
215        .collect();
216
217    let mut live: Vec<String> = Vec::new();
218    let mut i = 0;
219    while i < words.len() {
220        let word = words[i];
221        i += 1;
222        if word.eq_ignore_ascii_case("create") {
223            create_table(&words, &mut i, &mut live);
224        } else if word.eq_ignore_ascii_case("drop") {
225            drop_table(&words, &mut i, &mut live);
226        } else if word.eq_ignore_ascii_case("alter") {
227            rename_table(&words, &mut i, &mut live);
228        }
229    }
230    live
231}
232
233/// Whether the word at `at` is `keyword`, ignoring case. `false` past the end,
234/// so a statement cut off mid-way simply does not match.
235fn word_is(words: &[&str], at: usize, keyword: &str) -> bool {
236    words
237        .get(at)
238        .is_some_and(|word| word.eq_ignore_ascii_case(keyword))
239}
240
241/// `CREATE [TEMP|…] TABLE [IF NOT EXISTS] <name>`, from just after `CREATE`.
242fn create_table(words: &[&str], i: &mut usize, live: &mut Vec<String>) {
243    let mut temporary = false;
244    while CREATE_MODIFIERS
245        .iter()
246        .any(|modifier| word_is(words, *i, modifier))
247    {
248        temporary |= word_is(words, *i, "temp") || word_is(words, *i, "temporary");
249        *i += 1;
250    }
251    if !word_is(words, *i, "table") {
252        return;
253    }
254    *i += 1;
255    skip_keywords(words, i, &["if", "not", "exists"]);
256    let Some(name) = words.get(*i).copied() else {
257        return;
258    };
259    *i += 1;
260    if !temporary {
261        add(live, name);
262    }
263}
264
265/// `DROP TABLE [IF EXISTS] <name>`, from just after `DROP`.
266fn drop_table(words: &[&str], i: &mut usize, live: &mut Vec<String>) {
267    if !word_is(words, *i, "table") {
268        return;
269    }
270    *i += 1;
271    skip_keywords(words, i, &["if", "exists"]);
272    if let Some(name) = words.get(*i).copied() {
273        *i += 1;
274        remove(live, name);
275    }
276}
277
278/// `ALTER TABLE [IF EXISTS] <from> RENAME TO <to>`, from just after `ALTER`.
279/// Every other `ALTER TABLE` — including `RENAME COLUMN … TO …` — leaves the
280/// set alone.
281fn rename_table(words: &[&str], i: &mut usize, live: &mut Vec<String>) {
282    if !word_is(words, *i, "table") {
283        return;
284    }
285    *i += 1;
286    skip_keywords(words, i, &["if", "exists"]);
287    let Some(from) = words.get(*i).copied() else {
288        return;
289    };
290    *i += 1;
291    if !(word_is(words, *i, "rename") && word_is(words, *i + 1, "to")) {
292        return;
293    }
294    let Some(to) = words.get(*i + 2).copied() else {
295        return;
296    };
297    *i += 3;
298    remove(live, from);
299    add(live, to);
300}
301
302/// Steps over `keywords` when they all appear next, in order; leaves `i`
303/// alone otherwise, so a table actually called `if` is still read as a name.
304fn skip_keywords(words: &[&str], i: &mut usize, keywords: &[&str]) {
305    let matched = keywords
306        .iter()
307        .enumerate()
308        .all(|(offset, keyword)| word_is(words, *i + offset, keyword));
309    if matched {
310        *i += keywords.len();
311    }
312}
313
314/// Adds a table name, unquoted, unless an equal-ignoring-case one is there.
315fn add(live: &mut Vec<String>, name: &str) {
316    let name = unquote(name);
317    if !live.iter().any(|table| table.eq_ignore_ascii_case(&name)) {
318        live.push(name);
319    }
320}
321
322/// Removes a table name, ignoring case; a name that is not there is a drop of
323/// a table another module or an earlier deployment owns, and is not our
324/// business.
325fn remove(live: &mut Vec<String>, name: &str) {
326    let name = unquote(name);
327    live.retain(|table| !table.eq_ignore_ascii_case(&name));
328}
329
330/// `"users"` → `users`. Anything else is returned unchanged, including a
331/// half-quoted name, which will fail the comparison rather than be guessed at.
332fn unquote(name: &str) -> String {
333    name.strip_prefix('"')
334        .and_then(|rest| rest.strip_suffix('"'))
335        .unwrap_or(name)
336        .to_owned()
337}
338
339/// Replaces every SQL comment and single-quoted string literal with spaces,
340/// leaving the executable DDL and its byte positions alone.
341///
342/// Block comments nest in Postgres and do not in SQLite; this counts depth,
343/// which is right for Postgres and harmless for SQLite (a migration relying
344/// on the difference is not portable anyway). An unterminated comment or
345/// literal swallows the rest of the input rather than panicking — the
346/// database will reject it long before the lint matters.
347fn strip_non_ddl(sql: &str) -> String {
348    let bytes = sql.as_bytes();
349    let mut out = String::with_capacity(sql.len());
350    let mut i = 0;
351    while i < bytes.len() {
352        // `--` to end of line.
353        if bytes[i] == b'-' && bytes.get(i + 1) == Some(&b'-') {
354            while i < bytes.len() && bytes[i] != b'\n' {
355                out.push(' ');
356                i += 1;
357            }
358            continue;
359        }
360        // `/* ... */`, nesting.
361        if bytes[i] == b'/' && bytes.get(i + 1) == Some(&b'*') {
362            let mut depth = 1_usize;
363            out.push_str("  ");
364            i += 2;
365            while i < bytes.len() && depth > 0 {
366                if bytes[i] == b'/' && bytes.get(i + 1) == Some(&b'*') {
367                    depth += 1;
368                    out.push_str("  ");
369                    i += 2;
370                } else if bytes[i] == b'*' && bytes.get(i + 1) == Some(&b'/') {
371                    depth -= 1;
372                    out.push_str("  ");
373                    i += 2;
374                } else {
375                    out.push(if bytes[i] == b'\n' { '\n' } else { ' ' });
376                    i += 1;
377                }
378            }
379            continue;
380        }
381        // `'...'`, with `''` as the escape.
382        if bytes[i] == b'\'' {
383            out.push(' ');
384            i += 1;
385            while i < bytes.len() {
386                if bytes[i] == b'\'' {
387                    if bytes.get(i + 1) == Some(&b'\'') {
388                        out.push_str("  ");
389                        i += 2;
390                        continue;
391                    }
392                    out.push(' ');
393                    i += 1;
394                    break;
395                }
396                out.push(if bytes[i] == b'\n' { '\n' } else { ' ' });
397                i += 1;
398            }
399            continue;
400        }
401        // Not a byte we rewrite: copy the whole UTF-8 character.
402        let start = i;
403        i += 1;
404        while i < bytes.len() && (bytes[i] & 0xC0) == 0x80 {
405            i += 1;
406        }
407        out.push_str(&sql[start..i]);
408    }
409    out
410}
411
412#[cfg(test)]
413mod tests {
414    use super::lint_portable_sql;
415
416    #[test]
417    fn clean_portable_sql_passes() {
418        let sql = "CREATE TABLE t (id TEXT PRIMARY KEY, n INTEGER NOT NULL DEFAULT 0, \
419                   created_at TEXT NOT NULL, UNIQUE(id));";
420        assert!(lint_portable_sql(sql).is_empty());
421    }
422
423    #[test]
424    fn every_banned_token_is_flagged() {
425        let sql = "CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, \
426                   at datetime('now'), s SERIAL, n NOW(), j json_extract(x,'$'), c `col`);";
427        let found = lint_portable_sql(sql);
428        let tokens: Vec<&str> = found.iter().map(|(token, _)| *token).collect();
429        assert_eq!(
430            tokens,
431            [
432                "AUTOINCREMENT",
433                "datetime(",
434                "SERIAL",
435                "NOW()",
436                "json_extract",
437                "`"
438            ]
439        );
440    }
441
442    #[test]
443    fn matching_is_case_insensitive() {
444        assert!(
445            lint_portable_sql("SELECT autoincrement FROM t;")
446                .iter()
447                .any(|(token, _)| *token == "AUTOINCREMENT")
448        );
449    }
450
451    #[test]
452    fn prose_in_a_comment_is_not_ddl() {
453        // The regression: `module-cms` explains its tables in a `--`
454        // comment, and markdown-style backticks around a table name failed
455        // the whole module for MySQL quoting it does not use.
456        let sql = "-- A small content store. `cms_item` is the working copy;\n\
457                   -- `cms_revision` is append-only history (stored as BLOB\n\
458                   -- in some other database, but not here).\n\
459                   CREATE TABLE cms_item (id TEXT PRIMARY KEY);";
460        assert_eq!(lint_portable_sql(sql), vec![]);
461    }
462
463    #[test]
464    fn a_block_comment_is_not_ddl_and_may_nest() {
465        let sql = "/* uses `backticks` and /* nests, mentioning SERIAL */ still inside */ \
466                   CREATE TABLE t (id TEXT PRIMARY KEY);";
467        assert_eq!(lint_portable_sql(sql), vec![]);
468    }
469
470    #[test]
471    fn a_string_literal_is_data_not_ddl() {
472        // Storing the word as a value says nothing about the column type.
473        let sql = "INSERT INTO kinds (name) VALUES ('blob'), ('serial'), \
474                   ('it''s NOW() in prose');";
475        assert_eq!(lint_portable_sql(sql), vec![]);
476    }
477
478    #[test]
479    fn stripping_comments_does_not_hide_real_ddl() {
480        // The other half: the same tokens outside a comment still fail, and
481        // a comment must not swallow the statement that follows it.
482        let sql = "-- a note about ids\n\
483                   CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, c `col` TEXT);";
484        let tokens: Vec<&str> = lint_portable_sql(sql)
485            .iter()
486            .map(|(token, _)| *token)
487            .collect();
488        assert_eq!(tokens, ["AUTOINCREMENT", "`"]);
489    }
490
491    #[test]
492    fn a_double_quoted_identifier_is_still_ddl() {
493        // Double quotes are portable, so they are not stripped: a banned
494        // token inside them is a real column type, not prose.
495        let tokens: Vec<&str> = lint_portable_sql(r#"CREATE TABLE t ("data" BLOB);"#)
496            .iter()
497            .map(|(token, _)| *token)
498            .collect();
499        assert_eq!(tokens, ["BLOB"]);
500    }
501
502    #[test]
503    fn an_unterminated_comment_or_literal_does_not_panic() {
504        // Malformed SQL is the database's problem, not the lint's.
505        assert_eq!(lint_portable_sql("/* never closed"), vec![]);
506        assert_eq!(lint_portable_sql("SELECT 'never closed"), vec![]);
507        assert_eq!(lint_portable_sql("-- never newline"), vec![]);
508        // Multi-byte characters inside and outside the stripped spans.
509        assert_eq!(
510            lint_portable_sql("-- naïve ünicode ✓\nCREATE TABLE t (id TEXT);"),
511            vec![]
512        );
513    }
514
515    #[test]
516    fn now_without_parens_is_not_flagged() {
517        // `NOW()` is the dialect function; the word "now" in prose or a
518        // column name is not.
519        assert!(lint_portable_sql("SELECT now FROM t;").is_empty());
520    }
521}
522
523#[cfg(test)]
524mod created_tables_tests {
525    use super::created_tables;
526
527    #[test]
528    fn a_plain_create_is_found() {
529        assert_eq!(
530            created_tables("CREATE TABLE deletion_jobs (id TEXT PRIMARY KEY);"),
531            ["deletion_jobs"]
532        );
533        assert_eq!(
534            created_tables("create table if not exists users(id TEXT);"),
535            ["users"]
536        );
537        // No space before the column list, and a quoted name.
538        assert_eq!(
539            created_tables(r#"CREATE TABLE "users"(id TEXT);"#),
540            ["users"]
541        );
542    }
543
544    #[test]
545    fn every_create_in_one_set_is_found_in_order() {
546        // The anti-vacuity case at the scan's own level: a scan that stopped
547        // matching would return an empty vec and every absence assertion
548        // built on it would keep passing.
549        let sql = "CREATE TABLE a (id TEXT);\n\
550                   CREATE TABLE IF NOT EXISTS b (id TEXT);\n\
551                   CREATE UNLOGGED TABLE c (id TEXT);";
552        assert_eq!(created_tables(sql), ["a", "b", "c"]);
553    }
554
555    #[test]
556    fn an_index_view_or_trigger_is_not_a_table() {
557        // `CREATE UNIQUE INDEX deletion_jobs_confirmation_code ON deletion_jobs`
558        // is the real line this protects: a scan that read the word after
559        // `CREATE` would report the index name as a table.
560        let sql = "CREATE UNIQUE INDEX deletion_jobs_confirmation_code ON deletion_jobs (code);\n\
561                   CREATE INDEX by_status ON deletion_jobs (status);\n\
562                   CREATE VIEW live_jobs AS SELECT * FROM deletion_jobs;\n\
563                   CREATE TRIGGER t AFTER INSERT ON deletion_jobs BEGIN SELECT 1; END;";
564        assert_eq!(created_tables(sql), Vec::<String>::new());
565    }
566
567    #[test]
568    fn a_create_table_in_a_comment_or_a_string_is_not_ddl() {
569        // The regression `lint_portable_sql` already learned once: a migration
570        // that explains its own schema in prose must not report the tables the
571        // prose names. `deletion_jobs` opens with twelve lines of exactly that.
572        let sql = "-- CREATE TABLE ghost (id TEXT) is what this replaces.\n\
573                   /* CREATE TABLE also_a_ghost (id TEXT); */\n\
574                   INSERT INTO note (body) VALUES ('CREATE TABLE quoted_ghost (id TEXT)');\n\
575                   CREATE TABLE real_one (id TEXT);";
576        assert_eq!(created_tables(sql), ["real_one"]);
577    }
578
579    #[test]
580    fn a_temporary_table_is_not_the_modules_table() {
581        // It is gone with the connection, so no export could read it and no
582        // erasure could miss it. Requiring it in `tables()` would put a name
583        // in the export list that names nothing.
584        let sql = "CREATE TEMP TABLE scratch (id TEXT);\n\
585                   CREATE TEMPORARY TABLE also_scratch (id TEXT);\n\
586                   CREATE GLOBAL TEMPORARY TABLE still_scratch (id TEXT);\n\
587                   CREATE TABLE kept (id TEXT);";
588        assert_eq!(created_tables(sql), ["kept"]);
589    }
590
591    #[test]
592    fn the_rebuild_pattern_leaves_one_table() {
593        // `auth-core 0003` and `waitlist 0005`, both on main and both
594        // unchangeable: SQLite cannot relax a NOT NULL, so the table is
595        // rebuilt under another name and renamed over the original.
596        let sql = "CREATE TABLE IF NOT EXISTS single_use_tokens_rebuild (id TEXT);\n\
597                   INSERT INTO single_use_tokens_rebuild SELECT * FROM single_use_tokens;\n\
598                   DROP TABLE single_use_tokens;\n\
599                   ALTER TABLE single_use_tokens_rebuild RENAME TO single_use_tokens;";
600        assert_eq!(created_tables(sql), ["single_use_tokens"]);
601    }
602
603    #[test]
604    fn a_renamed_column_does_not_rename_the_table() {
605        let sql = "CREATE TABLE notes (id TEXT, body TEXT);\n\
606                   ALTER TABLE notes RENAME COLUMN body TO text;\n\
607                   ALTER TABLE notes ADD COLUMN amr TEXT;";
608        assert_eq!(created_tables(sql), ["notes"]);
609    }
610
611    #[test]
612    fn a_table_created_and_dropped_is_not_left_behind() {
613        let sql = "CREATE TABLE gone (id TEXT);\n\
614                   CREATE TABLE kept (id TEXT);\n\
615                   DROP TABLE IF EXISTS gone;\n\
616                   DROP INDEX kept_by_id;";
617        assert_eq!(created_tables(sql), ["kept"]);
618    }
619
620    #[test]
621    fn the_same_table_twice_is_reported_once() {
622        // Two migrations may both guard with IF NOT EXISTS.
623        let sql = "CREATE TABLE IF NOT EXISTS notes (id TEXT);\n\
624                   CREATE TABLE IF NOT EXISTS NOTES (id TEXT);";
625        assert_eq!(created_tables(sql), ["notes"]);
626    }
627
628    #[test]
629    fn a_name_the_scan_cannot_read_is_returned_rather_than_dropped() {
630        // The safe direction to be uncertain in: it fails the comparison
631        // loudly instead of escaping it silently.
632        assert_eq!(
633            created_tables("CREATE TABLE public.users (id TEXT);"),
634            ["public.users"]
635        );
636    }
637
638    #[test]
639    fn truncated_or_malformed_sql_does_not_panic() {
640        for sql in [
641            "CREATE",
642            "CREATE TABLE",
643            "CREATE TABLE IF NOT EXISTS",
644            "DROP TABLE",
645            "ALTER TABLE notes RENAME",
646            "ALTER TABLE notes RENAME TO",
647            "-- CREATE TABLE never_closed",
648            "CREATE TABLE t (id TEXT); -- naïve ünicode ✓",
649        ] {
650            let _ = created_tables(sql);
651        }
652    }
653}
654
655#[cfg(test)]
656mod card_data_tests {
657    use super::{card_data_hit, lint_card_data};
658
659    #[test]
660    fn a_card_number_column_is_flagged() {
661        let sql = "CREATE TABLE payment (id TEXT PRIMARY KEY, card_number TEXT)";
662        assert_eq!(lint_card_data(sql).len(), 1);
663        assert_eq!(lint_card_data(sql)[0].0, "card_number");
664        assert!(card_data_hit("cvv").is_some());
665        assert!(card_data_hit("exp_month").is_some());
666    }
667
668    #[test]
669    fn card_data_only_in_a_comment_or_string_is_not_flagged() {
670        // Documenting the rule must not trip it.
671        let sql = "CREATE TABLE t (id TEXT) -- never store card_number here\n";
672        assert!(lint_card_data(sql).is_empty());
673        let sql2 = "INSERT INTO note (body) VALUES ('do not store card_number')";
674        assert!(lint_card_data(sql2).is_empty());
675    }
676
677    #[test]
678    fn ambiguous_words_do_not_false_positive() {
679        // A music track, an audio pan, a session expiry are all legitimate.
680        for sql in [
681            "CREATE TABLE song (id TEXT, track INTEGER)",
682            "CREATE TABLE mix (id TEXT, pan REAL)",
683            "CREATE TABLE session (id TEXT, session_expiry TEXT)",
684            "CREATE TABLE t (id TEXT, token_expiry TEXT)",
685        ] {
686            assert!(lint_card_data(sql).is_empty(), "false positive on: {sql}");
687        }
688    }
689}
cratefield_core/lint.rs

cratefield_core/
lint.rs
