🧠 How SQL Injection works

1. The normal flow

SELECT * FROM users
WHERE username = 'alice' AND password = 'wonderland';

One row matches → user is logged in. Everyone is happy.

2. The attack: input becomes code

The vulnerable server concatenates user input straight into SQL:

"SELECT * FROM users WHERE username = '" + input + "' AND password = '" + input + "'"

Attacker types this in the username field:

' OR '1'='1' --

The server happily builds:

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

The -- turns the rest of the line into a comment, so the password check is gone. '1'='1' is always true → the WHERE clause matches the first row → attacker is logged in, often as admin. 💥

3. It's not just login bypass — attackers read & tamper with data

Auth bypass is the easy demo. In the real world, attackers do far more once they can inject SQL:

📤 UNION-based exfiltration (read anything)

Append a second SELECT to the original query and stitch its rows in:

' UNION SELECT id, username, password, secret FROM users --

The "username" column in the response now contains every user's password. Same trick works to read information_schema, other tables, secrets — anything the DB account can see.

🔧 Stacked-query tampering (change anything)

If the driver allows multiple statements per call, ; ends your query and opens a new one:

'; UPDATE users SET password='pwn3d' WHERE username='admin'; --

No login required — and your application logs show only the original SELECT. Drivers like better-sqlite3 or libpq's simple-query interface reject this, but sql.js, MySQL's multi=true, and many ORM raw-query helpers don't. Never rely on the driver — fix it at the query.

⏱️ Blind & time-based

Even when the app returns no data, attackers can ask "is the first letter of admin's password a?" using a CASE + sleep() trick, and read the response time. No errors, no rows leaked — just patience.

4. Why it works — the mental model

"... WHERE username = '" + input + "'"

"... WHERE username = ? AND password = ?"
params: [input1, input2]

5. Defense checklist

• ✅ Use parameterized queries / prepared statements — always, even for "trusted" input.
• ✅ Use an ORM that parameterizes by default. Audit any raw-SQL escape hatches.
• ✅ Hash passwords with bcrypt, argon2, or scrypt — never store plaintext (this demo does, deliberately, so you can see them leak). Even a perfect parameterized query won't save you if an attacker ever does get a dump.
• ✅ Apply least privilege on the DB account — the web app's user should not be able to DROP, alter schema, or read tables it doesn't need.
• ✅ Disable multi-statement support in the DB driver when you don't need it (e.g. MySQL multipleStatements: false).
• ✅ Validate & allow-list input where it makes sense (e.g. enum values, numeric IDs) — as defense-in-depth, not a substitute.
• ✅ Add a WAF as defense-in-depth — but never as the only defense.
• ✅ Log & alert on query errors and unusual row counts — UNION attacks often produce telltale spikes.
• ❌ Don't try to "escape" or blacklist characters yourself. You will miss one.