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.

Practice

1. What is the main purpose of using prepared statements in PHP when working with databases?

easy

A. To allow multiple database connections at once

B. To prevent SQL injection by separating SQL code from user data

C. To automatically format the output of SQL queries

D. To speed up the execution of all SQL queries

Solution

1. Step 1: Understand SQL injection risks

   SQL injection happens when user input is mixed directly with SQL code, allowing attackers to change the query.

2. Step 2: Role of prepared statements

   Prepared statements separate the SQL code from the data, so user input cannot change the query structure.

3. Final Answer:

   To prevent SQL injection by separating SQL code from user data -> Option B

4. Quick Check:

   Prepared statements prevent injection = D [OK]

Hint: Prepared statements separate code and data to stop injection [OK]

Common Mistakes:

• Thinking prepared statements speed up all queries
• Believing they format output automatically
• Confusing with multiple connections

2. Which of the following is the correct way to prepare and execute a statement in PHP PDO to prevent SQL injection?

easy

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

B. $stmt = $pdo->query('SELECT * FROM users WHERE id = $id');

C. $stmt = $pdo->prepare('SELECT * FROM users WHERE id = $id'); $stmt->execute();

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

Solution

1. Step 1: Check correct prepare syntax

   Use prepare() with placeholders like ? for parameters.

2. Step 2: Execute with parameters

   Call execute() with an array of values to bind to placeholders.

3. Final Answer:

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

4. Quick Check:

   prepare + execute with array = A [OK]

Hint: Use prepare() then execute() with array for safe queries [OK]

Common Mistakes:

• Using query() with variables directly
• Putting variables inside prepare() string
• Calling execute() before prepare()

3. What will be the output of the following PHP code snippet?

$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ?');
$stmt->execute([2]);
$result = $stmt->fetchColumn();
echo $result;

Assuming the user with id 2 has the name 'Alice'.

medium

A. 2

B. Array

C. Alice

D. Error

Solution

1. Step 1: Understand prepare and execute

   The statement prepares a query with a placeholder and executes it with id = 2.

2. Step 2: Fetch the result

   fetchColumn() returns the first column of the first row, which is the name 'Alice'.

3. Final Answer:

   Alice -> Option C

4. Quick Check:

   fetchColumn returns name 'Alice' = C [OK]

Hint: fetchColumn() returns first column value from executed statement [OK]

Common Mistakes:

• Expecting fetchColumn() to return an array
• Confusing id with name output
• Not passing parameters to execute()

4. Identify the error in this PHP code that uses prepared statements:

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(':email' => $email);

medium

A. execute() should be called before prepare()

B. The placeholder should be a question mark (?) instead of :email

C. prepare() cannot use named placeholders like :email

D. The execute() method argument should be an array, but syntax is incorrect

Solution

1. Step 1: Check placeholder usage

   Named placeholders like :email are valid in prepare statements.

2. Step 2: Check execute() argument syntax

   execute() expects an array, but ':email' => $email is not inside an array here.

3. Final Answer:

   The execute() method argument should be an array, but syntax is incorrect -> Option D

4. Quick Check:

   execute() needs array argument = A [OK]

Hint: Pass parameters as array to execute() [OK]

Common Mistakes:

• Using wrong placeholder style
• Passing parameters without array brackets
• Calling execute() before prepare()

5. You want to insert multiple user records safely using prepared statements in PHP. Which approach correctly prevents SQL injection and efficiently inserts multiple rows?

$users = [
  ['name' => 'John', 'email' => '[e-mail]'],
  ['name' => 'Jane', 'email' => '[e-mail]']
];
// Which code below is correct?

hard

A. foreach ($users as $user) {
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$stmt->execute([$user['name'], $user['email']]);
}

B. $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$stmt->execute($users);

C. foreach ($users as $user) {
$sql = "INSERT INTO users (name, email) VALUES ('{$user['name']}', '{$user['email']}')";
$pdo->query($sql);
}

D. $sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute();

Solution

1. Step 1: Check safe insertion of multiple rows

   foreach ($users as $user) { $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->execute([$user['name'], $user['email']]); } prepares once per iteration and executes with user data safely bound as parameters.

2. Step 2: Analyze other options

   $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->execute($users); passes array of arrays directly to execute(), which is invalid. foreach ($users as $user) { $sql = "INSERT INTO users (name, email) VALUES ('{$user['name']}', '{$user['email']}')"; $pdo->query($sql); } uses string interpolation risking injection. $sql = 'INSERT INTO users (name, email) VALUES (?, ?)'; $stmt = $pdo->prepare($sql); $stmt->execute(); calls execute() without parameters.

3. Final Answer:

   foreach ($users as $user) { $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->execute([$user['name'], $user['email']]); } -> Option A

4. Quick Check:

   Prepare and execute per user with parameters = B [OK]

Hint: Prepare inside loop and execute with user data array [OK]

Common Mistakes:

• Passing multidimensional array directly to execute()
• Using string interpolation instead of parameters
• Calling execute() without parameters