SQLi - SQL Injection: making your database say what it shouldn't

SQL injection occurs when user-controlled data is inserted directly into a SQL query, altering its structure and logic. The database then executes what the attacker wrote, not what the developer intended. It's one of the oldest vulnerabilities, and yet still one of the most common.

How it happens

Here is a typical authentication query built by string concatenation: the classic vulnerable code pattern:

Vulnerable code: direct concatenation

// Node.js
const query = `SELECT * FROM users
  WHERE email='${email}'
  AND password='${password}'`;

// PHP
$query = "SELECT * FROM users WHERE email='" . $email . "' AND password='" . $password . "'";

// Python
query = "SELECT * FROM users WHERE email='" + email + "' AND password='" + password + "'"

With normal inputs (email: alice@example.com, password: mypassword), the query is correct. Now with malicious input:

Authentication bypass

email:    admin@company.com' --
password: n'importe quoi

-- Requête résultante (le -- commente le reste) :
SELECT * FROM users WHERE email='admin@company.com' --' AND password='n'importe quoi'

-- Ce que la base de données exécute réellement :
SELECT * FROM users WHERE email='admin@company.com'

-- is the SQL comment in most databases. Everything after it is ignored. The password check is removed. The query returns the admin user, login succeeds.

Exploitation variants

UNION-based: data extraction: If the application's response displays the query result, the attacker can use UNION SELECT to extract any table from the database.

Extracting the users table via UNION

-- Injection dans un paramètre de recherche
id=1 UNION SELECT username, password_hash, email FROM users--

-- L'application affiche maintenant les données de la table users
-- à la place du résultat normal

-- Pour connaître les tables disponibles (MySQL) :
id=1 UNION SELECT table_name, column_name, null FROM information_schema.columns--

Blind SQLi: inference without visible results: If the application doesn't display query results (but behaves differently based on the response), the attacker can extract data bit by bit through boolean questions or time delays.

Blind SQLi: character-by-character extraction

-- Si l'application retourne 200 quand la condition est vraie, 404 quand elle est fausse
-- L'attaquant découvre le mot de passe caractère par caractère :
id=1 AND SUBSTRING(password_hash,1,1)='a'--   → 200 (vrai)
id=1 AND SUBSTRING(password_hash,1,1)='b'--   → 404 (faux)
...

-- Time-based : si l'application n'a même pas de comportement différent
-- On mesure le temps de réponse (MySQL) :
id=1 AND IF(SUBSTRING(password_hash,1,1)='a', SLEEP(3), 0)--

Injection in other contexts: SQL injection is not limited to login forms. It can affect URL parameters, HTTP headers, cookies, JSON or XML payloads sent to an API, anywhere a value is incorporated into a SQL query.

The real impact

• Authentication bypass: admin access without valid credentials
• Data exfiltration: full extraction of tables: users, passwords, client data
• Data modification and deletion: injected UPDATE, DELETE
• System command execution: on certain poorly configured databases (SQL Server with xp_cmdshell, MySQL with INTO OUTFILE)

SQL injection in an unprotected application is often the equivalent of root access to the database, from the internet.

Remediation

1. Parameterised queries (prepared statements): This is the only fundamental protection. The query and the data are sent separately to the SQL engine, which never interprets data as code. It is structurally impossible for a user value to alter the query logic.

Parameterised queries: Node.js (mysql2)

// mysql2
const [rows] = await db.execute(
  'SELECT * FROM users WHERE email = ? AND password_hash = ?',
  [email, passwordHash]
);

// pg (PostgreSQL)
const result = await db.query(
  'SELECT * FROM users WHERE email = $1 AND password_hash = $2',
  [email, passwordHash]
);

Parameterised queries: Python

# psycopg2 (PostgreSQL)
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND password_hash = %s",
    (email, password_hash)
)

# sqlite3
cursor.execute(
    "SELECT * FROM users WHERE email = ? AND password_hash = ?",
    (email, password_hash)
)

Parameterised queries: PHP (PDO)

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND password_hash = :hash');
$stmt->execute(['email' => $email, 'hash' => $passwordHash]);
$user = $stmt->fetch();

2. ORM with secure queries: Modern ORMs (Sequelize, Prisma, SQLAlchemy, Hibernate, ActiveRecord) generate parameterised queries by default. Be cautious with methods that allow injecting raw SQL (Sequelize.literal(), db.raw(), Query.filter() with raw string): they negate the protection.

Watch out for raw queries even with an ORM

// Dangereux - même avec Prisma ou Sequelize
const users = await prisma.$queryRaw(`SELECT * FROM users WHERE email = '${email}'`);

// Correct - paramètre sécurisé
const users = await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

3. Least privilege on the database account: The application's database account should only have access to the tables it needs, read/write only as required. If the application only reads, there's no need for DROP TABLE rights.

Protection summary

1. Always use parameterised queries, no exceptions, even for values that seem safe
2. Audit all string concatenation usages in SQL queries
3. If an ORM is used, identify and protect all raw queries
4. Apply least privilege on the application's database account
5. Never display SQL errors in production: they give the attacker valuable information