fortify scan: Access Control: Database

Without proper access control, executing a SQL statement that contains a user-controlled primary key can allow an attacker to view unauthorized records.

Explanation:

Database access control errors occur when:

1. Data enters a program from an untrusted source.

2. The data is used to specify the value of a primary key in a SQL query.

Example 1: The following code uses a parameterized statement, which escapes metacharacters and prevents SQL injection vulnerabilities, to construct and execute a SQL query that searches for an invoice matching the specified identifier. The identifier is selected from a list of all invoices associated with the current authenticated user.

...

int16 id = System.Convert.ToInt16(invoiceID.Text);

SqlCommand query = new SqlCommand(

            "SELECT * FROM invoices WHERE id = @id", conn);

query.Parameters.AddWithValue("@id", id);

SqlDataReader objReader = query.ExecuteReader();

...

The problem is that the developer has failed to consider all of the possible values of id. Although the interface generates a list of invoice identifiers that belong to the current user, an attacker might bypass this interface to request any desired invoice. Because the code in this example does not check to ensure that the user has permission to access the requested invoice, it will display any invoice, even if it does not belong to the current user.

Recommendations:

Rather than relying on the presentation layer to restrict values submitted by the user, access control should be handled by the application and database layers. Under no circumstances should a user be allowed to retrieve or modify a row in the database without the appropriate permissions. Every query that accesses the database should enforce this policy, which can often be accomplished by simply including the current authenticated username as part of the query.

Example 2: The following code implements the same functionality as Example 1 but imposes an additional constraint to verify that the invoice belongs to the currently authenticated user.

...

string user = ctx.getAuthenticatedUserName();

int16 id = System.Convert.ToInt16(invoiceID.Text);

SqlCommand query = new SqlCommand(

            "SELECT * FROM invoices WHERE id = @id AND user = @user", conn);

query.Parameters.AddWithValue("@id", id);

query.Parameters.AddWithValue("@user", user);

SqlDataReader objReader = query.ExecuteReader();

...