Security challenge

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.

Exercise Security challenge

Add PREPARE find_user(text) AS SELECT * FROM users WHERE email = $1 to the previous solution.

Show example 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;

Test yourself

Security challenge: match the terms

PREPARE

prepare
$1

parameter
injection

injection
value

value
What does PREPARE prevent?
- Missing indexes
- SQL injection
- Backups
- Triggers
Show answer

SQL injection — PREPARE prevents SQL injection.

Parameter placeholder

SELECT * FROM users WHERE email = ____;

Show answer

SELECT * FROM users WHERE email = $1;

Next lesson: Backup challenge

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies