PHP Database Security

Intermediate~30 min read

Database security is critical! Prepared statements protect against SQL injection - one of the most common and dangerous web vulnerabilities. Always use them!

<?php
// Prepared Statements & Security

// PDO Prepared Statements (SECURE)
$pdo = new PDO("mysql:host=localhost;dbname=myapp", "root", "");

// Named parameters
$sql = "SELECT * FROM users WHERE email = :email AND status = :status";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'email' => $_POST['email'],
    'status' => 'active'
]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// Positional parameters
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
$user = $stmt->fetch();

// MySQLi Prepared Statements
$mysqli = new mysqli("localhost", "root", "", "myapp");

$sql = "SELECT * FROM users WHERE email = ? AND status = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ss", $_POST['email'], "active");
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// WRONG - SQL Injection Vulnerable
// $sql = "SELECT * FROM users WHERE email = '{$_POST['email']}'";
// NEVER do this!

// Security Best Practices:
// 1. Always use prepared statements
// 2. Never concatenate user input into SQL
// 3. Validate and sanitize input
// 4. Use appropriate data types
// 5. Limit database user permissions

// Input Validation
function validateEmail($email)
{
    return filter_var($email, FILTER_VALIDATE_EMAIL);
}

if (validateEmail($_POST['email'])) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
    $stmt->execute(['email' => $_POST['email']]);
}

Output

Click Run to execute your code

PDO Prepared Statements

<?php
// SECURE - Named parameters
$sql = "SELECT * FROM users WHERE email = :email";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => $_POST['email']]);

// SECURE - Positional parameters
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
?>

MySQLi Prepared Statements

<?php
$sql = "SELECT * FROM users WHERE email = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $_POST['email']); // s = string
$stmt->execute();
$result = $stmt->get_result();
?>

⚠️ NEVER Do This!

<?php
// VULNERABLE TO SQL INJECTION!
$sql = "SELECT * FROM users WHERE email = '{$_POST['email']}'";
$result = $pdo->query($sql); // DON'T DO THIS!
?>

Security Best Practices

✅ Always use prepared statements
❌ Never concatenate user input into SQL
✅ Validate and sanitize all input
✅ Use appropriate data types
✅ Limit database user permissions
✅ Use HTTPS for sensitive data

Summary

Prepared statements: Prevent SQL injection
PDO: Use named (:param) or positional (?) parameters
MySQLi: Use bind_param()
Never: Concatenate user input into SQL

What's Next?

Congratulations! You've completed Module 10 (Database). You now have complete PHP mastery from basics to databases!

Previous PDO & CRUD

Next Error & Exceptions