How SQL injection exploits unsafe queries in PHP - Mechanics & Internals

Overview - How SQL injection exploits unsafe queries

What is it?

SQL injection is a security problem where bad people put harmful commands into a website's database query. This happens when a website takes user input and puts it directly into a database command without checking it first. The attacker tricks the website into running commands it should not, like stealing or changing data. This can cause big problems like data loss or theft.

Why it matters

Without understanding SQL injection, websites can be easily hacked, leading to stolen personal information, lost money, or damaged trust. It is like leaving your house door wide open for thieves. Learning about this helps protect websites and users from serious harm. If no one knew about this, many websites would be unsafe and cause real damage to people and businesses.

Where it fits

Before learning this, you should know basic SQL queries and how websites talk to databases. After this, you can learn how to fix these problems using safe coding practices like prepared statements and input validation. This topic fits in the security part of web development.

Mental Model

Core Idea

SQL injection happens when user input is treated as code inside a database command, letting attackers change what the command does.

Think of it like...

Imagine writing a letter where someone else can add secret instructions that make the postman deliver your letter to the wrong place or open your mailbox. The letter is like the database command, and the secret instructions are the injected SQL.

┌───────────────┐       ┌───────────────┐
│ User Input    │──────▶│ Unsafe Query  │
└───────────────┘       └───────────────┘
          │                      │
          ▼                      ▼
┌─────────────────────┐   ┌─────────────────────┐
│ Malicious SQL Code  │   │ Database Executes    │
│ inserted by attacker│   │ unexpected commands  │
└─────────────────────┘   └─────────────────────┘

Build-Up - 7 Steps

1

Foundation

What is SQL and Queries

Concept: Introduce what SQL is and how queries work to get or change data.

SQL is a language that talks to databases. A query is a command like 'Get all users' or 'Add a new user'. For example, SELECT * FROM users; gets all users from the database.

Result

You understand that SQL commands tell the database what data to show or change.

Knowing how queries work is key to seeing how attackers can change them with bad input.

2

Foundation

How User Input Affects Queries

Concept: Show how websites use user input to build SQL queries.

Websites often ask users for info, like a username. Then they build a query like: SELECT * FROM users WHERE name = '$input'; where $input is what the user typed.

Result

You see that user input becomes part of the SQL command sent to the database.

Understanding this connection reveals where unsafe input can cause problems.

3

Intermediate

Unsafe Query Example in PHP

🤔Before reading on: do you think putting user input directly into a query is safe or unsafe? Commit to your answer.

Concept: Show a PHP example that builds a query unsafely using user input.

This code takes the 'user' from the URL and puts it directly into the query.

Result

If the user types normal names, it works. But if they type something tricky, it can break the query.

Seeing real code helps you spot the exact place where injection can happen.

4

Intermediate

How Attackers Inject SQL

🤔Before reading on: do you think attackers add harmless text or special commands to input? Commit to your answer.

Concept: Explain how attackers add SQL code inside input to change the query meaning.

If an attacker types: ' OR '1'='1 The query becomes: SELECT * FROM users WHERE name = '' OR '1'='1' This always returns true, so the attacker can see all users.

Result

The attacker tricks the database into ignoring the original condition and returning all data.

Understanding how input changes query logic shows why unsafe queries are dangerous.

5

Intermediate

Consequences of SQL Injection

Concept: Describe what attackers can do with SQL injection.

Attackers can steal data, delete tables, add fake users, or even run commands on the server. This can cause data loss, privacy breaches, or website crashes.

Result

You realize SQL injection is a serious threat to website security and user safety.

Knowing the risks motivates learning how to prevent injection.

6

Advanced

Why Unsafe Queries Are Vulnerable

🤔Before reading on: do you think escaping input fully stops injection or only reduces risk? Commit to your answer.

Concept: Explain why simply adding quotes or slashes is not enough to stop injection.

Attackers find ways to bypass simple escaping by using different encodings or breaking out of quotes. Only using prepared statements or parameterized queries safely separates code from data.

Result

You understand that escaping is fragile and can fail, making prepared statements the best defense.

Knowing the limits of escaping prevents false confidence and security holes.

7

Expert

How Prepared Statements Stop Injection

🤔Before reading on: do you think prepared statements send user input as code or data? Commit to your answer.

Concept: Show how prepared statements send user input separately from the SQL command, so it cannot change the command.

prepare('SELECT * FROM users WHERE name = ?'); $stmt->bind_param('s', $user); $stmt->execute(); $result = $stmt->get_result(); ?> Here, the ? is a placeholder. The user input is sent later as data only.

Result

The database treats user input as plain data, never as code, so injection is impossible.

Understanding this mechanism is the key to writing secure database code.

Under the Hood

When a query is built by joining strings, the database receives one big command mixing code and data. If user input contains SQL syntax, it changes the command's meaning. Prepared statements send the SQL code first with placeholders, then send user data separately. The database knows to treat placeholders as data only, never executable code.

Why designed this way?

Early web apps built queries by string joining because it was simple. But this mixed code and data, causing injection risks. Prepared statements were designed to separate code from data, making injection impossible. This design balances ease of use with strong security.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ User Input    │──────▶│ Unsafe Query  │──────▶│ Database      │
│ (mixed code)  │       │ (string join) │       │ executes code │
└───────────────┘       └───────────────┘       └───────────────┘


┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ User Input    │──────▶│ Prepared      │──────▶│ Database      │
│ (data only)   │       │ Statement     │       │ treats data   │
│               │       │ (placeholders)│       │ as data only  │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does escaping user input always prevent SQL injection? Commit to yes or no.

Common Belief: Escaping quotes and special characters in user input fully protects against SQL injection.

Reality: Escaping can be bypassed by clever attackers using different encodings or breaking out of escaping rules.

Why it matters: Relying only on escaping leaves websites vulnerable to attacks that can steal or destroy data.

Quick: Can SQL injection only steal data, or can it also change or delete data? Commit to your answer.

Common Belief: SQL injection only lets attackers read data but not change it.

Reality: Attackers can also modify, delete, or add data, causing bigger damage than just reading.

Why it matters: Underestimating injection risks leads to insufficient defenses and bigger security breaches.

Quick: Is using prepared statements optional or mandatory for security? Commit to your answer.

Common Belief: Prepared statements are just a nice-to-have, not essential for security.

Reality: Prepared statements are the most reliable way to prevent injection and should always be used.

Why it matters: Ignoring prepared statements risks serious vulnerabilities that can be avoided.

Quick: Can SQL injection affect only websites, or can it also harm other systems? Commit to your answer.

Common Belief: SQL injection only affects websites that use databases.

Reality: Any system using SQL databases, including internal tools or apps, can be vulnerable.

Why it matters: Thinking injection is only a web problem can leave other systems exposed.

Expert Zone

1

Some databases have different syntax quirks that affect how injection can happen, so defenses must be database-specific.

2

Stacked queries (sending multiple commands in one query) increase injection risk but are disabled by default in some systems.

3

Even prepared statements can be misused if developers concatenate user input into other parts of the query, like table names.

When NOT to use

Avoid building SQL queries by string concatenation with user input. Instead, use prepared statements or ORM libraries that handle query building safely. For complex queries, use stored procedures or query builders that separate code and data.

Production Patterns

In real systems, developers use prepared statements everywhere user input is involved. They also apply input validation and least privilege database accounts. Logging and monitoring detect suspicious queries. Some use web application firewalls to block injection attempts.

Connections

Cross-Site Scripting (XSS)

Both are injection attacks where user input is treated as code instead of data.

Understanding SQL injection helps grasp how XSS works by injecting malicious scripts into web pages.

Input Validation

Input validation is a complementary defense that checks user input before it reaches the database.

Knowing injection shows why validating input reduces attack surface and improves security.

Code Injection in Operating Systems

Both involve attackers injecting code into a system to change its behavior.

Seeing SQL injection as a form of code injection connects web security to broader system security concepts.

Common Pitfalls

#1 Putting user input directly into SQL queries without any protection.

Wrong approach:

Correct approach:

prepare('SELECT * FROM users WHERE name = ?');
$stmt->bind_param('s', $_GET['user']);
$stmt->execute();
$result = $stmt->get_result();
?>

Root cause:

Misunderstanding that user input can contain harmful SQL code and that string concatenation mixes code and data.

#2 Relying only on escaping functions to prevent injection.

Wrong approach:

Correct approach:

prepare('SELECT * FROM users WHERE name = ?');
$stmt->bind_param('s', $_GET['user']);
$stmt->execute();
$result = $stmt->get_result();
?>

Root cause:

Believing escaping is foolproof, ignoring that attackers can bypass it with clever input.

#3 Using prepared statements but still concatenating user input into other query parts.

Wrong approach:

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

Correct approach:

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

Root cause:

Not realizing that prepared statements only protect placeholders, not other query parts.

Key Takeaways

SQL injection happens when user input is treated as code inside database commands, allowing attackers to change queries.

Building SQL queries by joining strings with user input is unsafe and leads to injection vulnerabilities.

Prepared statements separate code from data, making injection impossible and are the best defense.

Escaping input is not enough; attackers can bypass it with clever tricks.

Understanding injection helps protect websites and systems from serious data breaches and damage.

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