Prepared Statement — SQL Technical Interview Questions & Answers

Prepared Statement

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.

Technical Interview Tip: WHERE roll = ? and bind 12. Never glue user text into SQL.

Code Examples for Prepared Statement

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.

How to Explain Prepared Statement to an Interviewer

Why prepared statements?

SQL injection safety Optional plan reuse

What is SQL injection?

User text becomes SQL Stop it with bound parameters