SQL Injection Prevention: Parameterized Queries

Problem

SQL injection is a critical security vulnerability where attackers insert malicious SQL code into input fields, allowing them to manipulate database queries. This can lead to unauthorized data access, modification, deletion, or complete database compromise.

Vulnerable Example

-- Attacker input: admin' --
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
-- Result: Bypasses password check entirely

When user input is directly concatenated into SQL queries without validation or escaping, attackers can break out of intended query logic and execute arbitrary commands.

---

Solution

Parameterized Queries (also called prepared statements) separate SQL code structure from data values. The database engine treats parameters as pure data, never as executable code, making injection impossible.

Key Benefits

• Complete injection prevention: Data cannot be interpreted as SQL commands
• Performance optimization: Query plans are cached and reused
• Code clarity: Separates logic from data
• Database agnostic: Works across different database systems

---

Code Implementation

1. Python with SQLite

import sqlite3
from typing import Optional, List, Dict

class UserDatabase:
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row

    # ❌ VULNERABLE - String concatenation
    def get_user_unsafe(self, username: str) -> Optional[Dict]:
        query = f"SELECT * FROM users WHERE username = '{username}'"
        cursor = self.conn.cursor()
        cursor.execute(query)
        return dict(cursor.fetchone() or {})

    # ✅ SECURE - Parameterized query
    def get_user_safe(self, username: str) -> Optional[Dict]:
        query = "SELECT * FROM users WHERE username = ?"
        cursor = self.conn.cursor()
        cursor.execute(query, (username,))
        result = cursor.fetchone()
        return dict(result) if result else None

    # ✅ SECURE - Multiple parameters
    def authenticate_user(self, username: str, password: str) -> bool:
        query = "SELECT id FROM users WHERE username = ? AND password = ?"
        cursor = self.conn.cursor()
        cursor.execute(query, (username, password))
        return cursor.fetchone() is not None

    # ✅ SECURE - Insert with parameters
    def create_user(self, username: str, email: str, password: str) -> int:
        query = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)"
        cursor = self.conn.cursor()
        cursor.execute(query, (username, email, password))
        self.conn.commit()
        return cursor.lastrowid

    # ✅ SECURE - Update with parameters
    def update_user_email(self, user_id: int, new_email: str) -> bool:
        query = "UPDATE users SET email = ? WHERE id = ?"
        cursor = self.conn.cursor()
        cursor.execute(query, (new_email, user_id))
        self.conn.commit()
        return cursor.rowcount > 0

    # ✅ SECURE - Delete with parameters
    def delete_user(self, user_id: int) -> bool:
        query = "DELETE FROM users WHERE id = ?"
        cursor = self.conn.cursor()
        cursor.execute(query, (user_id,))
        self.conn.commit()
        return cursor.rowcount > 0

    # ✅ SECURE - Search with LIKE (still parameterized)
    def search_users(self, search_term: str) -> List[Dict]:
        query = "SELECT id, username, email FROM users WHERE username LIKE ?"
        cursor = self.conn.cursor()
        # Use % wildcards safely with parameters
        cursor.execute(query, (f"%{search_term}%",))
        return [dict(row) for row in cursor.fetchall()]

    def close(self):
        self.conn.close()

# Usage
db = UserDatabase("app.db")

# Safe - injection attempt is treated as literal string
user = db.get_user_safe("admin' --")  # Searches for username literally "admin' --"

# Safe - authentication
is_valid = db.authenticate_user("john_doe", "password123")

# Safe - create user
user_id = db.create_user("jane_smith", "jane@example.com", "secure_pass")

db.close()

2. Node.js with MySQL

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

class UserService {
    constructor(pool) {
        this.pool = pool;
    }

    // ❌ VULNERABLE - String concatenation
    async getUserUnsafe(username) {
        const query = `SELECT * FROM users WHERE username = '${username}'`;
        const connection = await this.pool.getConnection();
        const [rows] = await connection.query(query);
        connection.release();
        return rows[0];
    }

    // ✅ SECURE - Parameterized query
    async getUser(username) {
        const query = 'SELECT * FROM users WHERE username = ?';
        const connection = await this.pool.getConnection();
        const [rows] = await connection.execute(query, [username]);
        connection.release();
        return rows[0];
    }

    // ✅ SECURE - Multiple parameters
    async authenticateUser(username, password) {
        const query = 'SELECT id, username FROM users WHERE username = ? AND password = ?';
        const connection = await this.pool.getConnection();
        const [rows] = await connection.execute(query, [username, password]);
        connection.release();
        return rows.length > 0 ? rows[0] : null;
    }

    // ✅ SECURE - Insert with parameters
    async createUser(username, email, password) {
        const query = 'INSERT INTO users (username, email, password) VALUES (?, ?, ?)';
        const connection = await this.pool.getConnection();
        const [result] = await connection.execute(query, [username, email, password]);
        connection.release();
        return result.insertId;
    }

    // ✅ SECURE - Update with parameters
    async updateUserProfile(userId, bio, location) {
        const query = 'UPDATE users SET bio = ?, location = ? WHERE id = ?';
        const connection = await this.pool.getConnection();
        const [result] = await connection.execute(query, [bio, location, userId]);
        connection.release();
        return result.affectedRows > 0;
    }

    // ✅ SECURE - Complex query with multiple parameters
    async searchUsers(searchTerm, minAge, maxAge, limit = 10) {
        const query = `
            SELECT id, username, email, age 
            FROM users 
            WHERE (username LIKE ? OR email LIKE ?) 
            AND age BETWEEN ? AND ?
            LIMIT ?
        `;
        const connection = await this.pool.getConnection();
        const [rows] = await connection.execute(query, [
            `%${searchTerm}%`,
            `%${searchTerm}%`,
            minAge,
            maxAge,
            limit
        ]);
        connection.release();
        return rows;
    }

    // ✅ SECURE - Batch operations with transaction
    async createMultipleUsers(users) {
        const connection = await this.pool.getConnection();
        try {
            await connection.beginTransaction();
            const query = 'INSERT INTO users (username, email, password) VALUES (?, ?, ?)';

            for (const user of users) {
                await connection.execute(query, [user.username, user.email, user.password]);
            }

            await connection.commit();
            return true;
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
}

// Usage
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'myapp',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

const userService = new UserService(pool);

// Safe - injection attempt is treated as literal string
const user = await userService.getUser("admin' --");

// Safe - authentication
const authenticated = await userService.authenticateUser('john_doe', 'password123');

// Safe - search with parameters
const results = await userService.searchUsers('john', 18, 65, 20);

3. Java with JDBC

import java.sql.*;
import java.util.*;

public class UserRepository {
    private String connectionString;

    public UserRepository(String connectionString) {
        this.connectionString = connectionString;
    }

    // ❌ VULNERABLE - String concatenation
    public User getUserUnsafe(String username) throws SQLException {
        String query = "SELECT * FROM users WHERE username = '" + username + "'";
        try (Connection conn = DriverManager.getConnection(connectionString);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
        }
        return null;
    }

    // ✅ SECURE - Parameterized query with PreparedStatement
    public User getUser(String username) throws SQLException {
        String query = "SELECT id, username, email FROM users WHERE username = ?";
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, username);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToUser(rs);
                }
            }
        }
        return null;
    }

    // ✅ SECURE - Authentication with multiple parameters
    public boolean authenticateUser(String username, String password) throws SQLException {
        String query = "SELECT id FROM users WHERE username = ? AND password = ?";
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    // ✅ SECURE - Insert with parameters
    public int createUser(String username, String email, String password) throws SQLException {
        String query = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, username);
            pstmt.setString(2, email);
            pstmt.setString(3, password);
            pstmt.executeUpdate();

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getInt(1);
                }
            }
        }
        return -1;
    }

    // ✅ SECURE - Update with parameters
    public boolean updateUserEmail(int userId, String newEmail) throws SQLException {
        String query = "UPDATE users SET email = ? WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, newEmail);
            pstmt.setInt(2, userId);
            return pstmt.executeUpdate() > 0;
        }
    }

    // ✅ SECURE - Search with LIKE and parameters
    public List<User> searchUsers(String searchTerm, int limit) throws SQLException {
        String query = "SELECT id, username, email FROM users WHERE username LIKE ? LIMIT ?";
        List<User> users = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setString(1, "%" + searchTerm + "%");
            pstmt.setInt(2, limit);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
        }
        return users;
    }

    // ✅ SECURE - Complex query with multiple parameters
    public List<User> findUsersByAgeRange(int minAge, int maxAge, String city) throws SQLException {
        String query = "SELECT id, username, email, age FROM users WHERE age BETWEEN ? AND ? AND city = ?";
        List<User> users = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, minAge);
            pstmt.setInt(2, maxAge);
            pstmt.setString(3, city);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
        }
        return users;
    }

    // ✅ SECURE - Batch operations
    public void createMultipleUsers(List<User> users) throws SQLException {
        String query = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(connectionString);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            for (User user : users) {
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getEmail());
                pstmt.setString(3, user.getPassword());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        return new User(
            rs.getInt("id"),
            rs.getString("username"),
            rs.getString("email")
        );
    }
}

// Usage
UserRepository repo = new UserRepository("jdbc:mysql://localhost:3306/myapp");
User user = repo.getUser("john_doe");
boolean authenticated = repo.authenticateUser("john_doe", "password123");

4. PHP with PDO

```php <?php

class UserDatabase { private PDO $pdo;

public function __construct(string $dsn, string $user, string $password) { $this->pdo = new PDO($dsn, $user, $password); $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); }

// ❌ VULNERABLE - String concatenation public function getUserUnsafe(string $username): ?array { $query = "SELECT * FROM users WHERE username = '$username'"; $stmt = $this->pdo->query($query); return $stmt->fetch(PDO::FETCH_ASSOC) ?: null; }

// ✅ SECURE - Parameterized query with named placeholders public function getUser(string $username): ?array { $query = "SELECT id, username, email FROM users WHERE username = :username"; $stmt = $this->pdo->prepare($query); $stmt->execute([':username' => $username]); return $stmt->fetch(PDO::FETCH_ASSOC) ?: null; }

// ✅ SECURE - Parameterized query with positional placeholders public function authenticateUser(string $username, string $password): bool { $query = "SELECT id FROM users WHERE username = ? AND password = ?"; $stmt = $this->pdo->prepare($query); $stmt->execute([$username, $password]); return $stmt->rowCount() > 0; }

// ✅ SECURE - Insert with parameters public function createUser(string $username, string $email, string $password): int { $query = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)"; $stmt = $this->pdo->prepare($query); $stmt->execute([ ':username' => $username, ':email' => $email, ':password' => $password ]); return (int)$this->pdo->lastInsertId(); }

// ✅ SECURE - Update with parameters public function updateUserProfile(int $userId, string $bio, string $location): bool { $query = "UPDATE users SET bio = :bio, location = :location WHERE id = :id"; $stmt = $this->pdo->prepare($query); return $stmt->execute([ ':bio' => $bio, ':location' => $location, ':id' => $userId ]); }

// ✅ SECURE - Search with LIKE public function searchUsers(string $searchTerm, int $limit = 10): array { $query = "SELECT id, username, email FROM users WHERE username LIKE :search LIMIT :limit"; $stmt = $this->pdo->prepare($query); $stmt->bindValue(':search', "%$searchTerm%", PDO::PARAM_STR); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(PDO::FETCH_ASSOC); }

// ✅ SECURE - Complex query with multiple parameters public function findUsersByFilters(array $filters): array { $conditions = []; $params = [];

if (!empty($filters['username'])) { $conditions[] = "username LIKE :username"; $params[':username'] = "%{$filters['username']}%"; }

if (!empty($filters['minAge'])) { $conditions[] = "age >= :minAge"; $params[':minAge'] = $filters['minAge']; }

if (!empty($filters['maxAge'])) { $conditions[] = "age <= :maxAge"; $params[':maxAge'] = $filters['maxAge']; }

if (!empty($filters['city'])) { $conditions[] = "city = :city"; $params[':city'] = $filters['city']; }

$where = !empty($conditions) ? "WHERE " . implode(" AND ", $conditions) : ""; $query = "SELECT id, username, email, age FROM users $where LIMIT 50";

$stmt = $this->pdo->prepare($query); $stmt->execute($params); return $stmt->fetchAll(PDO::FETCH_ASSOC); }

// ✅ SECURE - Transaction with multiple statements public function transferUserData(int $fromUserId, int $toUserId): bool { try { $this->pdo->beginTransaction();

// Get data from source user $query1 = "SELECT * FROM user_data WHERE user_id = ?"; $stmt1 = $this->pdo->prepare($query1); $stmt1->execute([$fromUserId]); $data = $stmt1->fetchAll