SQL Injection Defenses

Reviewed & published by Brayan K

By the end of this lesson you'll understand exactly how SQL injection turns user input into runaway SQL — and you'll be able to shut it down with parameterised queries, allow-lists, and least-privilege accounts. This is the single most important security skill in all of databases.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• How string-concatenated queries let attackers inject SQL
• The classic attacks: ' OR '1'='1 and '; DROP TABLE --
• Parameterised queries / prepared statements — the #1 fix
• Input validation and allow-lists for table/column names
• Least-privilege DB accounts to limit the blast radius
• ORM safety, its raw-query escape hatches, and stored-procedure caveats

Our Sample Table: users

The attacks in this lesson target a login that reads from this users table. Notice that row 1 is the admin — that matters, because a bypass logs the attacker in as the first matching row.

1. How SQL Injection Works

SQL injection happens when user input gets glued directly into a query string, so the database can't tell your code apart from their text. It's the top web vulnerability on the OWASP list — and it's 100% preventable.

Imagine dictating a form letter to an assistant: "Dear ___, ...". You expect a name. But the visitor says "Bob. Also, shred every file in the cabinet." If your assistant blindly writes down everything, the extra sentence becomes an instruction. A concatenated query is that gullible assistant — and the quote character ' is how the attacker ends the "name" and starts dictating commands.

Below is the vulnerable pattern. The nameInput isn't a value to the database — once it's pasted into the string, it's part of the SQL itself.

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
-- A real system stores a HASH, never the password itself. This lesson is about
-- injection rather than hashing, so the column is named the way the queries
-- below name it, and every value is an obvious placeholder.
CREATE TABLE users (
    id            INTEGER PRIMARY KEY,
    username      TEXT,
    email         TEXT,
    password      TEXT,
    role          TEXT
);

INSERT INTO users (id, username, email, password, role) VALUES
    (1, 'alice', '[email protected]', 'not-a-real-password-1', 'admin'),
    (2, 'bob',   '[email protected]',   'not-a-real-password-2', 'member'),
    (3, 'cara',  '[email protected]',  'not-a-real-password-3', 'member');

-- ☠️ THE VULNERABLE PATTERN: building SQL by gluing strings together
-- This is pseudo-code for what your APP does, not SQL you run directly.

-- Your login code pastes the user's typed input straight into the query:
--   query = "SELECT id, username, role FROM users "
--         + "WHERE username = '" + nameInput + "' "
--         + "AND password = '" + passInput + "'"

-- Normal input → nameInput = alice , passInput = secret123
SELECT id, username, role FROM users
WHERE username = 'alice' AND password = 'secret123';
-- ✅ Returns the single row for alice. So far so good.

-- The problem: the user's text becomes part of the SQL CODE,
-- not just a value. A quote ' inside their input ends the string
-- early and lets them write their own SQL after it.

-- ✅ Expected result:
-- id | username | role

Now watch what a single quote does. The attacker's text closes the string early, adds OR '1'='1' (true for every row), and uses -- to comment out the rest of your query.

-- ☠️ ATTACK 1: the classic auth bypass  ' OR '1'='1
-- The attacker types this into the USERNAME box:   ' OR '1'='1' --
-- After string-concatenation, your query becomes:

SELECT id, username, role FROM users
WHERE username = '' OR '1'='1' --' AND password = '';
--                  ^^^^^^^^^^^^   ^^
--      always TRUE for every row   -- comments out the rest of the line

-- '1'='1' is true for EVERY row, so the WHERE matches everyone.
-- The password check is commented out by the -- entirely.
-- The app logs the attacker in as the FIRST row — usually the admin.

-- ✅ Expected result:
-- id | username | role
-- 1 | alice | admin
-- 2 | bob | member
-- 3 | cara | member

The same hole lets an attacker stack a whole new statement after a semicolon — including a destructive one like DROP TABLE — or bolt on a UNION SELECT to siphon data out of an unrelated table.

-- ☠️ ATTACK 2: stacked statement  '; DROP TABLE users; --
-- Some drivers let several statements run if separated by ;
-- The attacker types:   '; DROP TABLE users; --

SELECT id, username, role FROM users WHERE username = '';
DROP TABLE users;          -- a brand-new, attacker-written statement
-- --' AND password = ''   -- the leftover is commented out

-- If the database connection is allowed to run DDL, your users
-- table is now GONE. The same trick reads other tables too:
--   ' UNION SELECT card_number, NULL, NULL FROM payments --
-- ...returns credit-card numbers from a different table entirely.

-- ✅ Expected result:
-- id | username | role

Your Turn (a): spot why it's vulnerable

Fill in the blanks to explain the flaw and to show exactly what input breaks this concatenated query. The expected answer is in the comments so you can self-check.

-- 🎯 YOUR TURN (a) — spot the flaw, then break it on paper
-- This login query is built by string concatenation. Fill in the blanks.

-- query = "SELECT id FROM users WHERE email = '" + emailInput + "'"

-- 1) What makes this UNSAFE?
--    👉 The user's input becomes part of the SQL ___ , not just a value.
--       (one word: "code")

-- 2) Type this into the email box and write the query it produces:
--    emailInput = ' OR '1'='1' --
SELECT id FROM users WHERE email = '___';   -- 👉 paste the malicious input in place of ___

-- ✅ Expected: the line becomes
--    SELECT id FROM users WHERE email = '' OR '1'='1' --'
--    '1'='1' is always true → it returns EVERY user (auth bypass).

2. The #1 Fix: Parameterised Queries

A parameterised query (also called a prepared statement) splits the SQL from the data. You write the query with a placeholder — ?, $1, or :name depending on your engine — and hand the value over separately. The database compiles the query first, then slots your value in as pure data. Quotes in the input can no longer "escape" into the SQL, because the SQL is already finished.

Vulnerable vs. safe, side by side:

-- ❌ VULNERABLE — user input concatenated into the SQL string
-- (Python + psycopg, but the flaw is identical in every language)

name = request.form["username"]      # whatever the user typed
sql  = "SELECT id, role FROM users WHERE username = '" + name + "'"
cursor.execute(sql)

-- Input  alice          → ...WHERE username = 'alice'     ✅
-- Input  ' OR '1'='1     → ...WHERE username = '' OR '1'='1'  ☠️ returns all rows
-- The database cannot tell your code from their text.

-- ✅ SAFE — parameterised query (a.k.a. prepared statement)
-- The ? / %s is a PLACEHOLDER. The value travels separately from the SQL.

name = request.form["username"]
sql  = "SELECT id, role FROM users WHERE username = %s"   -- note: NO quotes
cursor.execute(sql, (name,))          -- value passed as DATA, not code

-- 1. The DB parses & compiles the query template FIRST.
-- 2. Then it binds your value into the prepared slot.
-- 3. ' OR '1'='1 is now searched for LITERALLY as a username.
--    No such user exists → the attack simply fails. No match, no harm.

Want to see the separation? PostgreSQL exposes it directly with PREPARE and EXECUTE: the plan is built once, and every value you pass afterwards is bound into a typed hole.

-- WHY PARAMETERS WIN: the SQL is fixed before your value is seen.
-- PostgreSQL lets you watch it happen with PREPARE / EXECUTE:

PREPARE find_user (text) AS
    SELECT id, username, role FROM users WHERE username = $1;

-- The plan is compiled now. $1 is a typed hole, not text to parse.
EXECUTE find_user('alice');                 -- ✅ one row for alice
EXECUTE find_user(''' OR ''1''=''1');        -- searches for that exact name
-- → 0 rows. The quotes are just characters inside a value; they cannot
--   "escape" into the query, because the query was already built.

DEALLOCATE find_user;   -- clean up the prepared statement when done

Your Turn (b): rewrite it with a placeholder

Take the same vulnerable login and make it safe by replacing the concatenation with a parameter placeholder. One blank — use ? (or $1 / :email).

-- 🎯 YOUR TURN (b) — make it injection-proof with a placeholder
-- Rewrite the SAME login using a parameter instead of concatenation.
-- Replace the ___ with a placeholder (use ? — or $1 / :email on your engine).

-- ❌ Before (vulnerable):
--    sql = "SELECT id FROM users WHERE email = '" + emailInput + "'"

-- ✅ After (safe): no quotes around the placeholder, value passed separately
sql = "SELECT id FROM users WHERE email = ___"   -- 👉 put the placeholder here
cursor.execute(sql, (emailInput,))                -- value travels as DATA

-- ✅ Expected: the placeholder is  ?  (or  $1 / :email ), e.g.
--    "SELECT id FROM users WHERE email = ?"
--    Now ' OR '1'='1 is searched for literally → the attack fails.

3. Defence in Depth (the other layers)

Parameters are the fix — but good security stacks layers, so a single mistake isn't fatal. The catch: you can never bind a table or column name as a parameter. For those, validate against an allow-list (a fixed set of names you accept). Escaping input by hand is a brittle last resort — get it wrong once and you're exposed.

-- DEFENCE: input validation & allow-lists (a SECOND layer, not the first)
-- Parameters protect VALUES. But you can never bind a table or column
-- name as a parameter — so for those, check against an allow-list.

-- ❌ WRONG: trusting the client / pasting a column name straight in
--    sql = "SELECT * FROM products ORDER BY " + sortColumn   -- injectable!

-- ✅ RIGHT: only permit known-good identifiers (an allow-list / whitelist)
--    allowed = {"name", "price", "created_at"}
--    if sortColumn not in allowed:
--        raise ValueError("bad sort column")
--    sql = "SELECT * FROM products ORDER BY " + sortColumn   -- now safe

-- Validate values too, as defence in depth (not as your only guard):
--   * type-check  → IDs must be integers
--   * length-cap  → username <= 50 chars
--   * format      → email matches a pattern
-- Clever attackers bypass weak validators with encoding tricks,
-- so validation SUPPLEMENTS parameters — it never replaces them.

4. Least-Privilege Database Accounts

Assume an attack might one day slip through. The login your app connects with should only be able to do the app's job — no DROP, no ALTER, no superuser. Then even a successful injection runs into permission denied for the worst operations.

-- DEFENCE: least-privilege database accounts (limit the blast radius)
-- If an attack ever lands, what the login is ALLOWED to do caps the damage.

-- Create a restricted role for the application:
CREATE ROLE app_user WITH LOGIN PASSWORD 'a-strong-secret';

-- Grant ONLY what the app needs — no DROP, no ALTER, no superuser:
GRANT SELECT, INSERT, UPDATE ON products, orders, customers TO app_user;
REVOKE CREATE ON SCHEMA public FROM app_user;

-- Now, even if injection sneaks through:
--   '; DROP TABLE users; --   → ERROR: permission denied   ✅ table survives
--   ' OR '1'='1               → still leaks rows            ⚠️ still bad
-- Least privilege softens the worst outcomes; it is NOT a substitute
-- for parameterised queries. Use both.

5. ORMs and Stored-Procedure Caveats

An ORM (Object-Relational Mapper — the library that turns objects into SQL, like Django, SQLAlchemy, ActiveRecord, or Prisma) parameterises for you by default, which is a big part of why ORMs are safer. But every ORM has a raw SQL escape hatch, and the moment you use it you're back to manual safety. Stored procedures aren't automatically safe either: if a procedure builds SQL from concatenated text and runs it with EXECUTE, it's just as injectable.

-- DEFENCE: ORM safety — and its escape hatches
-- ORMs (Django, SQLAlchemy, ActiveRecord, Prisma, Hibernate, EF) build
-- parameterised SQL for you BY DEFAULT — that is a big reason to use one.

-- ✅ SAFE — the ORM parameterises this automatically:
--    User.objects.filter(username=name)
--    db.users.findFirst({ where: { username: name } })

-- ⚠️ ESCAPE HATCH — raw SQL methods bypass that protection. Still parameterise:
-- ✅ Django:  User.objects.raw("SELECT * FROM users WHERE id = %s", [user_id])
-- ❌ Django:  User.objects.raw(f"SELECT * FROM users WHERE id = {user_id}")  -- f-string = injection
-- ✅ Sequelize: sequelize.query("... WHERE id = ?", { replacements: [id] })
-- ❌ Sequelize: sequelize.query("... WHERE id = " + id)

-- Stored-procedure caveat: a procedure is NOT automatically safe.
-- If it builds SQL with EXECUTE/EXEC on concatenated text, it is just as
-- injectable. Use parameters/USING inside procedures too:
-- ✅ EXECUTE 'SELECT * FROM logs WHERE user_id = $1' USING p_id;
-- ❌ EXECUTE 'SELECT * FROM logs WHERE user_id = ' || p_id;

Common Mistakes (and the fix)

• Escaping quotes by hand: doubling ' into '' yourself is fragile — encodings, Unicode, and numeric contexts slip past it. Use a parameter and let the driver handle it.
• Trusting client-side validation: JavaScript checks in the browser are a UX nicety, not security. Attackers call your API directly with curl. Validate and parameterise on the server.
• Quoting the placeholder: writing WHERE name = '?' (or '%s') breaks it — that's now a literal question mark. The placeholder takes no quotes: WHERE name = ?.
• Dynamic SQL inside a procedure: EXECUTE 'SELECT ... = ' || p_id is injectable even though it's "in the database." Use EXECUTE ... USING p_id with a placeholder.
• The ORM raw-query escape hatch: an f-string/concatenation in .raw(...) or .query(...) bypasses the ORM's protection. Pass parameters: .raw(sql, [id]), not .raw(f"...{id}").

📘 Quick Reference — parameter syntax by language/engine

Placeholder style varies (?, $1, :name, %s, @id) but the rule is the same everywhere: SQL with a placeholder, value passed separately.

Frequently Asked Questions

Q: If I parameterise everything, do I still need input validation?

Yes — as a second layer. Parameters stop injection in values, but they can't protect a table or column name you build dynamically, and validation enforces business rules (length, format). Use both; never let validation be your only defence.

Q: Can't I just escape the quotes myself?

Don't. Hand-escaping is notoriously easy to get wrong — character sets, Unicode look-alikes, and numeric contexts all create holes. A parameterised query is simpler and safer, so reach for escaping only as a genuine last resort.

Q: Are stored procedures automatically safe from injection?

No. A procedure that builds a query by concatenating strings and runs it with dynamic EXECUTE/EXEC is just as injectable as application code. Use parameters inside the procedure too (EXECUTE ... USING).

Q: My ORM builds the SQL for me — am I covered?

Mostly. ORMs parameterise normal queries by default, which is great. The risk is the raw SQL escape hatch: an f-string or string concatenation passed to .raw()/.query() bypasses that protection. Always pass values as parameters, even in raw queries.

Mini-Challenge: Lock Down the Login

Put it all together — a brief, a blank canvas, and the expected shape in the comments. Make the two-field login injection-proof, then sanity-check it in a playground.

-- 🎯 MINI-CHALLENGE: make this login injection-proof
-- You are given a vulnerable login that checks a username AND a password.
--   sql = "SELECT id, role FROM users "
--       + "WHERE username = '" + nameIn + "' AND password = '" + passIn + "'"
--
-- Rewrite it using parameter placeholders for BOTH values, then pass the
-- two values separately when you execute it.
--
-- ✅ Expected shape (placeholders may be ? , $1/$2 , or :name/:pass):
--    sql = "SELECT id, role FROM users WHERE username = ? AND password = ?"
--    execute(sql, [nameIn, passIn])
--    → ' OR '1'='1 in either box is treated as literal text, so it matches nobody.

-- your query here

🎉 Lesson Complete

• ✅ Injection happens when user input is concatenated into the SQL string
• ✅ ' OR '1'='1 bypasses auth; '; DROP TABLE -- destroys data
• ✅ Parameterised queries are the #1 fix — placeholder in the SQL, value passed separately
• ✅ Allow-lists guard table/column names; least privilege caps the blast radius
• ✅ ORMs parameterise by default, but raw queries and dynamic procedures need care
• ✅ Next: Backup & Restore — protect your data even when something goes wrong

Practice quiz

Continue this course

• Previous: Database Security: Roles, Privileges, Policies & Row-Level Security
• Next: Backup, Restore & High Availability Strategies — Plan database backups, test restores, and set up high availability
• Quick reference: SQL cheat sheet
• From the blog: SQL Database Design Best Practices