SQL Injection Defenses & Secure Query Practices
Understand how SQL injection attacks work and build bulletproof defenses with parameterized queries.
🎯 What You'll Learn
- How SQL injection attacks work (with examples)
- Parameterized queries — the primary defense
- Input validation and whitelist strategies
- Least privilege to limit blast radius
- Security testing tools and checklists
☠️ How SQL Injection Works
SQL injection is like someone slipping extra instructions into a form letter. You expected a name, but they wrote a command that changes what the letter says. It's the #1 web vulnerability according to OWASP, and it's 100% preventable.
SQL Injection Anatomy
How attackers manipulate queries through user input
-- HOW SQL INJECTION WORKS
-- The attacker manipulates input to change your SQL query
-- Your app builds this query with string concatenation:
-- query = "SELECT * FROM users WHERE username = '"
-- + userInput + "' AND password = '" + passInput + "'"
-- Normal input: username = alice, password = secret123
-- SELECT * FROM users
-- WHERE username = 'alice' AND password = 'secret123'
-- ✅ Works correctly
-- Malicious input: username = ' OR '1'='1' --
-- SELECT * FROM users
-- WHERE usern
...🛡️ Parameterized Queries
Parameterized queries separate SQL code from data. The database compiles the query first, then plugs in user input as values — never as executable code. This is the only reliable defense.
💡 Pro Tip — No Exceptions
Use parameterized queries for every query that includes user input — even if you think the input is "safe." Internal admin tools get hacked too. Make it a habit, not a decision.
Parameterized Queries
The primary defense in every language
-- DEFENSE #1: Parameterized Queries (Prepared Statements)
-- The ONLY reliable defense — use this ALWAYS
-- PostgreSQL prepared statement:
PREPARE find_user (text, text) AS
SELECT id, username, email FROM users
WHERE username = $1 AND password_hash = crypt($2, password_hash);
EXECUTE find_user('alice', 'secret123');
-- How it works internally:
-- 1. Database parses and compiles the query FIRST
-- 2. User input is bound as DATA, not as SQL code
-- 3. Even if input contains SQL, it's treated a
...✅ Input Validation
Input validation is a second layer of defense. It catches obvious attacks before they reach the database and enforces business rules (max length, allowed characters, valid formats).
⚠️ Common Mistake — Relying Only on Validation
Input validation alone is NOT sufficient. Clever attackers bypass validators with encoding tricks, Unicode characters, and edge cases. Always use parameterized queries AND validation together.
Input Validation
Whitelist strategies and safe dynamic identifiers
-- DEFENSE #2: Input Validation (defense in depth)
-- Validate BEFORE the query, but don't rely on this alone
-- Server-side validation examples:
-- 1. Type checking: ensure IDs are integers
-- if not isinstance(user_id, int): raise ValueError()
-- 2. Whitelist allowed values:
-- if sort_column not in ['name','date','price']: raise ValueError()
-- 3. Length limits: usernames max 50 chars
-- if len(username) > 50: raise ValueError()
-- 4. Pattern matching: email format
-- if not re.match(r'^[
...🔐 Least Privilege
If injection succeeds despite your defenses, least privilege limits the damage. An attacker who gains access to a read-only user can't DROP your tables.
Least Privilege
Restrict app user permissions to minimize damage
-- DEFENSE #3: Least Privilege
-- Even if injection succeeds, limit the damage
-- Application database user should NOT have:
-- ❌ DROP TABLE privileges
-- ❌ ALTER TABLE privileges
-- ❌ Access to other databases
-- ❌ Superuser/admin rights
-- Create a restricted application user:
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_pwd';
-- Grant only what the app needs:
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON products, orders, customers TO app_user;
GRANT USAGE
...🧪 Security Testing
Test your defenses before attackers do. Use automated scanners and manual testing with known injection payloads.
Security Testing
Test payloads, tools, and security checklist
-- DEFENSE #4: Security Testing
-- Common injection test payloads:
-- ' OR '1'='1
-- ' OR '1'='1' --
-- '; DROP TABLE users; --
-- ' UNION SELECT null, null, null --
-- 1; WAITFOR DELAY '0:0:5' -- (blind time-based)
-- ' AND 1=CONVERT(int, @@version) -- (error-based)
-- Automated tools:
-- sqlmap: automatic SQL injection detection and exploitation
-- OWASP ZAP: web application security scanner
-- Burp Suite: manual + automated security testing
-- Security checklist:
-- ✅ ALL queries use par
...📋 Quick Reference
| Defense | Priority |
|---|---|
| Parameterized queries | 🔴 Critical — always use |
| Input validation | 🟡 Important — second layer |
| Least privilege | 🟡 Important — limit damage |
| Error handling | 🟢 Good — hide SQL details |
| Security testing | 🟢 Good — verify defenses |
🎉 Lesson Complete!
You now know how to prevent SQL injection — the most common database attack. Next, learn about backup and restore strategies!
Sign up for free to track which lessons you've completed and get learning reminders.