Preventing injection with prepared statements in PHP - Step-by-Step Execution

Concept Flow - Preventing injection with prepared statements

Start

↓

Prepare SQL with placeholders

↓

Bind user input to placeholders

↓

Execute statement safely

↓

Fetch results or confirm action

↓

End

This flow shows how prepared statements separate SQL code from user input to stop injection.

Execution Sample

PHP

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

This code safely queries a user by email using a prepared statement to prevent injection.

Execution Table

Step	Action	SQL State	User Input	Result
1	Prepare statement with placeholder	SELECT * FROM users WHERE email = ?	N/A	Statement ready
2	Bind user input to placeholder	Placeholder bound to user input	[e-mail]	Input safely bound
3	Execute statement	Executing with bound input	[e-mail]	Query runs safely
4	Fetch result	Result fetched	N/A	User data retrieved
5	End	No injection risk	N/A	Safe execution complete

💡 Execution stops after fetching results safely without injection risk.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
$stmt	null	Prepared statement object	Executed statement object	Executed statement object
$email	[e-mail]	[e-mail]	[e-mail]	[e-mail]
$user	null	null	null	User data array or false

Key Moments - 3 Insights

Why can't we just put user input directly into the SQL string?

Directly inserting user input can let attackers add harmful SQL code. The execution_table shows how prepared statements keep SQL and input separate (Step 2 and 3).

What does the placeholder '?' do in the SQL?

The '?' marks where user input goes but does not run as code. Step 1 shows the SQL with placeholders ready before input is added.

How does binding input prevent injection?

Binding treats input as data only, never code. Step 2 binds input safely, so even if input has SQL code, it won't run.

Visual Quiz - 3 Questions

Look at the execution_table, what is the SQL state after Step 1?

A SQL with placeholders ready

B SQL executed with user input

C User input inserted directly

D No SQL prepared yet

💡 Hint

Check the 'SQL State' column at Step 1 in execution_table.

At which step does the user input get safely attached to the SQL?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look for 'Bind user input to placeholder' in execution_table.

If we skipped binding and inserted input directly, what risk increases?

A Faster execution

B SQL injection attack

C No result returned

D Syntax error in PHP

💡 Hint

Refer to key_moments about why direct input is dangerous.

Concept Snapshot

Prepared statements use placeholders (?) in SQL.
User input is bound separately, not mixed with SQL code.
This stops attackers from injecting harmful SQL.
Steps: prepare SQL, bind input, execute safely, fetch results.
Always use prepared statements for database queries with user data.

Full Transcript

This visual trace shows how prepared statements prevent SQL injection in PHP. First, the SQL query is prepared with a placeholder '?'. Then, user input is bound to this placeholder, keeping it separate from the SQL code. The statement executes safely with the bound input, and results are fetched. Variables like $stmt and $email change state as the code runs. Key moments explain why direct input is dangerous and how binding protects the query. The quiz tests understanding of each step and the security benefit.