1use crate::sql_types::{Identifier, QualifiedIdentifier};
4use crate::DbError;
5use std::fmt;
6
7#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
9pub struct TableRef {
10 raw: String,
11}
12
13impl TableRef {
14 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 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#[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 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 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#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
175#[non_exhaustive]
176pub enum CompareOp {
177 Eq,
179 Ne,
181 Gt,
183 Lt,
185 Gte,
187 Lte,
189 Like,
191}
192
193#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
195pub enum SortOrder {
196 Asc,
198 Desc,
200}
201
202#[derive(Debug, Clone, PartialEq)]
204pub enum Predicate {
205 Compare(FieldRef, CompareOp, serde_json::Value),
207 In(FieldRef, Vec<serde_json::Value>),
209 NotIn(FieldRef, Vec<serde_json::Value>),
211 Between(FieldRef, serde_json::Value, serde_json::Value),
213 IsNull(FieldRef),
215 IsNotNull(FieldRef),
217 And(Vec<Predicate>),
219 Or(Vec<Predicate>),
221}
222
223#[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 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 pub fn count(field: &FieldRef) -> Self {
254 Self::new(Aggregate::Count, field)
255 }
256
257 pub fn sum(field: &FieldRef) -> Self {
259 Self::new(Aggregate::Sum, field)
260 }
261
262 pub fn avg(field: &FieldRef) -> Self {
264 Self::new(Aggregate::Avg, field)
265 }
266
267 pub fn min(field: &FieldRef) -> Self {
269 Self::new(Aggregate::Min, field)
270 }
271
272 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 pub fn cast_double(mut self) -> Self {
288 self.cast_double = true;
289 self
290 }
291
292 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#[derive(Debug, Clone, PartialEq, Eq)]
339#[non_exhaustive]
340pub enum SqlExpr {
341 UnixTimestamp,
343 UnixTimestampAdd(i64),
345}
346
347impl SqlExpr {
348 pub fn unix_timestamp() -> Self {
351 Self::UnixTimestamp
352 }
353
354 pub fn unix_timestamp_add(seconds: i64) -> Self {
356 Self::UnixTimestampAdd(seconds)
357 }
358
359 #[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 #[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 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}