SQL Injection – Basics, Examples & Defense Techniques

1. What is SQL Injection?

SQL Injection, often abbreviated as SQLi, is a type of attack where the attacker can run malicious SQL code on a website’s database. Think of it as “tricking” the website into giving out information it shouldn’t.

2. How does SQL Injection work?

When a website asks for information, like your username and password, it’s using SQL to check that information in its database. If the website isn’t protected well, an attacker can enter SQL commands instead of regular information, which the website then runs unknowingly.

Imagine a website’s login form.

The code behind might look for usernames and passwords like this:

SELECT * FROM users WHERE username='ENTERED_USERNAME' AND password='ENTERED_PASSWORD';

An attacker could enter anything’ OR ‘1’=’1 as the username.

This would change the code to:

SELECT * FROM users WHERE username='anything' OR '1'='1' AND password='ENTERED_PASSWORD';

Because 1=1 is always true, the attacker could log in without knowing the actual password.

This is a bad example because it directly includes user input in the SQL query:

<?php
  
  $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);

  $username = $_POST['username'];
  
  $password = $_POST['password'];
  
  $query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
  
  $result = $pdo->query($query);
  
  if ($result->rowCount() > 0) {
  
      echo "Login successful!";
  
  } else {
  
      echo "Invalid credentials!";
  
  }
?>

An attacker could provide the following as the username: admin’ OR ‘1’=’1 causing all rows from the users table to be selected, likely bypassing the login.

The query would look like this:

$query = "SELECT * FROM users WHERE username='admin' or '1'='1' AND password='$password'";

If a website is vulnerable to SQLi, attackers can:

• View data they shouldn’t see (like other people’s passwords).
• Delete or change data.
• Sometimes, take over the whole website or server.

4. Different types of SQLi

Based on how they’re carried out,

4.1 Classic SQL Injection:

This is the most common type. It happens when user input is directly included in SQL queries without validation or escaping.

e.g.  Entering OR ‘1’=’1′ into a vulnerable login form might grant access without needing a correct password.

4.2 Blind SQL Injection:

Instead of getting a useful error message or data output, the attacker determines if the hypothesis is true based on other clues like the response time or a change in application behavior.

Types of Blind SQL Injection:

1. Boolean-based: Determines data truthiness by asking true or false questions.
2. Time-based: Delays the response to confirm a hypothesis. For instance, if an added SQL command causes a delay, the attacker knows their assumption might be correct.

4.3 Union-based SQL Injection:

This uses the UNION SQL operator to combine the results of the original query with results from one or more additional queries. This can be used to retrieve data from other tables.

e.g. An attacker might use UNION SELECT username, password FROM users to extract credentials from a user’s table.

4.4 Out-of-band SQL Injection:

Instead of using the same channel (e.g., HTTP) for both attack and data retrieval, the attacker uses a different channel (like DNS or HTTP requests) to exfiltrate data.

e.g. This method is beneficial when the server’s responses don’t display data but it’s still vulnerable to injection.

4.5 Error-based SQL Injection:

Attackers deliberately cause errors in SQL queries to force the database to reveal information in error messages.

e.g.  Input like ‘ might cause a SQL error. If the error message shows database details, attackers gain insights about its structure.

4.6 Stored Procedure Injection:

Exploits stored procedures (pre-saved SQL statements in a database). Some stored procedures might be misconfigured or written without security in mind.

e.g.  If an attacker identifies a vulnerable stored procedure, they can inject malicious SQL code into it.

5. SQL Injection Defense Techniques

• Use Prepared Statements:
• Stored Procedures:
• Escape User Input: This means making sure that any information from users is “cleaned” before it’s used in SQL commands.
• Limit Database Permissions: If a user doesn’t need to delete data, don’t let them. Only give users the permissions they really need.

5.1 Using Prepared Statements

This is a way of writing SQL code that allows the website to tell the difference between code and data.

The following example uses prepared statements to safely pass user input to the database:

<?php

  $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
  
  $username = $_POST['username'];
  
  $password = $_POST['password'];
  
  $query = "SELECT * FROM users WHERE username=:username AND password=:password";
  
  $stmt = $pdo->prepare($query);
  
  $stmt->bindParam(':username', $username, PDO::PARAM_STR);
  
  $stmt->bindParam(':password', $password, PDO::PARAM_STR);
  
  $stmt->execute();
  
  if ($stmt->rowCount() > 0) {
  
      echo "Login successful!";
  
  } else {
  
      echo "Invalid credentials!";
  
  }

?>

By using bindParam(), the user input is treated as data and not executable SQL code, ensuring that any injected SQL code won’t be executed.

5.2 Using Stored Procedures

These are set SQL commands that the database knows in advance, reducing the chances of running bad commands.

Stored procedures are pre-written SQL statements stored in the database itself. They can be executed without having to send the query directly from the application. This is one way to mitigate SQL injection, as the SQL code is predefined and parameters are passed in, rather than constructing the SQL with string concatenation.

The following example uses stored procedures.
Creating a stored procedure:

CREATE PROCEDURE GetUserByUsername(IN user_name VARCHAR(255))

BEGIN

    SELECT * FROM users WHERE username = user_name;

END

The parameters are directly passed on to the stored procedure in the code:

<?php

  $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
  
  $username = $_POST['username'];
  
  $stmt = $pdo->prepare("CALL GetUserByUsername(:username)");
  
  $stmt->bindParam(':username', $username, PDO::PARAM_STR);
  
  $stmt->execute();
  
  $userData = $stmt->fetchAll();
  
  print_r($userData);

?>

5.3 Escaping User Input

We can also escape user input using the PDO’s quote method. This method adds quotes around the input and escapes special characters.

<?php

  $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
  
  $username = $pdo->quote($_POST['username']);
  
  $password = $pdo->quote($_POST['password']);
  
  $query = "SELECT * FROM users WHERE username=$username AND password=$password";
  
  $result = $pdo->query($query);
  
  if ($result->rowCount() > 0) {
  
      echo "Login successful!";
  
  } else {
  
      echo "Invalid credentials!";
  
  }

?>

Note: While the quote() method can help against SQL injection, it's not always foolproof. Prepared statements are generally the more recommended method. Also, for password handling, never store or compare raw passwords. Always use proper hashing techniques (e.g., bcrypt).

6. Topic Recap

1. SQL Injection is a common and dangerous attack.
2. It tricks the website into running malicious SQL commands.
3. Websites should be protected using best practices like prepared statements, stored procedures and escaping user input to prevent SQLi.