#!/bin/bash
# SQL Injection Security Test Suite
# Tests parameterized query implementation

set -euo pipefail

# Colors
GREEN='\033[0;32m'
RED='\033[0;31m'
NC='\033[0m'

TESTS_PASSED=0
TESTS_FAILED=0
TOTAL_TESTS=0

# Test database
TEST_DB="/tmp/test-sql-injection-$$.db"
trap "rm -f '$TEST_DB'" EXIT

# Initialize test database
init_test_db() {
    sqlite3 "$TEST_DB" << 'EOF'
CREATE TABLE IF NOT EXISTS skills (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    content TEXT
);

INSERT INTO skills (name, content) VALUES ('test-skill', 'Content');
EOF
}

test_result() {
    local name="$1"
    local passed="$2"

    ((TOTAL_TESTS++))

    if [[ "$passed" == "true" ]]; then
        echo -e "${GREEN}PASS${NC}: $name"
        ((TESTS_PASSED++))
    else
        echo -e "${RED}FAIL${NC}: $name"
        ((TESTS_FAILED++))
    fi
}

# Test 1: Simple quote injection
test_quote_injection() {
    local injection="test'; DROP TABLE skills; --"

    # Before executing parameterized query, check table exists
    local count_before=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills;")

    # With parameterized query using stdin, injection should fail
    sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE name = ?;" <<< "$injection" 2>/dev/null || true

    # Verify table still exists
    local count_after=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills;" 2>/dev/null || echo "0")

    [[ "$count_before" == "$count_after" ]]
}

# Test 2: Comment injection
test_comment_injection() {
    local injection="test' OR '1'='1"

    # Parameterized query should treat as literal string
    local result=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE name = ?;" <<< "$injection")

    # Should return 0 (no match)
    [[ "$result" == "0" ]]
}

# Test 3: UNION injection
test_union_injection() {
    local injection="x' UNION SELECT 1,2,3 --"

    # Should return 0 matches
    local result=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE name = ?;" <<< "$injection")

    [[ "$result" == "0" ]]
}

# Test 4: Verify identifier validation
test_identifier_validation() {
    # Function should exist and work
    validate_sql_identifier() {
        local identifier="$1"
        [[ "$identifier" =~ ^[a-zA-Z_][a-zA-Z0-9_]*$ ]]
    }

    # Valid identifiers should pass
    validate_sql_identifier "valid_name" && \
    validate_sql_identifier "test123" && \
    ! validate_sql_identifier "invalid-name"
}

# Test 5: Parameterized INSERT
test_parameterized_insert() {
    local test_id="test_id_123"
    local test_name="test_name_456"

    # Use parameterized INSERT
    sqlite3 "$TEST_DB" "INSERT INTO skills (id, name, content) VALUES (?, ?, ?);" <<EOF
999
$test_id
test_content
EOF

    # Verify insert succeeded
    local result=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE id = 999;")

    [[ "$result" == "1" ]]
}

# Test 6: Parameterized UPDATE
test_parameterized_update() {
    local new_content="updated_content"
    local skill_name="test-skill"

    # Use parameterized UPDATE
    sqlite3 "$TEST_DB" "UPDATE skills SET content = ? WHERE name = ?;" <<EOF
$new_content
$skill_name
EOF

    # Verify update succeeded
    local result=$(sqlite3 "$TEST_DB" "SELECT content FROM skills WHERE name = '$skill_name';")

    [[ "$result" == "$new_content" ]]
}

# Test 7: Large payload injection
test_large_payload() {
    local large_payload=$(printf "x%.0s" {1..10000})
    large_payload="${large_payload}' OR '1'='1"

    # Should handle gracefully
    local result=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE name = ?;" <<< "$large_payload" 2>&1)

    [[ -z "$result" ]] || [[ "$result" == "0" ]]
}

# Test 8: Verify escaping approach is gone
test_no_escaping_needed() {
    local injection="test'; DROP TABLE skills; --"

    # Parameterized query - no escaping needed or used
    local result=$(sqlite3 "$TEST_DB" "SELECT COUNT(*) FROM skills WHERE name = ?;" <<< "$injection" 2>&1)

    # Should return 0, not execute DROP
    [[ "$result" == "0" ]]
}

main() {
    echo "SQL Injection Security Tests"
    echo "============================="
    echo ""

    init_test_db

    test_result "Quote injection blocked" "$(test_quote_injection && echo true || echo false)"
    test_result "Comment injection blocked" "$(test_comment_injection && echo true || echo false)"
    test_result "UNION injection blocked" "$(test_union_injection && echo true || echo false)"
    test_result "Identifier validation" "$(test_identifier_validation && echo true || echo false)"
    test_result "Parameterized INSERT works" "$(test_parameterized_insert && echo true || echo false)"
    test_result "Parameterized UPDATE works" "$(test_parameterized_update && echo true || echo false)"
    test_result "Large payload handling" "$(test_large_payload && echo true || echo false)"
    test_result "No escaping approach used" "$(test_no_escaping_needed && echo true || echo false)"

    echo ""
    echo "============================="
    echo "Results:"
    echo "  Passed: ${GREEN}$TESTS_PASSED${NC}/$TOTAL_TESTS"
    echo "  Failed: ${RED}$TESTS_FAILED${NC}/$TOTAL_TESTS"
    echo "  Pass Rate: $((TESTS_PASSED * 100 / TOTAL_TESTS))%"
    echo ""

    if [[ $TESTS_FAILED -eq 0 ]]; then
        echo -e "${GREEN}All tests PASSED${NC}"
        return 0
    else
        echo -e "${RED}Some tests FAILED${NC}"
        return 1
    fi
}

main