# Remediation Guide - CVE-2025-61246

This document provides comprehensive guidance for fixing the SQL injection vulnerability in the Online Shopping System PHP application.

## Executive Summary

The vulnerability exists due to improper handling of user input in SQL queries. The `proId` parameter is directly concatenated into SQL statements without sanitization or parameterization, allowing attackers to inject malicious SQL code.

## Immediate Actions

### 1. Apply Emergency Patch

**Priority**: CRITICAL - Implement immediately

Replace the vulnerable code in `review_action.php`:

#### ❌ Vulnerable Code

```php
<?php
// VULNERABLE - DO NOT USE
$proId = $_POST['proId'];
$query = "SELECT * FROM products WHERE id = " . $proId;
$result = mysqli_query($conn, $query);
?>
```

#### ✅ Secure Code (Prepared Statements)

```php
<?php
// SECURE - Using prepared statements
$proId = $_POST['proId'];

// Validate input
if (!is_numeric($proId)) {
    die("Invalid product ID");
}

// Use prepared statement
$stmt = $conn->prepare("SELECT * FROM products WHERE id = ?");
$stmt->bind_param("i", $proId);
$stmt->execute();
$result = $stmt->get_result();

// Process results
while ($row = $result->fetch_assoc()) {
    // Handle data
}

$stmt->close();
?>
```

### 2. Input Validation

Implement strict input validation for all user inputs:

```php
<?php
/**
 * Validate and sanitize product ID
 * @param mixed $input User input
 * @return int|false Validated integer or false
 */
function validateProductId($input) {
    // Remove whitespace
    $input = trim($input);
    
    // Check if numeric
    if (!is_numeric($input)) {
        return false;
    }
    
    // Convert to integer
    $productId = intval($input);
    
    // Check range (adjust based on your requirements)
    if ($productId < 1 || $productId > 999999) {
        return false;
    }
    
    return $productId;
}

// Usage
$proId = validateProductId($_POST['proId']);
if ($proId === false) {
    http_response_code(400);
    die(json_encode(['error' => 'Invalid product ID']));
}
?>
```

### 3. Implement WAF Rules

Deploy Web Application Firewall rules to block common SQL injection patterns:

#### ModSecurity Rules

```apache
# Block SQL injection attempts
SecRule ARGS "@rx (?i)(union|select|insert|update|delete|drop|create|alter|exec|script|javascript|eval)" \
    "id:1001,phase:2,deny,status:403,msg:'SQL Injection Attempt Detected'"

# Block time-based SQL injection
SecRule ARGS "@rx (?i)(sleep|benchmark|waitfor|delay)" \
    "id:1002,phase:2,deny,status:403,msg:'Time-based SQL Injection Attempt'"

# Block SQL comments
SecRule ARGS "@rx (?i)(--|#|/\*|\*/)" \
    "id:1003,phase:2,deny,status:403,msg:'SQL Comment Detected'"
```

## Long-Term Solutions

### 1. Use PDO with Prepared Statements

Migrate to PDO for better security and portability:

```php
<?php
class Database {
    private $pdo;
    
    public function __construct($host, $dbname, $username, $password) {
        try {
            $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ];
            $this->pdo = new PDO($dsn, $username, $password, $options);
        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            die("Database connection error");
        }
    }
    
    /**
     * Get product by ID (secure)
     */
    public function getProductById($productId) {
        $stmt = $this->pdo->prepare("SELECT * FROM products WHERE id = :id");
        $stmt->execute(['id' => $productId]);
        return $stmt->fetch();
    }
    
    /**
     * Get product reviews (secure)
     */
    public function getProductReviews($productId) {
        $stmt = $this->pdo->prepare(
            "SELECT r.*, u.username 
             FROM reviews r 
             JOIN users u ON r.user_id = u.id 
             WHERE r.product_id = :product_id 
             ORDER BY r.created_at DESC"
        );
        $stmt->execute(['product_id' => $productId]);
        return $stmt->fetchAll();
    }
}

// Usage
$db = new Database('localhost', 'shopping_db', 'user', 'password');
$product = $db->getProductById($_POST['proId']);
?>
```

### 2. Implement ORM (Object-Relational Mapping)

Consider using an ORM like Eloquent or Doctrine:

```php
<?php
// Using Laravel Eloquent (example)
use Illuminate\Database\Eloquent\Model;

class Product extends Model {
    protected $fillable = ['name', 'price', 'description'];
    
    public function reviews() {
        return $this->hasMany(Review::class);
    }
}

// Secure usage
$productId = request()->input('proId');
$product = Product::findOrFail($productId);
$reviews = $product->reviews()->with('user')->get();
?>
```

### 3. Create a Secure Data Access Layer

```php
<?php
class ProductRepository {
    private $db;
    
    public function __construct(Database $db) {
        $this->db = $db;
    }
    
    /**
     * Find product by ID with validation
     */
    public function findById($id) {
        // Validate input
        if (!$this->isValidId($id)) {
            throw new InvalidArgumentException("Invalid product ID");
        }
        
        // Use prepared statement
        $stmt = $this->db->prepare("SELECT * FROM products WHERE id = ?");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        
        return $stmt->get_result()->fetch_assoc();
    }
    
    /**
     * Validate ID
     */
    private function isValidId($id) {
        return is_numeric($id) && $id > 0 && $id <= PHP_INT_MAX;
    }
}
?>
```

### 4. Implement Content Security Policy

Add CSP headers to prevent XSS attacks:

```php
<?php
header("Content-Security-Policy: default-src 'self'; script-src 'self' 'unsafe-inline'; style-src 'self' 'unsafe-inline';");
header("X-Content-Type-Options: nosniff");
header("X-Frame-Options: DENY");
header("X-XSS-Protection: 1; mode=block");
?>
```

### 5. Error Handling

Implement proper error handling without exposing sensitive information:

```php
<?php
// config.php
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', '/var/log/php_errors.log');

// Custom error handler
function secureErrorHandler($errno, $errstr, $errfile, $errline) {
    // Log the actual error
    error_log("Error [$errno]: $errstr in $errfile on line $errline");
    
    // Show generic message to user
    if (ini_get('display_errors')) {
        echo "An error occurred. Please try again later.";
    }
    
    return true;
}

set_error_handler("secureErrorHandler");
?>
```

## Security Best Practices

### 1. Principle of Least Privilege

Configure database user with minimal permissions:

```sql
-- Create restricted user
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON shopping_db.products TO 'webapp_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON shopping_db.reviews TO 'webapp_user'@'localhost';

-- DO NOT GRANT
-- DROP, CREATE, ALTER, DELETE (on critical tables)
-- FILE, SUPER, PROCESS privileges

FLUSH PRIVILEGES;
```

### 2. Regular Security Audits

Implement automated security scanning:

```bash
# Install security scanner
composer require --dev sensiolabs/security-checker

# Run security check
php vendor/bin/security-checker security:check

# Static analysis
composer require --dev phpstan/phpstan
vendor/bin/phpstan analyse src/
```

### 3. Logging and Monitoring

Implement comprehensive logging:

```php
<?php
class SecurityLogger {
    private $logFile;
    
    public function __construct($logFile = '/var/log/security.log') {
        $this->logFile = $logFile;
    }
    
    public function logSuspiciousActivity($activity, $details = []) {
        $entry = [
            'timestamp' => date('Y-m-d H:i:s'),
            'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? 'unknown',
            'activity' => $activity,
            'details' => $details,
            'request_uri' => $_SERVER['REQUEST_URI'] ?? 'unknown'
        ];
        
        file_put_contents(
            $this->logFile,
            json_encode($entry) . PHP_EOL,
            FILE_APPEND | LOCK_EX
        );
    }
}

// Usage
$logger = new SecurityLogger();

// Log suspicious input
if (preg_match('/(\bselect\b|\bunion\b|\bsleep\b)/i', $_POST['proId'])) {
    $logger->logSuspiciousActivity('SQL Injection Attempt', [
        'parameter' => 'proId',
        'value' => $_POST['proId']
    ]);
    
    http_response_code(403);
    die("Forbidden");
}
?>
```

### 4. Rate Limiting

Implement rate limiting to prevent automated attacks:

```php
<?php
class RateLimiter {
    private $redis;
    private $maxAttempts = 10;
    private $decayMinutes = 1;
    
    public function __construct($redis) {
        $this->redis = $redis;
    }
    
    public function tooManyAttempts($key) {
        $attempts = $this->redis->get($key) ?? 0;
        return $attempts >= $this->maxAttempts;
    }
    
    public function hit($key) {
        $current = $this->redis->incr($key);
        
        if ($current === 1) {
            $this->redis->expire($key, $this->decayMinutes * 60);
        }
        
        return $current;
    }
}

// Usage
$limiter = new RateLimiter($redis);
$key = 'api_limit:' . $_SERVER['REMOTE_ADDR'];

if ($limiter->tooManyAttempts($key)) {
    http_response_code(429);
    die(json_encode(['error' => 'Too many requests']));
}

$limiter->hit($key);
?>
```

## Testing Remediation

### 1. Verify Patch Effectiveness

```bash
# Test with original payload
curl -X POST http://localhost/review_action.php \
  -d "proId=1' AND SLEEP(5)-- -" \
  -w "\nTime: %{time_total}s\n"

# Expected: No delay, proper error handling
```

### 2. Automated Testing

```php
<?php
// tests/SecurityTest.php
use PHPUnit\Framework\TestCase;

class SecurityTest extends TestCase {
    public function testSQLInjectionPrevention() {
        $maliciousInputs = [
            "1' OR '1'='1",
            "1' AND SLEEP(5)-- -",
            "1' UNION SELECT NULL-- -",
            "1'; DROP TABLE users-- -"
        ];
        
        foreach ($maliciousInputs as $input) {
            $result = validateProductId($input);
            $this->assertFalse($result, "Failed to block: $input");
        }
    }
    
    public function testValidInputAccepted() {
        $validInputs = ['1', '123', '999999'];
        
        foreach ($validInputs as $input) {
            $result = validateProductId($input);
            $this->assertIsInt($result, "Failed to accept valid input: $input");
        }
    }
}
?>
```

## Compliance Checklist

- [ ] All SQL queries use prepared statements or parameterized queries
- [ ] Input validation implemented for all user inputs
- [ ] Database user has minimal required privileges
- [ ] Error messages don't expose sensitive information
- [ ] Security logging implemented
- [ ] Rate limiting configured
- [ ] WAF rules deployed
- [ ] Security headers configured
- [ ] Code reviewed by security team
- [ ] Penetration testing completed
- [ ] Documentation updated

## References

- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [PHP Manual: Prepared Statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php)
- [CWE-89: SQL Injection](https://cwe.mitre.org/data/definitions/89.html)
- [NIST Secure Coding Guidelines](https://www.nist.gov/itl/ssd/software-quality-group/secure-coding)

## Support

For additional assistance with remediation:
- Contact: security@example.com
- Security Team: https://example.com/security