All Articles

SQL Injection: How It Works and How to Prevent It

Best Practices
#sql-injection#security#parameterized-queries#web-security#sql-interview

What SQL Injection Actually Is

SQL injection happens when untrusted input — usually from a user-facing form, URL parameter, or API request — gets concatenated directly into a SQL query string instead of being treated strictly as data. If an attacker can control part of that string, they can change the query's actual logic, not just the value it operates on. It remains one of the most serious and most common web application vulnerabilities, consistently appearing near the top of industry vulnerability rankings decades after it was first identified.

A Minimal Vulnerable Example

-- Application code builds a query by concatenating user input directly (pseudocode)
query = "SELECT * FROM users WHERE username = '" + user_input + "' AND password = '" + password_input + "'"

-- If a user submits username: alice, this becomes:
SELECT * FROM users WHERE username = 'alice' AND password = 'anything'

This looks safe on the surface — until the input itself contains SQL syntax rather than a plain value.

Classic Attack: Authentication Bypass

-- Attacker submits this as the username: admin' --
query = "SELECT * FROM users WHERE username = 'admin' --' AND password = '...'"

-- The -- starts a SQL comment, so everything after it is ignored:
SELECT * FROM users WHERE username = 'admin'   -- password check never happens

By injecting a quote to close the string literal early, followed by a comment marker, the attacker rewrites the query's actual logic — the password check is never evaluated at all, and the query returns the admin's row regardless of what password was submitted.

Classic Attack: UNION-Based Data Exfiltration

-- A product search page builds a query like this:
query = "SELECT name, price FROM products WHERE category = '" + user_input + "'"

-- Attacker submits: nonexistent' UNION SELECT username, password FROM users --
SELECT name, price FROM products WHERE category = 'nonexistent'
UNION SELECT username, password FROM users --'

If the application blindly displays whatever rows come back (expecting product names and prices), it now unknowingly displays usernames and passwords instead — the attacker used UNION to append an entirely different, unauthorized query onto the intended one.

Classic Attack: Destructive Injection

-- Attacker submits: '; DROP TABLE users; --
SELECT * FROM products WHERE id = ''; DROP TABLE users; --'

Whether this specific example works depends on whether the database driver allows multiple statements separated by semicolons in a single call — many do not by default — but it illustrates the core danger: unsanitized input can inject entirely new, destructive statements, not just alter a WHERE clause.

The Fix: Parameterized Queries (Prepared Statements)

The correct, industry-standard defense is to never build SQL by concatenating input into a string at all. Instead, use parameterized queries (also called prepared statements), where the query structure is sent to the database separately from the data values, which are then bound as parameters — the database never interprets parameter values as SQL syntax, no matter what they contain.

-- Node.js (node-postgres): the query structure and values are sent separately
const result = await client.query(
  'SELECT * FROM users WHERE username = $1 AND password_hash = $2',
  [username, passwordHash]
);

-- Python (psycopg2)
cursor.execute(
  "SELECT * FROM users WHERE username = %s AND password_hash = %s",
  (username, password_hash)
)

-- Java (JDBC PreparedStatement)
PreparedStatement stmt = connection.prepareStatement(
  "SELECT * FROM users WHERE username = ? AND password_hash = ?"
);
stmt.setString(1, username);
stmt.setString(2, passwordHash);

Even if username literally contains the string admin' --, a parameterized query treats it as a single literal value to compare against, not as SQL syntax to execute — the injection attack simply doesn't work, because there's no string concatenation for the attacker to break out of in the first place.

Why "Escaping" Input Isn't a Reliable Substitute

Manually escaping special characters (like doubling single quotes) is sometimes suggested as a lighter-weight fix, but it's fragile and error-prone in practice — different databases and contexts have different, subtle escaping rules, and it's easy to miss an edge case (encoding issues, alternate quote characters, database-specific syntax quirks) that a determined attacker can exploit. Parameterized queries eliminate the entire class of problem structurally, rather than relying on catching every edge case in a manual escaping function. Escaping should never be treated as equivalent to parameterization for security purposes.

ORMs Aren't Automatically Safe

-- Safe: the ORM parameterizes this automatically
User.where(username: params[:username])

-- Still vulnerable: raw SQL fragments built with string interpolation, even inside an ORM call
User.where("username = '#{params[:username]}'")   -- classic injection, just wrapped in an ORM method

Most modern ORMs parameterize queries automatically when you use their standard query-building methods — but nearly every ORM also offers an escape hatch for raw SQL fragments, and using string interpolation inside that escape hatch reintroduces the exact same vulnerability. The rule is about how the query is constructed, not which library is used to construct it.

Defense in Depth: Additional Layers

  • Least-privilege database accounts — the application's database user should only have the specific permissions it actually needs (e.g. no DROP TABLE privilege for a web-facing account), limiting the damage even if an injection somehow succeeds
  • Input validation — reject obviously malformed input early (e.g. an email field that doesn't match an email pattern) as a defense-in-depth layer, though this is a complement to parameterized queries, never a substitute for them
  • Web application firewalls (WAFs) — can catch some known injection patterns at the network layer, but are a supplementary safeguard, not a reliable primary defense, since attackers regularly find bypasses for signature-based detection
  • Regular dependency and framework updates — some historical injection vulnerabilities have come from bugs in ORM or driver libraries themselves, not just application code

Common Mistakes

  • String concatenation or interpolation to build any query with user input — the root cause of essentially every SQL injection vulnerability, regardless of language or framework.
  • Assuming an ORM makes raw SQL safe by default — raw SQL fragments inside an ORM's escape hatch are just as vulnerable as raw SQL anywhere else if built with string interpolation.
  • Relying on manual escaping instead of parameterized queries — fragile and easy to get subtly wrong across different input types and database contexts.
  • Granting a web application's database account excessive privileges — turns a successful injection into a far more damaging incident than it needed to be.

Common Interview Questions

  1. What is SQL injection, and how does it work? Untrusted input concatenated into a SQL string is interpreted as executable SQL syntax rather than as pure data, letting an attacker alter the query's logic.
  2. How do parameterized queries prevent SQL injection? The query structure and the data values are sent to the database separately, so parameter values are always treated as literal data, never as SQL syntax, regardless of their content.
  3. Is escaping user input as safe as using parameterized queries? No — escaping is fragile and error-prone across edge cases; parameterized queries eliminate the vulnerability structurally rather than relying on catching every edge case manually.
  4. Can SQL injection happen even when using an ORM? Yes, if raw SQL is built with string interpolation inside the ORM's raw-query escape hatch — the ORM itself doesn't protect against that specific pattern.

Frequently Asked Questions

Is SQL injection still a relevant threat today, given how well-known it is?

Yes — despite being understood for decades, it remains one of the most commonly exploited vulnerabilities in real-world breaches, largely due to legacy code, hand-rolled SQL in scripts and internal tools, and inconsistent application of parameterized queries across large codebases.

Does using a NoSQL database eliminate injection risk entirely?

No — NoSQL databases have their own analogous injection risks (often called NoSQL injection) when queries are built by concatenating untrusted input into a query object or string, particularly in systems that support a JavaScript-like query syntax. The same parameterization principle applies regardless of database type.

Build Secure SQL Habits

Understanding SQL injection is valuable both for interviews and for writing genuinely safer production code from day one. Try secure query patterns hands-on with our SQL practice questions, or work through realistic backend scenarios in our case studies. For the subquery and JOIN fundamentals that underlie safe query construction, see our subqueries guide.

Back to All Articles