dbmcp_sql/
validation.rs

1//! AST-based SQL validation for read-only mode enforcement.
2
3use crate::SqlError;
4use std::ops::ControlFlow;
5
6use sqlparser::ast::{Expr, Function, ObjectName, Select, Statement, TableFactor, Visit, Visitor};
7use sqlparser::dialect::Dialect;
8use sqlparser::parser::Parser;
9
10/// Classifies a validated read-only statement for pagination dispatch.
11#[derive(Debug, Clone, Copy, PartialEq, Eq)]
12pub enum StatementKind {
13    /// `SELECT`, `WITH ... SELECT`, or compound queries (`UNION`/`INTERSECT`/`EXCEPT`).
14    /// Composable inside a subquery wrap with an appended `LIMIT` / `OFFSET`.
15    Select,
16    /// `SHOW`, `DESC` / `DESCRIBE`, `USE`, or `EXPLAIN` — read-only but not
17    /// composable as a subquery source. Returned in a single response.
18    NonSelect,
19}
20
21/// Validates that a SQL query is read-only and classifies it for pagination.
22///
23/// Parses the query using the given dialect and checks:
24/// 1. Exactly one statement (multi-statement injection blocked)
25/// 2. Statement type is read-only (SELECT, SHOW, DESCRIBE, USE, EXPLAIN)
26/// 3. The statement wrapped by `EXPLAIN` is itself read-only
27/// 4. No data-modifying CTEs or `SELECT ... INTO` inside queries
28/// 5. No dangerous functions (file access, large objects, sequences, extensions)
29/// 6. No INTO OUTFILE/DUMPFILE clauses
30///
31/// Returns the classified [`StatementKind`] on success so callers that
32/// paginate can dispatch `Select` to a subquery-wrapped fetch and route
33/// `NonSelect` through a single-page pass-through.
34///
35/// # Errors
36///
37/// Returns [`SqlError`] if the query is not allowed in read-only mode.
38pub fn validate_read_only(sql: &str, dialect: &impl Dialect) -> Result<StatementKind, SqlError> {
39    let trimmed = sql.trim();
40    if trimmed.is_empty() {
41        return Err(SqlError::ReadOnlyViolation);
42    }
43
44    // Pre-check for INTO OUTFILE/DUMPFILE — sqlparser may not parse MySQL-specific syntax
45    let upper = trimmed.to_uppercase();
46    if upper.contains("INTO OUTFILE") || upper.contains("INTO DUMPFILE") {
47        return Err(SqlError::IntoOutfileBlocked);
48    }
49
50    // Must be exactly one statement
51    let stmt = parse_single(trimmed, dialect)?.ok_or(SqlError::ReadOnlyViolation)?;
52
53    classify(&stmt)
54}
55
56/// Checks that `stmt` is read-only and classifies it for pagination.
57fn classify(stmt: &Statement) -> Result<StatementKind, SqlError> {
58    match stmt {
59        Statement::Query(_) => {
60            check_data_modifying_queries(stmt)?;
61            check_dangerous_functions(stmt)?;
62            Ok(StatementKind::Select)
63        }
64        // `EXPLAIN ANALYZE` executes the wrapped statement, so it must be read-only too.
65        Statement::Explain { statement, .. } => {
66            classify(statement)?;
67            Ok(StatementKind::NonSelect)
68        }
69        Statement::ShowTables { .. }
70        | Statement::ShowColumns { .. }
71        | Statement::ShowCreate { .. }
72        | Statement::ShowVariable { .. }
73        | Statement::ShowVariables { .. }
74        | Statement::ShowStatus { .. }
75        | Statement::ShowDatabases { .. }
76        | Statement::ShowSchemas { .. }
77        | Statement::ShowCollation { .. }
78        | Statement::ShowFunctions { .. }
79        | Statement::ShowViews { .. }
80        | Statement::ShowObjects(_)
81        | Statement::ExplainTable { .. }
82        | Statement::Use(_) => Ok(StatementKind::NonSelect),
83        _ => Err(SqlError::ReadOnlyViolation),
84    }
85}
86
87/// Validates that a SQL string contains exactly one statement.
88///
89/// Unlike [`validate_read_only`], any statement type is accepted. Use this
90/// where the input is embedded into a wrapper (e.g. `EXPLAIN {query}`) that
91/// must never smuggle additional statements past the driver.
92///
93/// # Errors
94///
95/// Returns [`SqlError::MultiStatement`] if the input holds more than one
96/// statement, or [`SqlError::Query`] if it is empty or fails to parse.
97pub fn validate_single_statement(sql: &str, dialect: &impl Dialect) -> Result<(), SqlError> {
98    parse_single(sql.trim(), dialect)?.ok_or_else(|| SqlError::Query("Query must contain a statement".into()))?;
99    Ok(())
100}
101
102/// Parses `sql` and returns its only statement, `None` if it has none.
103fn parse_single(sql: &str, dialect: &impl Dialect) -> Result<Option<Statement>, SqlError> {
104    let mut statements =
105        Parser::parse_sql(dialect, sql).map_err(|e| SqlError::Query(format!("SQL parse error: {e}")))?;
106
107    if statements.len() > 1 {
108        return Err(SqlError::MultiStatement);
109    }
110    Ok(statements.pop())
111}
112
113/// Rejects data-modifying CTEs and `SELECT ... INTO` anywhere inside a query.
114fn check_data_modifying_queries(stmt: &Statement) -> Result<(), SqlError> {
115    if stmt.visit(&mut DataModifyingQueryChecker).is_break() {
116        return Err(SqlError::ReadOnlyViolation);
117    }
118    Ok(())
119}
120
121struct DataModifyingQueryChecker;
122
123impl Visitor for DataModifyingQueryChecker {
124    type Break = ();
125
126    fn pre_visit_statement(&mut self, stmt: &Statement) -> ControlFlow<Self::Break> {
127        // Nested statements (e.g. `WITH x AS (DELETE ... RETURNING *)`) must be queries.
128        if matches!(stmt, Statement::Query(_)) {
129            ControlFlow::Continue(())
130        } else {
131            ControlFlow::Break(())
132        }
133    }
134
135    fn pre_visit_select(&mut self, select: &Select) -> ControlFlow<Self::Break> {
136        // `SELECT ... INTO new_table` creates a table on PostgreSQL.
137        if select.into.is_some() {
138            return ControlFlow::Break(());
139        }
140        ControlFlow::Continue(())
141    }
142}
143
144/// Functions that read or write server files, mutate state, or reach the network.
145///
146/// Matched case-insensitively against the last segment of the function name.
147const DANGEROUS_FUNCTIONS: &[&str] = &[
148    // MySQL / MariaDB
149    "load_file",
150    // PostgreSQL
151    "pg_read_file",
152    "pg_read_binary_file",
153    "pg_ls_dir",
154    "pg_stat_file",
155    "lo_import",
156    "lo_export",
157    "setval",
158    // SQLite (CLI / extension functions)
159    "readfile",
160    "writefile",
161    "load_extension",
162];
163
164/// Function name prefixes blocked in addition to [`DANGEROUS_FUNCTIONS`].
165const DANGEROUS_FUNCTION_PREFIXES: &[&str] = &["dblink"];
166
167/// Check for dangerous function calls like `LOAD_FILE()` in the AST.
168fn check_dangerous_functions(stmt: &Statement) -> Result<(), SqlError> {
169    let mut checker = DangerousFunctionChecker { found: None };
170    let _ = stmt.visit(&mut checker);
171    if let Some(err) = checker.found {
172        return Err(err);
173    }
174    Ok(())
175}
176
177/// Returns the error for a call to `name` if it is a dangerous function.
178fn dangerous_function(name: &ObjectName) -> Option<SqlError> {
179    // Match the last segment so quoting (`LOAD_FILE`) or schema prefixes (pg_catalog.) don't evade it.
180    let last = name.0.last()?;
181    let func = last
182        .as_ident()
183        .map_or_else(|| last.to_string(), |ident| ident.value.clone());
184    let func = func.to_ascii_lowercase();
185
186    if func == "load_file" {
187        return Some(SqlError::LoadFileBlocked);
188    }
189    let blocked = DANGEROUS_FUNCTIONS.contains(&func.as_str())
190        || DANGEROUS_FUNCTION_PREFIXES
191            .iter()
192            .any(|prefix| func.starts_with(prefix));
193    blocked.then_some(SqlError::DangerousFunctionBlocked(func))
194}
195
196struct DangerousFunctionChecker {
197    found: Option<SqlError>,
198}
199
200impl DangerousFunctionChecker {
201    fn check(&mut self, name: &ObjectName) -> ControlFlow<()> {
202        if let Some(err) = dangerous_function(name) {
203            self.found = Some(err);
204            return ControlFlow::Break(());
205        }
206        ControlFlow::Continue(())
207    }
208}
209
210impl Visitor for DangerousFunctionChecker {
211    type Break = ();
212
213    fn pre_visit_expr(&mut self, expr: &Expr) -> ControlFlow<Self::Break> {
214        if let Expr::Function(Function { name, .. }) = expr {
215            return self.check(name);
216        }
217        ControlFlow::Continue(())
218    }
219
220    fn pre_visit_table_factor(&mut self, table_factor: &TableFactor) -> ControlFlow<Self::Break> {
221        // Set-returning calls in FROM, e.g. `SELECT * FROM pg_ls_dir('/')`.
222        match table_factor {
223            TableFactor::Table {
224                name, args: Some(_), ..
225            }
226            | TableFactor::Function { name, .. } => self.check(name),
227            _ => ControlFlow::Continue(()),
228        }
229    }
230}
231
232#[cfg(test)]
233mod tests {
234    use sqlparser::dialect::{MySqlDialect, PostgreSqlDialect, SQLiteDialect};
235
236    use super::*;
237
238    const MYSQL: MySqlDialect = MySqlDialect {};
239    const POSTGRES: PostgreSqlDialect = PostgreSqlDialect {};
240    const SQLITE: SQLiteDialect = SQLiteDialect {};
241
242    const DIALECT: MySqlDialect = MySqlDialect {};
243
244    // === Statement classification ===
245
246    #[test]
247    fn classifies_select_vs_non_select() {
248        // Select-like: plain SELECT, CTE, compound queries all map to StatementKind::Select.
249        assert_eq!(validate_read_only("SELECT 1", &DIALECT).unwrap(), StatementKind::Select,);
250        assert_eq!(
251            validate_read_only("WITH x AS (SELECT 1) SELECT * FROM x", &DIALECT).unwrap(),
252            StatementKind::Select,
253        );
254        assert_eq!(
255            validate_read_only("SELECT 1 UNION SELECT 2", &DIALECT).unwrap(),
256            StatementKind::Select,
257        );
258
259        // Catalog/metadata statements map to StatementKind::NonSelect.
260        assert_eq!(
261            validate_read_only("SHOW DATABASES", &DIALECT).unwrap(),
262            StatementKind::NonSelect,
263        );
264        assert_eq!(
265            validate_read_only("DESCRIBE users", &DIALECT).unwrap(),
266            StatementKind::NonSelect,
267        );
268        assert_eq!(
269            validate_read_only("USE app", &DIALECT).unwrap(),
270            StatementKind::NonSelect,
271        );
272        assert_eq!(
273            validate_read_only("EXPLAIN SELECT 1", &DIALECT).unwrap(),
274            StatementKind::NonSelect,
275        );
276    }
277
278    // === Allowed queries ===
279
280    #[test]
281    fn test_select_allowed() {
282        assert!(validate_read_only("SELECT * FROM users", &DIALECT).is_ok());
283        assert!(validate_read_only("select * from users", &DIALECT).is_ok());
284    }
285
286    #[test]
287    fn test_show_allowed() {
288        assert!(validate_read_only("SHOW DATABASES", &DIALECT).is_ok());
289        assert!(validate_read_only("SHOW TABLES", &DIALECT).is_ok());
290    }
291
292    #[test]
293    fn test_describe_allowed() {
294        // sqlparser parses DESC/DESCRIBE as ExplainTable
295        assert!(validate_read_only("DESC users", &DIALECT).is_ok());
296        assert!(validate_read_only("DESCRIBE users", &DIALECT).is_ok());
297    }
298
299    #[test]
300    fn test_use_allowed() {
301        assert!(validate_read_only("USE mydb", &DIALECT).is_ok());
302    }
303
304    // === Blocked statement types ===
305
306    #[test]
307    fn test_insert_blocked() {
308        assert!(matches!(
309            validate_read_only("INSERT INTO users VALUES (1)", &DIALECT),
310            Err(SqlError::ReadOnlyViolation)
311        ));
312    }
313
314    #[test]
315    fn test_update_blocked() {
316        assert!(matches!(
317            validate_read_only("UPDATE users SET name='x'", &DIALECT),
318            Err(SqlError::ReadOnlyViolation)
319        ));
320    }
321
322    #[test]
323    fn test_delete_blocked() {
324        assert!(matches!(
325            validate_read_only("DELETE FROM users", &DIALECT),
326            Err(SqlError::ReadOnlyViolation)
327        ));
328    }
329
330    #[test]
331    fn test_drop_blocked() {
332        assert!(matches!(
333            validate_read_only("DROP TABLE users", &DIALECT),
334            Err(SqlError::ReadOnlyViolation)
335        ));
336    }
337
338    #[test]
339    fn test_create_blocked() {
340        assert!(matches!(
341            validate_read_only("CREATE TABLE test (id INT)", &DIALECT),
342            Err(SqlError::ReadOnlyViolation)
343        ));
344    }
345
346    // === Comment bypass attacks ===
347
348    #[test]
349    fn test_comment_bypass_single_line() {
350        // With AST parsing, "SELECT 1 -- \nDELETE FROM users" is parsed as two statements
351        // (or the comment hides the DELETE, making it one SELECT).
352        // Either way, if it parses as multiple statements, it's blocked.
353        // If the parser treats -- as a comment and only sees SELECT 1, it's allowed.
354        let result = validate_read_only("SELECT 1 -- \nDELETE FROM users", &DIALECT);
355        // The parser should treat -- as comment, so only SELECT 1 remains → allowed
356        assert!(result.is_ok() || matches!(result, Err(SqlError::MultiStatement)));
357    }
358
359    #[test]
360    fn test_comment_bypass_multi_line() {
361        // "/* SELECT */ DELETE FROM users" — parser strips comment, sees DELETE
362        assert!(matches!(
363            validate_read_only("/* SELECT */ DELETE FROM users", &DIALECT),
364            Err(SqlError::ReadOnlyViolation)
365        ));
366    }
367
368    // === Dangerous functions ===
369
370    #[test]
371    fn test_load_file_blocked() {
372        assert!(matches!(
373            validate_read_only("SELECT LOAD_FILE('/etc/passwd')", &DIALECT),
374            Err(SqlError::LoadFileBlocked)
375        ));
376    }
377
378    #[test]
379    fn test_load_file_case_insensitive() {
380        assert!(matches!(
381            validate_read_only("SELECT load_file('/etc/passwd')", &DIALECT),
382            Err(SqlError::LoadFileBlocked)
383        ));
384    }
385
386    #[test]
387    fn test_load_file_with_spaces() {
388        // sqlparser normalizes function calls, so spaces before ( are handled
389        assert!(matches!(
390            validate_read_only("SELECT LOAD_FILE ('/etc/passwd')", &DIALECT),
391            Err(SqlError::LoadFileBlocked)
392        ));
393    }
394
395    // === INTO OUTFILE/DUMPFILE ===
396
397    #[test]
398    fn test_into_outfile_blocked() {
399        assert!(matches!(
400            validate_read_only("SELECT * FROM users INTO OUTFILE '/tmp/out'", &DIALECT),
401            Err(SqlError::IntoOutfileBlocked)
402        ));
403    }
404
405    #[test]
406    fn test_into_dumpfile_blocked() {
407        assert!(matches!(
408            validate_read_only("SELECT * FROM users INTO DUMPFILE '/tmp/out'", &DIALECT),
409            Err(SqlError::IntoOutfileBlocked)
410        ));
411    }
412
413    // === String literals should NOT trigger false positives ===
414
415    #[test]
416    fn test_load_file_in_string_allowed() {
417        // LOAD_FILE inside a string literal is NOT a function call in the AST
418        assert!(validate_read_only("SELECT 'LOAD_FILE(/etc/passwd)' FROM dual", &DIALECT).is_ok());
419    }
420
421    // === Empty / comment-only queries ===
422
423    #[test]
424    fn test_empty_query_blocked() {
425        assert!(matches!(
426            validate_read_only("", &DIALECT),
427            Err(SqlError::ReadOnlyViolation)
428        ));
429    }
430
431    #[test]
432    fn test_comment_only_blocked() {
433        // Comment-only input: parser returns empty statements or parse error
434        let result = validate_read_only("-- just a comment", &DIALECT);
435        assert!(result.is_err());
436    }
437
438    // === New tests for AST-based validation ===
439
440    #[test]
441    fn test_multi_statement_blocked() {
442        assert!(matches!(
443            validate_read_only("SELECT 1; SELECT 2", &DIALECT),
444            Err(SqlError::MultiStatement)
445        ));
446    }
447
448    #[test]
449    fn test_multi_statement_injection_blocked() {
450        assert!(matches!(
451            validate_read_only("SELECT 1; DROP TABLE users", &DIALECT),
452            Err(SqlError::MultiStatement)
453        ));
454    }
455
456    #[test]
457    fn test_set_statement_blocked() {
458        assert!(matches!(
459            validate_read_only("SET @var = 1", &DIALECT),
460            Err(SqlError::ReadOnlyViolation)
461        ));
462    }
463
464    #[test]
465    fn test_malformed_sql_rejected() {
466        let result = validate_read_only("SELEC * FORM users", &DIALECT);
467        assert!(result.is_err());
468    }
469
470    #[test]
471    fn test_select_with_subquery_allowed() {
472        assert!(validate_read_only("SELECT * FROM (SELECT 1) AS t", &DIALECT).is_ok());
473    }
474
475    #[test]
476    fn test_select_with_where_allowed() {
477        assert!(validate_read_only("SELECT * FROM users WHERE id = 1", &DIALECT).is_ok());
478    }
479
480    #[test]
481    fn test_select_count_allowed() {
482        assert!(validate_read_only("SELECT COUNT(*) FROM users", &DIALECT).is_ok());
483    }
484
485    // === T015: Multi-dialect parameterized tests ===
486
487    fn assert_allowed_all_dialects(sql: &str) {
488        assert!(validate_read_only(sql, &MYSQL).is_ok(), "MySQL should allow: {sql}");
489        assert!(
490            validate_read_only(sql, &POSTGRES).is_ok(),
491            "Postgres should allow: {sql}"
492        );
493        assert!(validate_read_only(sql, &SQLITE).is_ok(), "SQLite should allow: {sql}");
494    }
495
496    fn assert_blocked_all_dialects(sql: &str) {
497        assert!(validate_read_only(sql, &MYSQL).is_err(), "MySQL should block: {sql}");
498        assert!(
499            validate_read_only(sql, &POSTGRES).is_err(),
500            "Postgres should block: {sql}"
501        );
502        assert!(validate_read_only(sql, &SQLITE).is_err(), "SQLite should block: {sql}");
503    }
504
505    #[test]
506    fn select_allowed_all_dialects() {
507        assert_allowed_all_dialects("SELECT * FROM users");
508        assert_allowed_all_dialects("SELECT 1");
509        assert_allowed_all_dialects("SELECT COUNT(*) FROM t");
510    }
511
512    #[test]
513    fn insert_blocked_all_dialects() {
514        assert_blocked_all_dialects("INSERT INTO users VALUES (1)");
515    }
516
517    #[test]
518    fn update_blocked_all_dialects() {
519        assert_blocked_all_dialects("UPDATE users SET name = 'x'");
520    }
521
522    #[test]
523    fn delete_blocked_all_dialects() {
524        assert_blocked_all_dialects("DELETE FROM users");
525    }
526
527    #[test]
528    fn drop_blocked_all_dialects() {
529        assert_blocked_all_dialects("DROP TABLE users");
530    }
531
532    #[test]
533    fn create_blocked_all_dialects() {
534        assert_blocked_all_dialects("CREATE TABLE test (id INT)");
535    }
536
537    // === Single-statement guard (explainQuery) ===
538
539    fn assert_single_statement_rules(dialect: &impl Dialect) {
540        for sql in [
541            "SELECT 1",
542            "SELECT 1;",
543            "INSERT INTO t (a) VALUES (1)",
544            "DELETE FROM t WHERE a = 1",
545        ] {
546            assert!(
547                validate_single_statement(sql, dialect).is_ok(),
548                "expected {sql:?} to be accepted"
549            );
550        }
551        for sql in [
552            "SELECT 1; CREATE TABLE readonly_probe(i int)",
553            "SELECT 1; /* x */ DROP TABLE t",
554            "INSERT INTO t (a) VALUES (1); DELETE FROM t",
555        ] {
556            assert!(
557                matches!(validate_single_statement(sql, dialect), Err(SqlError::MultiStatement)),
558                "expected MultiStatement for {sql:?}"
559            );
560        }
561    }
562
563    #[test]
564    fn single_statement_rules_all_dialects() {
565        assert_single_statement_rules(&MYSQL);
566        assert_single_statement_rules(&POSTGRES);
567        assert_single_statement_rules(&SQLITE);
568    }
569
570    #[test]
571    fn single_statement_rejects_empty_input() {
572        for sql in ["", "   ", ";"] {
573            assert!(
574                matches!(validate_single_statement(sql, &POSTGRES), Err(SqlError::Query(_))),
575                "expected error for {sql:?}"
576            );
577        }
578    }
579
580    #[test]
581    fn multi_statement_blocked_all_dialects() {
582        let sql = "SELECT 1; DROP TABLE x";
583        assert!(matches!(validate_read_only(sql, &MYSQL), Err(SqlError::MultiStatement)));
584        assert!(matches!(
585            validate_read_only(sql, &POSTGRES),
586            Err(SqlError::MultiStatement)
587        ));
588        assert!(matches!(
589            validate_read_only(sql, &SQLITE),
590            Err(SqlError::MultiStatement)
591        ));
592    }
593
594    #[test]
595    fn empty_blocked_all_dialects() {
596        assert_blocked_all_dialects("");
597        assert_blocked_all_dialects("   ");
598    }
599
600    // === T016: Postgres-specific tests ===
601
602    #[test]
603    fn postgres_copy_to_blocked() {
604        let result = validate_read_only("COPY users TO '/tmp/out.csv'", &POSTGRES);
605        assert!(
606            matches!(result, Err(SqlError::ReadOnlyViolation)),
607            "Postgres COPY TO should be blocked: {result:?}"
608        );
609    }
610
611    #[test]
612    fn postgres_copy_from_blocked() {
613        let result = validate_read_only("COPY users FROM '/tmp/in.csv'", &POSTGRES);
614        assert!(result.is_err(), "Postgres COPY FROM should be blocked: {result:?}");
615    }
616
617    #[test]
618    fn postgres_generate_series_allowed() {
619        assert!(validate_read_only("SELECT * FROM generate_series(1, 10)", &POSTGRES).is_ok());
620    }
621
622    // === T017: SQLite-specific and cross-dialect tests ===
623
624    #[test]
625    fn show_databases_across_dialects() {
626        assert!(validate_read_only("SHOW DATABASES", &MYSQL).is_ok());
627        let pg_result = validate_read_only("SHOW DATABASES", &POSTGRES);
628        let sqlite_result = validate_read_only("SHOW DATABASES", &SQLITE);
629        assert!(
630            pg_result.is_ok() || pg_result.is_err(),
631            "Postgres may or may not parse SHOW DATABASES"
632        );
633        assert!(
634            sqlite_result.is_ok() || sqlite_result.is_err(),
635            "SQLite may or may not parse SHOW DATABASES"
636        );
637        if let Err(e) = &pg_result {
638            assert!(
639                !matches!(e, SqlError::ReadOnlyViolation),
640                "SHOW DATABASES should not be classified as a write: {e}"
641            );
642        }
643    }
644
645    // === T018: Unicode and null-byte validation tests ===
646
647    #[test]
648    fn unicode_cyrillic_semicolon_not_misclassified() {
649        let sql = "SELECT 1\u{037E} DROP TABLE users";
650        let result = validate_read_only(sql, &MYSQL);
651        assert!(
652            result.is_err(),
653            "SQL with Cyrillic question mark should not silently succeed as single SELECT"
654        );
655    }
656
657    #[test]
658    fn unicode_fullwidth_semicolon_not_misclassified() {
659        let sql = "SELECT 1\u{FF1B} DROP TABLE users";
660        let result = validate_read_only(sql, &MYSQL);
661        assert!(
662            result.is_err() || validate_read_only(sql, &MYSQL).is_ok(),
663            "fullwidth semicolon is a single token, not a statement separator"
664        );
665    }
666
667    // === Dangerous function deny list ===
668
669    #[test]
670    fn dangerous_functions_blocked_all_dialects() {
671        for sql in [
672            "SELECT pg_read_file('/etc/passwd')",
673            "SELECT pg_read_binary_file('/etc/passwd')",
674            "SELECT * FROM pg_ls_dir('/')",
675            "SELECT pg_ls_dir('/')",
676            "SELECT pg_stat_file('/etc/passwd')",
677            "SELECT lo_import('/etc/passwd')",
678            "SELECT lo_export(1, '/tmp/x')",
679            "SELECT setval('s', 1)",
680            "SELECT dblink_connect('host=attacker.example')",
681            "SELECT * FROM dblink('host=x', 'SELECT 1') AS t(a int)",
682            "SELECT readfile('/etc/passwd')",
683            "SELECT writefile('/tmp/x', 'data')",
684            "SELECT load_extension('/tmp/evil.so')",
685            "SELECT PG_READ_FILE('/etc/passwd')",
686            "SELECT pg_catalog.pg_read_file('/etc/passwd')",
687            "SELECT \"pg_read_file\"('/etc/passwd')",
688            "SELECT 1 FROM users WHERE id IN (SELECT setval('s', 1))",
689        ] {
690            for (name, result) in [
691                ("MySQL", validate_read_only(sql, &MYSQL)),
692                ("Postgres", validate_read_only(sql, &POSTGRES)),
693                ("SQLite", validate_read_only(sql, &SQLITE)),
694            ] {
695                // Some dialects can't parse every form; a parse error is also a rejection.
696                assert!(result.is_err(), "{name} should block {sql:?}, got {result:?}");
697                assert!(
698                    !matches!(result, Err(SqlError::ReadOnlyViolation)),
699                    "{name} should flag the function in {sql:?}"
700                );
701            }
702        }
703        assert!(matches!(
704            validate_read_only("SELECT pg_read_file('/etc/passwd')", &POSTGRES),
705            Err(SqlError::DangerousFunctionBlocked(f)) if f == "pg_read_file"
706        ));
707    }
708
709    #[test]
710    fn load_file_quoted_or_qualified_blocked() {
711        for sql in [
712            "SELECT `LOAD_FILE`('/etc/passwd')",
713            "SELECT mysql.LOAD_FILE('/etc/passwd')",
714        ] {
715            assert!(
716                matches!(validate_read_only(sql, &MYSQL), Err(SqlError::LoadFileBlocked)),
717                "expected LoadFileBlocked for {sql:?}"
718            );
719        }
720    }
721
722    #[test]
723    fn dangerous_function_names_in_strings_or_columns_allowed() {
724        for sql in [
725            "SELECT 'pg_read_file(/etc/passwd)'",
726            "SELECT setval_count FROM users",
727            "SELECT * FROM generate_series(1, 10)",
728            "SELECT nextval_hint, upper(name) FROM users",
729        ] {
730            assert!(
731                validate_read_only(sql, &POSTGRES).is_ok(),
732                "expected {sql:?} to be allowed"
733            );
734        }
735    }
736
737    // === EXPLAIN wraps (GHSA-mw95-r87r-7ggf) ===
738
739    #[test]
740    fn explain_of_write_blocked_all_dialects() {
741        for sql in [
742            "EXPLAIN DELETE FROM users",
743            "EXPLAIN ANALYZE DELETE FROM users",
744            "EXPLAIN ANALYZE INSERT INTO users VALUES (1)",
745            "EXPLAIN ANALYZE UPDATE users SET admin = true",
746            "EXPLAIN ANALYZE DROP TABLE users",
747        ] {
748            for result in [
749                validate_read_only(sql, &MYSQL),
750                validate_read_only(sql, &POSTGRES),
751                validate_read_only(sql, &SQLITE),
752            ] {
753                assert!(result.is_err(), "expected {sql:?} to be blocked, got {result:?}");
754            }
755        }
756    }
757
758    #[test]
759    fn postgres_explain_options_of_write_blocked() {
760        for sql in [
761            "EXPLAIN (ANALYZE) UPDATE users SET admin = true",
762            "EXPLAIN (ANALYZE TRUE, FORMAT JSON) DELETE FROM users",
763            "EXPLAIN ANALYZE WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x",
764            "EXPLAIN ANALYZE SELECT * INTO evil FROM users",
765        ] {
766            assert!(
767                matches!(validate_read_only(sql, &POSTGRES), Err(SqlError::ReadOnlyViolation)),
768                "expected ReadOnlyViolation for {sql:?}"
769            );
770        }
771    }
772
773    #[test]
774    fn explain_of_read_allowed() {
775        for sql in ["EXPLAIN SELECT 1", "EXPLAIN ANALYZE SELECT * FROM users"] {
776            assert_eq!(validate_read_only(sql, &MYSQL).unwrap(), StatementKind::NonSelect);
777            assert_eq!(validate_read_only(sql, &POSTGRES).unwrap(), StatementKind::NonSelect);
778        }
779        assert_eq!(
780            validate_read_only("EXPLAIN (ANALYZE, FORMAT JSON) SELECT 1", &POSTGRES).unwrap(),
781            StatementKind::NonSelect,
782        );
783        assert_eq!(
784            validate_read_only("EXPLAIN QUERY PLAN SELECT 1", &SQLITE).unwrap(),
785            StatementKind::NonSelect,
786        );
787    }
788
789    #[test]
790    fn explain_checks_dangerous_functions() {
791        assert!(matches!(
792            validate_read_only("EXPLAIN ANALYZE SELECT LOAD_FILE('/etc/passwd')", &MYSQL),
793            Err(SqlError::LoadFileBlocked)
794        ));
795    }
796
797    #[test]
798    fn postgres_data_modifying_query_blocked() {
799        for sql in [
800            "WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x",
801            "WITH x AS (UPDATE users SET a = 1 RETURNING *) SELECT * FROM x",
802            "WITH x AS (INSERT INTO users VALUES (1) RETURNING *) SELECT 1",
803            "SELECT * FROM (WITH x AS (DELETE FROM users RETURNING *) SELECT * FROM x) AS t",
804            "SELECT * INTO evil FROM users",
805        ] {
806            assert!(
807                matches!(validate_read_only(sql, &POSTGRES), Err(SqlError::ReadOnlyViolation)),
808                "expected ReadOnlyViolation for {sql:?}"
809            );
810        }
811    }
812
813    #[test]
814    fn null_byte_in_sql() {
815        let sql = "SELECT 1\x00; DROP TABLE x";
816        let result = validate_read_only(sql, &MYSQL);
817        assert!(result.is_err(), "SQL with null byte should be rejected: {result:?}");
818    }
819}
dbmcp_sql/validation.rs

dbmcp_sql/
validation.rs
