# PostgreSQL SQL Injection via psycopg2 String Interpolation

Language: Python
Severity: Critical
CWE: CWE-89

## Source
10

## Flow
10-11-5

## Sink
5

## Vulnerable Code
```python
import psycopg2

def fetch_iot_device_telemetry(conn, device_mac, metric_type):
    cursor = conn.cursor()
    query = "SELECT timestamp, value, unit FROM sensor_data WHERE device_mac='%s' AND metric='%s' ORDER BY timestamp DESC LIMIT 100" % (device_mac, metric_type)
    cursor.execute(query)
    telemetry_records = cursor.fetchall()
    cursor.close()
    return telemetry_records

def get_device_metrics(db_config, mac_address, sensor_metric):
    connection = psycopg2.connect(**db_config)
    results = fetch_iot_device_telemetry(connection, mac_address, sensor_metric)
    connection.close()
    return results
```

## Explanation

The code uses Python string formatting (%) to directly interpolate user-controlled parameters (device_mac, metric_type) into a SQL query string without any sanitization or parameterization. This allows attackers to inject arbitrary SQL commands by crafting malicious input that breaks out of the string context.

## Remediation

The fix replaces unsafe Python string formatting (% operator) with psycopg2's built-in parameterized query mechanism. By passing user-supplied values (device_mac, metric_type) as a tuple in the second argument to cursor.execute(), psycopg2 handles proper escaping and type conversion, completely preventing SQL injection regardless of input content.

## Secure Code
```python
import psycopg2

def fetch_iot_device_telemetry(conn, device_mac, metric_type):
    cursor = conn.cursor()
    query = "SELECT timestamp, value, unit FROM sensor_data WHERE device_mac=%s AND metric=%s ORDER BY timestamp DESC LIMIT 100"
    cursor.execute(query, (device_mac, metric_type))
    telemetry_records = cursor.fetchall()
    cursor.close()
    return telemetry_records

def get_device_metrics(db_config, mac_address, sensor_metric):
    connection = psycopg2.connect(**db_config)
    results = fetch_iot_device_telemetry(connection, mac_address, sensor_metric)
    connection.close()
    return results
```