OWASP Top 10 Vulnerability

SQL Injection
It's where an attacker can insert malicious SQL code into database queries executed by an application. This happens when applications fail to properly validate or sanitise user input before incorporating it directly into SQL statements. The database then executes the injected SQL commands as part of the query, treating user input as executable code rather than data.

Attackers can inject SQL commands through various input points such as form fields, URL parameters, cookies or any other user-controllable input that interacts with a database. Successful SQL injection attacks can lead to unauthorised data access, authentication bypass, data modification or deletion, and in some cases, even server compromise.

To stop SQL injection attacks, websites should process user inputs safely by:
1) keeping user data separate from database commands
2) checking that inputs match expected formats
3) using pre-approved database operations
4) limiting database permissions

How a real attacker could exploit this
Online Banking Customer Portal
A bank's customer portal where customers can view their account information. The bank has a feature where customers can search their transaction history by entering a transaction ID.

The Vulnerable Code

Behind the scenes, the developer has created this PHP code:

The Normal Operation

When a legitimate customer enters transaction ID T-12345:

1. The resulting query becomes:
   sql
   

   SELECT * FROM transactions 
   WHERE transaction_id = 'T-12345' AND user_id = '12345'
   

2. This returns only the specific transaction T-12345 belonging to user 12345.

The Attack

Now, a malicious user enters T-12345' OR '1'='1 in the transaction ID field:

1. The resulting query becomes:
   sql
   

   SELECT * FROM transactions 
   WHERE transaction_id = 'T-12345' OR '1'='1' AND user_id = '12345'
   

2. Due to SQL operator precedence, this is evaluated as:
   sql
   

   SELECT * FROM transactions 
   WHERE (transaction_id = 'T-12345' OR '1'='1') AND user_id = '12345'
   

3. Since '1'='1' is always true, this effectively becomes:
   sql
   

   SELECT * FROM transactions 
   WHERE (true) AND user_id = '12345'
   

4. This returns ALL transactions belonging to user 12345, not just the specific one requested.

• Recommend fixes (prepared statements, input validation) ### Implement Prepared Statements Fixed Code: php

// Create a prepared statement with placeholders
$query = "SELECT * FROM transactions WHERE transaction_id = ? AND user_id = ?";
$stmt = mysqli_prepare($connection, $query);

// Bind parameters with appropriate types
mysqli_stmt_bind_param($stmt, "ss", $transaction_id, $user_id);

// Set the user_id from the authenticated session, not from user input
$user_id = $_SESSION['user_id']; // Get from session, not user input

// Execute the query safely
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

Benefit: The database will treat the transaction ID strictly as data, not as executable SQL code, regardless of any special characters it contains.

2. Add Input Validation

Fixed Code:
php

// Validate transaction ID format before processing
if (!preg_match('/^T-\d{5}$/', $transaction_id)) {
    // Transaction IDs must follow pattern T-12345
    die("Invalid transaction ID format");
}

// Continue with prepared statement as above

Benefit: Rejects inputs that don't match the expected format before they even reach the database query.

3. Implement HTTP Parameter Pollution Protection

Fixed Code:
php

// Ensure parameters aren't provided multiple times
if (is_array($_GET['transaction_id'])) {
    die("Invalid request");
}
$transaction_id = $_GET['transaction_id'];

Benefit: Prevents attackers from manipulating parameter parsing to bypass other protections.

4. Add Extra Authorization Layer

Fixed Code:
php

// After retrieving the transaction
$result = mysqli_stmt_get_result($stmt);
$transaction = mysqli_fetch_assoc($result);

// Double-check this transaction belongs to the logged-in user
if ($transaction && $transaction['user_id'] != $_SESSION['user_id']) {
    die("Access denied: This transaction does not belong to your account");
}

Benefit: Even if other protections fail, this ensures users can only see their own transactions.

5. Implement Proper Error Handling

Fixed Code:

php

try {
    // Database operations with prepared statements here
} catch (Exception $e) {
    // Log the error with details for administrators
    error_log("Database error: " . $e->getMessage());

    // Show generic error to user
    die("An error occurred processing your request. Please contact support.");
}

Benefit: Prevents leaking of database errors that might help attackers refine their injection attempts.

Additional Security Measures

1. Apply Database Account Restrictions:
   • Create a dedicated database user for the application with minimal required permissions
   • Revoke unnecessary privileges like DROP, ALTER, etc.
2. Implement Web Application Firewall (WAF):
   • Add a WAF to detect and block common SQL injection patterns
3. Employ HTTPS:
   • Ensure all communication is encrypted to prevent interception of sensitive data
4. Add Transaction Logging:
   • Log all data access attempts for audit purposes
   • Implement alerts for suspicious query patterns

These comprehensive security measures would transform the vulnerable banking application into a much more secure system that properly protects customer financial data.