Unit 11 · Database Operations

11.3Review Questions

  1. Match C, R, U, D to SQL commands.
  2. What is SQL injection, and how do prepared statements prevent it?
  3. What is a primary key? Why use AUTO_INCREMENT?
  4. Why do we not run DELETE FROM products without WHERE?
  5. What is the benefit of a transaction when saving an order?
Show answers

  1. Create → INSERT, Read → SELECT, Update → UPDATE, Delete → DELETE.

  2. SQL injection is an attack where input typed by a user is inserted into an SQL string and changes the meaning of the query, for example entering ' OR '1'='1 to log in without a password, or deleting tables. Prepared statements send the SQL with placeholders (? or :name) first, then send the values separately; the database always treats the values as data, never as SQL code.

    PHP
    $stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);

  3. A primary key is a column (or columns) whose value uniquely identifies each row; it cannot be NULL or repeated, and other tables refer to it with foreign keys. AUTO_INCREMENT lets the database generate the next unique number automatically, so you never have to choose an id or risk duplicates.

  4. Without a WHERE clause, the statement deletes every row in the table, and there is no undo. Always use WHERE id = ? (and test with a SELECT using the same condition first).

  5. Saving an order involves several statements (insert the order, insert each order item, reduce stock). A transaction makes them all succeed together or none at all (beginTransaction, commit, or rollBack on error), so the database is never left with half an order or incorrect stock.