# SQL Injection via sqlite3.executescript() on Untrusted Input

Language: Python
Severity: Critical
CWE: CWE-89

## Source
3

## Flow
3-6-8

## Sink
8

## Vulnerable Code
```python
import sqlite3

def purge_expired_iot_telemetry(device_mac, retention_days):
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cursor = conn.cursor()
    cleanup_script = f"""
    DELETE FROM sensor_data WHERE device_id = '{device_mac}' AND timestamp < datetime('now', '-{retention_days} days');
    VACUUM;
    """
    cursor.executescript(cleanup_script)
    conn.commit()
    conn.close()
    return f"Cleaned telemetry for {device_mac}"
```

## Explanation

The function accepts untrusted input parameters (device_mac, retention_days) and directly interpolates them into an SQL script using f-strings without any sanitization or parameterization. The executescript() method then executes this dynamically constructed SQL, allowing an attacker to inject arbitrary SQL commands through either parameter.

## Remediation

The fix replaces the dangerous f-string interpolation and executescript() call with parameterized queries using cursor.execute() with placeholder parameters (?). Additionally, input validation is added to ensure device_mac matches a valid MAC address format and retention_days is a positive integer, providing defense-in-depth against injection attacks.

## Secure Code
```python
import sqlite3
import re

def purge_expired_iot_telemetry(device_mac, retention_days):
    # Validate device_mac format (MAC address pattern)
    if not re.match(r'^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$', device_mac):
        raise ValueError("Invalid MAC address format")
    
    # Validate retention_days is a positive integer
    retention_days = int(retention_days)
    if retention_days <= 0:
        raise ValueError("Retention days must be a positive integer")
    
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cursor = conn.cursor()
    
    # Use parameterized query for the DELETE statement
    cursor.execute(
        "DELETE FROM sensor_data WHERE device_id = ? AND timestamp < datetime('now', ?)",
        (device_mac, f'-{retention_days} days')
    )
    
    conn.commit()
    
    # VACUUM must be run separately and doesn't involve user input
    cursor.execute("VACUUM")
    
    conn.close()
    return f"Cleaned telemetry for {device_mac}"
```