Web Vulnerabilities: SQL Injections
Tags: cybersecurity, learning, sql, exploit
Published: 2023-12-11 10:56:58

*SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database.*

- **Impacts**
    - Unauthorized access to sensitive data such as passwords, credit card details and personal user information.
    - Reputational damage and regulatory fines.
    - Possibility of attacker gaining a persistent backdoor into an organization’s systems.

- **What parts of a `SQL query` can be injected?**
    - Common in `WHERE` clause of `SELECT` statement.
    - In `UPDATE` statements, within the updated values or the `WHERE` clause.
    - In `INSERT` statements, within the inserted values.
    - In `SELECT` statements, within the table or column name.
    - In `SELECT` statements, within the `ORDER BY` clause

- **Detection mechanisms**
    - Submit a single quote character `'` and look for errors and other anomalies.
    - Boolean conditions such as `OR 1=1` or `OR 1=2`
    - Payloads designed to trigger time delays such as `' OR '1'='1' AND SLEEP(5) --`
    - Comments with `--` at the end of an injection.

- **Examples**
    1. **Retrieving hidden data**
        
        Imagine a shopping application that displays products in different categories. When the user clicks on the **Gifts** category, their browser requests the URL: `https://insecure-website.com/products?category=Gifts` and causes the application to make a SQL query to retrieve details of relevant products from the data base as:
        
        ```sql
        SELECT * FROM products WHERE category = 'Gifts' AND released = 1
        ```
        
        This is vulnerable to SQL injection as follows:
        
        ```sql
        -- Payload = Gifts'-- shows gifts released or not
        SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1
        
        --Payload = Gifts' OR 1=1-- shows all items released or not
        SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1
        ```
        
        _`CAUTION`: When injecting the condition `OR 1=1` it’s common for applications to use data from a single request in multiple different queries. So if the condition reaches an `UPDATE` or `DELETE` statement, it can result in accidental data loss._
 2. **Subverting application logic**
        
        Imagine an application that lets users login with a username and password. The application checks the credentials by performing an SQL query as follows and if the query returns details of the user, login is successful else it is rejected.
        
        ```sql
        SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese'
        ```
        
        This is vulnerable to injection as follows:
        
        ```sql
        /*payload = administrator'-- allows the attacker to log in as any user
        without need for password*/
        SELECT * FROM users WHERE username = 'administrator'--' AND password = ''
        ```
        
        This query returns user whose `username` is `administrator` without the need for a password.
   3. **UNION attacks**
        
        Imagine an application executes the following query containing the user input `Gifts`:
        
        ```sql
        SELECT name, description FROM products WHERE category = 'Gifts'
        ```
        
        This is vulnerable to injection as follows:
        
        ```sql
        UNION SELECT username, password FROM users--
        
        --The malicious SQL query would then be:
        SELECT name, description FROM products
        WHERE category = '' 
        UNION 
        SELECT username, password FROM users--
        ```
        
        This causes the application to return all usernames and passwords along with the names and descriptions of the products. 
  4. **Blind `SQL` injection**
        
        Most `SQLi` are blind in nature but can still be exploited to gain unauthorized data. Some techniques are:
        
        a.  _Divide by zero_:
           ```sql
            SELECT * FROM users 
            WHERE username = '' OR 1=1 AND 1/0 --'
            AND password = '';
           ```
        b. _Triggering time delays with `SLEEP()`_
- **Prevention against SQL Injection using Prepared Statements or Parameterized Queries**
  - They make SQL injections virtually impossible.
    - Prepared statements separate code from data by ensuring that user-supplied input does not alter your program’s logic.
    - Prepared statements are compiled by the SQL server before adding user input.
    - Anything that is not part of the prepared statements is treated as string data and not as an executable SQL query.

    ```php
    // Create MySQL server and store username and password in variables.
    $mysqli = new mysqli("mysql_host", "mysql_username", "mysql_password", "database_name");
    $username = $_POST["username"];
    $password = $_POST["password"];

    // Vulnerable SQL statement
    $vuln = "SELECT Id FROM Users WHERE Username='$username' AND Password='$password'";
    $result = $mysqli->query($vuln);

    // Safe prepared statement
    $stmt = $mysqli->prepare("SELECT Id FROM Users WHERE Username =? AND Password =?");
    $stmt->bind_param("ss", $username, $password);
    $stmt->execute();

    // Unsafe prepared statement
    $stmt = $mysqli->prepare("SELECT Id FROM Users WHERE Username='$username' AND Password=password'");
    $stmt->execute();
    ```