SQL Injection Awareness and Prevention

# CHAPTER 8

SQL Injection Awareness and Prevention #

1. Introduction #

2. Learning Objectives #

• Define SQL Injection and understand its mechanical execution.

• Analyze how unsanitized input corrupts database queries.

• Understand Authentication Bypass via SQLi (e.g., ' OR 1=1 --).

• Recognize the devastating impact of successful SQL Injection.

• Implement Parameterized Queries (Prepared Statements) to permanently prevent SQLi.

3. Beginner-Friendly Explanation #

• Normal Operation: You walk up and say, "My name is John." The guard looks at the ledger, finds "John," and lets you in.

• The SQL Injection Attack: A malicious person walks up and says, "My name is John OR everyone is allowed in."

• The Vulnerability: The security guard is a robot. It does not realize that the second half of the sentence was a command. It reads the ledger, realizes that "everyone is allowed in" is a true statement, and opens the door for the entire street.

SQL Injection is tricking the database into treating your input as a command, rather than as simple text.

4. The Mechanics of SQL Injection #

// DANGEROUS, VULNERABLE CODE
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

If a normal user types admin, the query executed on the database is: SELECT * FROM users WHERE username = 'admin' AND password = 'password123'

The Attack: What if the attacker types this specific string into the username box: admin' OR 1=1 -- (The -- symbol means "ignore everything after this line" in SQL).

The PHP code carelessly drops that string into the query. The executed query becomes: SELECT * FROM users WHERE username = 'admin' OR 1=1 -- ' AND password = '...'

Because 1=1 is mathematically True, the database says, "Yes, the username is admin, OR true. Since true is true, I will log you in!" The attacker just bypassed the password requirement entirely.

5. The Impact #

• Read the entire database (dumping millions of user records).

• Modify or delete data (dropping tables, altering financial balances).

• In some cases, read physical files on the server or execute operating system commands.

6. Mini Project: Fix Vulnerable PHP Login Form Safely #

Parameterized queries force the database to treat the user input strictly as *data*, and absolutely never as *executable code*.

Step-by-Step Architecture Concept (Secure Remediation): Instead of building the string manually, we use a framework like PDO in PHP.

// SECURE CODE (Using PDO Prepared Statements)
$username = $_POST['username'];
$password = $_POST['password'];

// 1. Prepare the blueprint of the query. Use '?' as placeholders.
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');

// 2. Execute the query, passing the user input completely separately.
$stmt->execute([$username, $password]);

*If the attacker types admin' OR 1=1 -- now, the database will literally look for a user whose actual name is exactly the string admin' OR 1=1 --. It will fail to find them, and the attack is neutralized.*

7. Real-World Scenarios #

8. Best Practices #

• Least Privilege for Databases: Even with perfect code, defense-in-depth is required. The database user account that the web application uses to connect to the database must NOT be the root or admin account. If the application only needs to read blog posts, the database user should only have SELECT permissions. If an SQL injection is discovered, the attacker cannot delete (DROP) the tables because the database user lacks the permission to do so.

9. Security Recommendations #

• Avoid Automated Exploitation in Bug Bounties: Tools like sqlmap are extremely noisy and aggressive. If you are participating in a legal Bug Bounty program, running sqlmap against a production target will likely crash their database, getting you banned from the program. Professional testers learn how to identify and prove SQLi manually with minimal impact.

10. Troubleshooting Tips #

• Blind SQL Injection: Sometimes an application is vulnerable to SQLi, but it does not print the database errors or the extracted data to the screen. This is called "Blind" SQLi. Attackers test for this by injecting commands that tell the database to pause (e.g., WAITFOR DELAY '0:0:10'). If the website suddenly takes exactly 10 seconds to load, the attacker knows the database is executing their injected code, proving the vulnerability exists.

11. Exercises #

1. 1. Explain the mechanical process of how an attacker utilizes the string ' OR 1=1 -- to bypass an authentication portal.

1. 2. Why is attempting to filter or "sanitize" malicious characters (like quotes) considered an inferior defense compared to Parameterized Queries?

12. FAQs #

13. Interview Questions #

• Q: Describe the mechanics of an SQL Injection attack. Provide an example of vulnerable backend code, and contrast it with the enterprise-standard remediation methodology.

• Q: Explain the concept of Defense-in-Depth regarding database security. If a web application is definitively vulnerable to SQL Injection, how can the Principle of Least Privilege applied at the database-user level mitigate the potential impact of the breach?

14. Summary #

15. Next Chapter Recommendation #