SQL Injection (SQLi)

SQL Injection (SQLi) is one of the oldest and most dangerous web vulnerabilities. It happens when an application builds database queries using untrusted input, allowing an attacker to change the intended query logic. This tutorial explains what SQLi is, why it’s dangerous, common types, how to find it, and — most importantly — robust, practical defenses you can apply today.

What is SQL Injection?

SQL Injection occurs when user-supplied data is inserted into an SQL statement without proper handling. If input is concatenated directly into SQL, an attacker can manipulate the query structure to read, modify, or delete data the application should never expose.

Think of SQL as a language your app uses to ask the database for information. If you let users craft parts of those requests, you give them control over what the database does.

Why SQLi is Dangerous

Data breach: Attackers can read sensitive data (user accounts, PII, payment info).
Data loss or modification: They can delete or change records.
Authentication bypass: Attackers may log in as other users.
Privilege escalation & RCE: In some environments, SQLi can lead to command execution or full system takeover.
Compliance failure: Breaches caused by SQLi often violate regulations (GDPR, PCI-DSS).

Because of these risks, preventing SQLi is a top priority for any web application.

Common Types of SQL Injection (High-level)

Classic / Error-based: Leverages DB error messages to learn about schema.
Union-based: Uses UNION to append attacker-controlled result sets to a legitimate query.
Blind SQLi (Boolean / Time-based): No error output; attackers infer true/false from responses or timing.
Stored / Persistent SQLi: Malicious data is stored (e.g., in a comment) and later used in queries.
Second-order SQLi: Malicious input is stored safely but later used unsafely in another SQL context.

You don’t need to know exploit strings to defend — focus on patterns that close all these vectors.

How SQLi Typically Happens (Common Causes)

Concatenating user input into SQL strings.
Building dynamic SQL without parameter binding.
Over-permissive database accounts used by the app.
Verbose error messages leaked to users.
Missing input validation and output encoding where appropriate.
Unsafe use of ORM .raw() or string-based query builders.

Detecting SQL Injection (Defensive)

Code review: Look for string concatenation with SQL and any direct insertion of request data.
Automated scanning: Use SAST and DAST tools that flag likely injection points.
Penetration testing: Regular authorized tests by security teams.
Monitoring: Watch for unusual DB queries, spikes in slow queries, or repeated error patterns.
Error handling: If your app exposes raw DB errors to users, tighten error handling — such errors often point to exploitable code.

Detection is about finding risky patterns before attackers do.

Fundamental Rules for Prevention (Always follow these)

Use parameterized queries / prepared statements — never build SQL by concatenating user input.
Use ORMs or safe query builders when possible, and avoid raw SQL unless absolutely necessary.
Apply input validation and type checks — validate shapes and types (e.g., ensure IDs are integers).
Enforce least-privilege for database accounts — app DB user should have only the permissions it needs.
Avoid exposing detailed DB errors to end users; log them internally instead.
Use stored procedures only with parameterization (not as a substitute for safe parameter use).
Implement logging and monitoring to detect suspicious queries or large-scale data access.
Use a Web Application Firewall (WAF) as a compensating control, but never as the only defense.
Patch and harden your DB server and keep DB engines up to date.

Secure Coding Examples (Safe patterns)

Below are safe patterns for common stacks. These use parameter binding — the single strongest coding control.

Node.js (mysql2 / pg)

// mysql2 or pg parameterized query example
const sql = 'SELECT * FROM users WHERE id = ?';
const [rows] = await db.execute(sql, [userId]);

Python (psycopg2)

# psycopg2 parameterized
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
rows = cur.fetchall()

PHP (PDO)

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

Java (JDBC PreparedStatement)

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, userId);
ResultSet rs = ps.executeQuery();

.NET (SqlParameter)

using (var cmd = new SqlCommand("SELECT * FROM users WHERE id = @id", conn)) {
  cmd.Parameters.AddWithValue("@id", userId);
  using (var reader = cmd.ExecuteReader()) { ... }
}

Always pass user data as parameters; never interpolate values directly into SQL strings.

Additional Defensive Techniques

Strict input validation: Enforce types, length, and whitelist acceptable values for fields like IDs, enums, and dates.
Output least-privilege: Limit returned columns — don’t SELECT * unless required.
Separate DB roles: Use different DB users for read-only vs write operations.
Prepared statements for dynamic queries: When building dynamic filters, use parameterized building libraries rather than concatenation.
Use ORMs safely: Check how your ORM handles parameters; avoid unescaped raw SQL usage.
Stored procedures with parameters: Use only if you enforce parameterization inside them.
Limit error detail: Display friendly errors to users and log full details to secure internal logs.
Rate-limit suspicious endpoints: Slow down or block repeated malformed requests.

Testing & CI Integration

Add static analysis and dependency checks into CI pipelines.
Automate DAST scanning on staging environments.
Include security unit tests where feasible (validation tests, prepared statement checks).
Run scheduled penetration tests and remediate findings promptly.

Incident Response (If you suspect SQLi)

Isolate: Temporarily block the vulnerable endpoint or IPs if feasible.
Revoke/rotate credentials: If DB credentials may be compromised, rotate them.
Patch: Apply the safe parameterized fix immediately.
Audit logs: Search for anomalous queries or data exfiltration indicators.
Restore from backups if data was altered and integrity is in question.
Notify stakeholders per your incident response plan and legal requirements.
Retest to confirm the fix and monitor for recurrence.

Practical Checklist for Developers (copy into PR templates)

No SQL string concatenation with user input.
All DB queries use parameterization / prepared statements.
Input validation applied for every user-supplied field.
DB user privileges are minimized.
Error messages are generic in user-facing responses.
Logs capture sufficient detail for forensic review.
WAF rules reviewed and updated if necessary.
Automated security tests pass in CI.

Summary

SQL Injection is preventable. The strongest defenses are simple: parameterize, validate, and least-privilege. Treat every piece of external input as hostile until proven safe. Combine secure coding practices with monitoring, patching, and regular testing to keep applications resilient.