Preventing injection with prepared statements in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of prepared statement execution

What will be the output of this PHP code snippet when executed successfully?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$stmt = $pdo->prepare('INSERT INTO users (name) VALUES (:name)');
$stmt->execute([':name' => 'Alice']);
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$stmt->execute([':id' => 1]);
$result = $stmt->fetchColumn();
echo $result;
?>

A Alice

B 1

C NULL

D PDOException

💡 Hint

Look at what value is inserted and then selected by id.

The code inserts 'Alice' into the users table with id 1, then selects the name where id=1, so it outputs 'Alice'.

🧠 Conceptual

intermediate

Why use prepared statements?

Which of the following best explains why prepared statements help prevent SQL injection?

A They run queries faster by caching the entire database.

B They automatically encrypt all user inputs before running the query.

C They separate SQL code from data, so user input cannot change the SQL structure.

D They allow users to write raw SQL directly without restrictions.

💡 Hint

Think about how user input is handled differently with prepared statements.

Prepared statements keep the SQL code fixed and treat user input only as data, preventing attackers from injecting SQL commands.

🔧 Debug

advanced

Identify the error in prepared statement usage

What error will this PHP code produce when executed?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$stmt = $pdo->prepare('INSERT INTO users (name) VALUES (?)');
$stmt->execute(['name' => 'Bob']);
?>

A TypeError: execute() expects array with numeric keys

B No error, inserts 'Bob' successfully

C SyntaxError: Missing colon in parameter

D PDOException: SQLSTATE[HY093]: Invalid parameter number

💡 Hint

Check how parameters are passed for positional placeholders.

The prepared statement uses positional placeholders (?), but the execute array uses named keys, causing a parameter mismatch error.

📝 Syntax

advanced

Correct syntax for named placeholders

Which option shows the correct way to prepare and execute a statement with named placeholders in PHP PDO?

A

$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name');
$stmt->execute([':name' => 'Eve']);

B

$stmt = $pdo->prepare('SELECT * FROM users WHERE name = name');
$stmt->execute([':name' => 'Eve']);

C

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

D

$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name');
$stmt->execute(['name' => 'Eve']);

💡 Hint

Named placeholders require the colon in the execute array keys.

When using named placeholders, the keys in the execute array must include the colon prefix to match the placeholder names.

🚀 Application

expert

Preventing injection in dynamic queries

You want to select users by either 'id' or 'email' depending on user input. Which code snippet safely uses prepared statements to prevent SQL injection?

A

<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE $filter = :value");
$stmt->execute([':value' => $input]);
?>

B

<?php
if ($filter == 'id') {
  $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :value');
} else {
  $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :value');
}
$stmt->execute([':value' => $input]);
?>

C

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id OR email = :email');
$stmt->execute([':id' => $input, ':email' => $input]);
?>

D

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? OR email = ?');
$stmt->execute([$filter, $input]);
?>

💡 Hint

Think about how to avoid injecting the column name directly from user input.

Option B uses fixed SQL with placeholders only for values, never injecting user input into SQL structure. Options A and D inject $filter or mix parameters incorrectly. Option B searches both columns regardless of filter.