SQL injection

A login check is a question for the database. Is there a row where the username and the password both match what was typed?

Vulnerable

const query = `
  SELECT * FROM users
  WHERE username = '${username}' AND password = '${password}'
`

A row comes back, the credentials were good. No row, they weren't.

That reads like a question. To the database it's a sentence, and the visitor got to write part of it.

A query built by gluing strings together lets the sender decide what the query means.

The bug

SQL is a language, and this query is a program written fresh on every request. Most of it comes from you. Two pieces come from whoever is at the keyboard.

The database never sees that seam. By the time the query arrives, it's one continuous piece of text, and the database has no way of knowing which characters you wrote and which ones a stranger did. It parses the whole thing and does what it says.

That's SQL injection: input that gets read as part of the query instead of as a value inside it. The consequences follow from what SQL can express, which is quite a lot:

• Read rows the requester should never see.
• Change or delete data.
• Run administrative commands against the database system.
• On some setups, reach the operating system underneath.

JunoThe bug The template literal is what makes this hard to spot. It looks like you're filling in a blank, the way you would in a sentence.

What you're actually doing is handing the database a finished sentence and hoping the words someone else supplied are only words.

JunoThe bug In review, the tell is any query assembled with string concatenation or a template literal carrying a variable. Search for backticks and + near SELECT, INSERT, UPDATE and DELETE.

Being logged in doesn't help either. An authenticated user submitting a crafted value is the same bug, and they often have more interesting tables to reach.

JunoThe bug The bug class is broader than SQL, and recognising the shape is what transfers. Any time a value crosses from data into a language with a parser, that parser decides what the value means: a shell command, an XML document, a template, a file path.

Second-order injection is the variant that survives a partial fix. A value is stored safely through a parameterized insert, then read back later and concatenated into a different query by code assuming anything already in the database is trustworthy.

The payload sits inert in a row for months and fires from a reporting job. Parameterize every query, not only the ones touching user-facing input.

The attack

Leave the password alone. Type this as the username:

' OR 1=1 --

Substituted into the template, the database receives:

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

Three characters did the work:

1. The opening ' closes the username string early, so everything after it is read as query syntax rather than as a name.
2. OR 1=1 is a condition that's always true, so the whole WHERE clause is satisfied for every row.
3. -- begins a SQL comment, so the password check after it is text the database ignores.

Every user comes back. The application takes the first row as proof of a successful login and lets the attacker in as somebody else.

Point this at your own database

These payloads change and destroy data. That makes them unsafe anywhere with real records, including a staging copy of production. A local database you can drop and rebuild, or a system you have written permission to test.

JunoThe attack Read the payload as three moves rather than one string, and it stops looking like magic.

Close the quote so you're writing query instead of text. Add a condition that's always true. Comment out whatever you didn't want to deal with.

JunoThe attack Notice the attack didn't need a password, and didn't need to guess one. It removed the password check from the query.

That's why "our login is secure because passwords are hashed" misses the point here. Hashing protects the stored value if the table leaks. It does nothing when the comparison itself is never performed.

JunoThe attack One dialect detail worth carrying, because it has produced wrong conclusions in real testing. In MySQL, -- only begins a comment when followed by whitespace.

So --' is not a comment there, and a payload copied from a PostgreSQL example can fail against MySQL while the underlying bug is fully present. # is MySQL's other comment marker.

Which is the real lesson: a payload that does nothing has told you almost nothing. It rules out one string on one dialect, not the vulnerability.

The other half of the answer is what the query runs as. An application account with DROP rights, or read access to tables the feature never touches, turns one injection into a much larger incident.

Least privilege on the database user is what limits the damage when parameterization gets missed somewhere.

The fix

Stop building the sentence. Describe its structure once, then hand the values over separately:

Fixed

const query = 'SELECT * FROM users WHERE username = ? AND password = ?'

const [rows] = await db.execute(query, [username, password])

The ? marks are placeholders. The exact syntax varies by database and library, $1 and $2 in some, named parameters in others, but the shape is the same everywhere: the query text is fixed before any user value is in the room.

This is called a prepared statement with variable binding, or more commonly a parameterized query.

JunoThe fix The difference is when the database learns the structure. With the glued-together version, structure and data arrive as one lump and the database works out the shape from whatever it's given.

Here the shape is settled first. The values arrive afterwards, as values, and nothing about them can change a decision that has already been made.

JunoThe fix This is the rare security fix that's also the more pleasant code. No quote juggling, no escaping helper to remember, and the driver handles type conversion for you.

Treat any remaining concatenated query as something needing a written reason. An ORM, the object-relational mapper that generates SQL from your code, parameterizes automatically in its normal query methods.

Its raw-SQL escape hatch does not, so that's where to look first.

JunoThe fix Placeholders bind values, never identifiers. Table names, column names, and the direction in ORDER BY cannot be parameterized, because they're part of the structure the database is being told in advance.

So a sort endpoint taking a column name from a query string is still injectable with every value parameterized. The fix there is an allowlist: map the user's input to a known-good identifier and reject anything unmatched.

Build that allowlist with a Map, Object.hasOwn() or Object.create(null). A plain object literal is permeable, because ALLOWED[userInput] returns Object.prototype.toString for the input toString, which is truthy and sails through a naive check.

Why the fix works

Nothing here inspects the input or removes anything from it. ' OR 1=1 -- still arrives exactly as typed.

What changed is where it lands. The database already knows it's looking for one username and one password, so the payload gets compared as a username, character for character, against a column of names.

No account is called ' OR 1=1 --. No row comes back, the login fails, and that's the correct answer.

That's the defence at the database layer, and it sits alongside the others this section has been building:

Layer	What it decides
Browser validation	Whether the form submits, for honest users only
Schema validation on the server	Whether the request is the right shape to accept at all
Safe output	Whether a stored value can become code in a page
Parameterized queries	Whether a value can become part of a query

JunoWhy the fix works The payload isn't defused. It's being read as a name now, and it's a very unusual name that nobody has.

That's worth holding onto: the safest fixes tend to change what a value is treated as, rather than trying to work out whether it looks dangerous.

JunoWhy the fix works This is why escaping quotes by hand is the wrong instinct even when it appears to work. You end up maintaining a guess about one database's quoting rules.

The guess breaks on a different dialect, a different character encoding, or a numeric field where no quotes were involved to begin with.

Parameterization removes the question rather than answering it.

JunoWhy the fix works Worth being precise about what parameterization does and doesn't cover, because "we use an ORM" gets treated as a finished answer.

It covers values in a query whose structure you fixed. It does not cover identifiers, raw-SQL escape hatches, dynamically assembled WHERE fragments, or a stored procedure that concatenates internally. Each of those is a place the guarantee stops.

The reason schema validation still earns its place on top is that the two answer different questions. Parameterization makes a value safe for the database. Validation decides whether you wanted a 4,000-character username in the first place, which is the answer the database layer has no opinion about.

Try it

A registration form inserts a name and an email:

Vulnerable

const query = `
  INSERT INTO users (name, email)
  VALUES ('${name}', '${email}')
`

Play the attacker. Craft a value for the email field that would delete the users table outright.

Three things to work out: how to stop being inside the quoted value, how to stop being inside the brackets, and how to end one statement so a second one can start.

Compare your answers

The email value is:

'); DROP TABLE users; --

Which produces:

INSERT INTO users (name, email)
VALUES ('Dave', ''); DROP TABLE users; --')

Four moves, matching the three questions plus a tidy-up:

• ' closes the string the email was sitting inside.
• ) closes the VALUES bracket, completing the INSERT as a valid statement.
• ; ends that statement, so what follows is read as a new one.
• -- comments out the ') left dangling at the end, which would otherwise be a syntax error and cause the whole thing to be rejected.

The last move is the one people miss. Without it the statement is malformed, the database refuses all of it, and the attack fails for reasons unrelated to your defences.

Worth knowing: many drivers reject multiple statements in a single call by default, so this exact payload doesn't always land. That's a configuration protecting you, not a fix. The same hole still reads any data an attacker wants through a UNION SELECT, which needs no second statement at all.

Parameterize the query and the whole string becomes somebody's unusual email address, stored as typed.

Where this goes next

Three chapters, three bugs, one cause. Nothing checked what arrived before the application acted on it, so a value chosen by a stranger decided what the code did.

Each fix so far has been at the point of use: the right property for rendering, a limit on size, a placeholder in a query. Those are necessary, and they're also late: repeated at every destination, and simple to miss once.

Zod fundamentals starts the other half, describing the shape you expect once, at the boundary, and refusing everything that doesn't match.