SQL Injection Defenses & Secure Query Practices

-- ☠️ 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
...

-- ☠️ 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.

...

-- ☠️ 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 SEL
...

-- 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 c
...

-- 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 sortCol
...

-- 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:
--   '; DRO
...

-- 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", [us
...