User Controlled Primary Key

python.user_controlled_primary_key

Severity

high

Resource

Misconfiguration

Language

Python

Description

This vulnerability arises when an application uses user-supplied input to control primary key values for database queries or operations, which may lead to unauthorized data access or modification.

Rationale

When user input is directly used to build database queries, including primary keys, it can result in unauthorized access or manipulation of protected data.

This problem is particularly dangerous when primary keys are integers or predictable values, as it enables users to perform unintended actions like accessing records that belong to other users, potentially exposing or corrupting sensitive information.

For example, consider this Python code snippet:

from flask import Flask, jsonify
import psycopg2
import os

app = Flask(__name__)

# Database connection settings
DB_NAME = "jira_db"
DB_USER = "your_db_username"
DB_PASSWORD = "your_db_password"
DB_HOST = "localhost"
DB_PORT = "5432"

def get_db_connection():
    conn = psycopg2.connect(
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        port=DB_PORT
    )
    return conn

@app.route('/get_ticket/<jira_id>', methods=['GET'])
def get_ticket(jira_id):
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Execute query to retrieve ticket
        cursor.execute("SELECT jira_id, title, status FROM tickets WHERE jira_id = %s", (jira_id,)) # FLAW
        ticket = cursor.fetchone()

        if ticket:
            return jsonify({
                "jira_id": ticket[0],
                "title": ticket[1],
                "status": ticket[2]
            }), 200
        else:
            return jsonify({"error": "Ticket not found"}), 404

    except Exception as e:
        return jsonify({"error": str(e)}), 500

    finally:
        if conn:
            conn.close()

if __name__ == '__main__':
    app.run(debug=True)

In this example, the userId parameter is user controlled, which may allow a user to access data of any user simply by changing the userId value.

Remediation

To mitigate the risks of CWE-566, it’s essential to avoid constructing database queries using user-supplied input directly or allow user input to control database primary key values. Best practices include:

If the SQL query is constructed without relying on user-controlled input, such as through the use of parameterized SQL, attackers cannot access unauthorized records.

Configuration

The detector has the following configurable parameters:

sources, that indicates the source kinds to check.
neutralizations, that indicates the neutralization kinds to check.

Unless you need to change the default behavior, you typically do not need to configure this detector.

References

CWE-566 : Authorization Bypass Through User-Controlled SQL Primary Key.