SQL Injection Explained and Prevented

How a simple string formatting mistake can compromise an entire database — and the one technique that prevents it.

SQL injection has been consistently ranked among the most dangerous web application vulnerabilities for over two decades. Despite being well-understood and easily preventable, it continues to appear in modern applications because developers still make the same fundamental mistake: trusting user input as part of a SQL query.

This article explains what SQL injection actually is, demonstrates the vulnerable patterns, shows why parameterized queries work, and provides a practical defense checklist.

What Is SQL Injection?

SQL injection happens when user-supplied input is placed directly into a SQL query string using string concatenation or formatting. This allows the input to alter the query's structure, not just its data values.

Consider a login query built with an f-string:

# DANGEROUS - Do NOT use in production
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(query)

If a user enters ' OR '1'='1' -- as their username, the resulting SQL becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' --' AND password = 'anything'

The -- comments out the password check. The condition '1'='1' is always true. The attacker logs in without knowing any password.

The Four Types of SQL Injection

SQL injection manifests in several forms, each with different goals:

The One Fix: Parameterized Queries

Parameterized queries (also called prepared statements) separate the SQL structure from the data. The database engine parses the query template first, then binds the parameters as values — never as executable SQL.

# SAFE - Use parameterized queries
query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))

When the attacker enters ' OR '1'='1' --, the database treats the entire string as a literal value to match against the username column. It cannot alter the query structure because the SQL was already parsed before the parameters were bound.

This works identically across all major databases:

Proof: Local Demo Results

We built a local SQLite demo that executes both the vulnerable and parameterized versions of the same query. Here are the actual results:

Authentication Bypass Test

Input: ' OR '1'='1' --

[VULNERABLE] Generated SQL:
  SELECT id, username, role FROM users
  WHERE username = '' OR '1'='1' --' AND password_hash = 'anything'
  Result: [(1, 'alice'), (2, 'bob'), (3, 'charlie'), (4, 'diana')]
  >>> LOGIN BYPASSED - attacker got access without a password!

[SAFE] Generated SQL:
  SELECT id, username, role FROM users WHERE username = ? AND password = ?
  Parameters: ("' OR '1'='1' --", 'anything')
  Result: []
  >>> No match - payload treated as literal text.

Data Extraction Test (UNION-Based)

Input: ' UNION SELECT username, password_hash, role FROM users--

[VULNERABLE] Generated SQL:
  SELECT username, email, role FROM users WHERE username LIKE '%'
  UNION SELECT username, password_hash, role FROM users--%'

  EXPOSED: username=alice, data=a1b2c3d4e5
  EXPOSED: username=bob, data=x9y8z7w6v5
  EXPOSED: username=charlie, data=m3n4o5p6q7
  EXPOSED: username=diana, data=r8s9t0u1v2
  >>> All credentials stolen!

[SAFE] Generated SQL:
  SELECT username, email, role FROM users WHERE username LIKE ?
  Parameters: ("%' UNION SELECT username, password_hash, role FROM users--%",)
  Results: []
  >>> No data exposed - payload is just search text.

How Parameterized Queries Work Under the Hood

The key insight is the separation of code from data:

VULNERABLE (String Formatting):

  User Input  ──→  f-string  ──→  Complete SQL  ──→  Execute
                        |
                   Input IS part of SQL
                   syntax — that's the bug

SAFE (Parameterized Query):

  SQL Template:  SELECT * FROM users WHERE name = ?
  Parameters:    ('alice')
                           |
  Database parses SQL FIRST  (no user input)
  Then binds parameters as DATA  (not code)
                           |
  User input can NEVER change query structure

When you write WHERE name = ?, the database engine prepares the query plan — it knows exactly which tables to access, which columns to return, and how to filter. The ? placeholder is filled in afterward as a value, not as SQL syntax.

This is why parameterized queries are the defense against SQL injection. No amount of clever escaping, filtering, or input validation can match the fundamental security of separating code from data.

Defense in Depth: Additional Layers

While parameterized queries are the primary defense, defense in depth means adding complementary protections:

Language-Specific Parameterized Query Examples

Python (sqlite3 / psycopg2 / mysql-connector)

# sqlite3
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# psycopg2 (PostgreSQL)
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# SQLAlchemy ORM (auto-parameterized)
user = session.query(User).filter(User.id == user_id).first()

JavaScript / Node.js

// mysql2
connection.execute("SELECT * FROM users WHERE id = ?", [userId]);

// pg (PostgreSQL)
client.query("SELECT * FROM users WHERE id = $1", [userId]);

PHP (PDO)

// PDO - named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => $userId]);

Java (PreparedStatement)

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

C# / .NET

// SqlCommand with parameters
cmd.CommandText = "SELECT * FROM users WHERE id = @id";
cmd.Parameters.AddWithValue("@id", userId);

The ORM Safety Net

Modern ORM frameworks (SQLAlchemy, Django ORM, ActiveRecord, Sequelize, Entity Framework) use parameterized queries under the hood. If you use an ORM correctly, you get SQL injection protection for free:

# Django ORM - automatically parameterized
User.objects.filter(username=user_input)

# SQLAlchemy - automatically parameterized
session.query(User).filter(User.username == user_input).first()

# ActiveRecord (Rails) - automatically parameterized
User.where(username: user_input).first

Warning: ORMs are not immune. If you use raw SQL methods like Django's cursor.execute(raw_sql) or SQLAlchemy's text() with string formatting, you bypass the ORM's protections entirely.

Common Vulnerable Patterns to Avoid

SQL Injection Checklist

Use this checklist to audit your codebase:

[  ] All SQL queries use parameterized placeholders
[  ] No f-strings or string concatenation in SQL queries
[  ] ORMs used correctly (no raw SQL with formatting)
[  ] Database user has least privilege (no DROP/ALTER)
[  ] Input validation applied (type, length, format)
[  ] Output encoded when displaying user data (XSS)
[  ] Error messages hide SQL details from users
[  ] Stored procedures use parameterized calls
[  ] Dynamic table/column names use allowlists
[  ] Regular security scans include SQL injection tests

Dynamic Table and Column Names

A common pitfall: parameterized queries work for values, but you cannot use ? for table or column names. For those, use an allowlist:

# DANGEROUS - table name from user input
cursor.execute(f"SELECT * FROM {user_table}")

# SAFE - allowlist approach
ALLOWED_TABLES = {"users", "orders", "products"}
if user_table not in ALLOWED_TABLES:
    raise ValueError("Invalid table name")
cursor.execute(f"SELECT * FROM {user_table}")
# Even here, prefer fixed queries over dynamic ones

Try It Yourself

Experiment with SQL concepts interactively using BestWordz tools:

• Password Strength Checker — Test how resistant your passwords are to extraction
• JSON Formatter — Structure API responses safely
• HMAC-SHA256 Generator — Understand how parameterized queries protect data like HMACs protect integrity
• Security Headers Analyzer — Check your web application security headers
• CSP Builder — Content Security Policy complements output encoding
• URL Security Analyzer — Inspect URL parameters for injection risks

Related BestWordz Articles

• → Hashing vs Encryption vs Encoding: What's the Difference?
• → How HTTPS and TLS Actually Work
• → JWT Explained: Header, Payload and Signature
• → OAuth 2.0 Explained for Beginners
• → API Authentication Methods Compared

Further Reading

• → OWASP: SQL Injection
• → CWE-89: SQL Injection
• → OWASP SQL Injection Prevention Cheat Sheet
• → NIST NVD: SQL Injection Entries

Conclusion

SQL injection is one of the oldest and most well-documented vulnerabilities in software. It remains prevalent because developers still concatenate user input into SQL strings — a mistake that is both trivially easy to make and trivially easy to prevent.

The fix is simple: never build SQL queries by formatting strings with user input. Use parameterized queries everywhere. Your database driver handles the escaping correctly, and the database engine ensures user input is treated as data, never as executable SQL.

Add defense in depth — input validation, least-privilege database users, output encoding, and error message sanitization — but always start with parameterized queries. It is the single most effective defense against SQL injection.