SQL Injection

SQL injection is a vulnerability in which untrusted input is concatenated into a SQL string, allowing the input to change the structure of the query rather than being treated purely as data.

Concept

SQL injection is a vulnerability in which untrusted input is concatenated into a SQL string, allowing the input to change the structure of the query rather than being treated purely as data. It remains one of the most common and damaging web application vulnerabilities (OWASP Top 10 "Injection").

This module explains the mechanism so you can recognize and prevent it. It does not provide attack techniques against real systems.

Why It Matters

Successful injection can expose confidential data, bypass authentication, modify or delete data, and in some configurations compromise the server. Prevention is simple and must be applied consistently.

Syntax

The core fix is parameterization: the SQL text contains placeholders, and values are sent separately.

Driver / language Python DB-API (psycopg, mysqlclient) %s or %(name)s.NET (SqlClient) Node.js (mysql2) ? or :name Go (database/sql) $1 (PostgreSQL) / ? (MySQL)

Example

31.1 How injection happens (vulnerable pattern) If the input is a normal name such as ravi, the query is:

But because the input is inserted into the code of the query, input containing a quote character can end the string literal early and append additional SQL logic — for example, a condition that is always true — so the query returns rows it should not. The root cause is the same in every variant: data is being interpreted as code.

Other vulnerable patterns:

31.2 Parameterized queries (the fix) With parameters, the database receives the query structure and the values separately; a value can never become SQL syntax, whatever characters it contains.

31.3 Prepared statements at the SQL level Prepared statements also allow plan reuse, improving performance for repeated queries.

31.4 Safe dynamic SQL inside the database Identifiers (table/column names, sort direction) cannot be parameterized. Validate them against an allowlist:

31.5 Input validation Validation is a second layer, not a replacement for parameterization:

Check types (an order ID must be an integer).

Check ranges and lengths.

Use allowlists for enumerated values (status must be one of PENDING/SHIPPED/...).

Reject unexpected formats early with clear errors.

Escaping quotes manually is error-prone and not recommended; parameterize instead.

31.6 ORM considerations ORMs (Hibernate/JPA, Entity Framework, SQLAlchemy, Django ORM, Sequelize, Prisma) parameterize normal queries automatically. Risks remain when developers use raw SQL features with string building:

Also watch for: string-built ORDER BY/column names, HQL/JPQL concatenation, and "extra"/"raw" APIs.

31.7 Least privilege as damage control Even if injection occurs, a least-privileged account limits the impact:

The web application's account should not own tables or have DDL rights.

Reporting endpoints should use read-only accounts.

Sensitive tables should be accessible only through views/procedures.

Error messages returned to users should not reveal SQL or schema details (log them server-side instead).

# VULNERABLE — do not do this
username = request.form["username"]
query = "SELECT user_id, role FROM app_users WHERE username = '" + username + "'"
cursor.execute(query)

SELECT user_id, role FROM app_users WHERE username = 'ravi'

// Java — VULNERABLE
String sql = "SELECT * FROM orders WHERE order_id = " + orderIdParam;
statement.executeQuery(sql);

-- Dynamic SQL inside the database — VULNERABLE (SQL Server)
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM customers WHERE city = ''' + @city + N'''';
EXEC (@sql);

# Python (psycopg / PostgreSQL)
cursor.execute(
    "SELECT user_id, role FROM app_users WHERE username = %s",
    (username,)
)

# Python (sqlite3)
cursor.execute("SELECT user_id, role FROM app_users WHERE username = ?", (username,))

// Java JDBC PreparedStatement
String sql = "SELECT order_id, status, total_amount FROM orders WHERE customer_id = ? AND status = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, customerId);
    ps.setString(2, status);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { /* ... */ }
    }
}

// C# / .NET
using var cmd = new SqlCommand(
    "SELECT order_id, total_amount FROM orders WHERE customer_id = @customerId", conn);
cmd.Parameters.Add("@customerId", SqlDbType.Int).Value = customerId;
using var reader = cmd.ExecuteReader();

// Node.js (pg)
const result = await client.query(
  'SELECT order_id, total_amount FROM orders WHERE customer_id = $1 AND order_date >= $2',
  [customerId, fromDate]
);

// PHP PDO
$stmt = $pdo->prepare('SELECT order_id FROM orders WHERE customer_id = :cid');
$stmt->execute(['cid' => $customerId]);

-- PostgreSQL
PREPARE get_orders (INT) AS
    SELECT order_id, total_amount FROM orders WHERE customer_id = $1;
EXECUTE get_orders(2);
DEALLOCATE get_orders;

-- MySQL
PREPARE stmt FROM 'SELECT order_id, total_amount FROM orders WHERE customer_id = ?';
SET @cid = 2;
EXECUTE stmt USING @cid;
DEALLOCATE PREPARE stmt;

Expected Result

With the parameterized version, any input — including text containing quotes — is compared literally as a username. If no user has exactly that username, the query returns zero rows, which is the correct and safe behavior.

Common Mistakes

Building SQL with string concatenation or string formatting (+, f-strings, String.format, template literals).

Believing client-side (browser) validation prevents injection.

Parameterizing values but concatenating identifiers without an allowlist.

Concatenation inside stored procedures (EXEC(@sql)).

Displaying raw database errors to end users.

Connecting as an administrator account.

Best Practices

Always use parameterized queries / prepared statements. Allowlist identifiers and keywords that must be dynamic.

Validate input types and ranges.

Apply least privilege to application accounts.

Use static analysis/code review rules that flag string-built SQL.

Keep drivers, ORMs, and databases patched.

Log and monitor unusual query errors.

Interview Relevance

Very high for any developer role: "What is SQL injection?", "How do you prevent it?", "Why does parameterization work?", "Can ORMs be vulnerable?", "Can stored procedures be vulnerable?".

Practice Questions

Explain in your own words why parameterized queries prevent injection.

Rewrite a string-concatenated query in your favorite language using parameters.

How would you safely support a user-selected sort column?

Why is input validation alone insufficient?

List three ways least privilege limits the impact of an injection vulnerability.