mik_sql/
validate.rs

1//! Security validation layer for query filters and SQL identifiers.
2//!
3//! This module provides validation for:
4//! - User-provided filters (field whitelisting, operator blacklisting)
5//! - SQL identifiers (table names, column names) to prevent injection
6//! - Nesting depth limits for complex queries
7//!
8//! # Example
9//!
10//! ```ignore
11//! use mik_sql::{FilterValidator, merge_filters, Filter, Operator, Value};
12//!
13//! // Create validator with security rules
14//! let validator = FilterValidator::new()
15//!     .allow_fields(&["name", "email", "status"])
16//!     .deny_operators(&[Operator::Regex, Operator::ILike])
17//!     .max_depth(3);
18//!
19//! // System/policy filters (trusted, no validation)
20//! let trusted = vec![
21//!     Filter { field: "org_id".into(), op: Operator::Eq, value: Value::Int(123) },
22//!     Filter { field: "deleted_at".into(), op: Operator::Eq, value: Value::Null },
23//! ];
24//!
25//! // User-provided filters (validated)
26//! let user = vec![
27//!     Filter { field: "status".into(), op: Operator::Eq, value: Value::String("active".into()) },
28//! ];
29//!
30//! // Merge with validation
31//! let filters = merge_filters(trusted, user, &validator)?;
32//! ```
33
34use crate::{Filter, Operator, Value};
35use std::fmt;
36
37// ═══════════════════════════════════════════════════════════════════════════
38// SQL IDENTIFIER VALIDATION
39// ═══════════════════════════════════════════════════════════════════════════
40
41/// Maximum length for SQL identifiers (`PostgreSQL` limit is 63).
42const MAX_IDENTIFIER_LENGTH: usize = 63;
43
44/// Validate that a string is a safe SQL identifier.
45///
46/// A valid SQL identifier:
47/// - Starts with a letter (a-z, A-Z) or underscore
48/// - Contains only letters, digits (0-9), and underscores
49/// - Is not empty and not longer than 63 characters
50///
51/// This prevents SQL injection attacks by rejecting:
52/// - Special characters (quotes, semicolons, etc.)
53/// - SQL keywords as standalone identifiers
54/// - Unicode characters that could cause confusion
55///
56/// # Examples
57///
58/// ```
59/// use mik_sql::is_valid_sql_identifier;
60///
61/// assert!(is_valid_sql_identifier("users"));
62/// assert!(is_valid_sql_identifier("user_id"));
63/// assert!(is_valid_sql_identifier("_private"));
64/// assert!(is_valid_sql_identifier("Table123"));
65///
66/// // Invalid identifiers
67/// assert!(!is_valid_sql_identifier(""));           // empty
68/// assert!(!is_valid_sql_identifier("123abc"));     // starts with digit
69/// assert!(!is_valid_sql_identifier("user-name"));  // contains hyphen
70/// assert!(!is_valid_sql_identifier("user.id"));    // contains dot
71/// assert!(!is_valid_sql_identifier("user; DROP")); // contains special chars
72/// ```
73#[inline]
74#[must_use]
75pub fn is_valid_sql_identifier(s: &str) -> bool {
76    if s.is_empty() || s.len() > MAX_IDENTIFIER_LENGTH {
77        return false;
78    }
79
80    let mut chars = s.chars();
81
82    // First character must be letter or underscore
83    match chars.next() {
84        Some(c) if c.is_ascii_alphabetic() || c == '_' => {},
85        _ => return false,
86    }
87
88    // Rest must be letters, digits, or underscores
89    chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
90}
91
92/// Assert that a string is a valid SQL identifier.
93///
94/// # Panics
95///
96/// Panics with a descriptive error if the identifier is invalid.
97/// This is intended for programmer errors (invalid table/column names in code),
98/// not for user input validation.
99///
100/// # Examples
101///
102/// ```
103/// use mik_sql::assert_valid_sql_identifier;
104///
105/// assert_valid_sql_identifier("users", "table");    // OK
106/// assert_valid_sql_identifier("user_id", "column"); // OK
107/// ```
108///
109/// ```should_panic
110/// use mik_sql::assert_valid_sql_identifier;
111///
112/// assert_valid_sql_identifier("user; DROP TABLE", "table"); // Panics!
113/// ```
114#[inline]
115pub fn assert_valid_sql_identifier(s: &str, context: &str) {
116    assert!(
117        is_valid_sql_identifier(s),
118        "Invalid SQL {context} name '{s}': must start with letter/underscore, \
119             contain only ASCII alphanumeric/underscore, and be 1-63 chars"
120    );
121}
122
123/// Validate a SQL expression for computed fields.
124///
125/// Computed field expressions are dangerous because they're inserted directly
126/// into SQL. This function performs defense-in-depth validation to catch
127/// injection attempts, but **cannot provide complete protection**.
128///
129/// # Security Model
130///
131/// This validation is a safety net, not a security boundary. It catches:
132/// - Obvious injection patterns (comments, semicolons, SQL keywords)
133/// - Common attack vectors
134///
135/// It **cannot** catch:
136/// - All possible SQL injection variants
137/// - Database-specific syntax
138/// - Encoded or obfuscated attacks
139///
140/// **CRITICAL**: Only use computed fields with trusted expressions from code.
141/// Never pass user input to computed field expressions, even with validation.
142///
143/// # Valid expressions
144///
145/// - Simple field references: `first_name`, `price`
146/// - Arithmetic: `quantity * price`
147/// - String concatenation: `first_name || ' ' || last_name`
148/// - Functions: `COALESCE(nickname, name)`, `UPPER(name)`
149///
150/// # Invalid expressions (rejected)
151///
152/// - Comments: `--`, `/*`, `*/`
153/// - Statement terminators: `;`
154/// - SQL keywords: SELECT, INSERT, UPDATE, DELETE, DROP, etc.
155/// - System functions: `pg_`, `sqlite_`
156///
157/// # Examples
158///
159/// ```
160/// use mik_sql::is_valid_sql_expression;
161///
162/// assert!(is_valid_sql_expression("first_name || ' ' || last_name"));
163/// assert!(is_valid_sql_expression("quantity * price"));
164/// assert!(is_valid_sql_expression("COALESCE(nickname, name)"));
165///
166/// // Dangerous patterns are rejected
167/// assert!(!is_valid_sql_expression("1; DROP TABLE users"));
168/// assert!(!is_valid_sql_expression("name -- comment"));
169/// assert!(!is_valid_sql_expression("/* comment */ name"));
170/// ```
171#[inline]
172#[must_use]
173pub fn is_valid_sql_expression(s: &str) -> bool {
174    // Empty or oversized expressions are invalid
175    if s.is_empty() || s.len() > 1000 {
176        return false;
177    }
178
179    // No SQL comments
180    if s.contains("--") || s.contains("/*") || s.contains("*/") {
181        return false;
182    }
183
184    // No statement terminators
185    if s.contains(';') {
186        return false;
187    }
188
189    // No backticks (MySQL identifier quotes that could be used for injection)
190    if s.contains('`') {
191        return false;
192    }
193
194    // Check for dangerous SQL keywords using word boundary detection
195    let lower = s.to_ascii_lowercase();
196
197    // Dangerous DML/DDL keywords and functions
198    const DANGEROUS_KEYWORDS: &[&str] = &[
199        // DML/DDL statements
200        "select",
201        "insert",
202        "update",
203        "delete",
204        "drop",
205        "truncate",
206        "alter",
207        "create",
208        "grant",
209        "revoke",
210        "exec",
211        "execute",
212        "union",
213        "into",
214        "from",
215        "where",
216        "having",
217        "group",
218        "order",
219        "limit",
220        "offset",
221        "fetch",
222        "returning",
223        // Dangerous functions (timing attacks, DoS)
224        "sleep",
225        "benchmark",
226        "waitfor",
227        "pg_sleep",
228        "dbms_lock",
229        // File/network operations
230        "load_file",
231        "into_outfile",
232        "into_dumpfile",
233        // Encoding/conversion functions that could bypass keyword detection
234        "chr",
235        "char",
236        "ascii",
237        "unicode",
238        "hex",
239        "unhex",
240        "convert",
241        "cast",
242        "encode",
243        "decode",
244    ];
245
246    for keyword in DANGEROUS_KEYWORDS {
247        if contains_sql_keyword(&lower, keyword) {
248            return false;
249        }
250    }
251
252    // Block system catalog access patterns
253    if lower.contains("pg_")
254        || lower.contains("sqlite_")
255        || lower.contains("information_schema")
256        || lower.contains("sys.")
257    {
258        return false;
259    }
260
261    // Block hex escapes that could bypass other checks
262    if lower.contains("0x") || lower.contains("\\x") {
263        return false;
264    }
265
266    true
267}
268
269/// Check if a string contains a SQL keyword as a whole word.
270///
271/// This prevents false positives like "update" in "`last_updated`".
272#[inline]
273fn contains_sql_keyword(haystack: &str, keyword: &str) -> bool {
274    let bytes = haystack.as_bytes();
275    let kw_bytes = keyword.as_bytes();
276    let kw_len = kw_bytes.len();
277
278    if kw_len == 0 || bytes.len() < kw_len {
279        return false;
280    }
281
282    for i in 0..=(bytes.len() - kw_len) {
283        // Check if keyword matches at this position
284        if &bytes[i..i + kw_len] == kw_bytes {
285            // Check word boundaries (parentheses fix operator precedence: && binds tighter than ||)
286            let before_ok =
287                i == 0 || (!bytes[i - 1].is_ascii_alphanumeric() && bytes[i - 1] != b'_');
288            let after_ok = i + kw_len == bytes.len()
289                || (!bytes[i + kw_len].is_ascii_alphanumeric() && bytes[i + kw_len] != b'_');
290
291            if before_ok && after_ok {
292                return true;
293            }
294        }
295    }
296
297    false
298}
299
300/// Assert that a SQL expression is valid for computed fields.
301///
302/// # Panics
303///
304/// Panics if the expression contains dangerous patterns.
305#[inline]
306pub fn assert_valid_sql_expression(s: &str, context: &str) {
307    assert!(
308        is_valid_sql_expression(s),
309        "Invalid SQL expression for {context}: '{s}' contains dangerous patterns \
310             (comments, semicolons, or SQL keywords)"
311    );
312}
313
314/// Maximum number of value nodes to validate (defense-in-depth).
315const MAX_VALUE_NODES: usize = 10000;
316
317/// Validation configuration for user-provided filters.
318///
319/// Provides four layers of security:
320/// 1. Field whitelist - only specific fields can be queried
321/// 2. Operator blacklist - dangerous operators can be denied
322/// 3. Nesting depth limit - prevent complex nested queries
323/// 4. Total node count limit - prevent DoS via large arrays
324#[derive(Debug, Clone)]
325pub struct FilterValidator {
326    /// Allowed field names (whitelist). Empty = allow all fields.
327    pub allowed_fields: Vec<String>,
328    /// Denied operators (blacklist).
329    pub denied_operators: Vec<Operator>,
330    /// Maximum nesting depth for complex filters.
331    pub max_depth: usize,
332}
333
334impl FilterValidator {
335    /// Create a new validator with secure defaults.
336    ///
337    /// Defaults:
338    /// - No field restrictions (allow all)
339    /// - Denies `Regex` operator (`ReDoS` prevention)
340    /// - Max nesting depth: 5
341    ///
342    /// This is the recommended constructor for user-facing filters.
343    /// For internal/trusted filters where you need all operators,
344    /// use [`permissive()`](Self::permissive).
345    ///
346    /// # Example
347    ///
348    /// ```
349    /// use mik_sql::FilterValidator;
350    ///
351    /// let validator = FilterValidator::new()
352    ///     .allow_fields(&["name", "email", "status"]);
353    /// ```
354    #[must_use]
355    pub fn new() -> Self {
356        Self {
357            allowed_fields: Vec::new(),
358            denied_operators: vec![crate::Operator::Regex],
359            max_depth: 5,
360        }
361    }
362
363    /// Create a permissive validator that allows all operators.
364    ///
365    /// **Warning:** Only use this for trusted/internal filters, never for
366    /// user-provided input. The `Regex` operator can cause `ReDoS` attacks.
367    ///
368    /// # Example
369    ///
370    /// ```
371    /// use mik_sql::FilterValidator;
372    ///
373    /// // Only for trusted internal filters!
374    /// let validator = FilterValidator::permissive();
375    /// ```
376    #[must_use]
377    pub fn permissive() -> Self {
378        Self {
379            allowed_fields: Vec::new(),
380            denied_operators: Vec::new(),
381            max_depth: 5,
382        }
383    }
384
385    /// Set allowed fields (whitelist).
386    ///
387    /// Only fields in this list can be used in user filters.
388    /// If empty, all fields are allowed.
389    #[must_use]
390    pub fn allow_fields(mut self, fields: &[&str]) -> Self {
391        self.allowed_fields = fields.iter().map(|s| (*s).to_string()).collect();
392        self
393    }
394
395    /// Set denied operators (blacklist).
396    ///
397    /// These operators cannot be used in user filters.
398    /// Useful for blocking regex, pattern matching, or other expensive operations.
399    #[must_use]
400    pub fn deny_operators(mut self, ops: &[Operator]) -> Self {
401        self.denied_operators = ops.to_vec();
402        self
403    }
404
405    /// Set maximum nesting depth.
406    ///
407    /// Prevents complex nested queries that could impact performance.
408    /// Default is 5.
409    #[must_use]
410    pub fn max_depth(mut self, depth: usize) -> Self {
411        self.max_depth = depth;
412        self
413    }
414
415    /// Validate a filter against the configured rules.
416    ///
417    /// Returns an error if:
418    /// - Field is not in the allowed list (when list is not empty)
419    /// - Operator is in the denied list
420    /// - Array nesting depth exceeds maximum
421    pub fn validate(&self, filter: &Filter) -> Result<(), ValidationError> {
422        self.validate_with_depth(filter, 0)
423    }
424
425    /// Internal validation with depth tracking.
426    fn validate_with_depth(&self, filter: &Filter, depth: usize) -> Result<(), ValidationError> {
427        // Check nesting depth
428        if depth > self.max_depth {
429            return Err(ValidationError::NestingTooDeep {
430                max: self.max_depth,
431                actual: depth,
432            });
433        }
434
435        // Check field whitelist (only if not empty)
436        if !self.allowed_fields.is_empty() && !self.allowed_fields.contains(&filter.field) {
437            return Err(ValidationError::FieldNotAllowed {
438                field: filter.field.clone(),
439                allowed: self.allowed_fields.clone(),
440            });
441        }
442
443        // Check operator blacklist
444        if self.denied_operators.contains(&filter.op) {
445            return Err(ValidationError::OperatorDenied {
446                operator: filter.op,
447                field: filter.field.clone(),
448            });
449        }
450
451        // Recursively validate array values (for complex nested filters)
452        if let Value::Array(values) = &filter.value {
453            let mut node_count = 0;
454            for value in values {
455                self.validate_value_with_count(value, depth + 1, &mut node_count)?;
456            }
457        }
458
459        Ok(())
460    }
461
462    /// Validate nested values in arrays with node count tracking.
463    fn validate_value_with_count(
464        &self,
465        value: &Value,
466        depth: usize,
467        count: &mut usize,
468    ) -> Result<(), ValidationError> {
469        *count += 1;
470        if *count > MAX_VALUE_NODES {
471            return Err(ValidationError::TooManyNodes {
472                max: MAX_VALUE_NODES,
473            });
474        }
475
476        if depth > self.max_depth {
477            return Err(ValidationError::NestingTooDeep {
478                max: self.max_depth,
479                actual: depth,
480            });
481        }
482
483        if let Value::Array(values) = value {
484            for v in values {
485                self.validate_value_with_count(v, depth + 1, count)?;
486            }
487        }
488
489        Ok(())
490    }
491}
492
493impl Default for FilterValidator {
494    fn default() -> Self {
495        Self::new()
496    }
497}
498
499/// Validation error types.
500#[derive(Debug, Clone, PartialEq)]
501pub enum ValidationError {
502    /// Field is not in the allowed list.
503    FieldNotAllowed { field: String, allowed: Vec<String> },
504    /// Operator is denied for this field.
505    OperatorDenied { operator: Operator, field: String },
506    /// Nesting depth exceeds maximum.
507    NestingTooDeep { max: usize, actual: usize },
508    /// Too many value nodes (DoS prevention).
509    TooManyNodes { max: usize },
510}
511
512impl fmt::Display for ValidationError {
513    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
514        match self {
515            Self::FieldNotAllowed { field, allowed } => {
516                write!(
517                    f,
518                    "Field '{}' is not allowed. Allowed fields: {}",
519                    field,
520                    allowed.join(", ")
521                )
522            },
523            Self::OperatorDenied { operator, field } => {
524                write!(f, "Operator '{operator:?}' is denied for field '{field}'")
525            },
526            Self::NestingTooDeep { max, actual } => {
527                write!(f, "Filter nesting depth {actual} exceeds maximum {max}")
528            },
529            Self::TooManyNodes { max } => {
530                write!(f, "Filter contains too many value nodes (max {max})")
531            },
532        }
533    }
534}
535
536impl std::error::Error for ValidationError {}
537
538/// Merge trusted filters with validated user filters.
539///
540/// This function combines system/policy filters (trusted, no validation)
541/// with user-provided filters (validated against the validator rules).
542///
543/// # Arguments
544///
545/// * `trusted` - System filters (e.g., `org_id`, `tenant_id`, `deleted_at`)
546/// * `user` - User-provided filters from request
547/// * `validator` - Validation rules for user filters
548///
549/// # Returns
550///
551/// Combined filter list with trusted filters first, then validated user filters.
552///
553/// # Example
554///
555/// ```ignore
556/// // System ensures user can only see their org's data
557/// let trusted = vec![
558///     Filter { field: "org_id".into(), op: Operator::Eq, value: Value::Int(123) },
559/// ];
560///
561/// // User wants to filter by status
562/// let user = vec![
563///     Filter { field: "status".into(), op: Operator::Eq, value: Value::String("active".into()) },
564/// ];
565///
566/// let validator = FilterValidator::new().allow_fields(&["status", "name"]);
567/// let all_filters = merge_filters(trusted, user, &validator)?;
568/// // Result: [org_id=123, status='active']
569/// ```
570pub fn merge_filters(
571    trusted: Vec<Filter>,
572    user: Vec<Filter>,
573    validator: &FilterValidator,
574) -> Result<Vec<Filter>, ValidationError> {
575    // Validate all user filters
576    for filter in &user {
577        validator.validate(filter)?;
578    }
579
580    // Combine: trusted first, then user filters
581    let mut result = trusted;
582    result.extend(user);
583    Ok(result)
584}
585
586#[cfg(test)]
587mod tests {
588    use super::*;
589
590    #[test]
591    fn test_validator_default_is_secure() {
592        let validator = FilterValidator::new();
593        assert!(validator.allowed_fields.is_empty());
594        // new() now denies Regex by default for security
595        assert_eq!(validator.denied_operators, vec![Operator::Regex]);
596        assert_eq!(validator.max_depth, 5);
597    }
598
599    #[test]
600    fn test_validator_permissive() {
601        let validator = FilterValidator::permissive();
602        assert!(validator.allowed_fields.is_empty());
603        assert!(validator.denied_operators.is_empty());
604        assert_eq!(validator.max_depth, 5);
605    }
606
607    #[test]
608    fn test_validator_builder() {
609        let validator = FilterValidator::new()
610            .allow_fields(&["name", "email"])
611            .deny_operators(&[Operator::Regex, Operator::ILike])
612            .max_depth(3);
613
614        assert_eq!(validator.allowed_fields.len(), 2);
615        assert_eq!(validator.denied_operators.len(), 2);
616        assert_eq!(validator.max_depth, 3);
617    }
618
619    #[test]
620    fn test_validate_allowed_field() {
621        let validator = FilterValidator::new().allow_fields(&["name", "email", "status"]);
622
623        let filter = Filter {
624            field: "name".into(),
625            op: Operator::Eq,
626            value: Value::String("Alice".into()),
627        };
628
629        assert!(validator.validate(&filter).is_ok());
630    }
631
632    #[test]
633    fn test_validate_disallowed_field() {
634        let validator = FilterValidator::new().allow_fields(&["name", "email"]);
635
636        let filter = Filter {
637            field: "password".into(),
638            op: Operator::Eq,
639            value: Value::String("secret".into()),
640        };
641
642        let result = validator.validate(&filter);
643        assert!(result.is_err());
644
645        match result.unwrap_err() {
646            ValidationError::FieldNotAllowed { field, allowed } => {
647                assert_eq!(field, "password");
648                assert_eq!(allowed.len(), 2);
649            },
650            _ => panic!("Expected FieldNotAllowed error"),
651        }
652    }
653
654    #[test]
655    fn test_validate_empty_whitelist_allows_all() {
656        let validator = FilterValidator::new(); // No field restrictions
657
658        let filter = Filter {
659            field: "any_field".into(),
660            op: Operator::Eq,
661            value: Value::String("value".into()),
662        };
663
664        assert!(validator.validate(&filter).is_ok());
665    }
666
667    #[test]
668    fn test_validate_denied_operator() {
669        let validator = FilterValidator::new()
670            .allow_fields(&["name"])
671            .deny_operators(&[Operator::Regex, Operator::ILike]);
672
673        let filter = Filter {
674            field: "name".into(),
675            op: Operator::Regex,
676            value: Value::String("^A".into()),
677        };
678
679        let result = validator.validate(&filter);
680        assert!(result.is_err());
681
682        match result.unwrap_err() {
683            ValidationError::OperatorDenied { operator, field } => {
684                assert_eq!(operator, Operator::Regex);
685                assert_eq!(field, "name");
686            },
687            _ => panic!("Expected OperatorDenied error"),
688        }
689    }
690
691    #[test]
692    fn test_validate_allowed_operator() {
693        let validator = FilterValidator::new()
694            .allow_fields(&["status"])
695            .deny_operators(&[Operator::Regex]);
696
697        let filter = Filter {
698            field: "status".into(),
699            op: Operator::Eq,
700            value: Value::String("active".into()),
701        };
702
703        assert!(validator.validate(&filter).is_ok());
704    }
705
706    #[test]
707    fn test_validate_nesting_depth() {
708        let validator = FilterValidator::new().max_depth(2);
709
710        // Depth 0 - OK
711        let filter = Filter {
712            field: "tags".into(),
713            op: Operator::In,
714            value: Value::Array(vec![Value::String("rust".into())]),
715        };
716        assert!(validator.validate(&filter).is_ok());
717
718        // Depth 3 - exceeds max
719        let filter_deep = Filter {
720            field: "deep".into(),
721            op: Operator::In,
722            value: Value::Array(vec![Value::Array(vec![Value::Array(vec![Value::String(
723                "too deep".into(),
724            )])])]),
725        };
726        let result = validator.validate(&filter_deep);
727        assert!(result.is_err());
728
729        match result.unwrap_err() {
730            ValidationError::NestingTooDeep { max, actual } => {
731                assert_eq!(max, 2);
732                assert!(actual > max);
733            },
734            _ => panic!("Expected NestingTooDeep error"),
735        }
736    }
737
738    #[test]
739    fn test_merge_filters_success() {
740        let validator = FilterValidator::new().allow_fields(&["status", "name"]);
741
742        let trusted = vec![
743            Filter {
744                field: "org_id".into(),
745                op: Operator::Eq,
746                value: Value::Int(123),
747            },
748            Filter {
749                field: "deleted_at".into(),
750                op: Operator::Eq,
751                value: Value::Null,
752            },
753        ];
754
755        let user = vec![Filter {
756            field: "status".into(),
757            op: Operator::Eq,
758            value: Value::String("active".into()),
759        }];
760
761        let result = merge_filters(trusted, user, &validator);
762        assert!(result.is_ok());
763
764        let filters = result.unwrap();
765        assert_eq!(filters.len(), 3);
766        assert_eq!(filters[0].field, "org_id");
767        assert_eq!(filters[1].field, "deleted_at");
768        assert_eq!(filters[2].field, "status");
769    }
770
771    #[test]
772    fn test_merge_filters_validation_error() {
773        let validator = FilterValidator::new().allow_fields(&["status"]);
774
775        let trusted = vec![Filter {
776            field: "org_id".into(),
777            op: Operator::Eq,
778            value: Value::Int(123),
779        }];
780
781        // User tries to filter on disallowed field
782        let user = vec![Filter {
783            field: "password".into(),
784            op: Operator::Eq,
785            value: Value::String("hack".into()),
786        }];
787
788        let result = merge_filters(trusted, user, &validator);
789        assert!(result.is_err());
790    }
791
792    #[test]
793    fn test_merge_filters_empty_user() {
794        let validator = FilterValidator::new();
795
796        let trusted = vec![Filter {
797            field: "org_id".into(),
798            op: Operator::Eq,
799            value: Value::Int(123),
800        }];
801
802        let user = vec![];
803
804        let result = merge_filters(trusted, user, &validator);
805        assert!(result.is_ok());
806        assert_eq!(result.unwrap().len(), 1);
807    }
808
809    #[test]
810    fn test_merge_filters_empty_trusted() {
811        let validator = FilterValidator::new().allow_fields(&["name"]);
812
813        let trusted = vec![];
814        let user = vec![Filter {
815            field: "name".into(),
816            op: Operator::Eq,
817            value: Value::String("Alice".into()),
818        }];
819
820        let result = merge_filters(trusted, user, &validator);
821        assert!(result.is_ok());
822        assert_eq!(result.unwrap().len(), 1);
823    }
824
825    #[test]
826    fn test_multiple_validation_errors() {
827        let validator = FilterValidator::new()
828            .allow_fields(&["status"])
829            .deny_operators(&[Operator::Regex]);
830
831        // Disallowed field
832        let filter1 = Filter {
833            field: "password".into(),
834            op: Operator::Eq,
835            value: Value::String("x".into()),
836        };
837        assert!(validator.validate(&filter1).is_err());
838
839        // Denied operator
840        let filter2 = Filter {
841            field: "status".into(),
842            op: Operator::Regex,
843            value: Value::String("^A".into()),
844        };
845        assert!(validator.validate(&filter2).is_err());
846    }
847
848    #[test]
849    fn test_validation_error_display() {
850        let err = ValidationError::FieldNotAllowed {
851            field: "password".into(),
852            allowed: vec!["name".into(), "email".into()],
853        };
854        let msg = format!("{}", err);
855        assert!(msg.contains("password"));
856        assert!(msg.contains("name"));
857
858        let err = ValidationError::OperatorDenied {
859            operator: Operator::Regex,
860            field: "name".into(),
861        };
862        let msg = format!("{}", err);
863        assert!(msg.contains("Regex"));
864        assert!(msg.contains("name"));
865
866        let err = ValidationError::NestingTooDeep { max: 3, actual: 5 };
867        let msg = format!("{}", err);
868        assert!(msg.contains("3"));
869        assert!(msg.contains("5"));
870    }
871
872    #[test]
873    fn test_in_operator_validation() {
874        let validator = FilterValidator::new().allow_fields(&["status"]);
875
876        let filter = Filter {
877            field: "status".into(),
878            op: Operator::In,
879            value: Value::Array(vec![
880                Value::String("active".into()),
881                Value::String("pending".into()),
882            ]),
883        };
884
885        assert!(validator.validate(&filter).is_ok());
886    }
887
888    #[test]
889    fn test_not_in_operator_validation() {
890        let validator = FilterValidator::new()
891            .allow_fields(&["status"])
892            .deny_operators(&[Operator::NotIn]);
893
894        let filter = Filter {
895            field: "status".into(),
896            op: Operator::NotIn,
897            value: Value::Array(vec![Value::String("deleted".into())]),
898        };
899
900        assert!(validator.validate(&filter).is_err());
901    }
902
903    #[test]
904    fn test_null_value_validation() {
905        let validator = FilterValidator::new().allow_fields(&["deleted_at"]);
906
907        let filter = Filter {
908            field: "deleted_at".into(),
909            op: Operator::Eq,
910            value: Value::Null,
911        };
912
913        assert!(validator.validate(&filter).is_ok());
914    }
915
916    #[test]
917    fn test_bool_value_validation() {
918        let validator = FilterValidator::new().allow_fields(&["active"]);
919
920        let filter = Filter {
921            field: "active".into(),
922            op: Operator::Eq,
923            value: Value::Bool(true),
924        };
925
926        assert!(validator.validate(&filter).is_ok());
927    }
928
929    #[test]
930    fn test_numeric_value_validation() {
931        let validator = FilterValidator::new().allow_fields(&["age", "price"]);
932
933        let filter1 = Filter {
934            field: "age".into(),
935            op: Operator::Gte,
936            value: Value::Int(18),
937        };
938        assert!(validator.validate(&filter1).is_ok());
939
940        let filter2 = Filter {
941            field: "price".into(),
942            op: Operator::Lt,
943            value: Value::Float(99.99),
944        };
945        assert!(validator.validate(&filter2).is_ok());
946    }
947
948    // ═══════════════════════════════════════════════════════════════════════════
949    // SQL IDENTIFIER VALIDATION TESTS
950    // ═══════════════════════════════════════════════════════════════════════════
951
952    #[test]
953    fn test_valid_sql_identifiers() {
954        use super::is_valid_sql_identifier;
955
956        // Valid identifiers
957        assert!(is_valid_sql_identifier("users"));
958        assert!(is_valid_sql_identifier("user_id"));
959        assert!(is_valid_sql_identifier("_private"));
960        assert!(is_valid_sql_identifier("Table123"));
961        assert!(is_valid_sql_identifier("a"));
962        assert!(is_valid_sql_identifier("_"));
963        assert!(is_valid_sql_identifier("UPPERCASE"));
964        assert!(is_valid_sql_identifier("mixedCase"));
965        assert!(is_valid_sql_identifier("with_123_numbers"));
966    }
967
968    #[test]
969    fn test_invalid_sql_identifiers() {
970        use super::is_valid_sql_identifier;
971
972        // Empty
973        assert!(!is_valid_sql_identifier(""));
974
975        // Starts with digit
976        assert!(!is_valid_sql_identifier("123abc"));
977        assert!(!is_valid_sql_identifier("1"));
978
979        // Contains special characters
980        assert!(!is_valid_sql_identifier("user-name"));
981        assert!(!is_valid_sql_identifier("user.id"));
982        assert!(!is_valid_sql_identifier("user name"));
983        assert!(!is_valid_sql_identifier("user;drop"));
984        assert!(!is_valid_sql_identifier("table'"));
985        assert!(!is_valid_sql_identifier("table\""));
986        assert!(!is_valid_sql_identifier("table`"));
987        assert!(!is_valid_sql_identifier("table("));
988        assert!(!is_valid_sql_identifier("table)"));
989
990        // SQL injection attempts
991        assert!(!is_valid_sql_identifier("users; DROP TABLE"));
992        assert!(!is_valid_sql_identifier("users--"));
993        assert!(!is_valid_sql_identifier("users/*"));
994    }
995
996    #[test]
997    fn test_sql_identifier_length_limit() {
998        use super::is_valid_sql_identifier;
999
1000        // 63 chars = OK (PostgreSQL limit)
1001        let valid_63 = "a".repeat(63);
1002        assert!(is_valid_sql_identifier(&valid_63));
1003
1004        // 64 chars = too long
1005        let invalid_64 = "a".repeat(64);
1006        assert!(!is_valid_sql_identifier(&invalid_64));
1007    }
1008
1009    #[test]
1010    fn test_valid_sql_expressions() {
1011        use super::is_valid_sql_expression;
1012
1013        // Valid expressions
1014        assert!(is_valid_sql_expression("first_name || ' ' || last_name"));
1015        assert!(is_valid_sql_expression("quantity * price"));
1016        assert!(is_valid_sql_expression("COALESCE(nickname, name)"));
1017        assert!(is_valid_sql_expression("age + 1"));
1018        assert!(is_valid_sql_expression("CASE WHEN x > 0 THEN y ELSE z END"));
1019        assert!(is_valid_sql_expression("price * 1.1"));
1020        assert!(is_valid_sql_expression("UPPER(name)"));
1021        assert!(is_valid_sql_expression("LENGTH(description)"));
1022
1023        // Word boundary detection - these contain keywords as substrings but should be allowed
1024        assert!(is_valid_sql_expression("last_updated")); // contains "update"
1025        assert!(is_valid_sql_expression("created_at")); // contains "create"
1026        assert!(is_valid_sql_expression("selected_items")); // contains "select"
1027        assert!(is_valid_sql_expression("deleted_at")); // contains "delete"
1028        assert!(is_valid_sql_expression("order_total")); // contains "order"
1029        assert!(is_valid_sql_expression("group_name")); // contains "group"
1030        assert!(is_valid_sql_expression("from_date")); // contains "from"
1031        assert!(is_valid_sql_expression("where_clause")); // contains "where"
1032    }
1033
1034    #[test]
1035    fn test_invalid_sql_expressions() {
1036        use super::is_valid_sql_expression;
1037
1038        // Empty
1039        assert!(!is_valid_sql_expression(""));
1040
1041        // SQL comments
1042        assert!(!is_valid_sql_expression("name -- comment"));
1043        assert!(!is_valid_sql_expression("/* comment */ name"));
1044        assert!(!is_valid_sql_expression("name */ attack"));
1045
1046        // Statement terminators
1047        assert!(!is_valid_sql_expression("1; DROP TABLE users"));
1048        assert!(!is_valid_sql_expression("name;"));
1049
1050        // Backticks
1051        assert!(!is_valid_sql_expression("`table`"));
1052
1053        // SQL keywords as standalone words
1054        assert!(!is_valid_sql_expression("(SELECT password)"));
1055        assert!(!is_valid_sql_expression("INSERT INTO x"));
1056        assert!(!is_valid_sql_expression("DELETE FROM x"));
1057        assert!(!is_valid_sql_expression("DROP TABLE x"));
1058        assert!(!is_valid_sql_expression("UPDATE SET y=1"));
1059        assert!(!is_valid_sql_expression("UNION ALL"));
1060        assert!(!is_valid_sql_expression("x FROM y"));
1061        assert!(!is_valid_sql_expression("x WHERE y"));
1062
1063        // System catalog access
1064        assert!(!is_valid_sql_expression("pg_catalog.pg_tables"));
1065        assert!(!is_valid_sql_expression("sqlite_master"));
1066        assert!(!is_valid_sql_expression("information_schema.tables"));
1067
1068        // Hex escapes
1069        assert!(!is_valid_sql_expression("0x48454C4C4F"));
1070        assert!(!is_valid_sql_expression("\\x48454C4C4F"));
1071
1072        // Dangerous functions (timing attacks, DoS)
1073        assert!(!is_valid_sql_expression("SLEEP(10)"));
1074        assert!(!is_valid_sql_expression("pg_sleep(5)"));
1075        assert!(!is_valid_sql_expression("BENCHMARK(1000000, SHA1('test'))"));
1076        assert!(!is_valid_sql_expression("WAITFOR DELAY '0:0:5'"));
1077
1078        // File operations
1079        assert!(!is_valid_sql_expression("LOAD_FILE('/etc/passwd')"));
1080    }
1081
1082    #[test]
1083    #[should_panic(expected = "Invalid SQL table name")]
1084    fn test_assert_valid_identifier_panics() {
1085        use super::assert_valid_sql_identifier;
1086        assert_valid_sql_identifier("users; DROP TABLE", "table");
1087    }
1088
1089    #[test]
1090    #[should_panic(expected = "Invalid SQL expression")]
1091    fn test_assert_valid_expression_panics() {
1092        use super::assert_valid_sql_expression;
1093        assert_valid_sql_expression("1; DROP TABLE users", "computed field");
1094    }
1095
1096    #[test]
1097    fn test_new_denies_regex_by_default() {
1098        // new() now uses secure defaults
1099        let validator = FilterValidator::new().allow_fields(&["name"]);
1100
1101        // Regex should be denied by default
1102        let filter = Filter {
1103            field: "name".into(),
1104            op: Operator::Regex,
1105            value: Value::String("^test".into()),
1106        };
1107
1108        let result = validator.validate(&filter);
1109        assert!(result.is_err());
1110
1111        match result.unwrap_err() {
1112            ValidationError::OperatorDenied { operator, .. } => {
1113                assert_eq!(operator, Operator::Regex);
1114            },
1115            _ => panic!("Expected OperatorDenied error"),
1116        }
1117    }
1118
1119    #[test]
1120    fn test_permissive_allows_regex() {
1121        let validator = FilterValidator::permissive().allow_fields(&["name"]);
1122
1123        let filter = Filter {
1124            field: "name".into(),
1125            op: Operator::Regex,
1126            value: Value::String("^test".into()),
1127        };
1128
1129        // permissive() allows all operators
1130        assert!(validator.validate(&filter).is_ok());
1131    }
1132
1133    #[test]
1134    fn test_new_allows_safe_operators() {
1135        let validator = FilterValidator::new().allow_fields(&["name", "status"]);
1136
1137        // Safe operators should work
1138        let filter = Filter {
1139            field: "status".into(),
1140            op: Operator::Eq,
1141            value: Value::String("active".into()),
1142        };
1143        assert!(validator.validate(&filter).is_ok());
1144
1145        // Like is also allowed (less dangerous than regex)
1146        let filter = Filter {
1147            field: "name".into(),
1148            op: Operator::Like,
1149            value: Value::String("%test%".into()),
1150        };
1151        assert!(validator.validate(&filter).is_ok());
1152    }
1153
1154    // ═══════════════════════════════════════════════════════════════════════════
1155    // COMPOUND FILTER EDGE CASE TESTS
1156    // ═══════════════════════════════════════════════════════════════════════════
1157
1158    #[test]
1159    fn test_validate_compound_filter_deep_nesting() {
1160        use crate::builder::{CompoundFilter, FilterExpr, simple};
1161
1162        // Create deeply nested compound filters to test depth limits
1163        // Build: AND(OR(AND(filter1, filter2), filter3), filter4)
1164        let innermost = CompoundFilter::and(vec![
1165            simple("a", Operator::Eq, Value::Int(1)),
1166            simple("b", Operator::Eq, Value::Int(2)),
1167        ]);
1168
1169        let middle = CompoundFilter::or(vec![
1170            FilterExpr::Compound(innermost),
1171            simple("c", Operator::Eq, Value::Int(3)),
1172        ]);
1173
1174        let outer = CompoundFilter::and(vec![
1175            FilterExpr::Compound(middle),
1176            simple("d", Operator::Eq, Value::Int(4)),
1177        ]);
1178
1179        // Validator with limited depth should reject this structure
1180        // The nesting depth here is controlled by the number of Value nesting, not compound filter depth
1181        // Compound filter depth is separate from value nesting
1182        let validator = FilterValidator::new();
1183
1184        // For simple filter validation, the compound structure itself isn't checked
1185        // Each simple filter should pass individually
1186        let simple_filter = Filter {
1187            field: "a".into(),
1188            op: Operator::Eq,
1189            value: Value::Int(1),
1190        };
1191        assert!(validator.validate(&simple_filter).is_ok());
1192
1193        // Verify compound filter can be constructed without panic
1194        assert_eq!(outer.filters.len(), 2);
1195        assert_eq!(outer.op, crate::LogicalOp::And);
1196    }
1197
1198    #[test]
1199    fn test_validate_compound_not_single_element() {
1200        use crate::builder::{CompoundFilter, simple};
1201
1202        // NOT should wrap exactly one filter
1203        let not_filter = CompoundFilter::not(simple("deleted", Operator::Eq, Value::Bool(true)));
1204
1205        assert_eq!(not_filter.filters.len(), 1);
1206        assert_eq!(not_filter.op, crate::LogicalOp::Not);
1207    }
1208
1209    #[test]
1210    fn test_validate_compound_empty_filters() {
1211        use crate::builder::CompoundFilter;
1212
1213        // Edge case: Compound filter with empty filter list
1214        let empty_and = CompoundFilter::and(vec![]);
1215        let empty_or = CompoundFilter::or(vec![]);
1216
1217        // Empty compound filters should have 0 filters
1218        assert!(empty_and.filters.is_empty());
1219        assert!(empty_or.filters.is_empty());
1220    }
1221
1222    #[test]
1223    fn test_validate_deeply_nested_array_values() {
1224        // Test value nesting depth validation
1225        let validator = FilterValidator::new().max_depth(2);
1226
1227        // 2 levels of nesting - should pass
1228        let filter_ok = Filter {
1229            field: "tags".into(),
1230            op: Operator::In,
1231            value: Value::Array(vec![Value::Array(vec![Value::Int(1)])]),
1232        };
1233        assert!(validator.validate(&filter_ok).is_ok());
1234
1235        // 3 levels of nesting - should fail with max_depth(2)
1236        let filter_too_deep = Filter {
1237            field: "tags".into(),
1238            op: Operator::In,
1239            value: Value::Array(vec![Value::Array(vec![Value::Array(vec![Value::Int(1)])])]),
1240        };
1241        assert!(validator.validate(&filter_too_deep).is_err());
1242    }
1243
1244    // ═══════════════════════════════════════════════════════════════════════════
1245    // SQL INJECTION FUZZING TESTS
1246    // Production-critical security tests for large-scale deployment
1247    // ═══════════════════════════════════════════════════════════════════════════
1248
1249    #[test]
1250    fn test_sqli_classic_or_true() {
1251        use super::is_valid_sql_expression;
1252
1253        // Classic OR-based injection - these are blocked by comment/semicolon detection
1254        // Note: Simple OR expressions are allowed for computed fields (e.g., "(a > 0) OR (b > 0)")
1255        // The primary protection is parameterized queries, not expression validation
1256        assert!(!is_valid_sql_expression("' OR 1=1--")); // Blocked by --
1257        assert!(!is_valid_sql_expression("1; OR 1=1")); // Blocked by ;
1258
1259        // These would pass expression validation but values are parameterized
1260        // so they can't actually cause injection when used properly
1261        // The expression validator is defense-in-depth, not the primary protection
1262    }
1263
1264    #[test]
1265    fn test_sqli_drop_table() {
1266        use super::is_valid_sql_expression;
1267
1268        // DROP TABLE attacks
1269        assert!(!is_valid_sql_expression("'; DROP TABLE users--"));
1270        assert!(!is_valid_sql_expression("'; DROP TABLE users;--"));
1271        assert!(!is_valid_sql_expression("1; DROP TABLE users"));
1272        assert!(!is_valid_sql_expression("DROP TABLE users"));
1273        assert!(!is_valid_sql_expression("drop table users"));
1274        assert!(!is_valid_sql_expression("DrOp TaBlE users"));
1275    }
1276
1277    #[test]
1278    fn test_sqli_union_attacks() {
1279        use super::is_valid_sql_expression;
1280
1281        // UNION-based injection
1282        assert!(!is_valid_sql_expression("' UNION SELECT * FROM users--"));
1283        assert!(!is_valid_sql_expression(
1284            "' UNION ALL SELECT password FROM users--"
1285        ));
1286        assert!(!is_valid_sql_expression("1 UNION SELECT 1,2,3"));
1287        assert!(!is_valid_sql_expression(
1288            "UNION SELECT username,password FROM admin"
1289        ));
1290        assert!(!is_valid_sql_expression("' union select null,null,null--"));
1291    }
1292
1293    #[test]
1294    fn test_sqli_comment_injection() {
1295        use super::is_valid_sql_expression;
1296
1297        // Comment-based attacks - blocked by comment detection
1298        assert!(!is_valid_sql_expression("admin'--")); // SQL comment
1299        assert!(!is_valid_sql_expression("admin'/*")); // Block comment start
1300        assert!(!is_valid_sql_expression("*/; DROP TABLE users--")); // Block comment end + semicolon
1301        assert!(!is_valid_sql_expression("1/**/OR/**/1=1")); // Block comments
1302
1303        // Note: MySQL # comment is not blocked - this validator is for Postgres/SQLite
1304        // MySQL-specific attacks should be handled at the application layer if needed
1305    }
1306
1307    #[test]
1308    fn test_sqli_stacked_queries() {
1309        use super::is_valid_sql_expression;
1310
1311        // Stacked query attacks (semicolon-based)
1312        assert!(!is_valid_sql_expression(
1313            "; INSERT INTO users VALUES('hacker')"
1314        ));
1315        assert!(!is_valid_sql_expression("; UPDATE users SET role='admin'"));
1316        assert!(!is_valid_sql_expression("; DELETE FROM users"));
1317        assert!(!is_valid_sql_expression("1; SELECT * FROM passwords"));
1318        assert!(!is_valid_sql_expression("'; TRUNCATE TABLE logs;--"));
1319    }
1320
1321    #[test]
1322    fn test_sqli_time_based_blind() {
1323        use super::is_valid_sql_expression;
1324
1325        // Time-based blind injection
1326        assert!(!is_valid_sql_expression("SLEEP(5)"));
1327        assert!(!is_valid_sql_expression("1 AND SLEEP(5)"));
1328        assert!(!is_valid_sql_expression("pg_sleep(5)"));
1329        assert!(!is_valid_sql_expression("1; SELECT pg_sleep(10)"));
1330        assert!(!is_valid_sql_expression("BENCHMARK(10000000,SHA1('test'))"));
1331        assert!(!is_valid_sql_expression("WAITFOR DELAY '0:0:5'"));
1332        assert!(!is_valid_sql_expression("dbms_lock.sleep(5)"));
1333    }
1334
1335    #[test]
1336    fn test_sqli_file_operations() {
1337        use super::is_valid_sql_expression;
1338
1339        // File read/write attacks
1340        assert!(!is_valid_sql_expression("LOAD_FILE('/etc/passwd')"));
1341        assert!(!is_valid_sql_expression("load_file('/etc/shadow')"));
1342        assert!(!is_valid_sql_expression(
1343            "INTO OUTFILE '/var/www/shell.php'"
1344        ));
1345        assert!(!is_valid_sql_expression("INTO DUMPFILE '/tmp/data'"));
1346        assert!(!is_valid_sql_expression("into_outfile('/tmp/x')"));
1347        assert!(!is_valid_sql_expression("into_dumpfile('/tmp/x')"));
1348    }
1349
1350    #[test]
1351    fn test_sqli_system_catalog_access() {
1352        use super::is_valid_sql_expression;
1353
1354        // System catalog enumeration
1355        assert!(!is_valid_sql_expression("pg_tables"));
1356        assert!(!is_valid_sql_expression("pg_catalog.pg_tables"));
1357        assert!(!is_valid_sql_expression("sqlite_master"));
1358        assert!(!is_valid_sql_expression("information_schema.tables"));
1359        assert!(!is_valid_sql_expression("sys.tables"));
1360        assert!(!is_valid_sql_expression("SELECT FROM information_schema"));
1361    }
1362
1363    #[test]
1364    fn test_sqli_hex_encoding() {
1365        use super::is_valid_sql_expression;
1366
1367        // Hex-encoded attacks
1368        assert!(!is_valid_sql_expression("0x27")); // Single quote
1369        assert!(!is_valid_sql_expression("0x4F5220313D31")); // OR 1=1
1370        assert!(!is_valid_sql_expression("\\x27"));
1371        assert!(!is_valid_sql_expression("CHAR(0x27)"));
1372    }
1373
1374    #[test]
1375    fn test_sqli_keyword_boundary_detection() {
1376        use super::is_valid_sql_expression;
1377
1378        // These SHOULD be allowed - keywords as substrings of identifiers
1379        assert!(is_valid_sql_expression("order_id")); // order
1380        assert!(is_valid_sql_expression("reorder_count")); // order
1381        assert!(is_valid_sql_expression("group_name")); // group
1382        assert!(is_valid_sql_expression("ungroup")); // group
1383        assert!(is_valid_sql_expression("from_date")); // from
1384        assert!(is_valid_sql_expression("wherefrom")); // where, from
1385        assert!(is_valid_sql_expression("selected_items")); // select
1386        assert!(is_valid_sql_expression("preselect")); // select
1387        assert!(is_valid_sql_expression("delete_flag")); // delete
1388        assert!(is_valid_sql_expression("undelete")); // delete
1389        assert!(is_valid_sql_expression("update_time")); // update
1390        assert!(is_valid_sql_expression("last_updated")); // update
1391
1392        // These SHOULD be blocked - standalone keywords
1393        assert!(!is_valid_sql_expression("ORDER BY name"));
1394        assert!(!is_valid_sql_expression("GROUP BY id"));
1395        assert!(!is_valid_sql_expression("FROM users"));
1396        assert!(!is_valid_sql_expression("WHERE id=1"));
1397        assert!(!is_valid_sql_expression("SELECT *"));
1398        assert!(!is_valid_sql_expression("DELETE FROM"));
1399        assert!(!is_valid_sql_expression("UPDATE SET"));
1400    }
1401
1402    #[test]
1403    fn test_sqli_case_variations() {
1404        use super::is_valid_sql_expression;
1405
1406        // Case variations of dangerous keywords
1407        assert!(!is_valid_sql_expression("SELECT"));
1408        assert!(!is_valid_sql_expression("select"));
1409        assert!(!is_valid_sql_expression("SeLeCt"));
1410        assert!(!is_valid_sql_expression("sElEcT"));
1411
1412        assert!(!is_valid_sql_expression("UNION"));
1413        assert!(!is_valid_sql_expression("union"));
1414        assert!(!is_valid_sql_expression("UnIoN"));
1415
1416        assert!(!is_valid_sql_expression("DROP"));
1417        assert!(!is_valid_sql_expression("drop"));
1418        assert!(!is_valid_sql_expression("DrOp"));
1419    }
1420
1421    #[test]
1422    fn test_sqli_whitespace_variations() {
1423        use super::is_valid_sql_expression;
1424
1425        // Whitespace-based evasion - these should still be caught
1426        // Note: tabs and newlines in expressions
1427        assert!(!is_valid_sql_expression("SELECT\t*"));
1428        assert!(!is_valid_sql_expression("SELECT\n*"));
1429        assert!(!is_valid_sql_expression("  SELECT  "));
1430        assert!(!is_valid_sql_expression("DROP\t\tTABLE"));
1431    }
1432
1433    #[test]
1434    fn test_sqli_expression_length_limit() {
1435        use super::is_valid_sql_expression;
1436
1437        // Very long expressions should be rejected
1438        let long_expr = "a".repeat(1001);
1439        assert!(!is_valid_sql_expression(&long_expr));
1440
1441        // At limit should be OK
1442        let at_limit = "a".repeat(1000);
1443        assert!(is_valid_sql_expression(&at_limit));
1444    }
1445
1446    #[test]
1447    fn test_identifier_injection_attempts() {
1448        use super::is_valid_sql_identifier;
1449
1450        // SQL injection via identifier names
1451        assert!(!is_valid_sql_identifier("users; DROP TABLE x"));
1452        assert!(!is_valid_sql_identifier("users--"));
1453        assert!(!is_valid_sql_identifier("users/*comment*/"));
1454        assert!(!is_valid_sql_identifier("users'"));
1455        assert!(!is_valid_sql_identifier("users\""));
1456        assert!(!is_valid_sql_identifier("users`"));
1457        assert!(!is_valid_sql_identifier("users;"));
1458        assert!(!is_valid_sql_identifier("(SELECT 1)"));
1459        assert!(!is_valid_sql_identifier("1 OR 1=1"));
1460
1461        // Unicode injection attempts
1462        assert!(!is_valid_sql_identifier("users\u{0000}")); // Null byte
1463        assert!(!is_valid_sql_identifier("users\u{200B}")); // Zero-width space
1464        assert!(!is_valid_sql_identifier("usërs")); // Non-ASCII letter
1465        assert!(!is_valid_sql_identifier("用户")); // Chinese characters
1466
1467        // Fullwidth characters (potential bypass)
1468        assert!(!is_valid_sql_identifier("ｕｓｅｒｓ")); // Fullwidth letters
1469    }
1470
1471    #[test]
1472    fn test_valid_safe_expressions() {
1473        use super::is_valid_sql_expression;
1474
1475        // Legitimate expressions that should be allowed
1476        assert!(is_valid_sql_expression("first_name || ' ' || last_name"));
1477        assert!(is_valid_sql_expression("price * quantity"));
1478        assert!(is_valid_sql_expression("price * 1.15")); // With tax
1479        assert!(is_valid_sql_expression(
1480            "COALESCE(nickname, first_name, 'Anonymous')"
1481        ));
1482        assert!(is_valid_sql_expression("UPPER(TRIM(name))"));
1483        assert!(is_valid_sql_expression("LENGTH(description)"));
1484        assert!(is_valid_sql_expression("ABS(balance)"));
1485        assert!(is_valid_sql_expression("ROUND(price, 2)"));
1486        assert!(is_valid_sql_expression("LOWER(email)"));
1487        assert!(is_valid_sql_expression("created_at + INTERVAL '1 day'"));
1488        assert!(is_valid_sql_expression("age >= 18"));
1489        assert!(is_valid_sql_expression("status = 'active'"));
1490        assert!(is_valid_sql_expression("NOT is_deleted"));
1491        assert!(is_valid_sql_expression("(price > 0) AND (quantity > 0)"));
1492    }
1493
1494    #[test]
1495    fn test_filter_value_injection() {
1496        // Test that malicious values in filters are properly parameterized
1497        // (This tests the design, not execution - values go through $1, $2 placeholders)
1498        let validator = FilterValidator::new().allow_fields(&["name", "email"]);
1499
1500        // Malicious value in string - should be allowed because it's parameterized
1501        let filter = Filter {
1502            field: "name".into(),
1503            op: Operator::Eq,
1504            value: Value::String("'; DROP TABLE users--".into()),
1505        };
1506        // Filter validation passes - the value is parameterized, not interpolated
1507        assert!(validator.validate(&filter).is_ok());
1508
1509        // But the SQL builder would produce:
1510        // "SELECT * FROM users WHERE name = $1"
1511        // With params: ["'; DROP TABLE users--"]
1512        // This is SAFE because it's parameterized!
1513    }
1514
1515    #[test]
1516    fn test_filter_field_injection() {
1517        // Test that malicious field names are blocked by whitelist
1518        let validator = FilterValidator::new().allow_fields(&["name", "email"]);
1519
1520        // Attempting to use SQL injection as field name
1521        let filter = Filter {
1522            field: "name; DROP TABLE users--".into(),
1523            op: Operator::Eq,
1524            value: Value::String("test".into()),
1525        };
1526        // Should fail - field not in whitelist
1527        assert!(validator.validate(&filter).is_err());
1528
1529        // Even without whitelist, the field goes through identifier validation
1530        // when the query is built
1531    }
1532
1533    #[test]
1534    fn test_operator_based_attacks() {
1535        // Certain operators could be used for attacks
1536        let validator = FilterValidator::new()
1537            .allow_fields(&["name"])
1538            .deny_operators(&[Operator::Regex]); // ReDoS prevention
1539
1540        // Regex operator should be denied (ReDoS risk)
1541        let filter = Filter {
1542            field: "name".into(),
1543            op: Operator::Regex,
1544            value: Value::String("^(a+)+$".into()), // ReDoS pattern
1545        };
1546        assert!(validator.validate(&filter).is_err());
1547
1548        // LIKE is safer (no backtracking)
1549        let filter = Filter {
1550            field: "name".into(),
1551            op: Operator::Like,
1552            value: Value::String("%test%".into()),
1553        };
1554        assert!(validator.validate(&filter).is_ok());
1555    }
1556}
mik_sql/validate.rs

mik_sql/
validate.rs
