# SQL Injection

Language: Python
Severity: Critical
CWE: CWE-89

## Source
7, 8

## Flow
7-8-9-10

## Sink
10

## Vulnerable Code
```python
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def fetch_sensor_telemetry(device_id):
    conn = sqlite3.connect('iot_hub.db')
    cur = conn.cursor()
    threshold = request.args.get('alert_threshold', '50')
    region = request.args.get('region', 'us-east')
    qry = "SELECT sensor_id, reading, timestamp FROM telemetry WHERE device_id='" + device_id + "' AND region='" + region + "' AND reading > " + threshold
    cur.execute(qry)
    rows = cur.fetchall()
    conn.close()
    return rows
```

## Explanation

The HTTP query parameters 'alert_threshold' (line 7) and 'region' (line 8) are retrieved directly from user-controlled request arguments without any sanitization or validation. These untrusted values are then concatenated directly into a raw SQL query string on line 9, and executed on line 10 via cur.execute(qry), enabling a classic SQL injection attack through both a string field (region) and a numeric field (alert_threshold).

## Remediation

The fix replaces raw string concatenation with parameterized queries using SQLite's placeholder syntax (?), passing device_id, region, and threshold as bound parameters to cur.execute(). Additionally, the alert_threshold value is validated and cast to a float before use, ensuring that even the numeric field cannot be exploited. This prevents SQL injection across all three input vectors.

## Secure Code
```python
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def fetch_sensor_telemetry(device_id):
    conn = sqlite3.connect('iot_hub.db')
    cur = conn.cursor()
    threshold = request.args.get('alert_threshold', '50')
    region = request.args.get('region', 'us-east')
    # Validate that threshold is a valid number to prevent injection via numeric field
    try:
        threshold_val = float(threshold)
    except (ValueError, TypeError):
        threshold_val = 50.0
    qry = "SELECT sensor_id, reading, timestamp FROM telemetry WHERE device_id=? AND region=? AND reading > ?"
    cur.execute(qry, (device_id, region, threshold_val))
    rows = cur.fetchall()
    conn.close()
    return rows
```