DEV Community

Suprim Devkota
Suprim Devkota

Posted on • Updated on

Web Vulnerabilities: SQL Injections

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

Top comments (4)

Collapse
 
joolsmcfly profile image
Julien Dephix

Hi.

It's also important to sanitize input before using it.

Type hinting can also help.

$age = (int) $_POST['age'];
Enter fullscreen mode Exit fullscreen mode

ps: small typo in UNION attack :
WHERE cateogry = '' should be WHERE category = ''

Collapse
 
suprimdevkota profile image
Suprim Devkota

I'm planning on updating the article with more prevention mechanisms in the future. I wouldn't have noticed the typo on my own. Thank you so much!

Collapse
 
arafatweb profile image
Arafat Hossain Ar

Useful! Thanks

Collapse
 
suprimdevkota profile image
Suprim Devkota

I'm glad you liked it!