sz_orm_query_builder/
lib.rs

1//! # SZ-ORM QueryBuilder — 独立 SQL 构造器（sea-query 风格）
2//!
3//! 一个不绑定 Model 的纯 SQL 构造器，可独立编译、独立发布到 crates.io。
4//!
5//! 设计灵感来自 [sea-query](https://crates.io/crates/sea-query)：
6//! - 与 ORM 解耦：不依赖 `Model` trait，纯 SQL 构造
7//! - 多方言支持：通过 [`DbType`] 适配 MySQL/PostgreSQL/SQLite/Oracle
8//! - 链式 API：所有方法返回 `Self`
9//! - 零运行时开销：构造过程零数据库连接
10//!
11//! # 快速入门
12//!
13//! ```rust
14//! use sz_orm_core::DbType;
15//! use sz_orm_query_builder::{Query, SelectQuery};
16//!
17//! // SELECT
18//! let sql = Query::select()
19//!     .column("id")
20//!     .column("name")
21//!     .from("users")
22//!     .where_clause("age > 18")
23//!     .order_by("id", true)
24//!     .limit(10)
25//!     .build(DbType::MySQL);
26//! assert!(sql.contains("SELECT"));
27//! assert!(sql.contains("FROM `users`"));
28//!
29//! // INSERT
30//! let sql = Query::insert()
31//!     .into_table("users")
32//!     .value("name", "'Alice'")
33//!     .value("age", "30")
34//!     .build();
35//! assert!(sql.contains("INSERT INTO `users`"));
36//!
37//! // UPDATE
38//! let sql = Query::update()
39//!     .table("users")
40//!     .set("name", "'Bob'")
41//!     .where_clause("id = 1")
42//!     .build();
43//! assert!(sql.contains("UPDATE `users`"));
44//!
45//! // DELETE
46//! let sql = Query::delete()
47//!     .from_table("users")
48//!     .where_clause("id = 1")
49//!     .build();
50//! assert!(sql.contains("DELETE FROM"));
51//! ```
52//!
53//! # 与 sz-orm-core::QueryBuilder 的区别
54//!
55//! | 特性 | `sz-orm-core::QueryBuilder<M>` | sz-orm-query-builder::Query |
56//! |------|------------------------------|----------------------------|
57//! | 绑定 Model | 是（`<M: Model>`） | 否 |
58//! | 类型安全 | 编译期表/列校验 | 运行时字符串 |
59//! | 适用场景 | ORM 完整流程 | 纯 SQL 构造、动态查询 |
60//! | 依赖 | sz-orm-core 全部 | 仅 dialect 模块 |
61//! | 独立发布 | 否 | 是 |
62
63use sz_orm_core::DbType;
64
65/// 用反引号包裹标识符并转义内部反引号（MySQL 标准：` → ``）
66///
67/// # 安全性（门禁 9 修复）
68///
69/// 不转义的反引号包裹允许恶意标识符通过 ` 逃逸注入。本函数将标识符内
70/// 的反引号加倍（MySQL 标准转义），确保拼接后的 SQL 不会被恶意标识符突破。
71///
72/// 支持带点号的限定标识符: `u.id` → `u`.`id`
73fn quote_ident(s: &str) -> String {
74    s.split('.')
75        .map(|part| format!("`{}`", part.replace('`', "``")))
76        .collect::<Vec<_>>()
77        .join(".")
78}
79
80/// 校验 WHERE 条件字符串，拒绝明显的 SQL 注入模式
81///
82/// v0.2.2 修复 C-6：公开 `where_clause(condition: &str)` 接受任意字符串，存在 SQL 注入风险。
83/// 本函数检测高危模式（分号+SQL 关键字、行注释、块注释），拒绝明显恶意输入。
84///
85/// # 检测模式
86///
87/// - `;` 后跟 SQL 关键字（DROP/DELETE/UPDATE/INSERT/ALTER/TRUNCATE/EXEC/CREATE/GRANT/REVOKE）
88/// - `--` 行注释序列
89/// - `/*` 块注释起始
90/// - `*/` 块注释结束
91///
92/// # 注意
93///
94/// 此校验是基础防线，不能替代参数化查询。复杂 WHERE 条件应使用参数化 API。
95fn check_where_injection(condition: &str) {
96    let upper = condition.to_uppercase();
97    const SQL_KEYWORDS: &[&str] = &[
98        "DROP", "DELETE", "UPDATE", "INSERT", "ALTER", "TRUNCATE", "EXEC", "CREATE", "GRANT",
99        "REVOKE",
100    ];
101    for kw in SQL_KEYWORDS {
102        let pattern1 = format!(";{}", kw);
103        let pattern2 = format!("; {}", kw);
104        if upper.contains(&pattern1) || upper.contains(&pattern2) {
105            panic!(
106                "SQL injection detected in where_clause: semicolon followed by {} keyword: {:?}",
107                kw, condition
108            );
109        }
110    }
111    if condition.contains("--") {
112        panic!(
113            "SQL injection detected in where_clause: line comment '--' not allowed: {:?}",
114            condition
115        );
116    }
117    if condition.contains("/*") || condition.contains("*/") {
118        panic!(
119            "SQL injection detected in where_clause: block comment '/*' or '*/' not allowed: {:?}",
120            condition
121        );
122    }
123}
124
125/// 查询构造器入口
126pub struct Query;
127
128impl Query {
129    /// 创建 SELECT 查询
130    pub fn select() -> SelectQuery {
131        SelectQuery::new()
132    }
133
134    /// 创建 INSERT 查询
135    pub fn insert() -> InsertQuery {
136        InsertQuery::new()
137    }
138
139    /// 创建 UPDATE 查询
140    pub fn update() -> UpdateQuery {
141        UpdateQuery::new()
142    }
143
144    /// 创建 DELETE 查询
145    pub fn delete() -> DeleteQuery {
146        DeleteQuery::new()
147    }
148}
149
150/// SELECT 查询构造器
151#[derive(Debug, Clone, Default)]
152pub struct SelectQuery {
153    columns: Vec<String>,
154    from_table: Option<String>,
155    joins: Vec<String>,
156    wheres: Vec<String>,
157    order_by: Vec<String>,
158    group_by: Vec<String>,
159    having: Vec<String>,
160    limit: Option<u64>,
161    offset: Option<u64>,
162    distinct: bool,
163}
164
165impl SelectQuery {
166    /// 创建空的 SELECT 查询
167    pub fn new() -> Self {
168        Self::default()
169    }
170
171    /// 设置 DISTINCT
172    pub fn distinct(mut self) -> Self {
173        self.distinct = true;
174        self
175    }
176
177    /// 添加列
178    pub fn column(mut self, name: &str) -> Self {
179        self.columns.push(name.to_string());
180        self
181    }
182
183    /// 添加多个列
184    pub fn columns(mut self, names: &[&str]) -> Self {
185        for n in names {
186            self.columns.push(n.to_string());
187        }
188        self
189    }
190
191    /// 添加 `*` 列
192    pub fn all_columns(self) -> Self {
193        self.column("*")
194    }
195
196    /// 设置 FROM 表
197    pub fn from(mut self, table: &str) -> Self {
198        self.from_table = Some(table.to_string());
199        self
200    }
201
202    /// 添加 INNER JOIN
203    ///
204    /// # 安全性（门禁 9 修复）
205    ///
206    /// 表名经 `quote_ident()` 转义。`on` 条件为表达式，调用方应确保不使用恶意输入构造。
207    pub fn inner_join(mut self, table: &str, on: &str) -> Self {
208        self.joins.push(format!(
209            "INNER JOIN {} ON {}",
210            Self::quote_join_table(table),
211            on
212        ));
213        self
214    }
215
216    /// 添加 LEFT JOIN
217    ///
218    /// # 安全性（门禁 9 修复）
219    ///
220    /// 同 `inner_join`，表名经 `quote_ident()` 转义。
221    pub fn left_join(mut self, table: &str, on: &str) -> Self {
222        self.joins.push(format!(
223            "LEFT JOIN {} ON {}",
224            Self::quote_join_table(table),
225            on
226        ));
227        self
228    }
229
230    /// 添加 RIGHT JOIN
231    ///
232    /// # 安全性（门禁 9 修复）
233    ///
234    /// 同 `inner_join`，表名经 `quote_ident()` 转义。
235    pub fn right_join(mut self, table: &str, on: &str) -> Self {
236        self.joins.push(format!(
237            "RIGHT JOIN {} ON {}",
238            Self::quote_join_table(table),
239            on
240        ));
241        self
242    }
243
244    /// 对 JOIN 表名部分进行转义（支持别名：`orders o` → `` `orders` o ``）
245    fn quote_join_table(table: &str) -> String {
246        if let Some((tbl, alias)) = table.rsplit_once(' ') {
247            if alias.to_uppercase() == "AS" {
248                // `orders AS o`
249                format!("{} AS {}", quote_ident(tbl), alias)
250            } else {
251                // `orders o`
252                format!("{} {}", quote_ident(tbl), alias)
253            }
254        } else {
255            quote_ident(table)
256        }
257    }
258
259    /// 添加 WHERE 条件（AND 连接）
260    ///
261    /// # 安全性（v0.2.2 修复 C-6）
262    ///
263    /// 调用 `check_where_injection` 检测高危模式（分号+SQL 关键字、行注释、块注释）。
264    /// 复杂 WHERE 条件应使用参数化查询 API，避免直接拼接字符串。
265    pub fn where_clause(mut self, condition: &str) -> Self {
266        check_where_injection(condition);
267        self.wheres.push(condition.to_string());
268        self
269    }
270
271    /// 添加 OR WHERE 条件
272    ///
273    /// # 安全性（v0.2.2 修复 C-6）
274    ///
275    /// 同 `where_clause`，调用 `check_where_injection` 检测高危模式。
276    pub fn or_where(mut self, condition: &str) -> Self {
277        check_where_injection(condition);
278        self.wheres.push(format!("OR {}", condition));
279        self
280    }
281
282    /// 添加 GROUP BY
283    pub fn group_by(mut self, column: &str) -> Self {
284        self.group_by.push(column.to_string());
285        self
286    }
287
288    /// 添加 HAVING
289    pub fn having(mut self, condition: &str) -> Self {
290        self.having.push(condition.to_string());
291        self
292    }
293
294    /// 添加 ORDER BY
295    ///
296    /// # 参数
297    ///
298    /// - `column`: 列名
299    /// - `asc`: true=ASC, false=DESC
300    pub fn order_by(mut self, column: &str, asc: bool) -> Self {
301        let dir = if asc { "ASC" } else { "DESC" };
302        self.order_by.push(format!("{} {}", column, dir));
303        self
304    }
305
306    /// 设置 LIMIT
307    pub fn limit(mut self, n: u64) -> Self {
308        self.limit = Some(n);
309        self
310    }
311
312    /// 设置 OFFSET
313    pub fn offset(mut self, n: u64) -> Self {
314        self.offset = Some(n);
315        self
316    }
317
318    /// 生成分页（同时设置 LIMIT 和 OFFSET）
319    ///
320    /// # 参数
321    ///
322    /// - `page`: 页码（从 1 开始）
323    /// - `size`: 每页大小
324    pub fn paginate(self, page: u64, size: u64) -> Self {
325        let offset = (page.saturating_sub(1)) * size;
326        self.limit(size).offset(offset)
327    }
328
329    /// 生成 SQL
330    ///
331    /// # 参数
332    ///
333    /// - `db_type`: 数据库类型，用于选择方言
334    pub fn build(self, db_type: DbType) -> String {
335        let dialect = match sz_orm_core::get_dialect(db_type) {
336            Ok(d) => d,
337            Err(_) => return String::new(),
338        };
339
340        let mut sql = String::new();
341        sql.push_str("SELECT ");
342
343        if self.distinct {
344            sql.push_str("DISTINCT ");
345        }
346
347        if self.columns.is_empty() {
348            sql.push('*');
349        } else {
350            let cols: Vec<String> = self
351                .columns
352                .iter()
353                .map(|c| {
354                    if c == "*" {
355                        c.clone()
356                    } else {
357                        dialect.quote(c)
358                    }
359                })
360                .collect();
361            sql.push_str(&cols.join(", "));
362        }
363
364        if let Some(table) = self.from_table {
365            sql.push_str(" FROM ");
366            sql.push_str(&dialect.quote(&table));
367        }
368
369        for join in &self.joins {
370            sql.push(' ');
371            sql.push_str(join);
372        }
373
374        if !self.wheres.is_empty() {
375            sql.push_str(" WHERE ");
376            // 第一个条件不加 AND/OR 前缀
377            sql.push_str(&self.wheres[0]);
378            for w in &self.wheres[1..] {
379                if w.starts_with("OR ") {
380                    sql.push(' ');
381                    sql.push_str(w);
382                } else {
383                    sql.push_str(" AND ");
384                    sql.push_str(w);
385                }
386            }
387        }
388
389        if !self.group_by.is_empty() {
390            sql.push_str(" GROUP BY ");
391            sql.push_str(
392                &self
393                    .group_by
394                    .iter()
395                    .map(|c| quote_ident(c))
396                    .collect::<Vec<_>>()
397                    .join(", "),
398            );
399        }
400
401        if !self.having.is_empty() {
402            sql.push_str(" HAVING ");
403            sql.push_str(&self.having.join(" AND "));
404        }
405
406        if !self.order_by.is_empty() {
407            sql.push_str(" ORDER BY ");
408            sql.push_str(
409                &self
410                    .order_by
411                    .iter()
412                    .map(|s| {
413                        // 格式为 "column ASC" 或 "column DESC"
414                        if let Some((col, dir)) = s.rsplit_once(' ') {
415                            format!("{} {}", quote_ident(col), dir)
416                        } else {
417                            quote_ident(s)
418                        }
419                    })
420                    .collect::<Vec<_>>()
421                    .join(", "),
422            );
423        }
424
425        if let Some(limit) = self.limit {
426            sql.push_str(&format!(" LIMIT {}", limit));
427        }
428        if let Some(offset) = self.offset {
429            sql.push_str(&format!(" OFFSET {}", offset));
430        }
431
432        sql
433    }
434}
435
436/// INSERT 查询构造器
437#[derive(Debug, Clone, Default)]
438pub struct InsertQuery {
439    table: Option<String>,
440    columns: Vec<String>,
441    values: Vec<String>,
442}
443
444impl InsertQuery {
445    /// 创建空的 INSERT 查询
446    pub fn new() -> Self {
447        Self::default()
448    }
449
450    /// 设置目标表
451    pub fn into_table(mut self, table: &str) -> Self {
452        self.table = Some(table.to_string());
453        self
454    }
455
456    /// 添加列值对（值应为已转义的 SQL 字面量）
457    pub fn value(mut self, column: &str, value: &str) -> Self {
458        self.columns.push(column.to_string());
459        self.values.push(value.to_string());
460        self
461    }
462
463    /// 批量添加列值对
464    pub fn values(mut self, pairs: &[(&str, &str)]) -> Self {
465        for (c, v) in pairs {
466            self.columns.push(c.to_string());
467            self.values.push(v.to_string());
468        }
469        self
470    }
471
472    /// 构建 INSERT SQL（无方言，硬编码反引号）
473    ///
474    /// # 安全性（门禁 9 修复）
475    ///
476    /// 标识符经 `quote_ident()` 转义后包裹反引号，防止含 `` ` `` 的恶意标识符逃逸。
477    pub fn build(self) -> String {
478        let table = self.table.unwrap_or_default();
479        if table.is_empty() || self.columns.is_empty() {
480            return String::new();
481        }
482
483        let cols: Vec<String> = self.columns.iter().map(|c| quote_ident(c)).collect();
484        let vals: Vec<String> = self.values.iter().map(|v| v.to_string()).collect();
485
486        format!(
487            "INSERT INTO {} ({}) VALUES ({})",
488            quote_ident(&table),
489            cols.join(", "),
490            vals.join(", ")
491        )
492    }
493
494    /// 按指定方言生成 SQL
495    pub fn build_with_dialect(self, db_type: DbType) -> String {
496        let dialect = match sz_orm_core::get_dialect(db_type) {
497            Ok(d) => d,
498            Err(_) => return String::new(),
499        };
500
501        let table = self.table.unwrap_or_default();
502        if table.is_empty() || self.columns.is_empty() {
503            return String::new();
504        }
505
506        let cols: Vec<String> = self.columns.iter().map(|c| dialect.quote(c)).collect();
507
508        format!(
509            "INSERT INTO {} ({}) VALUES ({})",
510            dialect.quote(&table),
511            cols.join(", "),
512            self.values.join(", ")
513        )
514    }
515}
516
517/// UPDATE 查询构造器
518#[derive(Debug, Clone, Default)]
519pub struct UpdateQuery {
520    table: Option<String>,
521    sets: Vec<(String, String)>,
522    wheres: Vec<String>,
523}
524
525impl UpdateQuery {
526    /// 创建空的 UPDATE 查询
527    pub fn new() -> Self {
528        Self::default()
529    }
530
531    /// 设置目标表
532    pub fn table(mut self, table: &str) -> Self {
533        self.table = Some(table.to_string());
534        self
535    }
536
537    /// 添加 SET 赋值（值应为已转义的 SQL 字面量）
538    pub fn set(mut self, column: &str, value: &str) -> Self {
539        self.sets.push((column.to_string(), value.to_string()));
540        self
541    }
542
543    /// 批量添加 SET 赋值
544    pub fn sets(mut self, pairs: &[(&str, &str)]) -> Self {
545        for (c, v) in pairs {
546            self.sets.push((c.to_string(), v.to_string()));
547        }
548        self
549    }
550
551    /// 添加 WHERE 条件
552    ///
553    /// # 安全性（v0.2.2 修复 C-6）
554    ///
555    /// 调用 `check_where_injection` 检测高危模式。
556    pub fn where_clause(mut self, condition: &str) -> Self {
557        check_where_injection(condition);
558        self.wheres.push(condition.to_string());
559        self
560    }
561
562    /// 生成 SQL
563    ///
564    /// # 安全性（门禁 9 修复）
565    ///
566    /// 表名和列名经 `quote_ident()` 转义后包裹反引号，防止含 `` ` `` 的恶意标识符逃逸。
567    pub fn build(self) -> String {
568        let table = self.table.unwrap_or_default();
569        if table.is_empty() || self.sets.is_empty() {
570            return String::new();
571        }
572
573        let set_str: Vec<String> = self
574            .sets
575            .iter()
576            .map(|(c, v)| format!("{} = {}", quote_ident(c), v))
577            .collect();
578
579        let mut sql = format!("UPDATE {} SET {}", quote_ident(&table), set_str.join(", "));
580
581        if !self.wheres.is_empty() {
582            sql.push_str(" WHERE ");
583            sql.push_str(&self.wheres.join(" AND "));
584        }
585
586        sql
587    }
588
589    /// 按指定方言生成 SQL
590    pub fn build_with_dialect(self, db_type: DbType) -> String {
591        let dialect = match sz_orm_core::get_dialect(db_type) {
592            Ok(d) => d,
593            Err(_) => return String::new(),
594        };
595
596        let table = self.table.unwrap_or_default();
597        if table.is_empty() || self.sets.is_empty() {
598            return String::new();
599        }
600
601        let set_str: Vec<String> = self
602            .sets
603            .iter()
604            .map(|(c, v)| format!("{} = {}", dialect.quote(c), v))
605            .collect();
606
607        let mut sql = format!(
608            "UPDATE {} SET {}",
609            dialect.quote(&table),
610            set_str.join(", ")
611        );
612
613        if !self.wheres.is_empty() {
614            sql.push_str(" WHERE ");
615            sql.push_str(&self.wheres.join(" AND "));
616        }
617
618        sql
619    }
620}
621
622/// DELETE 查询构造器
623#[derive(Debug, Clone, Default)]
624pub struct DeleteQuery {
625    table: Option<String>,
626    wheres: Vec<String>,
627}
628
629impl DeleteQuery {
630    /// 创建空的 DELETE 查询
631    pub fn new() -> Self {
632        Self::default()
633    }
634
635    /// 设置目标表
636    pub fn from_table(mut self, table: &str) -> Self {
637        self.table = Some(table.to_string());
638        self
639    }
640
641    /// 添加 WHERE 条件
642    ///
643    /// # 安全性（v0.2.2 修复 C-6）
644    ///
645    /// 调用 `check_where_injection` 检测高危模式。
646    pub fn where_clause(mut self, condition: &str) -> Self {
647        check_where_injection(condition);
648        self.wheres.push(condition.to_string());
649        self
650    }
651
652    /// 生成 SQL
653    ///
654    /// # 安全性（门禁 9 修复）
655    ///
656    /// 表名经 `quote_ident()` 转义后包裹反引号，防止含 `` ` `` 的恶意表名逃逸。
657    pub fn build(self) -> String {
658        let table = self.table.unwrap_or_default();
659        if table.is_empty() {
660            return String::new();
661        }
662
663        let mut sql = format!("DELETE FROM {}", quote_ident(&table));
664
665        if !self.wheres.is_empty() {
666            sql.push_str(" WHERE ");
667            sql.push_str(&self.wheres.join(" AND "));
668        }
669
670        sql
671    }
672
673    /// 按指定方言生成 SQL
674    pub fn build_with_dialect(self, db_type: DbType) -> String {
675        let dialect = match sz_orm_core::get_dialect(db_type) {
676            Ok(d) => d,
677            Err(_) => return String::new(),
678        };
679
680        let table = self.table.unwrap_or_default();
681        if table.is_empty() {
682            return String::new();
683        }
684
685        let mut sql = format!("DELETE FROM {}", dialect.quote(&table));
686
687        if !self.wheres.is_empty() {
688            sql.push_str(" WHERE ");
689            sql.push_str(&self.wheres.join(" AND "));
690        }
691
692        sql
693    }
694}
695
696#[cfg(test)]
697mod tests {
698    use super::*;
699
700    // ---- Query::select 测试 ----
701
702    #[test]
703    fn test_select_basic() {
704        let sql = Query::select()
705            .column("id")
706            .column("name")
707            .from("users")
708            .build(DbType::MySQL);
709        assert!(sql.starts_with("SELECT "));
710        assert!(sql.contains("`id`"));
711        assert!(sql.contains("`name`"));
712        assert!(sql.contains("FROM `users`"));
713    }
714
715    #[test]
716    fn test_select_star() {
717        let sql = Query::select()
718            .all_columns()
719            .from("users")
720            .build(DbType::MySQL);
721        assert!(sql.contains("SELECT *"));
722        assert!(sql.contains("FROM `users`"));
723    }
724
725    #[test]
726    fn test_select_distinct() {
727        let sql = Query::select()
728            .distinct()
729            .column("name")
730            .from("users")
731            .build(DbType::MySQL);
732        assert!(sql.contains("SELECT DISTINCT"));
733    }
734
735    #[test]
736    fn test_select_with_where() {
737        let sql = Query::select()
738            .column("id")
739            .from("users")
740            .where_clause("age > 18")
741            .where_clause("status = 'active'")
742            .build(DbType::MySQL);
743        assert!(sql.contains("WHERE age > 18 AND status = 'active'"));
744    }
745
746    #[test]
747    fn test_select_with_or_where() {
748        let sql = Query::select()
749            .column("id")
750            .from("users")
751            .where_clause("age > 18")
752            .or_where("role = 'admin'")
753            .build(DbType::MySQL);
754        assert!(sql.contains("WHERE age > 18 OR role = 'admin'"));
755    }
756
757    #[test]
758    fn test_select_with_inner_join() {
759        let sql = Query::select()
760            .column("u.id")
761            .from("users u")
762            .inner_join("orders o", "u.id = o.user_id")
763            .build(DbType::MySQL);
764        assert!(sql.contains("INNER JOIN `orders` o ON u.id = o.user_id"));
765    }
766
767    #[test]
768    fn test_select_with_left_join() {
769        let sql = Query::select()
770            .column("u.id")
771            .from("users u")
772            .left_join("profiles p", "u.id = p.user_id")
773            .build(DbType::MySQL);
774        assert!(sql.contains("LEFT JOIN `profiles` p ON u.id = p.user_id"));
775    }
776
777    #[test]
778    fn test_select_with_order_by() {
779        let sql = Query::select()
780            .column("id")
781            .from("users")
782            .order_by("created_at", true)
783            .order_by("id", false)
784            .build(DbType::MySQL);
785        assert!(sql.contains("ORDER BY `created_at` ASC, `id` DESC"));
786    }
787
788    #[test]
789    fn test_select_with_limit_offset() {
790        let sql = Query::select()
791            .column("id")
792            .from("users")
793            .limit(10)
794            .offset(20)
795            .build(DbType::MySQL);
796        assert!(sql.contains("LIMIT 10"));
797        assert!(sql.contains("OFFSET 20"));
798    }
799
800    #[test]
801    fn test_select_paginate() {
802        let sql = Query::select()
803            .column("id")
804            .from("users")
805            .paginate(3, 20)
806            .build(DbType::MySQL);
807        // page 3, size 20 -> offset = (3-1)*20 = 40
808        assert!(sql.contains("LIMIT 20"));
809        assert!(sql.contains("OFFSET 40"));
810    }
811
812    #[test]
813    fn test_select_with_group_by_having() {
814        let sql = Query::select()
815            .column("status")
816            .from("users")
817            .group_by("status")
818            .having("COUNT(*) > 5")
819            .build(DbType::MySQL);
820        assert!(sql.contains("GROUP BY `status`"));
821        assert!(sql.contains("HAVING COUNT(*) > 5"));
822    }
823
824    #[test]
825    fn test_select_postgres_dialect() {
826        let sql = Query::select()
827            .column("id")
828            .from("users")
829            .build(DbType::PostgreSQL);
830        assert!(sql.contains("\"id\""));
831        assert!(sql.contains("FROM \"users\""));
832    }
833
834    #[test]
835    fn test_select_sqlite_dialect() {
836        let sql = Query::select()
837            .column("id")
838            .from("users")
839            .build(DbType::Sqlite);
840        assert!(sql.contains("\"id\""));
841    }
842
843    #[test]
844    fn test_select_multiple_joins() {
845        let sql = Query::select()
846            .column("u.id")
847            .from("users u")
848            .inner_join("orders o", "u.id = o.user_id")
849            .left_join("profiles p", "u.id = p.user_id")
850            .build(DbType::MySQL);
851        assert!(sql.contains("INNER JOIN `orders` o"));
852        assert!(sql.contains("LEFT JOIN `profiles` p"));
853    }
854
855    #[test]
856    fn test_select_columns_multiple() {
857        let sql = Query::select()
858            .columns(&["id", "name", "email"])
859            .from("users")
860            .build(DbType::MySQL);
861        assert!(sql.contains("`id`, `name`, `email`"));
862    }
863
864    #[test]
865    fn test_select_no_columns_defaults_star() {
866        let sql = Query::select().from("users").build(DbType::MySQL);
867        assert!(sql.contains("SELECT *"));
868    }
869
870    // ---- Query::insert 测试 ----
871
872    #[test]
873    fn test_insert_basic() {
874        let sql = Query::insert()
875            .into_table("users")
876            .value("name", "'Alice'")
877            .value("age", "30")
878            .build();
879        assert!(sql.starts_with("INSERT INTO `users`"));
880        assert!(sql.contains("`name`, `age`"));
881        assert!(sql.contains("'Alice', 30"));
882    }
883
884    #[test]
885    fn test_insert_values_batch() {
886        let sql = Query::insert()
887            .into_table("users")
888            .values(&[("name", "'Bob'"), ("age", "25"), ("email", "'bob@x.com'")])
889            .build();
890        assert!(sql.contains("`name`, `age`, `email`"));
891        assert!(sql.contains("'Bob', 25, 'bob@x.com'"));
892    }
893
894    #[test]
895    fn test_insert_empty_returns_empty() {
896        let sql = Query::insert().into_table("users").build();
897        assert_eq!(sql, "");
898    }
899
900    #[test]
901    fn test_insert_with_dialect() {
902        let sql = Query::insert()
903            .into_table("users")
904            .value("name", "'Alice'")
905            .build_with_dialect(DbType::PostgreSQL);
906        assert!(sql.contains("\"name\""));
907        assert!(sql.contains("\"users\""));
908    }
909
910    // ---- Query::update 测试 ----
911
912    #[test]
913    fn test_update_basic() {
914        let sql = Query::update()
915            .table("users")
916            .set("name", "'Bob'")
917            .where_clause("id = 1")
918            .build();
919        assert!(sql.starts_with("UPDATE `users` SET"));
920        assert!(sql.contains("`name` = 'Bob'"));
921        assert!(sql.contains("WHERE id = 1"));
922    }
923
924    #[test]
925    fn test_update_multiple_sets() {
926        let sql = Query::update()
927            .table("users")
928            .sets(&[("name", "'Bob'"), ("age", "30")])
929            .where_clause("id = 1")
930            .build();
931        assert!(sql.contains("`name` = 'Bob', `age` = 30"));
932    }
933
934    #[test]
935    fn test_update_no_where() {
936        let sql = Query::update()
937            .table("users")
938            .set("status", "'active'")
939            .build();
940        assert!(sql.contains("UPDATE `users` SET `status` = 'active'"));
941        assert!(!sql.contains("WHERE"));
942    }
943
944    #[test]
945    fn test_update_empty_returns_empty() {
946        let sql = Query::update().table("users").build();
947        assert_eq!(sql, "");
948    }
949
950    #[test]
951    fn test_update_with_dialect() {
952        let sql = Query::update()
953            .table("users")
954            .set("name", "'Bob'")
955            .build_with_dialect(DbType::PostgreSQL);
956        assert!(sql.contains("\"users\""));
957        assert!(sql.contains("\"name\""));
958    }
959
960    // ---- Query::delete 测试 ----
961
962    #[test]
963    fn test_delete_basic() {
964        let sql = Query::delete()
965            .from_table("users")
966            .where_clause("id = 1")
967            .build();
968        assert!(sql.starts_with("DELETE FROM `users`"));
969        assert!(sql.contains("WHERE id = 1"));
970    }
971
972    #[test]
973    fn test_delete_no_where() {
974        let sql = Query::delete().from_table("users").build();
975        assert!(sql.contains("DELETE FROM `users`"));
976        assert!(!sql.contains("WHERE"));
977    }
978
979    #[test]
980    fn test_delete_multiple_wheres() {
981        let sql = Query::delete()
982            .from_table("users")
983            .where_clause("id > 100")
984            .where_clause("status = 'inactive'")
985            .build();
986        assert!(sql.contains("WHERE id > 100 AND status = 'inactive'"));
987    }
988
989    #[test]
990    fn test_delete_empty_returns_empty() {
991        let sql = Query::delete().build();
992        assert_eq!(sql, "");
993    }
994
995    #[test]
996    fn test_delete_with_dialect() {
997        let sql = Query::delete()
998            .from_table("users")
999            .where_clause("id = 1")
1000            .build_with_dialect(DbType::PostgreSQL);
1001        assert!(sql.contains("\"users\""));
1002    }
1003
1004    // ---- 完整流程测试 ----
1005
1006    #[test]
1007    fn test_full_crud_flow() {
1008        // CREATE (用 INSERT 模拟)
1009        let insert = Query::insert()
1010            .into_table("users")
1011            .value("name", "'Alice'")
1012            .value("age", "30")
1013            .build();
1014        assert!(insert.contains("INSERT INTO"));
1015
1016        // READ
1017        let select = Query::select()
1018            .column("id")
1019            .column("name")
1020            .from("users")
1021            .where_clause("age > 18")
1022            .order_by("id", true)
1023            .limit(10)
1024            .build(DbType::MySQL);
1025        assert!(select.contains("SELECT"));
1026        assert!(select.contains("FROM"));
1027        assert!(select.contains("WHERE"));
1028        assert!(select.contains("ORDER BY"));
1029        assert!(select.contains("LIMIT"));
1030
1031        // UPDATE
1032        let update = Query::update()
1033            .table("users")
1034            .set("name", "'Bob'")
1035            .where_clause("id = 1")
1036            .build();
1037        assert!(update.contains("UPDATE"));
1038        assert!(update.contains("SET"));
1039        assert!(update.contains("WHERE"));
1040
1041        // DELETE
1042        let delete = Query::delete()
1043            .from_table("users")
1044            .where_clause("id = 1")
1045            .build();
1046        assert!(delete.contains("DELETE FROM"));
1047    }
1048
1049    #[test]
1050    fn test_complex_select_query() {
1051        let sql = Query::select()
1052            .distinct()
1053            .columns(&["u.id", "u.name", "o.total"])
1054            .from("users u")
1055            .inner_join("orders o", "u.id = o.user_id")
1056            .where_clause("u.status = 'active'")
1057            .where_clause("o.total > 100")
1058            .group_by("u.id")
1059            .having("SUM(o.total) > 1000")
1060            .order_by("u.id", true)
1061            .limit(20)
1062            .offset(40)
1063            .build(DbType::MySQL);
1064
1065        assert!(sql.contains("SELECT DISTINCT"));
1066        assert!(sql.contains("INNER JOIN `orders` o"));
1067        assert!(sql.contains("WHERE u.status = 'active' AND o.total > 100"));
1068        assert!(sql.contains("GROUP BY"));
1069        assert!(sql.contains("HAVING SUM(o.total) > 1000"));
1070        assert!(sql.contains("ORDER BY `u`.`id` ASC"));
1071        assert!(sql.contains("LIMIT 20"));
1072        assert!(sql.contains("OFFSET 40"));
1073    }
1074
1075    // ---- v0.2.2 修复 C-6：SQL 注入测试 ----
1076
1077    #[test]
1078    #[should_panic(expected = "SQL injection detected")]
1079    fn test_select_where_rejects_semicolon_drop() {
1080        let _ = Query::select()
1081            .column("id")
1082            .from("users")
1083            .where_clause("1=1; DROP TABLE users")
1084            .build(DbType::MySQL);
1085    }
1086
1087    #[test]
1088    #[should_panic(expected = "SQL injection detected")]
1089    fn test_select_where_rejects_semicolon_space_drop() {
1090        let _ = Query::select()
1091            .column("id")
1092            .from("users")
1093            .where_clause("1=1; DROP TABLE users")
1094            .build(DbType::MySQL);
1095    }
1096
1097    #[test]
1098    #[should_panic(expected = "SQL injection detected")]
1099    fn test_select_where_rejects_line_comment() {
1100        let _ = Query::select()
1101            .column("id")
1102            .from("users")
1103            .where_clause("id = 1 -- DROP TABLE users")
1104            .build(DbType::MySQL);
1105    }
1106
1107    #[test]
1108    #[should_panic(expected = "SQL injection detected")]
1109    fn test_select_where_rejects_block_comment() {
1110        let _ = Query::select()
1111            .column("id")
1112            .from("users")
1113            .where_clause("id = 1 /* comment */ OR 1=1")
1114            .build(DbType::MySQL);
1115    }
1116
1117    #[test]
1118    #[should_panic(expected = "SQL injection detected")]
1119    fn test_select_or_where_rejects_drop() {
1120        let _ = Query::select()
1121            .column("id")
1122            .from("users")
1123            .where_clause("id = 1")
1124            .or_where("1=1; DROP TABLE users")
1125            .build(DbType::MySQL);
1126    }
1127
1128    #[test]
1129    #[should_panic(expected = "SQL injection detected")]
1130    fn test_update_where_rejects_delete() {
1131        let _ = Query::update()
1132            .table("users")
1133            .set("name", "'x'")
1134            .where_clause("1=1; DELETE FROM users")
1135            .build();
1136    }
1137
1138    #[test]
1139    #[should_panic(expected = "SQL injection detected")]
1140    fn test_update_where_rejects_line_comment() {
1141        let _ = Query::update()
1142            .table("users")
1143            .set("name", "'x'")
1144            .where_clause("id = 1 -- bypass")
1145            .build();
1146    }
1147
1148    #[test]
1149    #[should_panic(expected = "SQL injection detected")]
1150    fn test_delete_where_rejects_drop() {
1151        let _ = Query::delete()
1152            .from_table("users")
1153            .where_clause("1=1; DROP TABLE users")
1154            .build();
1155    }
1156
1157    #[test]
1158    #[should_panic(expected = "SQL injection detected")]
1159    fn test_delete_where_rejects_block_comment() {
1160        let _ = Query::delete()
1161            .from_table("users")
1162            .where_clause("id = 1 /* */ OR 1=1")
1163            .build();
1164    }
1165
1166    #[test]
1167    #[should_panic(expected = "SQL injection detected")]
1168    fn test_delete_where_rejects_line_comment() {
1169        let _ = Query::delete()
1170            .from_table("users")
1171            .where_clause("id = 1--")
1172            .build();
1173    }
1174
1175    #[test]
1176    fn test_safe_where_clauses_pass() {
1177        // 这些是合法的 WHERE 条件，不应触发 panic
1178        let _ = Query::select()
1179            .column("id")
1180            .from("users")
1181            .where_clause("age > 18")
1182            .where_clause("name = 'Alice;Bob'") // 分号在字符串字面量中
1183            .where_clause("id IN (1, 2, 3)")
1184            .where_clause("created_at > '2026-01-01'")
1185            .build(DbType::MySQL);
1186
1187        let _ = Query::update()
1188            .table("users")
1189            .set("name", "'x'")
1190            .where_clause("id = 1")
1191            .build();
1192
1193        let _ = Query::delete()
1194            .from_table("users")
1195            .where_clause("id = 1")
1196            .build();
1197    }
1198}
sz_orm_query_builder/lib.rs

sz_orm_query_builder/
lib.rs
