SQL Injection

Asking a database for something means composing a sentence called a query and sending it. The easiest way to compose one is to take a value from the screen and paste it into the middle of the sentence as text. Take the username, drop it into the sentence, send it off. Queries were built this way for a long time, and they worked.

The problem is that this puts the value and the command on the same line. Someone types a single quote into the username field on a login screen and the error page prints the query back at them. What that moment reveals isn't that an error occurred. It's that the input field was a place where the query could be edited.

Once that's visible, the field for a name becomes a way into someone else's account, and the field for a search term becomes a way to read another company's list of documents. Nothing about the password got broken — the sentence that checks the password got rewritten. Which is why authentication built carefully still counts for nothing while this door is open, and why the vulnerability outlived the decade it was named in.

SQL injection is the name for this, and it's the reason separating values from commands became the default rather than a precaution.

It happens wherever the place for a value and the place for a command aren't distinguished. Build a query by joining text and the characters inside a value can be read as command, and the database has no way to tell the difference. If what arrives is grammatical, it runs.

Dictation is the closest comparison. Someone reading a sentence aloud to a person writing it down can say "erase what you've written so far," and nothing in the sound itself decides whether that gets written or obeyed. A query assembled from joined text is in exactly that position.

What becomes possible divides into four.

1. Bypassing a condition. Make the check on a password always true and walk into someone else's account.
2. Reading other people's data. A list that should hold only your documents comes back holding other accounts' too.
3. Changing and deleting. It doesn't stop at reading — values get rewritten and whole tables get dropped.
4. Getting out of the database. Depending on configuration, this reaches reading files on the server or running commands there.

The defenses have an order.

1. Use prepared statements. Send the database a sentence with the slot for the value left empty, then hand over the value separately. The value is treated as a value from the start, so nothing written in it can become a command. This is the only root fix; everything below it is support.
2. Use an ORM, the tool that maps objects in code to database tables. Its default path is the method above, so protection comes for free — and it ships with an escape hatch for writing raw queries, and that one line is where the door reopens.
3. Treat input validation as secondary. Stripping or blocking quote characters has several ways around it.
4. Apply least privilege, granting only the access actually needed. This shrinks the blast radius rather than preventing entry. If the account behind a read-only feature has no delete permission, item 3 above never happens.
5. Keep queries out of error pages. It's the first clue an attacker looks for, and it's free to remove.

Anywhere a value someone supplied reaches a query. Four places deserve the most attention.

The first is search, filters, and sorting. The values a user picks go straight into conditions, which makes this the most common location by a wide margin.

The second is login. One condition decides all of authentication there, so the damage is the largest available.

The third is admin screens. They tend to allow conditions to be combined freely, and "only staff use it" makes review go slack on exactly the screens with the most reach.

The fourth is input nobody thinks of as input: request headers, cookies, the filename of an upload, values arriving from another system. Review only the fields visible on screen and this whole category goes unexamined. A filename that reaches a query is the same hole wearing different clothes.

The places to catch it are fixed too. Code review, where a colleague reads the change before it merges, is the cheapest place to spot a query assembled from joined text. Automated scanners sweep for the same pattern, and a penetration test, which attempts real attacks, comes last. This item has stayed on the OWASP Top 10, the list of the most common web vulnerabilities, because it keeps being found.

"This query is being built by joining strings. Please switch it to binding."

Value and command are mixed, so fix it by separating them.

"We use an ORM and this one spot writes raw SQL."

An exception has been found outside what the tool was protecting.

"The error page prints the query."

Separate from whether a hole exists, clues are being handed out.

At Pagedeck, Theo found a sort order assembled by joining strings while working on search. A column name is part of the sentence rather than a value, so it can't be passed as a bound parameter. He replaced it with a list of permitted column names and a check that the incoming value is one of them.

Grace set external testing at once a year after that. Her reasoning was that what code review catches and what an actual attempt finds are different sets, and enterprise customers had started asking for the result before signing. The first test paid for itself by finding two spots review had waved through.

The most common mistake is believing that filtering out quote characters closes it. Textbook examples open with a quote, so blocking that looks sufficient. But the ways around it are numerous: writing the same character in a different encoding, using a comment marker to cut off the rest of the sentence, or working through a numeric field where no quote is needed at all. Responding by extending the blocklist means the list keeps growing while new bypasses keep arriving. Separating values from commands leaves nothing to filter.

The second is treating an ORM as safety. While everything stays on the default path it genuinely does protect, so the belief is easy to hold. Then performance or a complicated aggregation pushes someone into the raw query escape hatch, and the door is open again in that one line. Places that can't be passed as values, like a column name to sort by, stay open too. Using the tool and knowing where the tool stops have to travel together.