use crate::sql_types::{Identifier, QualifiedIdentifier};
use crate::DbError;
use std::fmt;
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub struct TableRef {
raw: String,
}
impl TableRef {
pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
let raw = value.into();
Identifier::parse(&raw)?;
Ok(Self { raw })
}
pub fn as_str(&self) -> &str {
&self.raw
}
#[doc(hidden)]
pub fn __from_validated_literal(value: &'static str) -> Self {
Self {
raw: value.to_string(),
}
}
#[doc(hidden)]
pub fn __from_validated_owned(raw: String) -> Self {
Self { raw }
}
}
impl fmt::Display for TableRef {
fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
formatter.write_str(&self.raw)
}
}
#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub struct FieldRef {
raw: String,
#[cfg(feature = "mysql")]
mysql: String,
#[cfg(feature = "postgres")]
postgres: String,
}
impl FieldRef {
pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
let raw = value.into();
let identifier = QualifiedIdentifier::parse(&raw)?;
let _ = &identifier;
Ok(Self {
#[cfg(feature = "mysql")]
mysql: identifier.render('`'),
#[cfg(feature = "postgres")]
postgres: identifier.render('"'),
raw,
})
}
pub fn as_str(&self) -> &str {
&self.raw
}
#[doc(hidden)]
pub fn __from_validated_literal(value: &'static str) -> Self {
Self {
#[cfg(feature = "mysql")]
mysql: value
.split('.')
.map(|part| format!("`{part}`"))
.collect::<Vec<_>>()
.join("."),
#[cfg(feature = "postgres")]
postgres: value
.split('.')
.map(|part| format!("\"{part}\""))
.collect::<Vec<_>>()
.join("."),
raw: value.to_string(),
}
}
#[doc(hidden)]
pub fn __from_validated_owned(raw: String) -> Self {
Self {
#[cfg(feature = "mysql")]
mysql: raw
.split('.')
.map(|part| format!("`{part}`"))
.collect::<Vec<_>>()
.join("."),
#[cfg(feature = "postgres")]
postgres: raw
.split('.')
.map(|part| format!("\"{part}\""))
.collect::<Vec<_>>()
.join("."),
raw,
}
}
#[cfg(feature = "mysql")]
#[doc(hidden)]
pub fn mysql_quoted(&self) -> &str {
&self.mysql
}
#[cfg(feature = "postgres")]
#[doc(hidden)]
pub fn postgres_quoted(&self) -> &str {
&self.postgres
}
}
#[doc(hidden)]
pub const fn __validate_table_literal(value: &str) {
validate_literal(value, false);
}
#[doc(hidden)]
pub const fn __validate_field_literal(value: &str) {
validate_literal(value, true);
}
const fn validate_literal(value: &str, qualified: bool) {
let bytes = value.as_bytes();
assert!(!bytes.is_empty(), "SQL identifier cannot be empty");
let mut index = 0;
let mut segment_start = true;
let mut dots = 0;
while index < bytes.len() {
let byte = bytes[index];
if byte == b'.' {
assert!(qualified, "table identifier cannot be qualified");
assert!(!segment_start, "identifier segment cannot be empty");
dots += 1;
assert!(dots <= 1, "field identifier accepts at most table.field");
segment_start = true;
} else if segment_start {
assert!(
byte == b'_' || byte.is_ascii_alphabetic(),
"identifier must start with ASCII letter or underscore"
);
segment_start = false;
} else {
assert!(
byte == b'_' || byte.is_ascii_alphanumeric(),
"identifier contains invalid character"
);
}
index += 1;
}
assert!(!segment_start, "identifier segment cannot be empty");
}
impl fmt::Display for FieldRef {
fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
formatter.write_str(&self.raw)
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
#[non_exhaustive]
pub enum CompareOp {
Eq,
Ne,
Gt,
Lt,
Gte,
Lte,
Like,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
pub enum SortOrder {
Asc,
Desc,
}
#[derive(Debug, Clone, PartialEq)]
pub enum Predicate {
Compare(FieldRef, CompareOp, serde_json::Value),
In(FieldRef, Vec<serde_json::Value>),
NotIn(FieldRef, Vec<serde_json::Value>),
Between(FieldRef, serde_json::Value, serde_json::Value),
IsNull(FieldRef),
IsNotNull(FieldRef),
And(Vec<Predicate>),
Or(Vec<Predicate>),
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct SelectExpr {
aggregate: Aggregate,
field: Option<FieldRef>,
alias: Option<FieldRef>,
cast_double: bool,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum Aggregate {
Count,
Sum,
Avg,
Min,
Max,
}
impl SelectExpr {
pub fn count_all() -> Self {
Self {
aggregate: Aggregate::Count,
field: None,
alias: None,
cast_double: false,
}
}
pub fn count(field: &FieldRef) -> Self {
Self::new(Aggregate::Count, field)
}
pub fn sum(field: &FieldRef) -> Self {
Self::new(Aggregate::Sum, field)
}
pub fn avg(field: &FieldRef) -> Self {
Self::new(Aggregate::Avg, field)
}
pub fn min(field: &FieldRef) -> Self {
Self::new(Aggregate::Min, field)
}
pub fn max(field: &FieldRef) -> Self {
Self::new(Aggregate::Max, field)
}
fn new(aggregate: Aggregate, field: &FieldRef) -> Self {
Self {
aggregate,
field: Some(field.clone()),
alias: None,
cast_double: false,
}
}
pub fn cast_double(mut self) -> Self {
self.cast_double = true;
self
}
pub fn alias(mut self, alias: &FieldRef) -> Self {
self.alias = Some(alias.clone());
self
}
#[cfg(feature = "mysql")]
pub(crate) fn mysql_sql(&self) -> String {
self.render('`', "DOUBLE")
}
#[cfg(feature = "postgres")]
pub(crate) fn postgres_sql(&self) -> String {
self.render('"', "DOUBLE PRECISION")
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
fn render(&self, quote: char, double_type: &str) -> String {
let function = match self.aggregate {
Aggregate::Count => "COUNT",
Aggregate::Sum => "SUM",
Aggregate::Avg => "AVG",
Aggregate::Min => "MIN",
Aggregate::Max => "MAX",
};
let operand = self.field.as_ref().map_or_else(
|| "*".to_string(),
|field| quote_qualified(field.as_str(), quote),
);
let aggregate = format!("{function}({operand})");
let expression = if self.cast_double {
format!("CAST({aggregate} AS {double_type})")
} else {
aggregate
};
self.alias.as_ref().map_or(expression.clone(), |alias| {
format!("{expression} AS {}", quote_qualified(alias.as_str(), quote))
})
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
#[non_exhaustive]
pub enum SqlExpr {
UnixTimestamp,
UnixTimestampAdd(i64),
}
impl SqlExpr {
pub fn unix_timestamp() -> Self {
Self::UnixTimestamp
}
pub fn unix_timestamp_add(seconds: i64) -> Self {
Self::UnixTimestampAdd(seconds)
}
#[cfg(feature = "mysql")]
pub(crate) fn mysql_render(&self) -> (&'static str, Option<i64>) {
match self {
Self::UnixTimestamp => ("UNIX_TIMESTAMP()", None),
Self::UnixTimestampAdd(seconds) => ("UNIX_TIMESTAMP() + ?", Some(*seconds)),
}
}
#[cfg(feature = "postgres")]
#[allow(dead_code)]
pub(crate) fn postgres_render(&self, placeholder: usize) -> (String, Option<i64>) {
match self {
Self::UnixTimestamp => ("EXTRACT(EPOCH FROM now())".to_string(), None),
Self::UnixTimestampAdd(seconds) => (
format!("EXTRACT(EPOCH FROM now()) + ${placeholder}"),
Some(*seconds),
),
}
}
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
fn quote_qualified(value: &str, quote: char) -> String {
value
.split('.')
.map(|segment| format!("{quote}{segment}{quote}"))
.collect::<Vec<_>>()
.join(".")
}
#[cfg(any(feature = "mysql", feature = "postgres"))]
impl SortOrder {
pub(crate) const fn is_ascending(self) -> bool {
matches!(self, Self::Asc)
}
}
#[cfg(test)]
#[allow(clippy::expect_used)]
mod tests {
use super::*;
#[test]
fn controlled_references_validate_once_and_render_both_dialects() {
let table = TableRef::new("org_user").expect("合法表名应创建成功");
let field = FieldRef::new("org_user.username").expect("合法限定字段应创建成功");
assert_eq!(table.as_str(), "org_user");
assert_eq!(field.as_str(), "org_user.username");
#[cfg(feature = "mysql")]
assert_eq!(field.mysql_quoted(), "`org_user`.`username`");
#[cfg(feature = "postgres")]
assert_eq!(field.postgres_quoted(), "\"org_user\".\"username\"");
}
#[test]
fn controlled_references_reject_expressions_and_injection_payloads() {
for invalid in ["", "users;DROP", "COUNT(*)", "users.id.extra", "用户"] {
assert!(TableRef::new(invalid).is_err());
assert!(FieldRef::new(invalid).is_err());
}
}
#[test]
fn server_expressions_render_fixed_fragments_with_bound_dynamic_parts() {
#[cfg(feature = "mysql")]
{
assert_eq!(
SqlExpr::unix_timestamp().mysql_render(),
("UNIX_TIMESTAMP()", None)
);
assert_eq!(
SqlExpr::unix_timestamp_add(900).mysql_render(),
("UNIX_TIMESTAMP() + ?", Some(900))
);
assert_eq!(
SqlExpr::unix_timestamp_add(-30).mysql_render(),
("UNIX_TIMESTAMP() + ?", Some(-30))
);
}
#[cfg(feature = "postgres")]
{
assert_eq!(
SqlExpr::unix_timestamp().postgres_render(1),
("EXTRACT(EPOCH FROM now())".to_string(), None)
);
assert_eq!(
SqlExpr::unix_timestamp_add(900).postgres_render(3),
("EXTRACT(EPOCH FROM now()) + $3".to_string(), Some(900))
);
}
}
#[cfg(feature = "mysql")]
#[tokio::test]
async fn mysql_query_builder_consumes_controlled_references_without_value_interpolation() {
let pool = sqlx::mysql::MySqlPoolOptions::new()
.connect_lazy("mysql://root:test@127.0.0.1:3306/test")
.expect("lazy MySQL pool 应创建成功");
let database = crate::Database::from_pool(pool, crate::DatabaseConfig::default())
.expect("默认 MySQL 配置应有效");
let table = TableRef::new("org_user").expect("固定表名有效");
let username = FieldRef::new("username").expect("固定字段名有效");
let sql = database
.table(&table)
.field(&username)
.where_and(&username, CompareOp::Eq, "alice")
.expect("固定受控条件应合法")
.order(&username, SortOrder::Asc)
.try_to_sql()
.expect("受控 MySQL 查询应生成 SQL");
assert!(sql.contains("`org_user`"));
assert!(sql.contains("`username`"));
assert!(sql.contains('?'));
assert!(!sql.contains("alice"));
}
#[cfg(feature = "postgres")]
#[tokio::test]
async fn postgres_query_builder_consumes_controlled_references_without_value_interpolation() {
let pool = sqlx::postgres::PgPoolOptions::new()
.connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
.expect("lazy PostgreSQL pool 应创建成功");
let database = crate::PgDatabase::from_pool(pool, crate::PgDatabaseConfig::default())
.expect("默认 PostgreSQL 配置应有效");
let table = TableRef::new("org_user").expect("固定表名有效");
let username = FieldRef::new("username").expect("固定字段名有效");
let sql = database
.table(&table)
.field(&username)
.where_and(&username, CompareOp::Eq, "alice")
.expect("固定受控条件应合法")
.order(&username, SortOrder::Desc)
.try_to_sql()
.expect("受控 PostgreSQL 查询应生成 SQL");
assert!(sql.contains("\"org_user\""));
assert!(sql.contains("\"username\""));
assert!(sql.contains("$1"));
assert!(!sql.contains("alice"));
}
}