Mohamed Idris

Posted on May 29

Part 13: SQL Injection and Staying Safe

#sql #database #learning

This post is part of the SQL: Zero to Ninja series.

You built a login form. A user types their email, you look them up, and you log them in. Works great. Then one day a stranger types something weird into that email box, and suddenly they are reading every user's data, or your users table is just... gone. That is SQL injection, and it is one of the oldest and nastiest bugs on the web. The good news: once you see how it happens, fixing it is easy and you never have to fear it again.

The idea in one line

SQL injection happens when you glue user input straight into your query as text, so the user can sneak in their own SQL, and the fix is to send the input as data, never as part of the query.

The metaphor: a form letter with blanks

Imagine a form letter:

Dear __________, your order #_______ is ready.

You fill the blanks with a name and a number. The sentence shape never changes. The blanks are just blanks. Safe.

Now imagine instead you hand a stranger your pen and say "write the whole letter yourself, I trust you." A nice person writes their name. A sneaky person writes "Sara. P.S. give me everyone's password." You just let them rewrite the sentence, not only fill the blank.

That is the whole story. Gluing input into SQL = handing over the pen. Using blanks (placeholders) = a safe form letter.

How injection actually happens

Here is the classic mistake. You build the query by adding strings together:

// DANGER: input is glued straight into the query text
const email = req.body.email;
const sql = "SELECT * FROM users WHERE email = '" + email + "'";
db.query(sql);

If a normal person types sara@mail.com, the query becomes:

SELECT * FROM users WHERE email = 'sara@mail.com'

Fine. But what if someone types this into the email box?

' OR '1'='1

Now your glued string becomes:

SELECT * FROM users WHERE email = '' OR '1'='1'

'1'='1' is always true, so the WHERE matches every row. The attacker just dumped your entire users table. No password needed.

It gets worse. Imagine someone types:

'; DROP TABLE users; --

Your query turns into:

SELECT * FROM users WHERE email = ''; DROP TABLE users; --'

That runs two commands. The second one deletes your whole users table. The -- at the end is a SQL comment that hides the leftover quote. (There is a famous xkcd comic about a kid literally named "Robert'); DROP TABLE Students; --" whose school lost all its records. Now you know why it is funny.)

The user did not "hack" anything clever. They just filled the blank with a pen you handed them.

The fix: parameterized queries (use the blanks)

Never build SQL by gluing input. Instead, write the query with placeholders, and pass the values separately. The database keeps them apart.

In Postgres the placeholders look like $1, $2:

// SAFE: the query shape is fixed, email is sent as data
const sql = "SELECT * FROM users WHERE email = $1";
db.query(sql, [email]);   // email goes in its own little box

In MySQL and many other drivers, the placeholder is a ?:

// SAFE: same idea, question-mark style
const sql = "SELECT * FROM users WHERE email = ?";
db.query(sql, [email]);

Now if someone types ' OR '1'='1, that whole thing is treated as one literal email string. The database goes looking for a user whose email is literally ' OR '1'='1 and finds nobody. No table dump. No drama.

Why this is actually safe

This is the key idea, so slow down here.

With placeholders, the database reads the query shape first, before it ever sees the user's value:

Step 1: lock the shape   -->   SELECT * FROM users WHERE email = (blank)
Step 2: drop in the data -->   the blank gets "' OR '1'='1" as plain text

Because the shape is locked in step 1, nothing in the data can add a new OR, a new ;, or a new command. The data can never become code. The pen stays in your hand. That is the whole magic.

Extra layers of safety (defense in depth)

Parameterized queries are the big one. These help too:

Use an ORM or query builder (Prisma, Sequelize, Knex). They use placeholders for you under the hood. Just know: if you drop down to a raw query and glue strings, you are wide open again. The ORM does not save you there.
Validate and constrain input. An email should look like an email. An id should be a number. Reject junk early.
Least privilege for the DB user. The account your app logs in with should not be allowed to DROP TABLE if it only ever reads and inserts. Then even a slip cannot wreck everything.
Do not leak raw SQL errors to users. An error like syntax error near 'DROP' tells an attacker exactly how to probe you. Show a friendly message, log the details privately.

Gotchas juniors hit

"I will just escape the quotes myself." Hand-escaping is a trap. There are edge cases, different databases, and encodings you will miss. Let the driver do it with placeholders.
"I use an ORM, so I am 100 percent safe." Only if you stick to its safe methods. A raw string query inside an ORM is just as dangerous as plain string gluing.
"My form validates the input in the browser." Client-side checks are for friendliness, not security. Anyone can skip your form and hit your API directly. Always validate and parameterize on the server.

Recap

SQL injection happens when user input is glued into the query as text, so it becomes runnable SQL.
Classic attacks: ' OR '1'='1 returns everyone, '; DROP TABLE users; -- destroys data.
The fix is parameterized queries: placeholders ($1 or ?) with values passed separately.
It is safe because the query shape is locked first, so data can never turn into code.
Add backup layers: ORMs (carefully), input validation, least privilege, and hidden error details.

Your turn

Take this vulnerable line and rewrite it safely with a placeholder:

const sql = "SELECT * FROM orders WHERE user_id = " + userId;

Which placeholder style would you use, and where does userId go now? If you can explain to a friend why the safe version cannot be tricked by 1 OR 1=1, you have got it. Up next, Part 14: Window Functions, where we earn the ninja headband.

Top comments (1)

Mohamed Idris • May 29

Part 13 Practice: Spot the Hole, Then Patch It

Time to play defender. Each exercise shows a vulnerable snippet using our shared schema (users, orders, products, order_items). Your job: rewrite it safely with parameterized queries before you peek. Examples use a Postgres-style driver ($1), but the ? style works the same way.

1. Look up a user by email (the classic login bug)

Rewrite this so an attacker typing ' OR '1'='1 cannot dump every user.

const sql = "SELECT * FROM users WHERE email = '" + email + "'";
db.query(sql);

Solution

const sql = "SELECT * FROM users WHERE email = $1";
db.query(sql, [email]);

Why: The query shape is locked before the database sees email. Whatever the user types is treated as one plain string, so it can never add a new condition or command.

2. Find one order by its id

This glues a number straight in. Patch it.

const sql = "SELECT * FROM orders WHERE id = " + orderId;
db.query(sql);

Solution

const sql = "SELECT * FROM orders WHERE id = $1";
db.query(sql, [orderId]);

Why: Even numbers are unsafe glued in. Someone could send 5 OR 1=1 as orderId and grab every order. With a placeholder, orderId is just data in its own box.

3. Insert a new user from a signup form

Two values, two holes. Make both safe.

const sql = "INSERT INTO users (name, email) VALUES ('" + name + "', '" + email + "')";
db.query(sql);

Solution

const sql = "INSERT INTO users (name, email) VALUES ($1, $2)";
db.query(sql, [name, email]);

Why: Each value gets its own placeholder and its own slot in the array, in order. A sneaky name like '); DROP TABLE users; -- becomes a harmless (if weird) literal name, not a second command.

4. Filter products by a category the user picked

Patch this search filter.

const sql = "SELECT * FROM products WHERE category = '" + category + "'";
db.query(sql);

Solution

const sql = "SELECT * FROM products WHERE category = $1";
db.query(sql, [category]);

Why: Same fix, every time. Notice the pattern: never put user input inside the quotes yourself. Hand it to the driver as a separate value.

5. The "I will just escape it myself" trap

A teammate says this is safe because they removed the quotes by hand:

const clean = email.replace(/'/g, "");   // strip single quotes
const sql = "SELECT * FROM users WHERE email = '" + clean + "'";
db.query(sql);

Rewrite it the right way, and note the problem.

Solution

const sql = "SELECT * FROM users WHERE email = $1";
db.query(sql, [email]);   // no manual cleaning needed

Why: Hand-escaping always misses something (other quote styles, encodings, comment tricks). Placeholders let the database handle escaping correctly for you. Do not reinvent it.

6. Conceptual: why do parameterized queries work?

In one or two sentences, explain why a placeholder query cannot be tricked by ' OR '1'='1.

Solution

// In words:
// The database fixes the QUERY SHAPE first (... WHERE email = blank),
// THEN drops the user's text into the blank as pure data.
// Since the shape is already locked, the data can never add a new
// condition or command. Data can never become code.

Why: This is the heart of the whole topic. If you can say "shape first, data second, so data can never become code," you truly understand it.

Great defending. You now know the single most common web security bug and exactly how to shut it down. Glue nothing, parameterize everything, and your apps are miles safer. One more stop: Part 14, Window Functions, the ninja finale.