Protecting Against SQL Injection

# CHAPTER 10

Protecting Against SQL Injection #

1. Introduction #

2. Learning Objectives #

• Explain the mechanics of a SQL Injection attack.

• Understand how string concatenation causes SQL vulnerabilities.

• Identify the catastrophic consequences of a successful SQLi attack.

• Refactor vulnerable PHP/MySQLi code into secure code.

• Implement PHP Data Objects (PDO) Prepared Statements.

3. Beginner-Friendly Explanation #

A SQL Injection is when you write malicious instructions in the blank space. Instead of "100", you write: "100. Actually, never mind, give me all the cash in the vault instead." If the teller is a dumb computer program (a vulnerable API), it reads the whole sentence as a valid instruction, ignores the original deposit plan, and hands you all the cash in the vault.

To stop this, we use Prepared Statements. This forces the teller to treat whatever you wrote strictly as a *value* (a number or a name), and absolutely never as an *instruction*.

4. Real-World Attack Scenarios #

• The Authentication Bypass: An API login endpoint expects a username. An attacker sends ' OR '1'='1. The resulting SQL query becomes SELECT * FROM users WHERE username = '' OR '1'='1'. Since 1 always equals 1, the database returns the first user in the table (usually the Admin) without requiring a password!

• Data Exfiltration (UNION-Based): An attacker injects a UNION SELECT statement into a search API. The API is supposed to return products, but the injected query forces the database to append the contents of the passwords table to the product results.

5. Vulnerable Code Example (String Concatenation) #

<?php
// VULNERABLE: Direct string concatenation
$username = $_POST[&#039;username']; // Attacker sends: admin'; DROP TABLE users; --

// The query becomes: SELECT * FROM users WHERE username = 'admin'; DROP TABLE users; --'
$query = "SELECT * FROM users WHERE username = &#039;$username'";
$db->query($query); 
?>

The attacker just terminated the first query with a semicolon ;, and executed a second query that deletes the entire user table.

6. Secure Code Example (PDO Prepared Statements) #

Using PDO (PHP Data Objects):

<?php
// 1. The SQL structure is defined with a placeholder (?)
// The database compiles this structure BEFORE looking at the user data.
$query = "SELECT * FROM users WHERE username = ?";

// 2. Prepare the statement
$stmt = $pdo->prepare($query);

// 3. Execute the statement, passing the user input as an array
$username = $_POST[&#039;username']; // Even if this is malicious...
$stmt->execute([$username]);    // ...it is treated STRICTLY as a string value.

$user = $stmt->fetch();
?>

If the attacker sends admin'; DROP TABLE users, the database literally searches for a user whose actual name is "admin'; DROP TABLE users". It finds nothing, and the attack fails harmlessly.

7. Named Placeholders #

<?php
$query = "UPDATE users SET email = :email, role = :role WHERE id = :id";
$stmt = $pdo->prepare($query);

// Execute takes an associative array mapping the placeholders to the data
$stmt->execute([
    &#039;:email' => $_POST['email'],
    &#039;:role'  => 'user',
    &#039;:id'    => $_SESSION['user_id']
]);
?>

8. What about mysqli_real_escape_string()? #

9. Best Practices #

• Use PDO Exclusively: Adopt PDO for all database interactions in PHP. It supports 12 different database types and enforces prepared statements cleanly.

• Never Concatenate: Make it a strict rule in your codebase: Never use the . operator or variable interpolation (e.g., "SELECT * FROM users WHERE id = $id") to insert variables into a SQL string.

• Least Privilege Database User: The MySQL user account your API uses should only have permission to execute DML (Select, Insert, Update, Delete). It should NEVER have permission to DROP tables or alter schema.

10. Common Mistakes #

• Prepared Statements on Table/Column Names: You *cannot* use placeholders (?) for Table names or Column names. SELECT * FROM ? WHERE ? = ? will crash. Placeholders are ONLY for values. If you must dynamically sort by a column name, you must strictly Allow-list the column name in PHP before inserting it into the query.

• Using Unprepared Queries for "Safe" Data: Developers sometimes skip prepared statements if the data comes from a trusted source (like an internal API or a cookie). Never assume data is safe. Use prepared statements for *everything*.

11. Mini Exercises #

1. 1. What character is used as an unnamed placeholder in a PDO prepared statement?

1. 2. Explain why a hacker injecting ' OR '1'='1 bypasses a poorly written login query.

12. Practice Challenges #

$search = $_GET['q'];
$sql = "SELECT id, name FROM products WHERE name LIKE '%" . $search . "%'";

Rewrite this code using PDO Prepared statements and named placeholders to make it secure against SQL injection.

13. MCQs with Answers #

14. Interview Questions #

• Q: Explain how a SQL Injection attack works, providing an example of an Authentication Bypass payload.

• Q: Exactly how do Prepared Statements (Parameterized Queries) neutralize SQL Injection? Why are they better than escaping strings?

• Q: Can you use a PDO placeholder (?) for a database table name? If not, how do you securely handle dynamic table selection?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #