# SQL Injection via f-string Interpolation in sqlite3 Query

Language: Python
Severity: Critical
CWE: CWE-89

## Source
16-18

## Flow
16-18 -> 3 -> 6 -> 7

## Sink
7

## Vulnerable Code
```python
import sqlite3
import json

def retrieve_iot_device_telemetry(device_mac, metric_type, time_range):
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cursor = conn.cursor()
    query = f"SELECT timestamp, metric_value, unit FROM device_metrics WHERE mac_address = '{device_mac}' AND metric_name = '{metric_type}' AND timestamp >= {time_range} ORDER BY timestamp DESC"
    cursor.execute(query)
    results = cursor.fetchall()
    telemetry_data = []
    for row in results:
        telemetry_data.append({
            'ts': row[0],
            'value': row[1],
            'unit': row[2]
        })
    conn.close()
    return json.dumps(telemetry_data)

def process_device_query(request_payload):
    mac = request_payload.get('device_id')
    metric = request_payload.get('metric')
    since = request_payload.get('since', '0')
    return retrieve_iot_device_telemetry(mac, metric, since)
```

## Explanation

The function accepts untrusted user input (device_id, metric, since) from request_payload and passes them directly to retrieve_iot_device_telemetry(), which constructs a SQL query using f-string interpolation without any sanitization or parameterization. This allows attackers to inject arbitrary SQL commands through any of the three parameters.

## Remediation

The fix replaces the f-string interpolation in the SQL query with parameterized placeholders (?). The user-supplied values are passed as a tuple to cursor.execute(), which safely binds them as parameters, preventing any injected SQL from being interpreted as code.

## Secure Code
```python
import sqlite3
import json

def retrieve_iot_device_telemetry(device_mac, metric_type, time_range):
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cursor = conn.cursor()
    query = "SELECT timestamp, metric_value, unit FROM device_metrics WHERE mac_address = ? AND metric_name = ? AND timestamp >= ? ORDER BY timestamp DESC"
    cursor.execute(query, (device_mac, metric_type, time_range))
    results = cursor.fetchall()
    telemetry_data = []
    for row in results:
        telemetry_data.append({
            'ts': row[0],
            'value': row[1],
            'unit': row[2]
        })
    conn.close()
    return json.dumps(telemetry_data)

def process_device_query(request_payload):
    mac = request_payload.get('device_id')
    metric = request_payload.get('metric')
    since = request_payload.get('since', '0')
    return retrieve_iot_device_telemetry(mac, metric, since)
```