yggdrasil/api/database/
read_only.rs

1//! SQL 只读判定（数据导出与 SQL 控制台共用）。
2//!
3//! 只有不修改数据、不建表、不加行锁的 `SELECT` 类查询，以及不带 `ANALYZE`
4//! 的 `EXPLAIN <只读查询>` 才算只读。`EXPLAIN ANALYZE` 会真正执行内层语句，
5//! `WITH d AS (DELETE ... RETURNING *) SELECT ...` 会在顶层 WITH 中写数据，
6//! 二者都不能按语句外形放行。
7//!
8//! 遍历范围是查询树（WITH 的各 CTE、集合运算两侧、FROM 中的派生表与嵌套 JOIN）。
9//! 表达式内的子查询不展开：PostgreSQL 语法不允许在那里出现数据修改语句或
10//! `SELECT INTO`，sqlparser 即使能解析，数据库也会拒绝执行。
11
12use sqlparser::ast::{Query, SetExpr, Statement, TableFactor, TableWithJoins, UtilityOption};
13
14/// 语句是否只读：顶层 `Query` 或非 ANALYZE 的 `EXPLAIN <Query>`，且查询树无副作用。
15pub(crate) fn is_read_only_statement(stmt: &Statement) -> bool {
16    match stmt {
17        Statement::Query(query) => QueryEffects::of(query).is_empty(),
18        Statement::Explain {
19            analyze,
20            statement,
21            options,
22            ..
23        } => {
24            !*analyze
25                && !options_enable_analyze(options.as_deref())
26                && matches!(statement.as_ref(), Statement::Query(query) if QueryEffects::of(query).is_empty())
27        }
28        _ => false,
29    }
30}
31
32/// 返回语句本身及其内部会被执行的数据修改语句。
33///
34/// 内部语句包括 `EXPLAIN` 的内层语句，以及查询树里的 INSERT/UPDATE/DELETE/MERGE
35/// （如 `WITH d AS (DELETE FROM t RETURNING *) SELECT ...`、`WITH x AS (...) DELETE ...`）。
36/// SQL 控制台护栏要逐条检查这些语句，否则包一层 EXPLAIN ANALYZE 或 CTE
37/// 就能绕过"UPDATE/DELETE 必须带 WHERE"。
38pub(crate) fn executed_statements(stmt: &Statement) -> Vec<&Statement> {
39    let mut out = Vec::new();
40    push_executed(stmt, &mut out);
41    out
42}
43
44fn push_executed<'a>(stmt: &'a Statement, out: &mut Vec<&'a Statement>) {
45    out.push(stmt);
46    match stmt {
47        Statement::Explain { statement, .. } => push_executed(statement, out),
48        Statement::Query(query) => {
49            for dml in QueryEffects::of(query).dml {
50                push_executed(dml, out);
51            }
52        }
53        _ => {}
54    }
55}
56
57/// `EXPLAIN (ANALYZE ...)` 选项形式是否开启了 ANALYZE。
58///
59/// 只有参数明确为 PostgreSQL 布尔假值（`false`/`off`/`no`/`0` 及其可接受的前缀）
60/// 才视为关闭；无法识别的参数一律按开启处理。
61fn options_enable_analyze(options: Option<&[UtilityOption]>) -> bool {
62    options.unwrap_or_default().iter().any(|opt| {
63        opt.name.value.eq_ignore_ascii_case("analyze")
64            && match &opt.arg {
65                None => true,
66                Some(arg) => !is_pg_false(&arg.to_string()),
67            }
68    })
69}
70
71/// PostgreSQL `parse_bool` 认可的假值：`false`/`no` 的任意非空前缀、`of`/`off`、`0`。
72fn is_pg_false(raw: &str) -> bool {
73    let value = raw.trim().trim_matches('\'').to_ascii_lowercase();
74    !value.is_empty()
75        && ("false".starts_with(&value)
76            || "no".starts_with(&value)
77            || value == "of"
78            || value == "off"
79            || value == "0")
80}
81
82/// 查询树中会产生副作用的节点。
83#[derive(Default)]
84struct QueryEffects<'a> {
85    /// 查询树内的 INSERT/UPDATE/DELETE/MERGE。
86    dml: Vec<&'a Statement>,
87    /// `SELECT ... INTO`（PostgreSQL 中会建表）。
88    select_into: bool,
89    /// `FOR UPDATE` / `FOR SHARE` 等行锁子句。
90    locks: bool,
91}
92
93impl<'a> QueryEffects<'a> {
94    fn of(query: &'a Query) -> Self {
95        let mut effects = Self::default();
96        effects.visit_query(query);
97        effects
98    }
99
100    fn is_empty(&self) -> bool {
101        self.dml.is_empty() && !self.select_into && !self.locks
102    }
103
104    fn visit_query(&mut self, query: &'a Query) {
105        if let Some(with) = &query.with {
106            for cte in &with.cte_tables {
107                self.visit_query(&cte.query);
108            }
109        }
110        if !query.locks.is_empty() {
111            self.locks = true;
112        }
113        self.visit_set_expr(&query.body);
114    }
115
116    fn visit_set_expr(&mut self, body: &'a SetExpr) {
117        match body {
118            SetExpr::Select(select) => {
119                if select.into.is_some() {
120                    self.select_into = true;
121                }
122                for table in &select.from {
123                    self.visit_table_with_joins(table);
124                }
125            }
126            SetExpr::Query(query) => self.visit_query(query),
127            SetExpr::SetOperation { left, right, .. } => {
128                self.visit_set_expr(left);
129                self.visit_set_expr(right);
130            }
131            SetExpr::Insert(stmt)
132            | SetExpr::Update(stmt)
133            | SetExpr::Delete(stmt)
134            | SetExpr::Merge(stmt) => self.dml.push(stmt),
135            SetExpr::Values(_) | SetExpr::Table(_) => {}
136        }
137    }
138
139    fn visit_table_with_joins(&mut self, table: &'a TableWithJoins) {
140        self.visit_table_factor(&table.relation);
141        for join in &table.joins {
142            self.visit_table_factor(&join.relation);
143        }
144    }
145
146    fn visit_table_factor(&mut self, factor: &'a TableFactor) {
147        match factor {
148            TableFactor::Derived { subquery, .. } => self.visit_query(subquery),
149            TableFactor::NestedJoin {
150                table_with_joins, ..
151            } => self.visit_table_with_joins(table_with_joins),
152            _ => {}
153        }
154    }
155}
156
157#[cfg(test)]
158mod tests {
159    use super::*;
160
161    fn parse_one(sql: &str) -> Statement {
162        let mut stmts =
163            sqlparser::parser::Parser::parse_sql(&sqlparser::dialect::PostgreSqlDialect {}, sql)
164                .unwrap_or_else(|e| panic!("{sql}: {e}"));
165        assert_eq!(stmts.len(), 1, "{sql}");
166        stmts.remove(0)
167    }
168
169    #[test]
170    fn read_only_statement_accepts_plain_queries_and_plain_explain() {
171        for sql in [
172            "SELECT 1",
173            "SELECT id FROM posts WHERE id > 0 ORDER BY id LIMIT 5",
174            "WITH t AS (SELECT 1) SELECT * FROM t",
175            "SELECT * FROM (SELECT id FROM posts) AS s JOIN tags ON true",
176            "SELECT 1 UNION SELECT 2",
177            "VALUES (1), (2)",
178            "EXPLAIN SELECT * FROM posts",
179            "EXPLAIN VERBOSE SELECT 1",
180            "EXPLAIN (FORMAT JSON) SELECT * FROM posts",
181            "EXPLAIN (ANALYZE false) SELECT 1",
182            "EXPLAIN (ANALYZE off, COSTS) SELECT 1",
183            "EXPLAIN (ANALYZE 0) SELECT 1",
184        ] {
185            assert!(is_read_only_statement(&parse_one(sql)), "应判只读：{sql}");
186        }
187    }
188
189    #[test]
190    fn read_only_statement_rejects_explain_analyze_in_any_form() {
191        for sql in [
192            "EXPLAIN ANALYZE DELETE FROM posts",
193            "EXPLAIN ANALYZE SELECT 1",
194            "EXPLAIN (ANALYZE) DELETE FROM posts WHERE id = 1",
195            "EXPLAIN (ANALYZE true) SELECT 1",
196            "EXPLAIN (FORMAT JSON, ANALYZE on) SELECT 1",
197            "EXPLAIN (analyze 1) SELECT 1",
198        ] {
199            assert!(!is_read_only_statement(&parse_one(sql)), "应拒绝：{sql}");
200        }
201    }
202
203    #[test]
204    fn read_only_statement_rejects_explain_of_non_query() {
205        // 内层必须同样满足只读查询规则，即便不带 ANALYZE。
206        for sql in [
207            "EXPLAIN DELETE FROM posts WHERE id = 1",
208            "EXPLAIN INSERT INTO posts (id) VALUES (1)",
209            "EXPLAIN WITH d AS (DELETE FROM posts RETURNING *) SELECT * FROM d",
210        ] {
211            assert!(!is_read_only_statement(&parse_one(sql)), "应拒绝：{sql}");
212        }
213    }
214
215    #[test]
216    fn read_only_statement_rejects_side_effects_inside_queries() {
217        for sql in [
218            "WITH d AS (DELETE FROM posts RETURNING *) SELECT * FROM d",
219            "WITH u AS (UPDATE posts SET title = 'x' WHERE id = 1 RETURNING id) SELECT 1",
220            "WITH i AS (INSERT INTO tags (name) VALUES ('x') RETURNING id) SELECT * FROM i",
221            "WITH x AS (SELECT 1) DELETE FROM posts WHERE id = 1",
222            "SELECT * INTO t FROM posts",
223            "SELECT * FROM posts FOR UPDATE",
224            "SELECT * FROM posts FOR SHARE SKIP LOCKED",
225            "SELECT * FROM (SELECT * FROM posts FOR UPDATE) AS s",
226        ] {
227            assert!(!is_read_only_statement(&parse_one(sql)), "应拒绝：{sql}");
228        }
229    }
230
231    #[test]
232    fn read_only_statement_rejects_non_query_statements() {
233        for sql in [
234            "DELETE FROM posts WHERE id = 1",
235            "INSERT INTO posts (id) VALUES (1)",
236            "TRUNCATE posts",
237            "SHOW search_path",
238        ] {
239            assert!(!is_read_only_statement(&parse_one(sql)), "应拒绝：{sql}");
240        }
241    }
242
243    #[test]
244    fn executed_statements_exposes_dml_behind_explain_and_cte() {
245        let explain = parse_one("EXPLAIN ANALYZE DELETE FROM posts");
246        let executed = executed_statements(&explain);
247        assert!(executed.iter().any(|s| matches!(s, Statement::Delete(_))));
248
249        let cte = parse_one("WITH d AS (UPDATE posts SET title = 'x' RETURNING *) SELECT * FROM d");
250        let executed = executed_statements(&cte);
251        assert!(executed.iter().any(|s| matches!(s, Statement::Update(_))));
252
253        let select = parse_one("SELECT 1");
254        assert_eq!(executed_statements(&select).len(), 1);
255    }
256}
yggdrasil/api/database/read_only.rs

yggdrasil/api/database/
read_only.rs
