Skip to content

Commit 29fc28a

Browse files
fix(mcp): detect data-modifying WITH statements in read-only guard
sqlparser 0.55 represents `WITH ... INSERT/UPDATE` as a Statement::Query whose body and CTEs carry the DML, so the top-level AST classifier let write statements pass sqlkit__execute_query's read-only gate and rejected them from sqlkit__execute_write. classify_query now walks the query body and every CTE (combining kinds by severity) so data-modifying statements are never classified as Read. The Postgres adapter also treated any WITH-prefixed statement as a row-returning query: a `WITH ... INSERT/UPDATE/DELETE` without RETURNING exposes zero columns, so the writes ran but the result was silently empty. Column-less prepared statements are now routed through execute() and report rows_affected. Ultraworked with [Sisyphus](https://github.com/code-yeongyu/oh-my-openagent) Co-authored-by: Sisyphus <[email protected]>
1 parent ae02a06 commit 29fc28a

2 files changed

Lines changed: 126 additions & 3 deletions

File tree

‎src-tauri/src/capabilities/sql_write.rs‎

Lines changed: 100 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
use std::sync::Arc;
88

99
use serde_json::{json, Value};
10-
use sqlparser::ast::Statement;
10+
use sqlparser::ast::{Query, SetExpr, Statement};
1111
use sqlparser::dialect::{GenericDialect, MsSqlDialect, MySqlDialect, PostgreSqlDialect};
1212
use sqlparser::parser::Parser;
1313

@@ -49,10 +49,57 @@ pub(crate) fn classify_sql(db_type: &str, sql: &str) -> Result<SqlKind, String>
4949
Ok(classify_statement(&stmt))
5050
}
5151

52+
/// Merge kinds found in different parts of one statement.
53+
///
54+
/// The most restrictive kind wins so a destructive statement never rides
55+
/// along inside a tree otherwise classified as Read.
56+
fn combine_kind(a: SqlKind, b: SqlKind) -> SqlKind {
57+
fn rank(kind: SqlKind) -> u8 {
58+
match kind {
59+
SqlKind::Read => 0,
60+
SqlKind::Write => 1,
61+
SqlKind::Ddl => 2,
62+
SqlKind::Delete => 3,
63+
SqlKind::Other => 4,
64+
}
65+
}
66+
if rank(b) > rank(a) {
67+
b
68+
} else {
69+
a
70+
}
71+
}
72+
73+
/// Classify a Query, walking into its body and every CTE.
74+
///
75+
/// sqlparser represents `WITH … INSERT/UPDATE` (PostgreSQL data-modifying
76+
/// CTEs) as a `Query` whose body carries the DML statement, so checking only
77+
/// the top-level variant would let write statements through as Read.
78+
fn classify_query(query: &Query) -> SqlKind {
79+
let mut kind = classify_set_expr(&query.body);
80+
if let Some(with) = &query.with {
81+
for cte in &with.cte_tables {
82+
kind = combine_kind(kind, classify_query(&cte.query));
83+
}
84+
}
85+
kind
86+
}
87+
88+
fn classify_set_expr(body: &SetExpr) -> SqlKind {
89+
match body {
90+
SetExpr::Select(_) | SetExpr::Values(_) | SetExpr::Table(_) => SqlKind::Read,
91+
SetExpr::Query(query) => classify_query(query),
92+
SetExpr::SetOperation { left, right, .. } => {
93+
combine_kind(classify_set_expr(left), classify_set_expr(right))
94+
}
95+
SetExpr::Insert(stmt) | SetExpr::Update(stmt) => classify_statement(stmt),
96+
}
97+
}
98+
5299
fn classify_statement(stmt: &Statement) -> SqlKind {
53100
match stmt {
54-
Statement::Query(_)
55-
| Statement::Explain { .. }
101+
Statement::Query(query) => classify_query(query),
102+
Statement::Explain { .. }
56103
| Statement::ExplainTable { .. }
57104
| Statement::ShowVariable { .. }
58105
| Statement::ShowVariables { .. }
@@ -408,6 +455,56 @@ mod tests {
408455
);
409456
}
410457

458+
#[test]
459+
fn classifies_with_dml_chain_as_write() {
460+
assert_eq!(
461+
classify_sql(
462+
"postgres",
463+
"WITH u AS (SELECT id FROM users WHERE email = '[email protected]'), \
464+
o AS (INSERT INTO organizations (name, slug, created_by_email) \
465+
VALUES ('wentsen', 'release-org', '[email protected]') RETURNING id) \
466+
INSERT INTO memberships (user_id, org_id, role, scope, permissions) \
467+
SELECT u.id, o.id, 'owner', 'org', '{}'::jsonb FROM u, o"
468+
)
469+
.unwrap(),
470+
SqlKind::Write
471+
);
472+
}
473+
474+
#[test]
475+
fn classifies_with_dml_in_cte_as_write() {
476+
assert_eq!(
477+
classify_sql(
478+
"postgres",
479+
"WITH i AS (INSERT INTO t (c) VALUES (1) RETURNING id) SELECT * FROM i"
480+
)
481+
.unwrap(),
482+
SqlKind::Write
483+
);
484+
}
485+
486+
#[test]
487+
fn classifies_with_update_as_write() {
488+
assert_eq!(
489+
classify_sql(
490+
"postgres",
491+
"WITH x AS (SELECT 1 AS v) UPDATE t SET c = (SELECT v FROM x)"
492+
)
493+
.unwrap(),
494+
SqlKind::Write
495+
);
496+
}
497+
498+
#[test]
499+
fn read_guard_rejects_with_insert() {
500+
let err = ensure_read_only(
501+
"postgres",
502+
"WITH x AS (SELECT 1) INSERT INTO t (c) SELECT 1",
503+
)
504+
.unwrap_err();
505+
assert!(err.contains("execute_write"), "got: {}", err);
506+
}
507+
411508
#[test]
412509
fn classifies_ddl_as_ddl() {
413510
assert_eq!(

‎src-tauri/src/database/postgres.rs‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1077,6 +1077,32 @@ impl DatabaseAdapter for PostgresAdapter {
10771077
.map_err(postgres_error_to_db_error)?
10781078
};
10791079

1080+
// A WITH-prefixed statement may be data-modifying — PostgreSQL runs
1081+
// `WITH … INSERT/UPDATE/DELETE` (no RETURNING) as a query that
1082+
// exposes zero columns, so a plain query() would silently swallow
1083+
// the writes. Route column-less statements through execute() so
1084+
// callers receive a rows_affected count instead of an empty result.
1085+
if statement.columns().is_empty() {
1086+
let affected = if let Some(timeout_duration) = timeout {
1087+
tokio::time::timeout(timeout_duration, client.execute(&statement, &[]))
1088+
.await
1089+
.map_err(|_| {
1090+
DbError::Timeout(format!(
1091+
"Query timed out after {:?}",
1092+
timeout_duration
1093+
))
1094+
})?
1095+
.map_err(postgres_error_to_db_error)?
1096+
} else {
1097+
client
1098+
.execute(&statement, &[])
1099+
.await
1100+
.map_err(postgres_error_to_db_error)?
1101+
};
1102+
return Ok(QueryResult::affected(affected)
1103+
.with_execution_time(start.elapsed().as_millis() as u64));
1104+
}
1105+
10801106
let result = if let Some(timeout_duration) = timeout {
10811107
tokio::time::timeout(timeout_duration, client.query(&statement, &[]))
10821108
.await

0 commit comments

Comments
 (0)