Security challenge
- SQL course
- Expert
- Lesson 44/45
- 6 min
Secure the app with parameterised queries and GRANT.
Security challenge
Third challenge: prepare PREPARE find_user(text) against SQL injection on the users table. The parameterized query carries the email value safely.
PREPARE find_user(text) AS
SELECT * FROM users WHERE email = $1;PREPARE blocks injection.
All columns are selected from users. WHERE keeps only the rows where email = $1 holds.
Practice Security challenge
Add PREPARE find_user(text) AS SELECT * FROM users WHERE email = $1 to the previous solution.
Starter code
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- security
PREPARESample solution
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password_hash TEXT
);
-- optimization
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- security
PREPARE find_user(text) AS SELECT * FROM users WHERE email = $1;Expected output
PREPARE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Security challenge: match the terms
| Term | Meaning |
|---|---|
PREPARE |
prepare |
$1 |
parameter |
injection |
injection |
value |
value |
2 What does PREPARE prevent?
- Missing indexes
- SQL injection Correct answer
- Backups
- Triggers
Explanation: PREPARE prevents SQL injection.
3 Parameter placeholder
Question
SELECT * FROM users WHERE email = ____;
Answer
SELECT * FROM users WHERE email = $1;Explanation: $1 is a parameter placeholder; the email value is sent separately from the query.