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:
     

     SELECT * FROM products WHERE category = 'Gifts' AND released = 1
     

     This is vulnerable to SQL injection as follows:
     

     -- 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.
     

     SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese'
     

     This is vulnerable to injection as follows:
     

     /*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:
     

     SELECT name, description FROM products WHERE category = 'Gifts'
     

     This is vulnerable to injection as follows:
     

     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:
     

         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.

  // 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();