SQLite: Reject DML in nested queries
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs index 69bbe35..35abf32 100644 --- a/src/dialect/mod.rs +++ b/src/dialect/mod.rs
@@ -1499,6 +1499,11 @@ false } + /// Returns true if the dialect accepts `INSERT`, `UPDATE`, `DELETE` or `MERGE` in a subquery, CTE or `INSERT` source. + fn supports_nested_dml_query(&self) -> bool { + true + } + /// Returns true if this dialect supports `SET` statements without an explicit /// assignment operator such as `=`. For example: `SET SHOWPLAN_XML ON`. fn supports_set_stmt_without_operator(&self) -> bool {
diff --git a/src/dialect/sqlite.rs b/src/dialect/sqlite.rs index f63e4cb..c3e0a8d 100644 --- a/src/dialect/sqlite.rs +++ b/src/dialect/sqlite.rs
@@ -146,4 +146,9 @@ fn supports_parens_around_table_factor(&self) -> bool { true } + + /// See <https://sqlite.org/lang_select.html> + fn supports_nested_dml_query(&self) -> bool { + false + } }
diff --git a/src/parser/mod.rs b/src/parser/mod.rs index db54296..f128a32 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs
@@ -656,7 +656,7 @@ } Keyword::SELECT | Keyword::WITH | Keyword::VALUES | Keyword::FROM => { self.prev_token(); - self.parse_query().map(Into::into) + self.parse_query_allowing_dml(true).map(Into::into) } Keyword::TRUNCATE => self.parse_truncate().map(Into::into), Keyword::ATTACH => { @@ -14831,8 +14831,13 @@ /// preceded with some `WITH` CTE declarations and optionally followed /// by `ORDER BY`. Unlike some other parse_... methods, this one doesn't /// expect the initial keyword to be already consumed - #[cfg_attr(feature = "recursive-protection", recursive::recursive)] pub fn parse_query(&mut self) -> Result<Box<Query>, ParserError> { + self.parse_query_allowing_dml(self.dialect.supports_nested_dml_query()) + } + + /// Parses a query whose body may be `INSERT`, `UPDATE`, `DELETE` or `MERGE` when `allow_dml` is set. + #[cfg_attr(feature = "recursive-protection", recursive::recursive)] + fn parse_query_allowing_dml(&mut self, allow_dml: bool) -> Result<Box<Query>, ParserError> { let _guard = self.recursion_counter.try_decrease()?; let with = if self.parse_keyword(Keyword::WITH) { let with_token = self.get_current_token(); @@ -14844,7 +14849,7 @@ } else { None }; - if self.parse_keyword(Keyword::INSERT) { + if allow_dml && self.parse_keyword(Keyword::INSERT) { Ok(Query { with, body: self.parse_insert_setexpr_boxed(self.get_current_token().clone())?, @@ -14858,7 +14863,7 @@ pipe_operators: vec![], } .into()) - } else if self.parse_keyword(Keyword::UPDATE) { + } else if allow_dml && self.parse_keyword(Keyword::UPDATE) { Ok(Query { with, body: self.parse_update_setexpr_boxed(self.get_current_token().clone())?, @@ -14872,7 +14877,7 @@ pipe_operators: vec![], } .into()) - } else if self.parse_keyword(Keyword::DELETE) { + } else if allow_dml && self.parse_keyword(Keyword::DELETE) { Ok(Query { with, body: self.parse_delete_setexpr_boxed(self.get_current_token().clone())?, @@ -14886,7 +14891,7 @@ pipe_operators: vec![], } .into()) - } else if self.parse_keyword(Keyword::MERGE) { + } else if allow_dml && self.parse_keyword(Keyword::MERGE) { Ok(Query { with, body: self.parse_merge_setexpr_boxed(self.get_current_token().clone())?,
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index 4d51989..fab9209 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs
@@ -10720,7 +10720,7 @@ #[test] fn test_merge_in_cte() { - verified_only_select( + all_dialects_where(|d| d.supports_nested_dml_query()).verified_only_select( "WITH x AS (\ MERGE INTO t USING (VALUES (1)) ON 1 = 1 \ WHEN MATCHED THEN DELETE \
diff --git a/tests/sqlparser_sqlite.rs b/tests/sqlparser_sqlite.rs index 2e60c75..06dd71e 100644 --- a/tests/sqlparser_sqlite.rs +++ b/tests/sqlparser_sqlite.rs
@@ -1142,6 +1142,53 @@ ); } +#[test] +fn parse_nested_dml_rejected() { + let body_error = |found: &str| { + format!("Expected: SELECT, VALUES, or a subquery in the query body, found: {found}") + }; + for (sql, error) in [ + ("INSERT INTO u INSERT INTO u SELECT 1", body_error("INSERT")), + ( + "INSERT INTO u (a) INSERT INTO u SELECT 1", + body_error("INSERT"), + ), + ( + "INSERT INTO u WITH x AS (SELECT 1) INSERT INTO u SELECT 1", + body_error("INSERT"), + ), + ( + "SELECT * FROM (INSERT INTO u SELECT 1)", + "Expected: ), found: u".to_string(), + ), + ( + "WITH x AS (INSERT INTO u SELECT 1) SELECT 1", + body_error("INSERT"), + ), + ( + "WITH x AS (UPDATE u SET a = 1) SELECT 1", + body_error("UPDATE"), + ), + ("WITH x AS (DELETE FROM u) SELECT 1", body_error("DELETE")), + ] { + assert_eq!( + sqlite().parse_sql_statements(sql), + Err(ParserError::ParserError(error)), + "{sql}" + ); + } + + let nested_dml = all_dialects_where(|d| d.supports_nested_dml_query()); + nested_dml.verified_stmt("INSERT INTO u (a) INSERT INTO u SELECT 1"); + nested_dml.verified_stmt("SELECT * FROM (INSERT INTO u SELECT 1)"); + nested_dml.verified_stmt("WITH x AS (INSERT INTO u SELECT 1) SELECT 1"); + + sqlite().verified_stmt("WITH x AS (SELECT 1) INSERT INTO u SELECT * FROM x"); + sqlite().verified_stmt("WITH x AS (SELECT 1) UPDATE u SET a = 2"); + sqlite().verified_stmt("WITH x AS (SELECT 1) DELETE FROM u"); + sqlite().verified_stmt("INSERT INTO u (a) WITH x AS (SELECT 1) SELECT * FROM x"); +} + fn sqlite() -> TestedDialects { TestedDialects::new(vec![Box::new(SQLiteDialect {})]) }