How SQL injection exploits unsafe queries in PHP - Visual Walkthrough

Concept Flow - How SQL injection exploits unsafe queries

User Input Entered

↓

Input Concatenated into SQL Query

↓

Query Sent to Database

↓

Database Executes Query

↓

If Input Contains SQL Code

Yes↓

Malicious SQL Runs - Data Leaked or Modified

No↓

Query Runs Normally

User input is directly added to a SQL query string. If input contains SQL code, it changes the query meaning, causing harmful effects.

Execution Sample

PHP

<?php
$user = $_GET['user'];
$query = "SELECT * FROM users WHERE name = '$user'";
$result = mysqli_query($conn, $query);
?>

This PHP code builds a SQL query by inserting user input directly, which can be exploited if input is malicious.

Execution Table

Step	User Input	Query Built	Query Sent to DB	Effect
1	Alice	SELECT * FROM users WHERE name = 'Alice'	SELECT * FROM users WHERE name = 'Alice'	Returns Alice's data
2	Bob	SELECT * FROM users WHERE name = 'Bob'	SELECT * FROM users WHERE name = 'Bob'	Returns Bob's data
3	' OR 1=1 --	SELECT * FROM users WHERE name = '' OR 1=1 --	SELECT * FROM users WHERE name = '' OR 1=1 --	Returns all users (SQL Injection)
4	'; DROP TABLE users; --	SELECT * FROM users WHERE name = ''; DROP TABLE users; --	SELECT * FROM users WHERE name = ''; DROP TABLE users; --	Deletes users table (SQL Injection)
5	admin	SELECT * FROM users WHERE name = 'admin'	SELECT * FROM users WHERE name = 'admin'	Returns admin data

💡 Execution stops after query is sent; malicious input changes query meaning causing data leak or damage.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5
user	null	Alice	Bob	' OR 1=1 --	'; DROP TABLE users; --	admin
query	null	SELECT * FROM users WHERE name = 'Alice'	SELECT * FROM users WHERE name = 'Bob'	SELECT * FROM users WHERE name = '' OR 1=1 --	SELECT * FROM users WHERE name = ''; DROP TABLE users; --	SELECT * FROM users WHERE name = 'admin'

Key Moments - 3 Insights

Why does the query return all users when input is "' OR 1=1 --"?

Because the input closes the string and adds OR 1=1, which is always true, so the WHERE clause matches all rows (see execution_table row 3).

How can input like "'; DROP TABLE users; --" delete the table?

The input ends the first query and adds a new command to drop the table; the -- comments out the rest, so the database runs both commands (see execution_table row 4).

Why is directly inserting user input into queries unsafe?

Because malicious input can change the query structure and run harmful SQL commands, as shown in the execution_table rows 3 and 4.

Visual Quiz - 3 Questions

Look at the execution table, what is the query built when user input is "Bob"?

A SELECT * FROM users WHERE name = 'Bob'

B SELECT * FROM users WHERE name = '' OR '1'='1'

C SELECT * FROM users WHERE name = 'Alice'

D SELECT * FROM users WHERE name = ''; DROP TABLE users; --

💡 Hint

Check execution_table row 2 under 'Query Built' column.

At which step does the SQL injection cause all users to be returned?

A Step 1

B Step 4

C Step 3

D Step 5

💡 Hint

Look at execution_table row 3 where the input is "' OR 1=1 --".

If the user input was sanitized to prevent quotes, how would the query change at step 3?

A It would cause a syntax error

B It would search for the literal string including quotes

C It would still return all users

D It would delete the users table

💡 Hint

Sanitizing input escapes quotes, so the input is treated as text, not SQL code (see variable_tracker for query changes).

Concept Snapshot

Unsafe SQL queries directly insert user input into query strings.
Malicious input can change query logic or add commands.
This is called SQL injection and can leak or destroy data.
Always use safe methods like prepared statements to prevent it.

Full Transcript

This example shows how unsafe SQL queries in PHP can be exploited by SQL injection. The code takes user input and inserts it directly into a SQL query string. When the input contains special SQL code like "' OR 1=1 --" or "'; DROP TABLE users; --", it changes the meaning of the query. This can cause the database to return all user data or even delete tables. The execution table traces each step: user input, the built query, and the effect. The variable tracker shows how variables change with each input. Key moments explain why certain inputs cause harmful effects. The visual quiz tests understanding by asking about specific steps and outcomes. The quick snapshot reminds that direct insertion of user input is unsafe and prepared statements should be used instead.

Practice

1. What is the main risk of using unsafe SQL queries in PHP without proper input handling?

easy

A. The website will load faster

B. Users will see more colorful pages

C. Attackers can insert harmful SQL commands to access or damage the database

D. The server will automatically block all users

Solution

1. Step 1: Understand unsafe SQL queries

   Unsafe queries allow user input directly in SQL commands without checks.

2. Step 2: Identify the risk of SQL injection

   This lets attackers add harmful commands to steal or change data.

3. Final Answer:

   Attackers can insert harmful SQL commands to access or damage the database -> Option C

4. Quick Check:

   Unsafe queries = SQL injection risk [OK]

Hint: Unsafe queries let attackers add harmful commands [OK]

Common Mistakes:

• Thinking unsafe queries improve speed
• Confusing SQL injection with visual changes
• Assuming server blocks all users automatically

2. Which PHP code snippet is unsafe and vulnerable to SQL injection?

easy

A. $query = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'";

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

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

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

Solution

1. Step 1: Identify direct user input in query string

   $query = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'"; directly inserts $_GET['name'] into the SQL string without protection.

2. Step 2: Compare with prepared statements

   Options A, B, and D use prepared statements which safely handle input.

3. Final Answer:

   $query = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'"; -> Option A

4. Quick Check:

   Direct input in query = unsafe [OK]

Hint: Directly adding user input to query is unsafe [OK]

Common Mistakes:

• Confusing prepared statements with unsafe queries
• Thinking all string concatenations are safe
• Ignoring the use of placeholders in queries

3. What will be the output of this PHP code if the URL parameter is ?id=1 OR 1=1?

$id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = $id";
echo $query;

medium

A. SELECT * FROM users WHERE id = 1

B. SELECT * FROM users WHERE id = 1 OR 1=1

C. Syntax error in SQL query

D. SELECT * FROM users WHERE id = '1 OR 1=1'

Solution

1. Step 1: Substitute the URL parameter into the query

   The variable $id becomes the string "1 OR 1=1" directly inserted into the query.

2. Step 2: Form the final SQL query string

   The query becomes: SELECT * FROM users WHERE id = 1 OR 1=1, which is a valid SQL statement.

3. Final Answer:

   SELECT * FROM users WHERE id = 1 OR 1=1 -> Option B

4. Quick Check:

   Unsafe input inserted directly = query with injected condition [OK]

Hint: Direct input inserts raw text into query [OK]

Common Mistakes:

• Assuming input is automatically quoted
• Expecting syntax error without quotes
• Ignoring how SQL interprets OR conditions

4. Identify the error in this PHP code that makes it vulnerable to SQL injection:

$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $query);

medium

A. The query string is missing a semicolon

B. The variable $username is not defined

C. mysqli_query cannot run SELECT queries

D. Using prepared statements would prevent SQL injection

Solution

1. Step 1: Check how user input is used in the query

   The variable $username is inserted directly into the SQL string without sanitization.

2. Step 2: Suggest the fix to prevent injection

   Using prepared statements safely handles input and prevents injection attacks.

3. Final Answer:

   Using prepared statements would prevent SQL injection -> Option D

4. Quick Check:

   Direct input in query = vulnerability [OK]

Hint: Use prepared statements to fix injection bugs [OK]

Common Mistakes:

• Thinking variable is undefined
• Believing mysqli_query can't run SELECT
• Focusing on missing semicolon instead of security

5. You want to fix this vulnerable PHP code to prevent SQL injection:

$email = $_POST['email'];
$query = "SELECT * FROM users WHERE email = '$email'";
$result = mysqli_query($conn, $query);

Which is the best way to fix it?

hard

A. $stmt = $conn->prepare('SELECT * FROM users WHERE email = ?'); $stmt->bind_param('s', $email); $stmt->execute();

B. $email = addslashes($_POST['email']); $query = "SELECT * FROM users WHERE email = '$email'"; mysqli_query($conn, $query);

C. $query = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'"; mysqli_query($conn, $query);

D. $email = htmlspecialchars($_POST['email']); $query = "SELECT * FROM users WHERE email = '$email'"; mysqli_query($conn, $query);

Solution

1. Step 1: Review each option's security

   $stmt = $conn->prepare('SELECT * FROM users WHERE email = ?'); $stmt->bind_param('s', $email); $stmt->execute(); uses prepared statements with parameter binding, which safely handles input.

2. Step 2: Compare other options

   $email = addslashes($_POST['email']); $query = "SELECT * FROM users WHERE email = '$email'"; mysqli_query($conn, $query); uses addslashes which is not fully safe; C is unsafe direct insertion; D uses htmlspecialchars which is for HTML, not SQL.

3. Final Answer:

   $stmt = $conn->prepare('SELECT * FROM users WHERE email = ?'); $stmt->bind_param('s', $email); $stmt->execute(); -> Option A

4. Quick Check:

   Prepared statements = best SQL injection protection [OK]

Hint: Use prepared statements with bind_param for safety [OK]

Common Mistakes:

• Relying on addslashes for security
• Using htmlspecialchars for SQL input
• Continuing direct string concatenation