# SQL Injection

Language: Python
Severity: Critical
CWE: CWE-89

## Source
14

## Flow
14-13-4-7-8

## Sink
8

## Vulnerable Code
```python
from flask import Flask, request
import sqlite3
app = Flask(__name__)
def fetch_iot_telemetry(device_filter):
    conn = sqlite3.connect('iot_hub.db')
    cur = conn.cursor()
    qry = "SELECT dev_id, payload, ts FROM telemetry WHERE region='" + device_filter + "' ORDER BY ts DESC"
    cur.execute(qry)
    rows = cur.fetchall()
    conn.close()
    return rows
@app.route('/api/telemetry')
def telemetry_endpoint():
    region = request.args.get('region', 'us-east')
    return str(fetch_iot_telemetry(region))
```

## Explanation

The 'region' parameter is retrieved directly from the HTTP query string on line 14 without any sanitization or validation. It is passed into fetch_iot_telemetry() and concatenated directly into a raw SQL string on line 7, then executed on line 8 via cur.execute(qry), allowing an attacker to manipulate the SQL query structure.

## Remediation

The fix replaces the vulnerable string concatenation in the SQL query with a parameterized query using a placeholder ('?') and passes the user input as a bound parameter tuple to cur.execute(). This ensures the database driver treats the input strictly as data, never as executable SQL. Additionally, an allowlist of valid regions and a strict regex validation are applied at the endpoint level to reject malicious or unexpected input before it ever reaches the database layer.

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

app = Flask(__name__)

ALLOWED_REGIONS = {'us-east', 'us-west', 'eu-west', 'eu-central', 'ap-south', 'ap-northeast'}

def fetch_iot_telemetry(device_filter):
    conn = sqlite3.connect('iot_hub.db')
    cur = conn.cursor()
    qry = "SELECT dev_id, payload, ts FROM telemetry WHERE region=? ORDER BY ts DESC"
    cur.execute(qry, (device_filter,))
    rows = cur.fetchall()
    conn.close()
    return rows

@app.route('/api/telemetry')
def telemetry_endpoint():
    region = request.args.get('region', 'us-east')
    if region not in ALLOWED_REGIONS:
        if not re.fullmatch(r'[a-zA-Z0-9\-]{1,30}', region):
            return jsonify({'error': 'Invalid region parameter'}), 400
    return jsonify({'telemetry': fetch_iot_telemetry(region)})
```