# SQL Injection via unsanitized ORDER BY clause concatenation

Language: Python
Severity: Critical
CWE: CWE-89

## Source
1

## Flow
1-3-4

## Sink
4

## Vulnerable Code
```python
def fetch_iot_device_telemetry(device_type, sort_field):
    conn = psycopg2.connect(database="iot_hub", user="admin")
    cursor = conn.cursor()
    query = f"SELECT device_id, temperature, humidity, timestamp FROM sensor_data WHERE type='{device_type}' ORDER BY {sort_field}"
    cursor.execute(query)
    telemetry_records = cursor.fetchall()
    cursor.close()
    conn.close()
    return telemetry_records
```

## Explanation

The sort_field parameter is user-controlled and directly concatenated into the SQL ORDER BY clause without any validation or sanitization. This allows an attacker to inject arbitrary SQL commands through the sorting parameter, potentially enabling UNION-based attacks, data exfiltration, or error-based SQL injection to extract sensitive database information.

## Remediation

The fix implements a whitelist approach for the ORDER BY clause, only allowing predefined valid column names through a dictionary lookup, which prevents any SQL injection through the sort parameter. Additionally, the device_type parameter is now passed as a parameterized query argument using %s placeholder instead of string interpolation, eliminating the secondary injection vector in the WHERE clause.

## Secure Code
```python
def fetch_iot_device_telemetry(device_type, sort_field):
    ALLOWED_SORT_FIELDS = {
        'device_id': 'device_id',
        'temperature': 'temperature',
        'humidity': 'humidity',
        'timestamp': 'timestamp'
    }
    
    if sort_field not in ALLOWED_SORT_FIELDS:
        raise ValueError(f"Invalid sort field: {sort_field}. Allowed fields: {list(ALLOWED_SORT_FIELDS.keys())}")
    
    safe_sort_field = ALLOWED_SORT_FIELDS[sort_field]
    
    conn = psycopg2.connect(database="iot_hub", user="admin")
    cursor = conn.cursor()
    query = f"SELECT device_id, temperature, humidity, timestamp FROM sensor_data WHERE type=%s ORDER BY {safe_sort_field}"
    cursor.execute(query, (device_type,))
    telemetry_records = cursor.fetchall()
    cursor.close()
    conn.close()
    return telemetry_records
```