SQL injection

Description

SQL Injection is a vulnerability where attackers inject malicious SQL queries into input fields, manipulating the behavior of the application's SQL database. By leveraging this vulnerability, attackers can gain unauthorized access to the database, retrieve, modify, or delete sensitive data, or execute administrative operations on the database. SQL Injection typically arises due to improperly sanitized user inputs, allowing attackers to bypass authentication, execute arbitrary SQL commands, and exploit the underlying database.

Examples

Java

  import java.sql.*;

  public class SQLInjectionDemo {
      public static void main(String[] args) {
          try {
              // Vulnerable Java code prone to SQL Injection
              Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database", "username", "password");
              Statement stmt = conn.createStatement();

              // Vulnerable SQL query without sanitization
              String username = args[0];
              String password = args[1];
              String sql = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";
              ResultSet rs = stmt.executeQuery(sql);

              if (rs.next()) {
                  // User authenticated
              } else {
                  // Invalid credentials
              }
              conn.close();
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  }

JavaScript

  const mysql = require('mysql');

  // Vulnerable Node.js code prone to SQL Injection
  const connection = mysql.createConnection({
      host: 'localhost',
      user: 'username',
      password: 'password',
      database: 'database'
  });

  // Vulnerable SQL query without sanitization
  const username = req.body.username;
  const password = req.body.password;
  const sql = `SELECT * FROM users WHERE username='${username}' AND password='${password}'`;

  connection.query(sql, (error, results) => {
      if (error) throw error;

      if (results.length > 0) {
          // User authenticated
      } else {
          // Invalid credentials
      }
  });

  connection.end();

PHP

  <?php
  // Vulnerable PHP code prone to SQL Injection
  $conn = new mysqli('localhost', 'username', 'password', 'database');
  if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
  }

  // Vulnerable SQL query without sanitization
  $username = $_POST['username'];
  $password = $_POST['password'];
  $sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {
      // User authenticated
  } else {
      // Invalid credentials
  }
  $conn->close();
  ?>

Recommendation

To prevent SQL injection attacks, consider the following measures:

Parameterized Queries: Use parameterized queries or prepared statements to execute SQL queries. Parameterization allows for separating SQL code from user input, preventing injection attacks.
Input Sanitization: Validate and sanitize user inputs before using them in SQL queries. Implement strict input validation by allowing only expected characters and formats.
Least Privilege Principle: Use the principle of least privilege for database users. Assign minimal necessary permissions to limit the impact of successful injection attacks.
ORMs and Libraries: Utilize Object-Relational Mapping (ORM) libraries or frameworks that handle SQL queries dynamically. These frameworks often provide built-in protections against injection attacks.

Examples

Java

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;

  public class ParametrizedQueryExample {
      public static void main(String[] args) {
          String username = "userInput"; // User input
          String password = "userInput"; // User input

          try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "username", "password")) {
              String query = "SELECT * FROM users WHERE username = ? AND password = ?";
              PreparedStatement statement = connection.prepareStatement(query);

              statement.setString(1, username);
              statement.setString(2, password);

              ResultSet resultSet = statement.executeQuery();
              while (resultSet.next()) {
                  // Process the results
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }

JavaScript

  const mysql = require('mysql2/promise');

  async function fetchUser(username, password) {
      const connection = await mysql.createConnection({
          host: 'localhost',
          user: 'username',
          password: 'password',
          database: 'db'
      });

      const [rows] = await connection.execute('SELECT * FROM users WHERE username = ? AND password = ?', [username, password]);
      connection.end();
      return rows;
  }

  // Usage
  fetchUser('userInput', 'userInput')
      .then(rows => {
          // Process the results
      })
      .catch(err => {
          console.error(err);
      });

PHP

  <?php
      $username = $_POST['username'];
      $password = $_POST['password'];

      $db = new mysqli('localhost', 'username', 'password', 'dbname');

      if ($stmt = $db->prepare("SELECT * FROM users WHERE username = ? AND password = ?")) {
          $stmt->bind_param('ss', $username, $password);
          $stmt->execute();

          $result = $stmt->get_result();

          while ($row = $result->fetch_assoc()) {
              // Process the results
          }

          $stmt->close();
      }

      $db->close();
  ?>

Links

Standards

CWE_TOP_25:
- CWE_89
OWASP_ASVS_L1:
- V5_3_5
OWASP_ASVS_L2:
- V5_3_5
OWASP_ASVS_L3:
- V5_3_5
PCI_STANDARDS:
- REQ_2_2
- REQ_3_5
- REQ_4_2
- REQ_6_2
- REQ_6_3
- REQ_6_4
- REQ_11_3
SOC2_CONTROLS:
- CC_2_1
- CC_3_4
- CC_4_1
- CC_7_1
- CC_7_2
- CC_7_4
- CC_7_5