# SQL Injection

Language: Python
Severity: Critical
CWE: CWE-89

## Source
9

## Flow
9-11

## Sink
11

## Vulnerable Code
```python
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def fetch_device_telemetry(device_id, metric_type):
    conn = sqlite3.connect('iot_telemetry.db')
    cur = conn.cursor()
    qry = "SELECT ts, val FROM sensor_readings WHERE dev_id='" + device_id + "' AND metric='" + metric_type + "' ORDER BY ts DESC LIMIT 50"
    cur.execute(qry)
    rows = cur.fetchall()
    conn.close()
    return rows
@app.route('/api/v2/telemetry')
def telemetry_endpoint():
    dev = request.args.get('device_id', '')
    mtype = request.args.get('metric', 'temperature')
    return str(fetch_device_telemetry(dev, mtype))
```

## Explanation

User-supplied HTTP query parameters 'device_id' and 'metric' (lines 13-14) are retrieved without sanitization and passed directly into fetch_device_telemetry(), where they are concatenated into a raw SQL string (line 7) and executed via cur.execute() (line 8). This classic string concatenation pattern allows an attacker to break out of the intended SQL context and inject arbitrary SQL commands.

## Remediation

The fix replaces the dangerous string concatenation in the SQL query with parameterized placeholders (`?`) and passes the user-supplied `device_id` and `metric_type` values as a tuple to `cur.execute()`. This ensures the database driver properly escapes and binds the parameters, completely preventing SQL injection regardless of what input the attacker supplies. An additional basic input validation check was added for the required `device_id` parameter.

## Secure Code
```python
from flask import Flask, request, jsonify
import sqlite3

app = Flask(__name__)

def fetch_device_telemetry(device_id, metric_type):
    conn = sqlite3.connect('iot_telemetry.db')
    cur = conn.cursor()
    qry = "SELECT ts, val FROM sensor_readings WHERE dev_id=? AND metric=? ORDER BY ts DESC LIMIT 50"
    cur.execute(qry, (device_id, metric_type))
    rows = cur.fetchall()
    conn.close()
    return rows

@app.route('/api/v2/telemetry')
def telemetry_endpoint():
    dev = request.args.get('device_id', '')
    mtype = request.args.get('metric', 'temperature')
    if not dev:
        return jsonify({"error": "device_id parameter is required"}), 400
    return str(fetch_device_telemetry(dev, mtype))
```