# 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_sensor_telemetry(device_id, metric_type):
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cur = conn.cursor()
    usr_filter = request.args.get('threshold', '0')
    time_range = request.args.get('range', '24h')
    qry = f"SELECT ts, val FROM sensor_data WHERE device_id='{device_id}' AND metric='{metric_type}' AND val > {usr_filter} AND recorded_within='{time_range}'"
    cur.execute(qry)
    rows = cur.fetchall()
    conn.close()
    return rows
```

## Explanation

Lines 7-8 retrieve user-controlled inputs 'threshold' and 'range' directly from HTTP query parameters without any sanitization or parameterization. These values are interpolated into a raw SQL f-string on line 9, and the constructed query is executed on line 10 via cur.execute(qry), allowing an attacker to inject arbitrary SQL into any of the four interpolated fields (device_id, metric_type, usr_filter, time_range).

## Remediation

The fix replaces the dangerous f-string SQL construction with a parameterized query using placeholder markers (`?`). All four user-controlled values—`device_id`, `metric_type`, `usr_filter`, and `time_range`—are now passed as bound parameters to `cur.execute()`, which ensures the database driver properly escapes them and prevents any injected SQL from being interpreted as part of the query structure.

## Secure Code
```python
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def fetch_sensor_telemetry(device_id, metric_type):
    conn = sqlite3.connect('/var/iot/telemetry.db')
    cur = conn.cursor()
    usr_filter = request.args.get('threshold', '0')
    time_range = request.args.get('range', '24h')
    qry = "SELECT ts, val FROM sensor_data WHERE device_id=? AND metric=? AND val > ? AND recorded_within=?"
    cur.execute(qry, (device_id, metric_type, usr_filter, time_range))
    rows = cur.fetchall()
    conn.close()
    return rows
```