Input Validation and Injection Attacks
Why parameterized queries stop SQL injection, and the broader discipline of validating input and encoding output before it reaches another interpreter.
Input Validation and Injection Attacks
The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.
Not started
What you'll learn
- SQL injection happens when user input gets concatenated directly into a query string, so a quote mark or a semicolon in the input can change the query's actual structure instead of just supplying a value.
- Parameterized queries (prepared statements) fix this at the root by sending the query structure and the user's data to the database separately — the database never interprets the data as part of the command, no matter what characters it contains.
- An ORM doesn't make injection impossible on its own; it makes it the default-safe path, because its query-builder methods parameterize automatically. Dropping into raw SQL or string-built queries inside an ORM re-opens the same hole.
- Allow-listing (defining exactly what valid input looks like and rejecting everything else) is a stronger validation strategy than block-listing (trying to list every bad pattern), because an attacker only needs to find one pattern you forgot to block.
- The same root cause — untrusted data getting interpreted as code instead of as data — shows up beyond SQL: command injection (shell metacharacters in a string passed to a shell), and template injection (user input evaluated as template syntax instead of rendered as plain text).
Why this keeps happening
SQL injection has been a known, named, well-documented vulnerability since the late 1990s, it’s been in every “top ten web vulnerabilities” list ever published, and it is still one of the most common ways real production databases get compromised. That’s not because the fix is obscure — it’s because the vulnerable pattern is the first thing that occurs to someone who hasn’t been shown why it’s wrong, and it looks completely reasonable: you take a value from the request and put it into your query string. In most languages that’s a one-line string concatenation or an f-string, and it works fine in every manual test you run, because your manual tests don’t type a single quote into the username field.
The video above (Computerphile, with Tom Scott) demonstrates exactly this failure mode end to end — the kind of query that looks innocent, the character that breaks it, and the fix — and its “Prepared Statements” section is the part worth paying closest attention to. The written sections below extend past what one video can cover: the ORM angle, allow-listing versus block-listing, and injection beyond SQL.
The mechanism: data becomes code
Here’s a query built the naive way, in pseudocode that could be Python, PHP, JavaScript, or almost anything else:
query = "SELECT * FROM users WHERE username = '" + username + "'"
If username is alice, you get SELECT * FROM users WHERE username = 'alice' — fine. But the database doesn’t know that username was supposed to be “just a value.” It only sees the final string. If an attacker submits ' OR '1'='1, the resulting query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1'
'1'='1' is always true, so this returns every row in the table, bypassing whatever the WHERE clause was supposed to restrict. That’s a relatively mild example — the same technique, pushed further, can append entirely new statements (; DROP TABLE users; --) depending on what the particular database driver allows in a single call. The underlying bug is the same in every variant: user-controlled text was allowed to change the structure of a command, not just supply a value inside it.
The fix: parameterized queries
The reason escaping input (manually replacing quotes with escaped quotes, stripping semicolons, and so on) is a bad long-term fix is that you’re trying to anticipate every character and encoding that could matter to the underlying parser, across every database driver and client encoding you’ll ever use. It’s exactly the kind of problem that’s easy to get mostly right and permanently vulnerable to the one case you missed.
The actual fix removes the problem instead of patching around it. A parameterized query (also called a prepared statement) sends the query’s structure and its data to the database as two separate things:
query = "SELECT * FROM users WHERE username = ?"
db.execute(query, [username])
The database first compiles the query shape — with a placeholder standing in for a value — and only afterward substitutes username into that placeholder slot, strictly as a value. It’s not that the database “escapes” the quote character correctly this time; it’s that the value never passes through the SQL parser as text at all, so there’s no parsing step left for an attacker to hijack. A username of ' OR '1'='1 is just a (failed) literal string to match against, exactly as harmless as it should be. This is why the fix is categorical rather than a matter of being careful enough: parameterization isn’t a better escaping function, it’s the removal of the step that made escaping necessary in the first place.
Every mainstream database driver supports this natively — it’s rarely more than a few characters of extra syntax compared to the unsafe version, which is part of why there’s no longer a good excuse for building queries by concatenation.
Where ORMs fit in
An ORM (object-relational mapper) like SQLAlchemy, Prisma, ActiveRecord, or Entity Framework generates SQL for you from method calls — User.where(name: input) instead of a hand-written query string. Under the hood, these query-builder methods parameterize automatically, so using an ORM’s normal API is, by default, immune to this entire class of bug without you thinking about it.
The catch: almost every ORM also offers a raw-SQL escape hatch, for the queries too complex or too performance-sensitive to express through the builder API. If you reach for that escape hatch and build the raw SQL string with concatenated user input — which is easy to do, because it looks exactly like the vulnerable pattern above — you’ve reintroduced the vulnerability inside a codebase that otherwise looks safe. The lesson isn’t “ORMs prevent injection,” it’s “ORMs make the safe path the default path, but the unsafe path is still reachable, and it still has the same bug it always had.”
Validation: allow-listing versus block-listing
Parameterized queries solve the SQL-structure problem specifically. A broader discipline sits above it: deciding what counts as valid input for a field at all, independent of which backend system eventually consumes it.
Block-listing means trying to enumerate the bad: reject input containing <script>, reject semicolons, reject certain keywords. The structural problem with block-listing is that you’re trying to predict every dangerous pattern in advance, across every encoding (URL-encoded, Unicode look-alikes, case variations) an attacker might use to sneak the same payload past your filter. History has not been kind to block-lists — they get bypassed constantly, because finding the one gap in a list is exactly the kind of puzzle attackers are good at.
Allow-listing flips the framing: define precisely what valid input looks like for this field — an email matches a specific shape, a US ZIP code is five digits, a status field is one of exactly three enum values — and reject anything that doesn’t match that definition. This is a strictly easier problem, because “valid” is usually a small, well-defined set, while “invalid” is everything else in an effectively infinite space. Allow-listing isn’t always possible (a free-text comment field has no tight shape to enforce), but wherever a field has a knowable valid shape, prefer constraining to it over trying to filter out danger.
Beyond SQL: the same bug, different interpreter
SQL injection is the most famous member of a broader family of injection bugs, and recognizing the family resemblance is the useful takeaway, even at a survey level:
- Command injection happens when user input reaches a call that runs a shell command —
exec(),os.system(), a subprocess call built from a string — and the input contains shell metacharacters (;,|,&&, backticks) that the shell interprets as command separators or substitutions instead of as plain argument text. The fix mirrors SQL’s: pass arguments as a list to a function that invokes the program directly, never build a shell command string by concatenating user input into it. - Template injection happens when user input is substituted into a template before the template engine renders it, rather than being passed in as a value the template merely displays. If a template engine evaluates expressions inside
{{ }}or similar delimiters, and an attacker’s input contains that syntax, it can execute as template logic instead of appearing as inert text — sometimes with enough reach to execute arbitrary code on the server.
In every case, the question to ask is the same: is this untrusted data about to be parsed as commands by whatever receives it, or is it staying data all the way through? For the architecture-level view of where injection sits among other classes of web vulnerability, see OWASP Top 10 for Architects — this lesson is the hands-on coding discipline underneath that broader survey, not a restatement of it.
Check yourself
What specifically goes wrong when a query is built like `"SELECT * FROM users WHERE name = '" + userInput + "'"`?
If `userInput` contains a single quote, it closes the string literal early, and anything after it is parsed as SQL rather than as data. An input like `' OR '1'='1` turns the WHERE clause into something always true, and more dangerous inputs can append entirely new statements — the fix isn't escaping harder, it's never building the query as a string with user data mixed into it in the first place.
Why does a parameterized query prevent injection even if the input contains SQL syntax like quotes or semicolons?
In a parameterized query, the query's structure (the SQL text with placeholders) is sent to the database separately from the actual values. The database compiles the structure first, then substitutes the supplied values purely as data into those placeholders — it never re-parses the values as SQL text, so nothing inside them can alter the query's shape, regardless of what characters they contain.
If a team uses an ORM for all their queries, are they automatically safe from SQL injection?
Only for the parts that stay inside the ORM's own query-building API, which parameterizes under the hood. Most ORMs also offer an escape hatch for raw SQL or raw string fragments (for complex queries the ORM can't express cleanly), and if user input gets concatenated into one of those raw strings, the same injection vulnerability reappears — the ORM doesn't protect code paths it isn't actually building the query for.
Why is allow-listing generally considered stronger than block-listing for input validation?
Block-listing requires you to anticipate and enumerate every dangerous pattern in advance, and attackers are specifically good at finding the one pattern, encoding, or edge case the list missed. Allow-listing flips the default: define the narrow set of input that's actually valid for that field (an email format, a positive integer, a value from a fixed enum) and reject anything that doesn't match, so an attacker has to produce something that looks like legitimate data, not just avoid a few blocked characters.
How does command injection or template injection relate to SQL injection, mechanically?
All three share the same root cause: untrusted input gets passed to an interpreter — a SQL engine, a shell, a template renderer — in a way that lets the input influence the interpreter's *commands*, not just supply a value the interpreter was already going to use. Command injection happens when user input reaches a shell call with metacharacters like `;` or `|` left unescaped; template injection happens when user input is substituted into a template string before rendering, instead of being passed in as data the template merely displays.
Go deeper
Notes
Notes are saved to your account.
Everything else on this page works without one — progress and bookmarks keep themselves in this browser and come with you when you sign in. Notes are the one thing worth not trusting to a browser you might clear.
Your notes could not be loaded.