fraiseql-core 2.2.0

//! Behavioral SQL injection prevention tests for WHERE clause operators.
//!
//! Every test generates actual SQL via `WhereSqlGenerator::to_sql()` and verifies
//! that injection payloads are safely escaped — not just stored in a data structure.
//!
//! # Injection Vectors Tested
//!
//! - SQL termination: `'; DROP TABLE users; --`
//! - Boolean logic: `' OR '1'='1`
//! - Comment injection: `admin'--`
//! - UNION attacks: `' UNION SELECT * FROM passwords --`
//! - Stacked queries: `1; DELETE FROM users WHERE '1'='1`
//! - Parenthesis breakout: `') OR ('1'='1`
//! - Quote variation: `" OR ""=""`
//!
//! # Verification Strategy
//!
//! For each payload, we verify:
//! 1. SQL generation succeeds (no panic, no error)
//! 2. The raw payload does NOT appear unescaped in the SQL
//! 3. Single quotes in payloads are doubled (SQL escaping)

#![allow(clippy::unwrap_used)] // Reason: test code, panics are acceptable
#![allow(clippy::default_trait_access)] // Reason: test setup uses Default::default() for brevity
use fraiseql_core::db::{
    WhereSqlGenerator,
    where_clause::{WhereClause, WhereOperator},
};
use serde_json::json;

/// Comprehensive list of SQL injection payloads to test against all operators.
const INJECTION_PAYLOADS: &[&str] = &[
    // SQL termination
    "'; DROP TABLE users; --",
    "' OR '1'='1",
    "admin'--",
    "' UNION SELECT * FROM passwords --",
    "1; DELETE FROM users WHERE '1'='1",
    "') OR ('1'='1",
    "\" OR \"\"=\"\"",
    "' OR 1=1 --",
    "admin' OR 'a'='a",
    "1' UNION SELECT NULL, NULL, NULL --",
    "' OR 'a'='a",
    "admin' #",
    "' /**/OR/**/1=1 --",
    "1' AND '1'='1",
    "' AND 1=1 --",
];

/// Assert that a SQL string safely escapes a payload containing single quotes.
///
/// "Safely escaped" means every single quote in the payload is doubled ('') in the SQL,
/// so the payload becomes a string literal rather than executable SQL.
fn assert_injection_safe(sql: &str, payload: &str, context: &str) {
    // If payload contains single quotes, they must be doubled in the SQL output
    if payload.contains('\'') {
        let escaped_payload = payload.replace('\'', "''");
        assert!(
            sql.contains(&escaped_payload),
            "{context}: single quotes in payload must be doubled.\n  payload: {payload}\n  sql: {sql}"
        );
    }

    // SQL keywords from payloads must never appear as executable SQL outside quotes.
    // We check this by verifying there's no unescaped semicolon+keyword pattern.
    let dangerous_patterns = [
        "DROP TABLE",
        "DELETE FROM",
        "INSERT INTO",
        "UNION SELECT",
        "WAITFOR DELAY",
    ];
    for pattern in dangerous_patterns {
        if payload.contains(pattern) {
            // The pattern must be inside a SQL string literal (between quotes),
            // not as a raw SQL statement. Since we escape quotes, the payload
            // becomes part of a value literal — verify the SQL still contains
            // the string value boundary.
            assert!(
                sql.contains('\''),
                "{context}: SQL must contain string literals for value with {pattern}"
            );
        }
    }
}

// =============================================================================
// Operator-specific injection tests
// =============================================================================

#[test]
fn test_eq_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["email".to_string()],
            operator: WhereOperator::Eq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert_injection_safe(&sql, payload, "Eq operator");
    }
}

#[test]
fn test_neq_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["email".to_string()],
            operator: WhereOperator::Neq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("!="), "Neq must use != operator: {sql}");
        assert_injection_safe(&sql, payload, "Neq operator");
    }
}

#[test]
fn test_contains_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["comment".to_string()],
            operator: WhereOperator::Contains,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("LIKE"), "Contains must use LIKE: {sql}");
        assert_injection_safe(&sql, payload, "Contains operator");
    }
}

#[test]
fn test_icontains_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["name".to_string()],
            operator: WhereOperator::Icontains,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("ILIKE"), "Icontains must use ILIKE: {sql}");
        assert_injection_safe(&sql, payload, "Icontains operator");
    }
}

#[test]
fn test_startswith_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["username".to_string()],
            operator: WhereOperator::Startswith,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("LIKE"), "Startswith must use LIKE: {sql}");
        assert_injection_safe(&sql, payload, "Startswith operator");
    }
}

#[test]
fn test_istartswith_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["username".to_string()],
            operator: WhereOperator::Istartswith,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("ILIKE"), "Istartswith must use ILIKE: {sql}");
        assert_injection_safe(&sql, payload, "Istartswith operator");
    }
}

#[test]
fn test_endswith_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["domain".to_string()],
            operator: WhereOperator::Endswith,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("LIKE"), "Endswith must use LIKE: {sql}");
        assert_injection_safe(&sql, payload, "Endswith operator");
    }
}

#[test]
fn test_iendswith_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["domain".to_string()],
            operator: WhereOperator::Iendswith,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("ILIKE"), "Iendswith must use ILIKE: {sql}");
        assert_injection_safe(&sql, payload, "Iendswith operator");
    }
}

#[test]
fn test_numeric_operators_injection_generates_safe_sql() {
    let operators = vec![
        (WhereOperator::Gt, ">"),
        (WhereOperator::Gte, ">="),
        (WhereOperator::Lt, "<"),
        (WhereOperator::Lte, "<="),
    ];

    for (op, expected_sql_op) in operators {
        for payload in INJECTION_PAYLOADS {
            let clause = WhereClause::Field {
                path:     vec!["age".to_string()],
                operator: op.clone(),
                value:    json!(payload),
            };

            let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
            assert!(sql.contains(expected_sql_op), "{op:?} must use {expected_sql_op}: {sql}");
            assert_injection_safe(&sql, payload, &format!("{op:?} operator"));
        }
    }
}

#[test]
fn test_in_operator_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["status".to_string()],
            operator: WhereOperator::In,
            value:    json!([payload, "safe_value"]),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("ARRAY["), "In must use ARRAY[]: {sql}");
        assert_injection_safe(&sql, payload, "In operator");
    }
}

#[test]
fn test_nin_operator_injection_generates_safe_sql() {
    for payload in INJECTION_PAYLOADS {
        let clause = WhereClause::Field {
            path:     vec!["status".to_string()],
            operator: WhereOperator::Nin,
            value:    json!([payload, "safe_value"]),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(sql.contains("ARRAY["), "Nin must use ARRAY[]: {sql}");
        assert_injection_safe(&sql, payload, "Nin operator");
    }
}

// =============================================================================
// Path injection tests (field names, not values)
// =============================================================================

#[test]
fn test_injection_in_simple_path_generates_safe_sql() {
    let malicious_paths = vec![
        "email'; DROP TABLE users; --",
        "field' OR '1'='1",
        "name'; DELETE FROM passwords; --",
    ];

    for malicious_field in malicious_paths {
        let clause = WhereClause::Field {
            path:     vec![malicious_field.to_string()],
            operator: WhereOperator::Eq,
            value:    json!("safe_value"),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();

        // The field name's single quotes must be escaped
        assert!(
            sql.contains("''"),
            "Path injection: single quotes must be doubled in field name.\n  field: {malicious_field}\n  sql: {sql}"
        );

        // The value must still be correctly compared
        assert!(sql.contains("= 'safe_value'"), "Value comparison must remain intact: {sql}");
    }
}

#[test]
fn test_injection_in_nested_path_generates_safe_sql() {
    let malicious_paths = vec![
        vec!["user".to_string(), "email'; DROP TABLE--".to_string()],
        vec!["data".to_string(), "'; DELETE FROM ".to_string()],
        vec!["profile".to_string(), r#"" OR "=" "#.to_string()],
    ];

    for path in malicious_paths {
        let clause = WhereClause::Field {
            path:     path.clone(),
            operator: WhereOperator::Eq,
            value:    json!("test@example.com"),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();

        // Nested paths use data#>'{...}' syntax — quotes must be escaped
        assert!(sql.contains("data#>'{"), "Nested path must use JSON path syntax: {sql}");

        // Any single quote in field names must be doubled
        let has_quote = path.iter().any(|s| s.contains('\''));
        if has_quote {
            assert!(
                sql.contains("''"),
                "Path component quotes must be doubled.\n  path: {path:?}\n  sql: {sql}"
            );
        }
    }
}

// =============================================================================
// Compound clause injection tests
// =============================================================================

#[test]
fn test_injection_in_and_clause_generates_safe_sql() {
    let clause = WhereClause::And(vec![
        WhereClause::Field {
            path:     vec!["email".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("'; DROP TABLE users; --"),
        },
        WhereClause::Field {
            path:     vec!["status".to_string()],
            operator: WhereOperator::Eq,
            value:    json!("' OR '1'='1"),
        },
    ]);

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();

    // Both payloads must be safely escaped in the combined SQL
    assert!(sql.contains("AND"), "Must produce AND clause: {sql}");
    assert_injection_safe(&sql, "'; DROP TABLE users; --", "AND clause (first)");
    assert_injection_safe(&sql, "' OR '1'='1", "AND clause (second)");
}

#[test]
fn test_injection_in_or_clause_generates_safe_sql() {
    let clause = WhereClause::Or(vec![
        WhereClause::Field {
            path:     vec!["name".to_string()],
            operator: WhereOperator::Contains,
            value:    json!("' UNION SELECT * FROM passwords --"),
        },
        WhereClause::Field {
            path:     vec!["bio".to_string()],
            operator: WhereOperator::Icontains,
            value:    json!("'; WAITFOR DELAY '00:00:05'--"),
        },
    ]);

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();

    assert!(sql.contains("OR"), "Must produce OR clause: {sql}");
    assert_injection_safe(&sql, "' UNION SELECT * FROM passwords --", "OR clause (first)");
}

#[test]
fn test_injection_in_not_clause_generates_safe_sql() {
    let clause = WhereClause::Not(Box::new(WhereClause::Field {
        path:     vec!["test".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("'; DROP TABLE users; --"),
    }));

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    assert!(sql.starts_with("NOT ("), "Must produce NOT clause: {sql}");
    assert_injection_safe(&sql, "'; DROP TABLE users; --", "NOT clause");
}

// =============================================================================
// Edge case injection tests
// =============================================================================

#[test]
fn test_null_byte_injection_generates_safe_sql() {
    let clause = WhereClause::Field {
        path:     vec!["data".to_string()],
        operator: WhereOperator::Eq,
        value:    json!("test\0attack"),
    };

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    // Null bytes in JSON strings are already escaped by serde_json
    assert!(!sql.is_empty(), "SQL must be generated");
}

#[test]
fn test_unicode_quote_injection_generates_safe_sql() {
    let unicode_payloads = vec![
        "'\u{2019}", // Right single quotation mark
        "'\u{201C}", // Left double quotation mark
        "\u{FB02}",  // Ligature fi
    ];

    for payload in unicode_payloads {
        let clause = WhereClause::Field {
            path:     vec!["text".to_string()],
            operator: WhereOperator::Contains,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert_injection_safe(&sql, payload, "Unicode quote injection");
    }
}

#[test]
fn test_long_payload_injection_generates_safe_sql() {
    let long_payload = "x".repeat(10000) + "' OR '1'='1";

    let clause = WhereClause::Field {
        path:     vec!["comment".to_string()],
        operator: WhereOperator::Contains,
        value:    json!(long_payload),
    };

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    assert_injection_safe(&sql, &long_payload, "Long payload injection");
}

#[test]
fn test_encoded_payloads_generate_safe_sql() {
    let encoded_payloads = vec![
        "%27%20OR%20%271%27%3D%271",  // URL encoded: ' OR '1'='1
        "0x27 OR 0x31=0x31",          // Hex encoded
        "0x3c7375622066696c653d7e20", // Hex encoded
    ];

    for payload in encoded_payloads {
        let clause = WhereClause::Field {
            path:     vec!["data".to_string()],
            operator: WhereOperator::Eq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        // Encoded payloads are treated as literal strings — they should pass through
        // as-is (the DB won't decode URL/hex encoding)
        assert!(sql.contains(payload), "Encoded payload must be preserved as literal: {sql}");
    }
}

#[test]
fn test_backslash_escaping_generates_safe_sql() {
    let backslash_payloads = vec![
        "\\'; DROP TABLE users; --",
        "\\'OR\\'1\\'=\\'1",
        "admin\\' #",
    ];

    for payload in backslash_payloads {
        let clause = WhereClause::Field {
            path:     vec!["user".to_string()],
            operator: WhereOperator::Eq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert_injection_safe(&sql, payload, "Backslash escaping");
    }
}

#[test]
fn test_comment_techniques_generate_safe_sql() {
    let comment_payloads = vec!["'; --", "'; #", "'; /**/", "1' /*! UNION SELECT 1 */"];

    for payload in comment_payloads {
        let clause = WhereClause::Field {
            path:     vec!["id".to_string()],
            operator: WhereOperator::Eq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert_injection_safe(&sql, payload, "Comment technique");
    }
}

#[test]
fn test_real_world_owasp_payloads_generate_safe_sql() {
    let real_world_payloads = vec![
        "' OR '1'='1' --",
        "admin' --",
        "' OR 1=1 --",
        "' UNION ALL SELECT NULL,NULL,NULL --",
        "' UNION SELECT table_name FROM information_schema.tables --",
        "'; WAITFOR DELAY '00:00:05'--",
        "' AND SLEEP(5) --",
        "'; DROP TABLE users; --",
        "'; INSERT INTO users VALUES(...); --",
        "' AND '1'=('1",
        "\\x27 OR \\x31=\\x31 --",
    ];

    for payload in real_world_payloads {
        let clause = WhereClause::Field {
            path:     vec!["vulnerable_field".to_string()],
            operator: WhereOperator::Eq,
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert_injection_safe(&sql, payload, "OWASP payload");
    }
}

// =============================================================================
// Meta-tests (all operators covered)
// =============================================================================

#[test]
fn test_all_supported_operators_generate_safe_sql_for_injection() {
    let operators: Vec<(WhereOperator, &str)> = vec![
        (WhereOperator::Eq, "="),
        (WhereOperator::Neq, "!="),
        (WhereOperator::Gt, ">"),
        (WhereOperator::Gte, ">="),
        (WhereOperator::Lt, "<"),
        (WhereOperator::Lte, "<="),
        (WhereOperator::Contains, "LIKE"),
        (WhereOperator::Icontains, "ILIKE"),
        (WhereOperator::Startswith, "LIKE"),
        (WhereOperator::Istartswith, "ILIKE"),
        (WhereOperator::Endswith, "LIKE"),
        (WhereOperator::Iendswith, "ILIKE"),
    ];

    let payload = "'; DROP TABLE users; --";

    for (op, expected_sql_op) in operators {
        let clause = WhereClause::Field {
            path:     vec!["field".to_string()],
            operator: op.clone(),
            value:    json!(payload),
        };

        let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
        assert!(
            sql.contains(expected_sql_op),
            "{op:?}: expected SQL operator {expected_sql_op} in: {sql}"
        );
        assert_injection_safe(&sql, payload, &format!("{op:?}"));
    }
}

#[test]
fn test_is_null_operator_ignores_value_payload() {
    // IsNull uses the value as a boolean (true/false), not as a SQL value,
    // so injection payloads in the value field are harmless
    let clause = WhereClause::Field {
        path:     vec!["deleted_at".to_string()],
        operator: WhereOperator::IsNull,
        value:    json!(true),
    };

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    assert_eq!(sql, "data->>'deleted_at' IS NULL");

    // Even with a string payload, IsNull extracts .as_bool() which defaults to true
    let clause = WhereClause::Field {
        path:     vec!["deleted_at".to_string()],
        operator: WhereOperator::IsNull,
        value:    json!("'; DROP TABLE users; --"),
    };

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    // The injection payload is never included in the SQL
    assert!(!sql.contains("DROP"), "IsNull must not include value payload in SQL: {sql}");
}

#[test]
fn test_array_operator_injection_generates_safe_sql() {
    let clause = WhereClause::Field {
        path:     vec!["tags".to_string()],
        operator: WhereOperator::ArrayContains,
        value:    json!(["normal", "'; DROP TABLE users; --"]),
    };

    let sql = WhereSqlGenerator::to_sql(&clause).unwrap();
    assert!(sql.contains("::jsonb"), "Must produce JSONB cast: {sql}");
    // Single quotes inside JSON string values are doubled for SQL safety
    assert!(sql.contains("''"), "Quotes inside JSON array values must be doubled: {sql}");
}

#[test]
fn test_empty_boolean_clauses_generate_safe_sql() {
    // Empty AND → TRUE, empty OR → FALSE (safe defaults)
    let and_sql = WhereSqlGenerator::to_sql(&WhereClause::And(vec![])).unwrap();
    assert_eq!(and_sql, "TRUE");

    let or_sql = WhereSqlGenerator::to_sql(&WhereClause::Or(vec![])).unwrap();
    assert_eq!(or_sql, "FALSE");
}