Skip to main content

yang_db/
reference.rs

1//! 查询热路径使用的受控 SQL 引用与封闭操作符。
2
3use crate::sql_types::{Identifier, QualifiedIdentifier};
4use crate::DbError;
5use std::fmt;
6
7/// 构建期校验完成的数据库表引用。
8#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
9pub struct TableRef {
10    raw: String,
11}
12
13impl TableRef {
14    /// 校验并创建表引用。
15    pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
16        let raw = value.into();
17        Identifier::parse(&raw)?;
18        Ok(Self { raw })
19    }
20
21    /// 返回未加方言引号的已校验表名。
22    pub fn as_str(&self) -> &str {
23        &self.raw
24    }
25
26    #[doc(hidden)]
27    pub fn __from_validated_literal(value: &'static str) -> Self {
28        Self {
29            raw: value.to_string(),
30        }
31    }
32
33    #[doc(hidden)]
34    pub fn __from_validated_owned(raw: String) -> Self {
35        Self { raw }
36    }
37}
38
39impl fmt::Display for TableRef {
40    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
41        formatter.write_str(&self.raw)
42    }
43}
44
45/// 构建期校验完成的列或 `table.column` 引用。
46#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
47pub struct FieldRef {
48    raw: String,
49    #[cfg(feature = "mysql")]
50    mysql: String,
51    #[cfg(feature = "postgres")]
52    postgres: String,
53}
54
55impl FieldRef {
56    /// 校验并创建字段引用。
57    pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
58        let raw = value.into();
59        let identifier = QualifiedIdentifier::parse(&raw)?;
60        let _ = &identifier;
61        Ok(Self {
62            #[cfg(feature = "mysql")]
63            mysql: identifier.render('`'),
64            #[cfg(feature = "postgres")]
65            postgres: identifier.render('"'),
66            raw,
67        })
68    }
69
70    /// 返回未加方言引号的已校验字段名。
71    pub fn as_str(&self) -> &str {
72        &self.raw
73    }
74
75    #[doc(hidden)]
76    pub fn __from_validated_literal(value: &'static str) -> Self {
77        Self {
78            #[cfg(feature = "mysql")]
79            mysql: value
80                .split('.')
81                .map(|part| format!("`{part}`"))
82                .collect::<Vec<_>>()
83                .join("."),
84            #[cfg(feature = "postgres")]
85            postgres: value
86                .split('.')
87                .map(|part| format!("\"{part}\""))
88                .collect::<Vec<_>>()
89                .join("."),
90            raw: value.to_string(),
91        }
92    }
93
94    #[doc(hidden)]
95    pub fn __from_validated_owned(raw: String) -> Self {
96        Self {
97            #[cfg(feature = "mysql")]
98            mysql: raw
99                .split('.')
100                .map(|part| format!("`{part}`"))
101                .collect::<Vec<_>>()
102                .join("."),
103            #[cfg(feature = "postgres")]
104            postgres: raw
105                .split('.')
106                .map(|part| format!("\"{part}\""))
107                .collect::<Vec<_>>()
108                .join("."),
109            raw,
110        }
111    }
112
113    #[cfg(feature = "mysql")]
114    #[doc(hidden)]
115    pub fn mysql_quoted(&self) -> &str {
116        &self.mysql
117    }
118
119    #[cfg(feature = "postgres")]
120    #[doc(hidden)]
121    pub fn postgres_quoted(&self) -> &str {
122        &self.postgres
123    }
124}
125
126#[doc(hidden)]
127pub const fn __validate_table_literal(value: &str) {
128    validate_literal(value, false);
129}
130
131#[doc(hidden)]
132pub const fn __validate_field_literal(value: &str) {
133    validate_literal(value, true);
134}
135
136const fn validate_literal(value: &str, qualified: bool) {
137    let bytes = value.as_bytes();
138    assert!(!bytes.is_empty(), "SQL identifier cannot be empty");
139    let mut index = 0;
140    let mut segment_start = true;
141    let mut dots = 0;
142    while index < bytes.len() {
143        let byte = bytes[index];
144        if byte == b'.' {
145            assert!(qualified, "table identifier cannot be qualified");
146            assert!(!segment_start, "identifier segment cannot be empty");
147            dots += 1;
148            assert!(dots <= 1, "field identifier accepts at most table.field");
149            segment_start = true;
150        } else if segment_start {
151            assert!(
152                byte == b'_' || byte.is_ascii_alphabetic(),
153                "identifier must start with ASCII letter or underscore"
154            );
155            segment_start = false;
156        } else {
157            assert!(
158                byte == b'_' || byte.is_ascii_alphanumeric(),
159                "identifier contains invalid character"
160            );
161        }
162        index += 1;
163    }
164    assert!(!segment_start, "identifier segment cannot be empty");
165}
166
167impl fmt::Display for FieldRef {
168    fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
169        formatter.write_str(&self.raw)
170    }
171}
172
173/// QueryBuilder 接受的封闭比较操作符。
174#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
175#[non_exhaustive]
176pub enum CompareOp {
177    /// 等于。
178    Eq,
179    /// 不等于。
180    Ne,
181    /// 大于。
182    Gt,
183    /// 小于。
184    Lt,
185    /// 大于等于。
186    Gte,
187    /// 小于等于。
188    Lte,
189    /// SQL LIKE。
190    Like,
191}
192
193/// 受控排序方向。
194#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
195pub enum SortOrder {
196    /// 升序。
197    Asc,
198    /// 降序。
199    Desc,
200}
201
202/// 受控字段引用组成的布尔查询树。
203#[derive(Debug, Clone, PartialEq)]
204pub enum Predicate {
205    /// 字段比较。
206    Compare(FieldRef, CompareOp, serde_json::Value),
207    /// IN。
208    In(FieldRef, Vec<serde_json::Value>),
209    /// NOT IN。
210    NotIn(FieldRef, Vec<serde_json::Value>),
211    /// BETWEEN。
212    Between(FieldRef, serde_json::Value, serde_json::Value),
213    /// IS NULL。
214    IsNull(FieldRef),
215    /// IS NOT NULL。
216    IsNotNull(FieldRef),
217    /// AND 组。
218    And(Vec<Predicate>),
219    /// OR 组。
220    Or(Vec<Predicate>),
221}
222
223/// 受控 SELECT 聚合表达式。
224#[derive(Debug, Clone, PartialEq, Eq)]
225pub struct SelectExpr {
226    aggregate: Aggregate,
227    field: Option<FieldRef>,
228    alias: Option<FieldRef>,
229    cast_double: bool,
230}
231
232#[derive(Debug, Clone, Copy, PartialEq, Eq)]
233enum Aggregate {
234    Count,
235    Sum,
236    Avg,
237    Min,
238    Max,
239}
240
241impl SelectExpr {
242    /// `COUNT(*)`。
243    pub fn count_all() -> Self {
244        Self {
245            aggregate: Aggregate::Count,
246            field: None,
247            alias: None,
248            cast_double: false,
249        }
250    }
251
252    /// `COUNT(field)`。
253    pub fn count(field: &FieldRef) -> Self {
254        Self::new(Aggregate::Count, field)
255    }
256
257    /// `SUM(field)`。
258    pub fn sum(field: &FieldRef) -> Self {
259        Self::new(Aggregate::Sum, field)
260    }
261
262    /// `AVG(field)`。
263    pub fn avg(field: &FieldRef) -> Self {
264        Self::new(Aggregate::Avg, field)
265    }
266
267    /// `MIN(field)`。
268    pub fn min(field: &FieldRef) -> Self {
269        Self::new(Aggregate::Min, field)
270    }
271
272    /// `MAX(field)`。
273    pub fn max(field: &FieldRef) -> Self {
274        Self::new(Aggregate::Max, field)
275    }
276
277    fn new(aggregate: Aggregate, field: &FieldRef) -> Self {
278        Self {
279            aggregate,
280            field: Some(field.clone()),
281            alias: None,
282            cast_double: false,
283        }
284    }
285
286    /// 将聚合结果转换为方言对应的双精度浮点数。
287    pub fn cast_double(mut self) -> Self {
288        self.cast_double = true;
289        self
290    }
291
292    /// 设置受控输出别名。
293    pub fn alias(mut self, alias: &FieldRef) -> Self {
294        self.alias = Some(alias.clone());
295        self
296    }
297
298    #[cfg(feature = "mysql")]
299    pub(crate) fn mysql_sql(&self) -> String {
300        self.render('`', "DOUBLE")
301    }
302
303    #[cfg(feature = "postgres")]
304    pub(crate) fn postgres_sql(&self) -> String {
305        self.render('"', "DOUBLE PRECISION")
306    }
307
308    #[cfg(any(feature = "mysql", feature = "postgres"))]
309    fn render(&self, quote: char, double_type: &str) -> String {
310        let function = match self.aggregate {
311            Aggregate::Count => "COUNT",
312            Aggregate::Sum => "SUM",
313            Aggregate::Avg => "AVG",
314            Aggregate::Min => "MIN",
315            Aggregate::Max => "MAX",
316        };
317        let operand = self.field.as_ref().map_or_else(
318            || "*".to_string(),
319            |field| quote_qualified(field.as_str(), quote),
320        );
321        let aggregate = format!("{function}({operand})");
322        let expression = if self.cast_double {
323            format!("CAST({aggregate} AS {double_type})")
324        } else {
325            aggregate
326        };
327        self.alias.as_ref().map_or(expression.clone(), |alias| {
328            format!("{expression} AS {}", quote_qualified(alias.as_str(), quote))
329        })
330    }
331}
332
333/// 受控服务端 SQL 标量表达式。
334///
335/// 只能由白名单构造函数创建:渲染进 SQL 文本的是固定片段,动态部分一律走
336/// 绑定参数,调用方无法注入任意 SQL。目前覆盖服务端 Unix 时间戳场景
337/// (MySQL `UNIX_TIMESTAMP()`),后续新增函数按同一模式扩展枚举变体即可。
338#[derive(Debug, Clone, PartialEq, Eq)]
339#[non_exhaustive]
340pub enum SqlExpr {
341    /// 服务端当前 Unix 时间戳(秒)。
342    UnixTimestamp,
343    /// 服务端当前 Unix 时间戳加偏移秒数;偏移量以绑定参数传递。
344    UnixTimestampAdd(i64),
345}
346
347impl SqlExpr {
348    /// 服务端当前 Unix 时间戳(秒):MySQL `UNIX_TIMESTAMP()`,
349    /// PostgreSQL `EXTRACT(EPOCH FROM now())`。
350    pub fn unix_timestamp() -> Self {
351        Self::UnixTimestamp
352    }
353
354    /// 服务端当前 Unix 时间戳加 `seconds` 秒;`seconds` 以绑定参数传递,可正可负。
355    pub fn unix_timestamp_add(seconds: i64) -> Self {
356        Self::UnixTimestampAdd(seconds)
357    }
358
359    /// 渲染 MySQL 片段,返回(固定 SQL 片段,可选的 i64 绑定参数)。
360    #[cfg(feature = "mysql")]
361    pub(crate) fn mysql_render(&self) -> (&'static str, Option<i64>) {
362        match self {
363            Self::UnixTimestamp => ("UNIX_TIMESTAMP()", None),
364            Self::UnixTimestampAdd(seconds) => ("UNIX_TIMESTAMP() + ?", Some(*seconds)),
365        }
366    }
367
368    /// 渲染 PostgreSQL 片段;`placeholder` 为携带参数时 `$n` 占位符的序号。
369    ///
370    /// PostgreSQL 构造器尚未接入表达式入口,此渲染当前仅由单元测试覆盖,
371    /// 待 PG 侧需要时直接复用。
372    #[cfg(feature = "postgres")]
373    #[allow(dead_code)]
374    pub(crate) fn postgres_render(&self, placeholder: usize) -> (String, Option<i64>) {
375        match self {
376            Self::UnixTimestamp => ("EXTRACT(EPOCH FROM now())".to_string(), None),
377            Self::UnixTimestampAdd(seconds) => (
378                format!("EXTRACT(EPOCH FROM now()) + ${placeholder}"),
379                Some(*seconds),
380            ),
381        }
382    }
383}
384
385#[cfg(any(feature = "mysql", feature = "postgres"))]
386fn quote_qualified(value: &str, quote: char) -> String {
387    value
388        .split('.')
389        .map(|segment| format!("{quote}{segment}{quote}"))
390        .collect::<Vec<_>>()
391        .join(".")
392}
393
394#[cfg(any(feature = "mysql", feature = "postgres"))]
395impl SortOrder {
396    pub(crate) const fn is_ascending(self) -> bool {
397        matches!(self, Self::Asc)
398    }
399}
400
401#[cfg(test)]
402#[allow(clippy::expect_used)]
403mod tests {
404    use super::*;
405
406    #[test]
407    fn controlled_references_validate_once_and_render_both_dialects() {
408        let table = TableRef::new("org_user").expect("合法表名应创建成功");
409        let field = FieldRef::new("org_user.username").expect("合法限定字段应创建成功");
410
411        assert_eq!(table.as_str(), "org_user");
412        assert_eq!(field.as_str(), "org_user.username");
413        #[cfg(feature = "mysql")]
414        assert_eq!(field.mysql_quoted(), "`org_user`.`username`");
415        #[cfg(feature = "postgres")]
416        assert_eq!(field.postgres_quoted(), "\"org_user\".\"username\"");
417    }
418
419    #[test]
420    fn controlled_references_reject_expressions_and_injection_payloads() {
421        for invalid in ["", "users;DROP", "COUNT(*)", "users.id.extra", "用户"] {
422            assert!(TableRef::new(invalid).is_err());
423            assert!(FieldRef::new(invalid).is_err());
424        }
425    }
426
427    #[test]
428    fn server_expressions_render_fixed_fragments_with_bound_dynamic_parts() {
429        #[cfg(feature = "mysql")]
430        {
431            assert_eq!(
432                SqlExpr::unix_timestamp().mysql_render(),
433                ("UNIX_TIMESTAMP()", None)
434            );
435            assert_eq!(
436                SqlExpr::unix_timestamp_add(900).mysql_render(),
437                ("UNIX_TIMESTAMP() + ?", Some(900))
438            );
439            // 负偏移同样走绑定参数,片段保持固定。
440            assert_eq!(
441                SqlExpr::unix_timestamp_add(-30).mysql_render(),
442                ("UNIX_TIMESTAMP() + ?", Some(-30))
443            );
444        }
445        #[cfg(feature = "postgres")]
446        {
447            assert_eq!(
448                SqlExpr::unix_timestamp().postgres_render(1),
449                ("EXTRACT(EPOCH FROM now())".to_string(), None)
450            );
451            assert_eq!(
452                SqlExpr::unix_timestamp_add(900).postgres_render(3),
453                ("EXTRACT(EPOCH FROM now()) + $3".to_string(), Some(900))
454            );
455        }
456    }
457
458    #[cfg(feature = "mysql")]
459    #[tokio::test]
460    async fn mysql_query_builder_consumes_controlled_references_without_value_interpolation() {
461        let pool = sqlx::mysql::MySqlPoolOptions::new()
462            .connect_lazy("mysql://root:test@127.0.0.1:3306/test")
463            .expect("lazy MySQL pool 应创建成功");
464        let database = crate::Database::from_pool(pool, crate::DatabaseConfig::default())
465            .expect("默认 MySQL 配置应有效");
466        let table = TableRef::new("org_user").expect("固定表名有效");
467        let username = FieldRef::new("username").expect("固定字段名有效");
468
469        let sql = database
470            .table(&table)
471            .field(&username)
472            .where_and(&username, CompareOp::Eq, "alice")
473            .expect("固定受控条件应合法")
474            .order(&username, SortOrder::Asc)
475            .try_to_sql()
476            .expect("受控 MySQL 查询应生成 SQL");
477
478        assert!(sql.contains("`org_user`"));
479        assert!(sql.contains("`username`"));
480        assert!(sql.contains('?'));
481        assert!(!sql.contains("alice"));
482    }
483
484    #[cfg(feature = "postgres")]
485    #[tokio::test]
486    async fn postgres_query_builder_consumes_controlled_references_without_value_interpolation() {
487        let pool = sqlx::postgres::PgPoolOptions::new()
488            .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
489            .expect("lazy PostgreSQL pool 应创建成功");
490        let database = crate::PgDatabase::from_pool(pool, crate::PgDatabaseConfig::default())
491            .expect("默认 PostgreSQL 配置应有效");
492        let table = TableRef::new("org_user").expect("固定表名有效");
493        let username = FieldRef::new("username").expect("固定字段名有效");
494
495        let sql = database
496            .table(&table)
497            .field(&username)
498            .where_and(&username, CompareOp::Eq, "alice")
499            .expect("固定受控条件应合法")
500            .order(&username, SortOrder::Desc)
501            .try_to_sql()
502            .expect("受控 PostgreSQL 查询应生成 SQL");
503
504        assert!(sql.contains("\"org_user\""));
505        assert!(sql.contains("\"username\""));
506        assert!(sql.contains("$1"));
507        assert!(!sql.contains("alice"));
508    }
509}