SQL Injection: How It Works and How to Prevent It
On this page
What is SQL injection?
A login form builds a query by gluing strings together. The developer expects a username. The attacker sends a fragment of SQL instead, and the database can't tell the difference: it runs whatever the final string says. That's SQL injection, still one of the most damaging vulnerabilities after more than 25 years.
SQL injection demo: OR 1=1 and UNION SELECT
A small table and the same lookup written two ways, using Node's built-in SQLite:
// VULNERABLE: input is spliced into the SQL text
const vulnerable = (name) =>
db.prepare(`SELECT id, username, email FROM users WHERE username = '${name}'`).all();
// SAFE: the query text is fixed; input is sent separately as a parameter
const safe = (name) =>
db.prepare('SELECT id, username, email FROM users WHERE username = ?').all(name);Three inputs:
input: bo
concatenated : [{"id":2,"username":"bo","email":"bo@example.com"}]
parameterized: [{"id":2,"username":"bo","email":"bo@example.com"}]
input: x' OR '1'='1
concatenated : [{"id":1,"username":"ana",...},{"id":2,"username":"bo",...},{"id":3,"username":"cy",...}]
parameterized: []
input: x' UNION SELECT id, username, CAST(is_admin AS TEXT) FROM users --
concatenated : [{"id":1,"username":"ana","email":"1"},{"id":2,"username":"bo","email":"0"},{"id":3,"username":"cy","email":"0"}]
parameterized: []' OR '1'='1closes the string and adds a condition that's always true: every user comes back. In a login query, that can mean logging in without a password.UNION SELECTappends a second query and smuggles its results into the response: here, which users are admins, shown in the email column. Real attacks pull password hashes or entire tables this way.- With parameters, both payloads are just strange usernames that match nothing.
When the results aren't shown: blind SQL injection
Many vulnerable pages never display query results; a login form just says yes or no. That doesn't save them. In blind SQL injection the attacker asks yes-or-no questions and reads the answer from the page's behavior:
x' OR (SELECT substr(password_hash, 1, 1) FROM users WHERE username = 'ana') = 'a' --If the page behaves as "found", the first character is a; if not, try b. A script asks thousands of these questions and rebuilds the whole value one character at a time. When even the page looks identical either way, attackers add a deliberate delay (pg_sleep(5) in PostgreSQL) and read the answer from the response time. So "we never show query results" isn't a defense; parameters are.
A related trap is second-order injection: input is stored safely, then later read back from the database and concatenated into another query by code that assumed "it's from our own database, so it's safe". Treat data from your own tables as untrusted too when building queries.
Why parameterized queries prevent SQL injection
With a parameterized (prepared) query, the SQL text and the values travel separately. The database parses the query's structure first, then binds the values into fixed slots. No value can add a clause, close a string or start a comment, whatever characters it contains. This isn't escaping; it's a different channel.
SQL injection defenses, in order
- Parameterized queries everywhere (
?,$1,:name). This is the fix. - ORMs and query builders (Prisma, SQLAlchemy, Entity Framework, Knex) parameterize for you, but their raw-SQL escape hatches (
$queryRawUnsafe,text()with f-strings) reintroduce the risk. - Allow-lists for identifiers. Table names, column names and
ORDER BYdirections can't be parameters. Map user input to known values:{"newest": "created_at DESC"}[sort]. - Least-privilege database accounts: the app's user shouldn't own the schema, drop tables or read other applications' data. It limits the damage if injection happens.
- Generic errors: database error messages help attackers refine payloads (Day 22).
Escaping input by hand and blocklists of "bad words" (SELECT, --) are not defenses; they miss encodings and edge cases.
NoSQL, LDAP and command injection: the same mistake
The same mistake, mixing code and data in one string, causes NoSQL injection ({"$ne": null} in a MongoDB query), LDAP injection, and command injection (Day 14). The fix is always the same idea: keep structure and data apart.
Check yourself
The value is bound into a slot after the query's structure is parsed, so it's compared as a literal username and can't add SQL logic.
No; parameters hold values only, not identifiers like column or table names. Map the user's choice to an allow-listed column name in code, such as {"date": "created_at"}, and reject anything else.
Defense in depth: if an injection slips through somewhere, in a raw query or a stored procedure, an account that can only read and write its own tables can't drop tables, read other schemas or run admin commands. It limits the damage of one mistake.
Yes, through blind SQL injection: the attacker asks yes-or-no questions and reads the answer from the page's behavior or response time.
Mostly, when you use their query builders. Raw query methods with string concatenation are still vulnerable, so review any raw SQL in ORM code.