Patched code sample

import sqlite3
from flask import Flask, jsonify, request

# This code represents a conceptual fix. The actual parsl-visualize code
# would be part of a larger application.

# --- Application Setup ---
app = Flask(__name__)

# In-memory SQLite database for demonstration purposes
# In a real Parsl deployment, this would connect to the monitoring database.
# check_same_thread=False is used for this simple Flask example.
db_conn = sqlite3.connect(":memory:", check_same_thread=False)

def setup_database():
    """Create a table and insert some sample data."""
    cursor = db_conn.cursor()
    cursor.execute('''
        CREATE TABLE workflows (
            id TEXT PRIMARY KEY,
            workflow_name TEXT NOT NULL,
            status TEXT NOT NULL,
            run_time REAL
        )
    ''')
    sample_data = [
        ('2024-08-15-abcde', 'science_run_1', 'completed', 3600.5),
        ('2024-08-16-fghij', 'data_processing', 'failed', 120.2),
    ]
    cursor.executemany('INSERT INTO workflows VALUES (?, ?, ?, ?)', sample_data)
    db_conn.commit()

setup_database()

# --- The Fixed Route ---
# This route demonstrates the secure way to handle user input in a SQL query.
@app.route('/workflow/<string:workflow_id>')
def get_workflow_details(workflow_id):
    """
    Fetches workflow details from the database for a given workflow_id.

    This function fixes the SQL Injection vulnerability by using a
    parameterized query. The database driver handles the safe substitution
    of the 'workflow_id' variable, preventing malicious SQL from being executed.
    """
    cursor = db_conn.cursor()

    # The SQL query uses a '?' placeholder for the user-supplied input.
    # This is the standard way to create parameterized queries with sqlite3.
    sql_query = "SELECT id, workflow_name, status, run_time FROM workflows WHERE id = ?"

    # The user-supplied 'workflow_id' is passed as a separate parameter
    # to the execute method. The database driver ensures it is treated as a
    # literal value and not as executable SQL code.
    cursor.execute(sql_query, (workflow_id,))

    result = cursor.fetchone()

    if result:
        # Return the found data
        return jsonify({
            "id": result[0],
            "workflow_name": result[1],
            "status": result[2],
            "run_time": result[3],
        })
    else:
        # Return an error if not found
        return jsonify({"error": "Workflow not found"}), 404

if __name__ == '__main__':
    # To test this fix:
    # 1. Run this Python script.
    # 2. Access http://127.0.0.1:5000/workflow/2024-08-15-abcde in your browser.
    #    - This should return the data for 'science_run_1'.
    # 3. Try to inject SQL by accessing a URL like:
    #    http://127.0.0.1:5000/workflow/x'%20OR%201=1;%20--
    #    - This will correctly result in a "Workflow not found" error,
    #      demonstrating that the injection attack failed. The input was
    #      treated as a single, literal string.
    app.run(debug=False)