Vulnerable code sample

# DISCLAIMER: This code is for educational purposes only.
# It is a representation of a vulnerability and should not be used in production.

import sqlite3
from flask import Flask, request, jsonify

# --- Setup for demonstration ---
# This part simulates the Tautulli environment
app = Flask(__name__)

def setup_database():
    """Creates a dummy database and table to simulate the environment."""
    conn = sqlite3.connect(':memory:') # Use in-memory DB for this example
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE session_history (
            id INTEGER PRIMARY KEY,
            user_id INTEGER,
            section_id INTEGER,
            started INTEGER,
            stopped INTEGER
        )
    ''')
    # Add some dummy data
    cursor.execute("INSERT INTO session_history (user_id, section_id, started) VALUES (1, 10, 1640995200)")
    cursor.execute("INSERT INTO session_history (user_id, section_id, started) VALUES (2, 20, 1641081600)")
    conn.commit()
    return conn

db_connection = setup_database()
# --- End of setup ---


# The following function represents the vulnerable code as described in CVE-2026-31799
@app.route('/api/v2', methods=['GET'])
def get_home_stats():
    """
    A representation of the vulnerable /api/v2?cmd=get_home_stats endpoint.
    
    This endpoint is vulnerable to SQL injection because it uses %-string formatting
    to insert user-provided parameters directly into an SQL query.

    Example of a malicious request for boolean-blind inference:
    /api/v2?cmd=get_home_stats&section_id=1) AND (SELECT 1 FROM session_history LIMIT 1) = 1 --
    
    If the above returns a normal result, the subquery was true.
    If it returns an error or different result, the subquery was false.
    An attacker can use this to exfiltrate data character by character.
    """
    if request.args.get('cmd') != 'get_home_stats':
        return jsonify({"response": {"result": "error", "message": "Invalid command"}}), 400

    # Get parameters from the request URL
    section_id = request.args.get('section_id')
    user_id = request.args.get('user_id')
    after = request.args.get('after')
    before = request.args.get('before')

    cursor = db_connection.cursor()

    # The vulnerable query construction using %-string formatting
    query = "SELECT COUNT(*) as history_count FROM session_history WHERE 1=1"
    
    if section_id:
        query += " AND section_id = %s" % section_id
    
    if user_id:
        query += " AND user_id = %s" % user_id
    
    if after:
        query += " AND started > %s" % after
        
    if before:
        query += " AND started < %s" % before
        
    try:
        # The unsanitized query is executed here
        cursor.execute(query)
        result = cursor.fetchone()
        count = result[0] if result else 0
        
        # Return a simulated successful response
        return jsonify({
            "response": {
                "result": "success",
                "message": None,
                "data": {
                    "stat_id": "get_home_stats",
                    "rows": [{"history_count": count}]
                }
            }
        })
    except Exception as e:
        # Errors from malformed SQL injection payloads would be caught here,
        # which is essential for blind inference attacks.
        return jsonify({"response": {"result": "error", "message": str(e)}}), 500

# To run this demonstration:
# 1. Make sure you have Flask installed (`pip install Flask`).
# 2. Run this Python script.
# 3. Access http://127.0.0.1:8181/api/v2?cmd=get_home_stats&section_id=10 in your browser.
# 4. To see the exploit, try an injection payload:
#    http://127.0.0.1:8181/api/v2?cmd=get_home_stats&section_id=1) AND 1=1 --
#    http://127.0.0.1:8181/api/v2?cmd=get_home_stats&section_id=1) AND 1=0 --
if __name__ == '__main__':
    app.run(port=8181)