How SQL injection exploits unsafe queries in PHP - Quick Revision & Summary

Recall & Review

beginner

What is SQL injection?

SQL injection is a security attack where a bad user adds harmful SQL code into a query to trick the database into doing things it shouldn't, like giving secret data or changing information.

beginner

Why are unsafe SQL queries vulnerable to injection?

Unsafe queries mix user input directly into SQL commands without checking or cleaning it. This lets attackers add extra commands that the database runs, causing harm.

beginner

Example of unsafe PHP SQL query using user input

In PHP, writing:
$query = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'";
is unsafe because the user can add SQL code inside the 'name' input.

intermediate

How does an attacker exploit an unsafe query?

The attacker types SQL code into input fields. For example, entering ' OR '1'='1' can change the query to always be true, showing all data instead of just one user.

beginner

What is a simple way to prevent SQL injection in PHP?

Use prepared statements with placeholders. This keeps user input separate from SQL code, so the database treats it only as data, not commands.

What does SQL injection allow an attacker to do?

A Encrypt user passwords automatically

B Speed up database queries

C Run harmful SQL commands through user input

D Backup the database safely

SQL injection lets attackers add harmful SQL code via user input to manipulate the database.

Which PHP code is unsafe and vulnerable to SQL injection?

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

B Escaping user input properly

C Using prepared statements with bind parameters

D $query = "SELECT * FROM users WHERE id = ?";

Directly inserting user input into SQL strings without checks is unsafe.

What is a common sign of an SQL injection attack in a query?

A User input contains SQL keywords like OR or --

B Query runs faster than usual

C Database connection fails

D User input is empty

Attackers use SQL keywords in input to change query logic.

How do prepared statements help prevent SQL injection?

A They speed up the database server

B They delete harmful data automatically

C They encrypt the database

D They separate SQL code from user data

Prepared statements keep user input separate so it can't change SQL commands.

Which of these is NOT a good practice to avoid SQL injection?

A Using prepared statements

B Directly inserting user input into SQL queries

C Validating and sanitizing user input

D Limiting database user permissions

Directly inserting user input without checks is unsafe and leads to injection.

Explain in your own words how SQL injection exploits unsafe queries.

Think about how mixing user input and SQL code can be dangerous.

Describe a simple PHP example of an unsafe SQL query and how it can be fixed.

Focus on how user input is included in the query.

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