SQL Injection

Critical Risk Injection

sqldatabaseinjectionuser-inputweb

What it is

A code injection technique that exploits security vulnerabilities in database-driven applications by inserting malicious SQL statements into entry fields.

Language:

import sqlite3

def get_user_by_id(user_id):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    # VULNERABLE: Direct string concatenation
    query = f"SELECT * FROM users WHERE id = {user_id}"
    cursor.execute(query)

    result = cursor.fetchone()
    conn.close()
    return result

# Malicious input: get_user_by_id("1 OR 1=1 --")
# Results in: SELECT * FROM users WHERE id = 1 OR 1=1 --

import sqlite3

def get_user_by_id(user_id):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    # SECURE: Using parameterized query
    query = "SELECT * FROM users WHERE id = ?"
    cursor.execute(query, (user_id,))

    result = cursor.fetchone()
    conn.close()
    return result

# Input validation
def get_user_by_id_safe(user_id):
    # Validate input type
    if not isinstance(user_id, int):
        raise ValueError("User ID must be an integer")

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    query = "SELECT * FROM users WHERE id = ?"
    cursor.execute(query, (user_id,))

    result = cursor.fetchone()
    conn.close()
    return result

💡 Why This Fix Works

The vulnerable code directly concatenates user input into the SQL query, allowing attackers to inject malicious SQL. The fixed version uses parameterized queries which separate SQL logic from data.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

function getUserByEmail(email) {
  // VULNERABLE: String concatenation
  const query = `SELECT * FROM users WHERE email = '\${email}'`;

  return new Promise((resolve, reject) => {
    connection.query(query, (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
}

// Malicious input: getUserByEmail("'; DROP TABLE users; --")

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'app_user',
  password: 'password',
  database: 'myapp'
});

function getUserByEmail(email) {
  // SECURE: Using parameterized query
  const query = 'SELECT * FROM users WHERE email = ?';

  return new Promise((resolve, reject) => {
    connection.query(query, [email], (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
}

// Alternative: Using async/await with validation
async function getUserByEmailSafe(email) {
  // Input validation
  if (!email || typeof email !== 'string') {
    throw new Error('Valid email is required');
  }

  // Email format validation
  const emailRegex = /^[^s@]+@[^s@]+.[^s@]+$/;
  if (!emailRegex.test(email)) {
    throw new Error('Invalid email format');
  }

  const query = 'SELECT * FROM users WHERE email = ?';

  return new Promise((resolve, reject) => {
    connection.query(query, [email], (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
}

💡 Why This Fix Works

Template literals and string concatenation make JavaScript particularly vulnerable to SQL injection. Using parameterized queries with the mysql library prevents this attack.

import java.sql.*;

public class UserDAO {
    private Connection connection;

    public User getUserById(String userId) throws SQLException {
        // VULNERABLE: String concatenation
        String query = "SELECT * FROM users WHERE id = " + userId;

        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        if (rs.next()) {
            return new User(
                rs.getInt("id"),
                rs.getString("username"),
                rs.getString("email")
            );
        }
        return null;
    }
}

import java.sql.*;

public class UserDAO {
    private Connection connection;

    public User getUserById(int userId) throws SQLException {
        // SECURE: Using PreparedStatement
        String query = "SELECT * FROM users WHERE id = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(query)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return new User(
                    rs.getInt("id"),
                    rs.getString("username"),
                    rs.getString("email")
                );
            }
        }
        return null;
    }

    // Method with input validation
    public User getUserByIdSafe(String userIdStr) throws SQLException, IllegalArgumentException {
        // Validate and convert input
        int userId;
        try {
            userId = Integer.parseInt(userIdStr);
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("Invalid user ID format");
        }

        if (userId <= 0) {
            throw new IllegalArgumentException("User ID must be positive");
        }

        return getUserById(userId);
    }
}

💡 Why This Fix Works

PreparedStatement in Java automatically handles parameter escaping and prevents SQL injection. The method also shows proper input validation.

-- VULNERABLE: Overprivileged database user
CREATE USER 'app_user'@'%' IDENTIFIED BY 'simple_password';
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';

-- Allowing connections from anywhere
BIND_ADDRESS = 0.0.0.0

-- No connection limits
-- No query time limits

-- SECURE: Least privilege principle
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'C0mpl3x_P@ssw0rd_2024!';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'app_user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON myapp.posts TO 'app_user'@'localhost';

-- Limit connections
CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'C0mpl3x_P@ssw0rd_2024!'
  WITH MAX_CONNECTIONS_PER_HOUR 1000
       MAX_QUERIES_PER_HOUR 10000;

-- Secure binding
BIND_ADDRESS = 127.0.0.1

-- Enable query logging for monitoring
GENERAL_LOG = ON
SLOW_QUERY_LOG = ON

💡 Why This Fix Works

Database configuration plays a crucial role in preventing SQL injection impact. Use least privilege access, strong passwords, and proper network restrictions.

Why it happens

The most common cause of SQL injection is directly concatenating user input into SQL query strings. This allows attackers to inject malicious SQL code that gets executed as part of the query. Developers often use string concatenation or template literals without realizing the security implications.

Root causes

String Concatenation in SQL Queries

Preview example – JAVASCRIPT

// Vulnerable approach
const query = "SELECT * FROM users WHERE id = " + userId;
// Attacker input: "1 OR 1=1 --"
// Results in: SELECT * FROM users WHERE id = 1 OR 1=1 --

Insufficient Input Validation

Many applications fail to properly validate and sanitize user input before using it in database queries. Input validation should include type checking, format validation, length restrictions, and whitelist validation. Without proper validation, malicious payloads can be injected into the application.

Preview example – PYTHON

# Vulnerable: No input validation
def get_user(user_id):
    query = f"SELECT * FROM users WHERE id = {user_id}"
    # Any input is accepted, including "1; DROP TABLE users; --"

Dynamic Query Construction

Building SQL queries dynamically based on user input without proper escaping mechanisms creates vulnerabilities. This includes using ORMs improperly, building WHERE clauses dynamically, or constructing complex queries based on user preferences without sanitization.

Preview example – JAVASCRIPT

// Vulnerable dynamic query building
function buildQuery(filters) {
    let query = "SELECT * FROM products WHERE 1=1";
    for (let field in filters) {
        query += " AND " + field + " = '" + filters[field] + "'";
    }
    return query;
}

Overprivileged Database Access

Using database connections with excessive privileges amplifies the impact of SQL injection attacks. When the application connects to the database with administrative privileges, successful injection attacks can lead to complete database compromise, including dropping tables, accessing sensitive data, or executing system commands.

Preview example – SQL

-- Dangerous: App user with admin privileges
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
-- Allows: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.

Fixes

Use Parameterized Queries and Prepared Statements

The most effective defense against SQL injection is using parameterized queries or prepared statements. These separate SQL logic from data, ensuring that user input is treated as data rather than executable code. Most modern database libraries and ORMs provide built-in support for parameterized queries.

View implementation – JAVASCRIPT

// Secure parameterized query
const query = 'SELECT * FROM users WHERE id = ? AND status = ?';
db.query(query, [userId, status], (err, results) => {
    // Handle results safely
});

Implement Comprehensive Input Validation

Establish robust input validation at multiple layers of your application. Use whitelist validation where possible, implement type checking, enforce length limits, and validate data formats. Never rely solely on client-side validation - always validate on the server side as well.

View implementation – PYTHON

# Python input validation example
import re
from typing import Optional

def validate_user_id(user_id: str) -> Optional[int]:
    # Type checking and format validation
    if not user_id.isdigit():
        raise ValueError("User ID must be numeric")

    user_id_int = int(user_id)
    if user_id_int <= 0 or user_id_int > 999999:
        raise ValueError("User ID out of valid range")

    return user_id_int

Apply Principle of Least Privilege

Configure database users with minimal necessary permissions. Create separate database accounts for different application functions, restrict network access to databases, and regularly audit database permissions. Avoid using administrative accounts for application connections.

View implementation – SQL

-- Create restricted database user
CREATE USER 'app_read'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT ON app_db.users TO 'app_read'@'localhost';
GRANT SELECT ON app_db.products TO 'app_read'@'localhost';

-- Separate user for write operations
CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'app_write'@'localhost';

Use ORM Security Features

Modern Object-Relational Mappers (ORMs) provide built-in protection against SQL injection when used correctly. Leverage ORM query builders, avoid raw SQL when possible, and use ORM-specific parameterization methods. Always stay updated with the latest ORM security best practices.

View implementation – PYTHON

// Django ORM - secure by default
from django.contrib.auth.models import User

# Safe: Uses parameterized queries internally
users = User.objects.filter(email=user_email, is_active=True)

# Dangerous: Raw SQL
users = User.objects.raw(
    f"SELECT * FROM auth_user WHERE email = '{user_email}'"
)

Implement Defense in Depth

Layer multiple security controls including Web Application Firewalls (WAF), database activity monitoring, regular security testing, and code reviews. Use tools like static analysis security testing (SAST) to automatically detect potential SQL injection vulnerabilities during development.

View implementation – YAML

# Example security configuration
# 1. WAF rules for SQL injection patterns
# 2. Database monitoring
# 3. Regular penetration testing
# 4. Automated security scanning in CI/CD

version: '3'
services:
  app:
    security_opt:
      - no-new-privileges:true
    environment:
      - DB_HOST=database
      - DB_USER=limited_user  # Not admin!

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities