Prevent SQL Injection | Database Security Best Practices

# CHAPTER 26

Database Security and SQL Injection Prevention #

1. Introduction #

2. Learning Objectives #

• Understand the mechanics of an SQL Injection attack.

• Identify vulnerable application code.

• Implement the absolute solution: Prepared Statements.

• Understand the Principle of Least Privilege.

• Apply basic Database Security best practices.

3. The Anatomy of an SQL Injection Attack #

// VULNERABLE PHP CODE!
$email = $_POST['email']; // The user typed:  admin@site.com
$sql = "SELECT * FROM users WHERE email = '" . $email . "';";

*If the user plays nice, the final SQL string sent to the database looks like this:* SELECT * FROM users WHERE email = 'admin@site.com'; (Perfect).

The Attack: What if a hacker types this exact string into the email box: ' OR 1=1; --

*The PHP script blindly stitches it in. The final SQL sent to the database becomes:* SELECT * FROM users WHERE email = '' OR 1=1; --';

Why this is catastrophic: The database evaluates 1=1. Is 1 equal to 1? Yes. True. Because of the OR operator, the entire WHERE clause evaluates to True for *every single row in the table*. The database returns the first user in the table (which is usually the Admin). The hacker instantly bypasses the password check and logs in as the Admin!

*(Worse: They could have typed '; DROP TABLE users; -- and deleted your database!).*

4. The Cure: Prepared Statements (Parameterized Queries) #

With Prepared Statements, you separate the SQL structure from the Data. You send the SQL structure to the database first, using placeholders (?). The database compiles it. *Then*, you send the user's input separately.

// SECURE PHP CODE (Using PDO Prepared Statements)

// 1. Send the Structure FIRST. (The '?' is a placeholder)
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");

// 2. Execute the query, passing the User Input safely.
$stmt->execute([$hacker_input]);

Why this is unbreakable: Because the SQL structure is already compiled, when the hacker sends '; DROP TABLE users; --, the database treats it as a literal text string. It will physically search the table for a user whose legal email address is literally "; DROP TABLE users; --". It finds nothing, and the attack fails harmlessly.

5. The Principle of Least Privilege #

• The Web App user should only have SELECT, INSERT, UPDATE, DELETE permissions.

• The Web App user should never have DROP or ALTER permissions. If they can't drop tables, a hacker who gains access to that connection cannot drop tables either!

6. Hashing Passwords #

*What the database should see:* INSERT INTO users (password_hash) VALUES ('$2y$10$abcdefghijklmnopqrstuv...');

7. Common Mistakes #

• Using String Replacement for Security: Beginners sometimes try to "sanitize" inputs by writing functions that delete the word DROP or remove single quotes from user input. Hackers are smarter than these filters. They will encode their payloads or bypass your Regex. Never sanitize manually. ONLY use Prepared Statements.

8. Best Practices #

• Disable Remote Root Login: By default, your database server should physically reject any connection attempting to log in as root unless the connection originates from the exact same physical server (localhost). Never expose port 3306 or 5432 directly to the public internet without a firewall.

9. Exercises #

1. 1. What is the exact definition of an SQL Injection attack?

1. 2. What cryptographic technique must be applied to user passwords *before* they are stored in the SQL database?

10. SQL Challenges #

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the mechanical step-by-step process of how a classic ' OR 1=1; -- SQL Injection attack forces a vulnerable login script to authenticate an attacker.

• Q: Why is attempting to manually "sanitize" or "clean" user input considered an obsolete and dangerous architectural practice compared to utilizing Prepared Statements?

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #