SQL Injection and Secure Application Access

SQL injection happens when untrusted input is concatenated into SQL text, allowing input to change the query's structure.

Concept

SQL injection happens when untrusted input is concatenated into SQL text, allowing input to change the query's structure. The fix is parameterized queries / prepared statements, where the SQL structure and the data values are sent separately. This module shows vulnerable patterns only to recognize them; it does not provide attack techniques.

Why It Matters

Injection remains one of the most exploited web vulnerabilities. Every MySQL connector supports parameters; using them consistently eliminates the problem.

Syntax

Language / connector Python mysql-connector-python, PyMySQL, mysqlclient %s (or %(name)s) Java JDBC (Connector/J) Node.js mysql2

? (use execute() for true server-side prepared statements) PHP PDO / mysqli ? or :name C# MySqlConnector / Connector/NET Go database/sql + go-sql-driver

PREPARE ... FROM '... ?'; EXECUTE ... USING @v

Example

29.1 Vulnerable vs safe 29.2 Dynamic identifiers: allowlist Table/column names and ASC/DESC cannot be parameters:

29.3 Inside stored procedures 29.4 MySQL-specific hardening Do not enable allowMultiQueries=true (Connector/J) or multi-statement flags unless necessary; they widen the impact of any injection.

Keep the NO_BACKSLASH_ESCAPES mode consistent between client and server if you ever escape manually (better: never escape manually).

Least privilege: an injected query run by an account without DDL/FILE privileges can do far less harm.

Return generic errors to users; log details server-side.

ORMs (SQLAlchemy, Hibernate, Sequelize, Prisma, Django, Laravel Eloquent) parameterize by default — but raw-query APIs with string interpolation are still vulnerable.

# VULNERABLE — user input becomes part of the SQL code
vin = request.args["vin"]
cur.execute("SELECT * FROM vehicles WHERE vin = '" + vin + "'")

# SAFE — the value is sent as data; it can never change the query structure
cur.execute("SELECT vehicle_id, model FROM vehicles WHERE vin = %s", (vin,))

// SAFE — JDBC PreparedStatement
PreparedStatement ps = conn.prepareStatement(
    "SELECT test_case, result FROM test_result WHERE vehicle_id = ? AND executed_at >= ?");
ps.setInt(1, vehicleId);
ps.setTimestamp(2, Timestamp.valueOf(since));
ResultSet rs = ps.executeQuery();

// SAFE — PHP PDO (disable emulated prepares for native server-side prepares)
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->prepare('SELECT order_id, total_amount FROM orders WHERE customer_id = ?');
$stmt->execute([$customerId]);

// SAFE — Node.js mysql2
const [rows] = await conn.execute('SELECT name FROM customers WHERE country = ? AND city = ?', [country, city]);

SORTABLE = {"date": "order_date", "amount": "total_amount"}
col = SORTABLE.get(request.args.get("sort"), "order_date")
direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
cur.execute(f"SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = %s ORDER BY {col} {direction}",
            (customer_id,))

-- VULNERABLE
SET @sql = CONCAT('SELECT * FROM orders WHERE status = ''', p_status, '''');
-- SAFE
SET @status = p_status;
PREPARE s FROM 'SELECT * FROM orders WHERE status = ?';
EXECUTE s USING @status;
DEALLOCATE PREPARE s;

Expected Result

With parameters, input containing quotes or SQL keywords is compared literally as a VIN; no vehicle matches, so the query returns zero rows.

Common Mistakes

String concatenation, f-strings, or template literals to build SQL.

Manual escaping instead of parameters.

Parameterizing values but concatenating identifiers without an allowlist.

Enabling multi-statement execution.

Best Practices

Parameterize every value; allowlist every identifier.

Least-privilege accounts; TLS; secrets in a vault.

Static analysis and code review rules against string-built SQL.

Interview Relevance

Very high: "What is SQL injection and how do you prevent it in MySQL applications?", "Why are prepared statements safe?", "Can stored procedures be vulnerable?".

Practice Questions

Rewrite a concatenated query in your language using parameters.

Implement a safe user-selectable sort column.

Explain how least privilege limits injection damage.