Authorization Bypass Through User-Controlled SQL Primary Key

Description

Authorization Bypass Through User-Controlled SQL Primary Key is a vulnerability where an application allows users to specify primary key values used in database queries without proper authorization verification, enabling access to records they should not be permitted to view or modify. This occurs when three conditions align: untrusted user input enters the application, that input specifies a primary key value in SQL queries, and the application fails to verify the user has permission to access the specified record. Attackers exploit this by modifying the primary key parameter to access other users' data.

Risk

User-controlled primary key vulnerabilities lead to horizontal privilege escalation, where users can access other users' data at the same privilege level. Attackers can enumerate record IDs to systematically access all records in a table. Sensitive personal information, financial records, medical data, or confidential business information can be exposed. Beyond reading unauthorized data, attackers may be able to modify or delete other users' records if the application allows updates based on the same unvalidated key. This vulnerability is particularly dangerous because it often bypasses application-level access controls entirely, as the database has no knowledge of which records a user should access.

Solution

Implement proper authorization checks that verify the current user has permission to access the requested record before executing database operations. Store ownership or access control information with each record and verify it server-side. Use indirect references that map user-visible identifiers to actual database keys, preventing direct manipulation. Implement parameterized queries to prevent SQL injection while also adding authorization logic. Consider using row-level security features available in modern databases. Log all data access attempts for audit purposes. Never trust user-supplied primary keys without authorization verification.

Common Consequences

Impact	Details
Confidentiality	Scope: Confidentiality Read Application Data - Attackers can access sensitive data belonging to other users by manipulating primary key values to point to unauthorized records.
Integrity	Scope: Integrity Modify Application Data - If the application allows updates using user-controlled keys, attackers can modify or delete other users' data.
Access Control	Scope: Access Control Bypass Protection Mechanism - Authorization controls are bypassed because the database query retrieves any record matching the specified key regardless of ownership.

Example Code

Vulnerable Code

// Vulnerable: C# ASP.NET displaying invoice without authorization check
using System.Data.SqlClient;
using System.Web.UI;

public partial class VulnerableInvoice : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Vulnerable: Invoice ID from user request
        string invoiceId = Request.QueryString["id"];

        string connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            // Vulnerable: Parameterized query but NO authorization check
            string query = "SELECT * FROM invoices WHERE invoice_id = @id";
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@id", invoiceId);

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            // Vulnerable: Displays any invoice regardless of ownership
            // Attacker can change ?id=123 to ?id=456 to see other invoices
            if (reader.Read())
            {
                lblInvoiceNumber.Text = reader["invoice_number"].ToString();
                lblAmount.Text = reader["amount"].ToString();
                lblCustomerName.Text = reader["customer_name"].ToString();
            }
        }
    }
}

// Vulnerable: Java servlet with IDOR vulnerability
import javax.servlet.http.*;
import java.sql.*;

public class VulnerableOrderServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        // Vulnerable: Order ID from user input
        String orderId = request.getParameter("orderId");

        try (Connection conn = dataSource.getConnection()) {
            // Vulnerable: No check if user owns this order
            String sql = "SELECT * FROM orders WHERE order_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, orderId);

            ResultSet rs = stmt.executeQuery();

            // Vulnerable: Returns any order, not just user's orders
            // User can access any order by changing the orderId parameter
            if (rs.next()) {
                Order order = new Order();
                order.setId(rs.getLong("order_id"));
                order.setCustomerId(rs.getLong("customer_id"));
                order.setAmount(rs.getBigDecimal("amount"));
                order.setItems(rs.getString("items"));

                request.setAttribute("order", order);
                request.getRequestDispatcher("/order.jsp").forward(request, response);
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }

    // Vulnerable: Update without ownership check
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String orderId = request.getParameter("orderId");
        String newStatus = request.getParameter("status");

        try (Connection conn = dataSource.getConnection()) {
            // Vulnerable: Can update any order, not just user's
            String sql = "UPDATE orders SET status = ? WHERE order_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, newStatus);
            stmt.setString(2, orderId);

            stmt.executeUpdate();
            // Attacker can cancel or modify other users' orders!
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

<?php
// Vulnerable: PHP user profile access

function get_user_profile($user_id) {
    $conn = get_database_connection();

    // Vulnerable: No authorization check
    $stmt = $conn->prepare("SELECT * FROM users WHERE user_id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();

    // Attacker can view any user's profile by changing the ID
    return $stmt->get_result()->fetch_assoc();
}

// Vulnerable: Profile page
$profile_id = $_GET['id'];  // User-controlled
$profile = get_user_profile($profile_id);

// Vulnerable: Displaying potentially another user's data
echo "Name: " . htmlspecialchars($profile['name']);
echo "Email: " . htmlspecialchars($profile['email']);
echo "SSN: " . htmlspecialchars($profile['ssn']);  // Sensitive!

// Vulnerable: Document download
function download_document($doc_id) {
    $conn = get_database_connection();

    // Vulnerable: No ownership verification
    $stmt = $conn->prepare("SELECT file_path, filename FROM documents WHERE doc_id = ?");
    $stmt->bind_param("i", $doc_id);
    $stmt->execute();
    $doc = $stmt->get_result()->fetch_assoc();

    // Attacker can download any document by ID enumeration
    header('Content-Disposition: attachment; filename="' . $doc['filename'] . '"');
    readfile($doc['file_path']);
}
?>

// Vulnerable: Node.js API with IDOR
const express = require('express');
const app = express();

// Vulnerable: Get account balance
app.get('/api/account/:accountId', async (req, res) => {
    const accountId = req.params.accountId;  // User-controlled

    // Vulnerable: No authorization check
    const account = await db.query(
        'SELECT * FROM accounts WHERE account_id = $1',
        [accountId]
    );

    // Returns any account balance, not just the user's
    res.json(account.rows[0]);
});

// Vulnerable: Transfer money
app.post('/api/transfer', async (req, res) => {
    const { fromAccountId, toAccountId, amount } = req.body;

    // Vulnerable: No verification that user owns fromAccountId
    await db.query(
        'UPDATE accounts SET balance = balance - $1 WHERE account_id = $2',
        [amount, fromAccountId]
    );

    await db.query(
        'UPDATE accounts SET balance = balance + $1 WHERE account_id = $2',
        [amount, toAccountId]
    );

    // Attacker can transfer from any account!
    res.json({ success: true });
});

// Vulnerable: Delete message
app.delete('/api/messages/:messageId', async (req, res) => {
    const messageId = req.params.messageId;

    // Vulnerable: Deletes any message regardless of ownership
    await db.query('DELETE FROM messages WHERE message_id = $1', [messageId]);

    res.json({ deleted: true });
});

Fixed Code

// Fixed: C# with authorization check
using System.Data.SqlClient;
using System.Web.UI;

public partial class SecureInvoice : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Get current user ID from authenticated session
        int currentUserId = GetAuthenticatedUserId();
        if (currentUserId == 0)
        {
            Response.Redirect("/login");
            return;
        }

        string invoiceId = Request.QueryString["id"];

        string connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            // Fixed: Query includes ownership check
            string query = @"SELECT * FROM invoices
                           WHERE invoice_id = @id
                           AND customer_id = @userId";

            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@id", invoiceId);
            cmd.Parameters.AddWithValue("@userId", currentUserId);  // Fixed: Verify ownership

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                lblInvoiceNumber.Text = reader["invoice_number"].ToString();
                lblAmount.Text = reader["amount"].ToString();
                lblCustomerName.Text = reader["customer_name"].ToString();
            }
            else
            {
                // Fixed: Invoice not found OR user doesn't own it
                lblError.Text = "Invoice not found";
            }
        }
    }

    private int GetAuthenticatedUserId()
    {
        // Get user ID from secure session
        return Session["UserId"] != null ? (int)Session["UserId"] : 0;
    }
}

// Fixed: Java servlet with proper authorization
import javax.servlet.http.*;
import java.sql.*;

public class SecureOrderServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        // Fixed: Get authenticated user
        HttpSession session = request.getSession(false);
        if (session == null || session.getAttribute("userId") == null) {
            response.sendError(HttpServletResponse.SC_UNAUTHORIZED);
            return;
        }
        Long userId = (Long) session.getAttribute("userId");

        String orderId = request.getParameter("orderId");

        try (Connection conn = dataSource.getConnection()) {
            // Fixed: Include user ID in query to verify ownership
            String sql = "SELECT * FROM orders WHERE order_id = ? AND customer_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, orderId);
            stmt.setLong(2, userId);

            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Order order = mapOrder(rs);
                request.setAttribute("order", order);
                request.getRequestDispatcher("/order.jsp").forward(request, response);
            } else {
                // Fixed: Order not found or user doesn't own it
                response.sendError(HttpServletResponse.SC_NOT_FOUND, "Order not found");
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        HttpSession session = request.getSession(false);
        if (session == null || session.getAttribute("userId") == null) {
            response.sendError(HttpServletResponse.SC_UNAUTHORIZED);
            return;
        }
        Long userId = (Long) session.getAttribute("userId");

        String orderId = request.getParameter("orderId");
        String newStatus = request.getParameter("status");

        // Fixed: Validate status value
        if (!isValidStatus(newStatus)) {
            response.sendError(HttpServletResponse.SC_BAD_REQUEST, "Invalid status");
            return;
        }

        try (Connection conn = dataSource.getConnection()) {
            // Fixed: Include customer_id in WHERE clause
            String sql = "UPDATE orders SET status = ? WHERE order_id = ? AND customer_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, newStatus);
            stmt.setString(2, orderId);
            stmt.setLong(3, userId);

            int updated = stmt.executeUpdate();

            if (updated == 0) {
                // Fixed: Order not found or user doesn't own it
                response.sendError(HttpServletResponse.SC_NOT_FOUND, "Order not found");
            } else {
                response.setStatus(HttpServletResponse.SC_OK);
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

<?php
// Fixed: PHP with authorization checks

function get_user_profile($profile_id, $current_user_id) {
    $conn = get_database_connection();

    // Fixed: Verify authorization
    // Option 1: Only allow users to view their own profile
    if ($profile_id != $current_user_id && !is_admin($current_user_id)) {
        return null;  // Not authorized
    }

    $stmt = $conn->prepare("SELECT * FROM users WHERE user_id = ?");
    $stmt->bind_param("i", $profile_id);
    $stmt->execute();

    return $stmt->get_result()->fetch_assoc();
}

// Fixed: Secure profile access
session_start();
$current_user_id = $_SESSION['user_id'] ?? null;

if (!$current_user_id) {
    header('Location: /login');
    exit;
}

$profile_id = $_GET['id'];
$profile = get_user_profile($profile_id, $current_user_id);

if (!$profile) {
    http_response_code(403);
    die('Access denied');
}

// Fixed: Document download with ownership check
function download_document($doc_id, $user_id) {
    $conn = get_database_connection();

    // Fixed: Include owner_id in query
    $stmt = $conn->prepare(
        "SELECT file_path, filename FROM documents
         WHERE doc_id = ? AND owner_id = ?"
    );
    $stmt->bind_param("ii", $doc_id, $user_id);
    $stmt->execute();
    $doc = $stmt->get_result()->fetch_assoc();

    if (!$doc) {
        http_response_code(404);
        die('Document not found');
    }

    // Fixed: Validate file path is within allowed directory
    $real_path = realpath($doc['file_path']);
    $allowed_dir = realpath('/var/www/documents/');

    if (strpos($real_path, $allowed_dir) !== 0) {
        http_response_code(403);
        die('Access denied');
    }

    header('Content-Disposition: attachment; filename="' .
           basename($doc['filename']) . '"');
    readfile($real_path);
}
?>

// Fixed: Node.js API with proper authorization
const express = require('express');
const app = express();

// Fixed: Authentication middleware
function authenticate(req, res, next) {
    if (!req.session || !req.session.userId) {
        return res.status(401).json({ error: 'Unauthorized' });
    }
    next();
}

// Fixed: Get account balance with ownership check
app.get('/api/account/:accountId', authenticate, async (req, res) => {
    const accountId = req.params.accountId;
    const userId = req.session.userId;

    // Fixed: Verify ownership in query
    const result = await db.query(
        'SELECT * FROM accounts WHERE account_id = $1 AND user_id = $2',
        [accountId, userId]
    );

    if (result.rows.length === 0) {
        return res.status(404).json({ error: 'Account not found' });
    }

    res.json(result.rows[0]);
});

// Fixed: Transfer money with ownership verification
app.post('/api/transfer', authenticate, async (req, res) => {
    const { fromAccountId, toAccountId, amount } = req.body;
    const userId = req.session.userId;

    // Fixed: Verify user owns the source account
    const fromAccount = await db.query(
        'SELECT * FROM accounts WHERE account_id = $1 AND user_id = $2',
        [fromAccountId, userId]
    );

    if (fromAccount.rows.length === 0) {
        return res.status(403).json({ error: 'Access denied to source account' });
    }

    // Fixed: Verify sufficient balance
    if (fromAccount.rows[0].balance < amount) {
        return res.status(400).json({ error: 'Insufficient funds' });
    }

    // Fixed: Use transaction for atomic transfer
    const client = await db.getClient();
    try {
        await client.query('BEGIN');

        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE account_id = $2 AND user_id = $3',
            [amount, fromAccountId, userId]
        );

        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE account_id = $2',
            [amount, toAccountId]
        );

        await client.query('COMMIT');
        res.json({ success: true });
    } catch (err) {
        await client.query('ROLLBACK');
        throw err;
    } finally {
        client.release();
    }
});

// Fixed: Delete message with ownership check
app.delete('/api/messages/:messageId', authenticate, async (req, res) => {
    const messageId = req.params.messageId;
    const userId = req.session.userId;

    // Fixed: Include owner_id in delete
    const result = await db.query(
        'DELETE FROM messages WHERE message_id = $1 AND owner_id = $2 RETURNING *',
        [messageId, userId]
    );

    if (result.rowCount === 0) {
        return res.status(404).json({ error: 'Message not found' });
    }

    res.json({ deleted: true });
});

module.exports = app;

---

CVE Examples

• CVE-2019-17382: Asterisk Manager Interface allowed unauthorized access through manipulated ActionID.
• CVE-2018-7584: PHP IMAP function allowed IDOR through mailbox parameter manipulation.
• CVE-2020-13151: Aerospike allowed unauthorized data access through predictable record IDs.

---

References

1. MITRE Corporation. "CWE-566: Authorization Bypass Through User-Controlled SQL Primary Key." https://cwe.mitre.org/data/definitions/566.html
2. OWASP. "Insecure Direct Object References Prevention Cheat Sheet."
3. OWASP. "Testing for Insecure Direct Object References."