Prepared statements and why they matter in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a prepared statement execution

What will be the output of this PHP code using a prepared statement?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$stmt = $mysqli->prepare('SELECT name FROM users WHERE id = ?');
$id = 2;
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
echo $name;
?>

A An error because bind_param is missing a parameter

B The name of the user with id 2

C Empty output because fetch() was not called

D The string 'id' printed instead of the name

💡 Hint

Remember that bind_param binds the variable by reference and fetch() retrieves the result.

The prepared statement selects the name where id equals 2. bind_param binds the integer 2 to the placeholder. execute() runs the query. bind_result binds the result to $name. fetch() fetches the row, so echo $name prints the user's name.

🧠 Conceptual

intermediate

Why use prepared statements?

Which of the following is the main reason to use prepared statements in PHP when working with databases?

A They prevent SQL injection by separating code from data

B They allow queries to run without a database connection

C They automatically cache query results for faster access

D They format the output of queries into JSON automatically

💡 Hint

Think about security and how user input is handled.

Prepared statements separate the SQL code from the data values, which prevents attackers from injecting malicious SQL code. This is the main security benefit.

🔧 Debug

advanced

Identify the error in this prepared statement code

What error will this PHP code produce when executed?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$stmt = $mysqli->prepare('INSERT INTO users (name, age) VALUES (?, ?)');
$name = 'Alice';
$age = 'twenty';
$stmt->bind_param('si', $name, $age);
$stmt->execute();
?>

A TypeError because 'twenty' is not an integer

B No error, the data is inserted successfully

C SyntaxError due to wrong SQL syntax

D Runtime error because bind_param expects variables passed by reference

💡 Hint

Check the types expected by bind_param and the actual variable types.

bind_param expects the second parameter to be an integer ('i'), but 'twenty' is a string that cannot be converted to integer, causing a TypeError or failure during execution.

📝 Syntax

advanced

Which prepared statement code is syntactically correct?

Which option shows a syntactically correct way to prepare and execute a statement in PHP?

A

$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?')
$stmt->bind_param('i', $id)
$stmt->execute()

B

$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param('i' $id);
$stmt->execute();

C

$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param(i, $id);
$stmt->execute();

D

$stmt = $mysqli->prepare('SELECT * FROM users WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();

💡 Hint

Check for missing semicolons and correct quotes around types.

Option D has correct syntax with semicolons and quotes around 'i'. Option D misses semicolons. Option D misses quotes around 'i'. Option D misses a comma between parameters.

🚀 Application

expert

How many rows will be returned?

Given this PHP code using prepared statements, how many rows will be returned by the query?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$stmt = $mysqli->prepare('SELECT * FROM users WHERE age > ?');
$age_limit = 30;
$stmt->bind_param('i', $age_limit);
$stmt->execute();
$result = $stmt->get_result();
echo $result->num_rows;
?>

A An error because get_result() is not supported

B All users regardless of age

C Zero, because $age_limit is not defined with $

D The number of users older than 30

💡 Hint

Check variable naming carefully.

The variable $age_limit is missing the $ sign in assignment, so PHP treats it as a constant or undefined variable, causing a warning and $age_limit being null or zero, resulting in zero rows or error.