Lesson 29: Preventing SQL Injection (Database Side)

Beyond parameterized queries in application code, the database itself supports roles and privileges that limit blast radius if something does go wrong.

Real-time example

A reporting user account only has SELECT privileges — even if its credentials leak, it can't DROP or DELETE anything.

CREATE ROLE reporting_user LOGIN PASSWORD 'secret';
GRANT SELECT ON orders, customers TO reporting_user;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM reporting_user;

What's happening

Even a successfully injected query run as reporting_user physically cannot modify or delete data — the database enforces it at the privilege level.

Defense in depth: parameterized queries prevent injection; least-privilege roles limit the damage if a query gets through anyway.