SQL · Theory

Prepared Statement

Theory

312/439

Prepared Statement

A prepared statement is SQL with ? placeholders: SELECT * FROM students WHERE roll = ?. You send the number separately. The server reuses the plan. More important: it does not glue user text into SQL — that is how you stop SQL injection.

In PHP: mysqli prepare + bind_param. PDO: prepare + execute. In Java: PreparedStatement. Never: "WHERE name = '" + userInput + "'".

PREPARE stmt FROM 'SELECT name FROM students WHERE roll = ?'; EXECUTE stmt USING @r; DEALLOCATE PREPARE stmt; is the SQL-only form.

Trap: escaping quotes by hand and calling it ‘safe’. Trap: prepared statement for a table name (you cannot bind identifiers — whitelist them).

On the example next to this theory: Prepared Statement: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE roll = ? and bind 12. Never glue user text into SQL.

Prepared Statement — sample query

-- Prepared Statement
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Prepared Statement: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefSQL with ? + bound values. Stops injection. Reuses the plan.
RuleBind user input. Never string-concat SQL.
TrapConcatenating name into WHERE.

SQL · Theory

Prepared Statement

← All stacks

Theory

312/439

Prepared Statement

In PHP: mysqli prepare + bind_param. PDO: prepare + execute. In Java: PreparedStatement. Never: "WHERE name = '" + userInput + "'".

PREPARE stmt FROM 'SELECT name FROM students WHERE roll = ?'; EXECUTE stmt USING @r; DEALLOCATE PREPARE stmt; is the SQL-only form.

Trap: escaping quotes by hand and calling it ‘safe’. Trap: prepared statement for a table name (you cannot bind identifiers — whitelist them).

On the example next to this theory: Prepared Statement: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WHERE roll = ? and bind 12. Never glue user text into SQL.

Prepared Statement — sample query

-- Prepared Statement
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Prepared Statement: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefSQL with ? + bound values. Stops injection. Reuses the plan.
RuleBind user input. Never string-concat SQL.
TrapConcatenating name into WHERE.

Prepared Statement

Prepared Statement — sample query

Short notes

Questions

Prepared Statement

Prepared Statement — sample query

Short notes

Questions