br_db/types/
mod.rs

1use json::{JsonValue, object};
2
3static DISABLE_FIELD: &[&str] = &["default", "select", "delete", "insert", "update", "order", "group", "user", "password", "desc", "index", "from", "host", "user", "read", "partition"];
4
5/// SQL injection prevention - security critical module
6pub mod sql_safety {
7    static SQL_KEYWORDS: &[&str] = &[
8        "select", "insert", "update", "delete", "drop", "truncate", "alter",
9        "create", "exec", "execute", "union",
10    ];
11    
12    static DANGEROUS_PATTERNS: &[&str] = &[
13        "--", "/*", "*/", ";", "xp_", "sp_", "0x",
14    ];
15
16    #[inline]
17    pub fn validate_table_name(name: &str) -> bool {
18        if name.is_empty() || name.len() > 128 {
19            return false;
20        }
21        let bytes = name.as_bytes();
22        let first = bytes[0];
23        if !first.is_ascii_alphabetic() && first != b'_' {
24            return false;
25        }
26        for &b in bytes {
27            if !b.is_ascii_alphanumeric() && b != b'_' {
28                return false;
29            }
30        }
31        let lower = name.to_lowercase();
32        if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
33            return false;
34        }
35        !DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
36    }
37
38    #[inline]
39    pub fn validate_field_name(name: &str) -> bool {
40        if name.is_empty() || name.len() > 256 {
41            return false;
42        }
43        for part in name.split('.') {
44            if part.is_empty() {
45                return false;
46            }
47            let bytes = part.as_bytes();
48            let first = bytes[0];
49            if !first.is_ascii_alphabetic() && first != b'_' {
50                return false;
51            }
52            for &b in bytes {
53                if !b.is_ascii_alphanumeric() && b != b'_' {
54                    return false;
55                }
56            }
57        }
58        let lower = name.to_lowercase();
59        if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
60            return false;
61        }
62        !DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
63    }
64
65    #[inline]
66    pub fn escape_string(value: &str) -> String {
67        value
68            .replace('\\', "\\\\")
69            .replace('\'', "''")
70            .replace('\0', "")
71            .replace('\n', "\\n")
72            .replace('\r', "\\r")
73    }
74
75    #[inline]
76    pub fn validate_compare_orator(op: &str) -> bool {
77        matches!(
78            op.to_lowercase().as_str(),
79            "=" | "!=" | "<>" | "<" | ">" | "<=" | ">=" | "like" | "not like" |
80            "in" | "not in" | "notin" | "between" | "is" | "isnot" | "is not" |
81            "set" | "location" | "notlike"
82        )
83    }
84
85    #[cfg(test)]
86    mod tests {
87        use super::*;
88
89        #[test]
90        fn test_validate_table_name_valid() {
91            assert!(validate_table_name("users"));
92            assert!(validate_table_name("user_profiles"));
93            assert!(validate_table_name("_private_table"));
94            assert!(validate_table_name("Table123"));
95            assert!(validate_table_name("batch_insert_perf"));
96            assert!(validate_table_name("my_update_log"));
97        }
98
99        #[test]
100        fn test_validate_table_name_invalid() {
101            assert!(!validate_table_name(""));
102            assert!(!validate_table_name("123table"));
103            assert!(!validate_table_name("user-name"));
104            assert!(!validate_table_name("table.name"));
105            assert!(!validate_table_name("table name"));
106        }
107
108        #[test]
109        fn test_validate_table_name_sql_keywords() {
110            assert!(!validate_table_name("select"));
111            assert!(!validate_table_name("SELECT"));
112            assert!(!validate_table_name("insert"));
113            assert!(!validate_table_name("drop"));
114            assert!(!validate_table_name("union"));
115        }
116
117        #[test]
118        fn test_validate_table_name_dangerous_patterns() {
119            assert!(!validate_table_name("table;drop"));
120            assert!(!validate_table_name("table--comment"));
121            assert!(!validate_table_name("xp_cmdshell"));
122            assert!(!validate_table_name("sp_execute"));
123        }
124
125        #[test]
126        fn test_validate_table_name_length() {
127            let long_name = "a".repeat(128);
128            assert!(validate_table_name(&long_name));
129            let too_long = "a".repeat(129);
130            assert!(!validate_table_name(&too_long));
131        }
132
133        #[test]
134        fn test_validate_field_name_valid() {
135            assert!(validate_field_name("id"));
136            assert!(validate_field_name("user_name"));
137            assert!(validate_field_name("_hidden"));
138            assert!(validate_field_name("table1.field1"));
139        }
140
141        #[test]
142        fn test_validate_field_name_invalid() {
143            assert!(!validate_field_name(""));
144            assert!(!validate_field_name("123field"));
145            assert!(!validate_field_name("field-name"));
146            assert!(!validate_field_name(".field"));
147            assert!(!validate_field_name("field."));
148        }
149
150        #[test]
151        fn test_escape_string() {
152            assert_eq!(escape_string("hello"), "hello");
153            assert_eq!(escape_string("it's"), "it''s");
154            assert_eq!(escape_string("new\nline"), "new\\nline");
155            assert_eq!(escape_string("carriage\rreturn"), "carriage\\rreturn");
156        }
157
158        #[test]
159        fn test_escape_string_sql_injection() {
160            assert_eq!(escape_string("'; DROP TABLE users; --"), "''; DROP TABLE users; --");
161            assert_eq!(escape_string("1' OR '1'='1"), "1'' OR ''1''=''1");
162        }
163
164        #[test]
165        fn test_validate_compare_operator_valid() {
166            assert!(validate_compare_orator("="));
167            assert!(validate_compare_orator("!="));
168            assert!(validate_compare_orator("like"));
169            assert!(validate_compare_orator("LIKE"));
170            assert!(validate_compare_orator("in"));
171            assert!(validate_compare_orator("between"));
172        }
173
174        #[test]
175        fn test_validate_compare_operator_invalid() {
176            assert!(!validate_compare_orator(""));
177            assert!(!validate_compare_orator("invalid"));
178            assert!(!validate_compare_orator("=="));
179            assert!(!validate_compare_orator("&&"));
180        }
181    }
182}
183
184#[inline]
185pub fn quote_identifier(name: &str, mode: &str) -> String {
186    match mode {
187        "mysql" | "sqlite" => format!("`{}`", name),
188        "pgsql" => format!("\"{}\"", name),
189        "mssql" => format!("[{}]", name),
190        _ => name.to_string(),
191    }
192}
193
194#[cfg(feature = "db-sqlite")]
195pub mod sqlite;
196#[cfg(feature = "db-sqlite")]
197pub mod sqlite_transaction;
198#[cfg(feature = "db-mysql")]
199pub mod mysql;
200#[cfg(feature = "db-mysql")]
201pub mod mysql_transaction;
202#[cfg(feature = "db-mssql")]
203pub mod mssql;
204#[cfg(feature = "db-pgsql")]
205pub mod pgsql;
206#[cfg(feature = "db-pgsql")]
207pub mod pgsql_transaction;
208
209pub trait DbMode {
210    /// 数据库是否存在
211    fn database_tables(&mut self) -> JsonValue;
212    /// 创建数据库
213    fn database_create(&mut self, name: &str) -> bool;
214    /// 备份
215    fn backups(&mut self, _filename: &str) -> bool {
216        false
217    }
218}
219
220#[derive(Debug, Clone)]
221pub struct TableOptions {
222    table_name: String,
223    table_title: String,
224    table_key: String,
225    table_fields: JsonValue,
226    table_unique: Vec<String>,
227    table_index: Vec<Vec<String>>,
228    table_partition: bool,
229    table_partition_columns: JsonValue,
230}
231impl TableOptions {
232    pub fn set_table_name(&mut self, name: &str) {
233        self.table_name = name.to_string()
234    }
235    pub fn set_table_title(&mut self, name: &str) {
236        self.table_title = name.to_string()
237    }
238    pub fn set_table_key(&mut self, name: &str) {
239        self.table_key = name.to_string()
240    }
241    pub fn set_table_fields(&mut self, fields: JsonValue) {
242        self.table_fields = fields;
243    }
244    pub fn set_table_unique(&mut self, unique: Vec<&str>) {
245        self.table_unique = unique.iter().map(|s| s.to_string()).collect();
246    }
247    pub fn set_table_index(&mut self, index: Vec<Vec<&str>>) {
248        self.table_index = index.iter().map(|s| s.iter().map(|s| s.to_string()).collect()).collect();
249    }
250    pub fn set_table_partition(&mut self, index: bool) {
251        self.table_partition = index;
252    }
253    pub fn set_table_partition_columns(&mut self, index: JsonValue) {
254        self.table_partition_columns = index;
255    }
256}
257impl Default for TableOptions {
258    fn default() -> Self {
259        Self {
260            table_name: "".to_string(),
261            table_title: "".to_string(),
262            table_key: "".to_string(),
263            table_fields: JsonValue::Null,
264            table_unique: vec![],
265            table_index: vec![],
266            table_partition: false,
267            table_partition_columns: JsonValue::Null,
268        }
269    }
270}
271pub trait Mode: DbMode {
272    fn table_create(&mut self, options: TableOptions) -> JsonValue;
273    fn table_update(&mut self, options: TableOptions) -> JsonValue;
274    /// 获取表信息
275    fn table_info(&mut self, table: &str) -> JsonValue;
276    /// 判断表是否存在
277    fn table_is_exist(&mut self, name: &str) -> bool;
278    /// 当前表
279    fn table(&mut self, name: &str) -> &mut Self;
280    /// 别名
281    fn change_table(&mut self, name: &str) -> &mut Self;
282
283    /// 主键自增
284    fn autoinc(&mut self) -> &mut Self;
285    /// 返回sql语句
286    fn fetch_sql(&mut self) -> &mut Self;
287    /// 排序
288    fn order(&mut self, field: &str, by: bool) -> &mut Self;
289    /// 分组
290    fn group(&mut self, field: &str) -> &mut Self;
291    /// 消除重复记录
292    fn distinct(&mut self) -> &mut Self;
293    /// JSON 字段
294    fn json(&mut self, field: &str) -> &mut Self;
295    /// location 字段
296    fn location(&mut self, field: &str) -> &mut Self;
297    /// 显示字段
298    fn field(&mut self, field: &str) -> &mut Self;
299    /// 隐藏字段
300    fn hidden(&mut self, name: &str) -> &mut Self;
301    /// 查询
302    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
303    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
304
305    /// 比较两个列
306    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self;
307    /// 更新指定列
308    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self;
309    /// 分页
310    fn page(&mut self, page: i32, limit: i32) -> &mut Self;
311    /// 查询指定列并返回数组
312    fn column(&mut self, field: &str) -> JsonValue;
313    /// 总数量
314    fn count(&mut self) -> JsonValue;
315    /// 最大值
316    fn max(&mut self, field: &str) -> JsonValue;
317    /// 最小值
318    fn min(&mut self, field: &str) -> JsonValue;
319    /// 合计
320    fn sum(&mut self, field: &str) -> JsonValue;
321    /// 平均值
322    fn avg(&mut self, field: &str) -> JsonValue;
323    /// 获取集合
324    fn select(&mut self) -> JsonValue;
325    /// 获取单记录
326    fn find(&mut self) -> JsonValue;
327    /// 获取单一列值
328    fn value(&mut self, field: &str) -> JsonValue;
329    /// 添加
330    fn insert(&mut self, data: JsonValue) -> JsonValue;
331    /// 批量添加
332    fn insert_all(&mut self, data: JsonValue) -> JsonValue;
333
334    /// 更新
335    fn update(&mut self, data: JsonValue) -> JsonValue;
336    /// 批量更新
337    fn update_all(&mut self, data: JsonValue) -> JsonValue;
338    /// 删除
339    fn delete(&mut self) -> JsonValue;
340
341    /// 事务开始
342    fn transaction(&mut self) -> bool;
343    /// 事务提交
344    fn commit(&mut self) -> bool;
345    /// 事务回滚
346    fn rollback(&mut self) -> bool;
347    /// sql语句执行
348    fn sql(&mut self, sql: &str) -> Result<JsonValue, String>;
349    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String>;
350    /// 自增
351    fn inc(&mut self, field: &str, num: f64) -> &mut Self;
352    /// 自减
353    fn dec(&mut self, field: &str, num: f64) -> &mut Self;
354
355    /// 构造子查询
356    fn buildsql(&mut self) -> String;
357
358    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self;
359    /// 连结
360    /// * main_table 主表表名
361    /// * main_fields 主表字段
362    /// * right_table 关联表名
363    /// * right_fields 关联字段名
364    fn join(&mut self, main_table: &str, main_fields: &str, right_table: &str, right_fields: &str) -> &mut Self;
365
366    /// 内连接--用来取交集
367    ///
368    /// * table 链表表名
369    /// * main_fields 主表字段
370    /// * second_fields 附表关联字段
371    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self;
372}
373
374#[derive(Clone, Debug)]
375pub struct Params {
376    pub mode: String,
377    pub autoinc: bool,
378    pub table: String,
379    pub where_and: Vec<String>,
380    pub where_or: Vec<String>,
381    pub where_column: String,
382    pub update_column: Vec<String>,
383    pub inc_dec: JsonValue,
384    pub page: i32,
385    pub limit: i32,
386    pub fields: JsonValue,
387    pub top: String,
388    pub top2: String,
389    pub order: JsonValue,
390    pub group: JsonValue,
391    pub distinct: bool,
392    pub json: JsonValue,
393    pub location: JsonValue,
394    pub sql: bool,
395    pub join: Vec<String>,
396    pub join_inner: Vec<String>,
397    pub join_table: String,
398}
399
400impl Params {
401    pub fn default(mode: &str) -> Self {
402        Self {
403            mode: mode.to_string(),
404            autoinc: false,
405            table: "".to_string(),
406            where_and: vec![],
407            where_or: vec![],
408            where_column: "".to_string(),
409            update_column: vec![],
410            inc_dec: object! {},
411            page: -1,
412            limit: 10,
413            fields: object! {},
414            top: String::new(),
415            top2: String::new(),
416            order: object! {},
417            group: object! {},
418            distinct: false,
419            json: object! {},
420            location: object! {},
421            sql: false,
422            join: Vec::new(),
423            join_inner: Vec::new(),
424            join_table: "".to_string(),
425        }
426    }
427    pub fn where_sql(&mut self) -> String {
428        let mut where_and_sql = vec![];
429        let mut where_or_sql = vec![];
430        let mut sql = vec![];
431
432        for item in self.where_or.iter() {
433            where_or_sql.push(item.clone());
434        }
435        if !where_or_sql.is_empty() {
436            sql.push(format!(" ( {} ) ", where_or_sql.join(" OR ")));
437        }
438
439        for item in self.where_and.iter() {
440            where_and_sql.push(item.clone());
441        }
442        if !where_and_sql.is_empty() {
443            sql.push(where_and_sql.join(" AND "));
444        }
445
446        if !self.where_column.is_empty() {
447            sql.push(self.where_column.clone());
448        }
449
450        if !sql.is_empty() {
451            return format!("WHERE {}", sql.join(" AND "));
452        }
453        "".to_string()
454    }
455    pub fn page_limit_sql(&mut self) -> String {
456        if self.page == -1 {
457            return "".to_string();
458        }
459        match self.mode.as_str() {
460            "mysql" => {
461                format!("LIMIT {},{}", self.page * self.limit - self.limit, self.limit)
462            }
463            "sqlite" => {
464                format!("LIMIT {} OFFSET {}", self.limit, self.page * self.limit - self.limit)
465            }
466            _ => "".to_string()
467        }
468    }
469    pub fn fields(&mut self) -> String {
470        let mut fields = vec![];
471        for (_, value) in self.fields.entries() {
472            match self.mode.as_str() {
473                "mssql" => {
474                    fields.push(format!("{value}"));
475                }
476                "mysql" => {
477                    if let Some(s) = value.as_str() {
478                        if DISABLE_FIELD.contains(&s) {
479                            fields.push(format!("`{value}`"));
480                        } else {
481                            fields.push(format!("{value}"));
482                        }
483                    } else {
484                        fields.push(format!("{value}"));
485                    }
486                }
487                _ => {
488                    fields.push(format!("{value}"));
489                }
490            }
491        }
492        let fields = {
493            if fields.is_empty() {
494                "*".into()
495            } else {
496                fields.join(",")
497            }
498        };
499        match self.mode.as_str() {
500            "mysql" => {
501                fields.to_string()
502            }
503            "sqlite" => {
504                fields.to_string()
505            }
506            "mssql" => {
507                fields.to_string()
508            }
509            _ => fields.to_string()
510        }
511    }
512    pub fn top(&mut self) -> String {
513        match self.mode.as_str() {
514            "mssql" => {
515                let wheres = self.where_sql();
516                if !self.top2.is_empty() {
517                    let order = self.order();
518                    if order.is_empty() {
519                        self.top = format!("(select ROW_NUMBER() OVER(ORDER BY rand()) as ROW,* from {} {}) as ", self.table, wheres);
520                    } else {
521                        self.top = format!("(select ROW_NUMBER() OVER({}) as ROW,* from {} {}) as ", order, self.table, wheres);
522                    }
523                    return self.top.to_string();
524                }
525                self.top.to_string()
526            }
527            _ => {
528                "".to_string()
529            }
530        }
531    }
532    pub fn top2(&mut self) -> String {
533        match self.mode.as_str() {
534            "mssql" => {
535                if self.where_and.is_empty() && self.where_or.is_empty() && !self.top2.is_empty() {
536                    return format!("where {}", self.top2);
537                }
538                if (!self.where_and.is_empty() || !self.where_or.is_empty()) && !self.top2.is_empty() {
539                    return format!("AND {}", self.top2);
540                }
541                self.top2.to_string()
542            }
543            _ => {
544                "".to_string()
545            }
546        }
547    }
548    pub fn table(&mut self) -> String {
549        match self.mode.as_str() {
550            "mssql" => {
551                if !self.top2.is_empty() {
552                    return "t".to_string();
553                }
554                self.table.to_string()
555            }
556            _ => {
557                self.table.to_string()
558            }
559        }
560    }
561    pub fn join(&mut self) -> String {
562        match self.mode.as_str() {
563            "mssql" => {
564                self.join.join(" ")
565            }
566            _ => {
567                self.join.join(" ")
568            }
569        }
570    }
571
572    // 当前只匹配了MySQL
573    pub fn join_inner(&mut self) -> String {
574        match self.mode.as_str() {
575            "mysql" => {
576                let mut join_inner = "".to_string();
577                for item in self.join_inner.iter() {
578                    join_inner = format!("{join_inner} {item}");
579                }
580                join_inner.to_string()
581            }
582            _ => {
583                "".to_string()
584            }
585        }
586    }
587
588
589    pub fn order(&mut self) -> String {
590        let mut sql = vec![];
591        for (field, item) in self.order.entries() {
592            match self.mode.as_str() {
593                "mssql" => {
594                    if DISABLE_FIELD.contains(&field) {
595                        sql.push(format!("[{field}] {item}"));
596                    } else {
597                        sql.push(format!("{field} {item}"));
598                    }
599                }
600
601                "pgsql" => {
602                    if DISABLE_FIELD.contains(&field) {
603                        sql.push(format!("\"{field}\" {item}"));
604                    } else {
605                        sql.push(format!("{field} {item}"));
606                    }
607                }
608                _ => {
609                    if DISABLE_FIELD.contains(&field) {
610                        sql.push(format!("`{field}` {item}"));
611                    } else {
612                        sql.push(format!("{field} {item}"));
613                    }
614                }
615            }
616        }
617        if !sql.is_empty() {
618            return format!("ORDER BY {}", sql.join(","));
619        }
620        "".to_string()
621    }
622    pub fn group(&mut self) -> String {
623        let mut sql = vec![];
624        for (_, field) in self.group.entries() {
625            if DISABLE_FIELD.contains(&&*field.clone().to_string()) {
626                sql.push(format!("`{field}`"));
627            } else if field.to_string().contains(".") {
628                sql.push(format!("{}", field));
629            } else {
630                sql.push(format!("{}.{}", self.table, field));
631            }
632        }
633        if !sql.is_empty() {
634            return format!("GROUP BY {}", sql.join(","));
635        }
636        "".to_string()
637    }
638    pub fn distinct(&self) -> String {
639        if self.distinct {
640            "DISTINCT".to_string()
641        } else {
642            "".to_string()
643        }
644    }
645    pub fn select_sql(&mut self) -> String {
646        format!("SELECT {} {} FROM {} {} {} {} {} {} {} {} {}", self.distinct(), self.fields(), self.top(), self.table(), self.join(), self.join_inner(), self.where_sql(), self.top2(), self.group(), self.order(), self.page_limit_sql())
647    }
648}
br_db/types/mod.rs

br_db/types/
mod.rs
