Building a Secure PHP Authentication System with MySQL

Authentication is a crucial part of web applications, ensuring that users can securely log in, register, and manage their accounts. In this guide, we'll build a secure PHP authentication system using MySQL from scratch. This system will include:

• ✅ User Registration
• ✅ Login & Logout
• ✅ Password Hashing (for security)
• ✅ Session Management
• ✅ Basic Security Measures (SQL Injection Prevention, CSRF Protection)

Table of Contents

1. Prerequisites
2. Setting Up the Database
3. Creating the Project Structure
4. Building the Registration System
5. Implementing Login Functionality
6. Adding Logout Functionality
7. Enhancing Security
8. Conclusion

1. Prerequisites

Before starting, ensure you have:

• PHP (8.0+ recommended)
• MySQL (or MariaDB)
• A web server (Apache, Nginx, or PHP built-in server)
• Basic knowledge of PHP & MySQL

2. Setting Up the Database

First, let's create a MySQL database and a users table.

SQL Query to Create Database & Table

CREATE DATABASE php_auth;
USE php_auth;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• id → Auto-incremented primary key.
• username & email → Unique to prevent duplicates.
• password → Will store hashed passwords (never store plain text!).
• created_at → Tracks registration time.

3. Creating the Project Structure

Organize your project like this:

php-auth/
│── includes/
│   ├── config.php       # Database connection
│   ├── functions.php    # Helper functions
│   └── auth.php         # Auth logic (login, register)
│── index.php            # Homepage
│── register.php         # Registration form
│── login.php            # Login form
│── dashboard.php        # Protected page
│── logout.php           # Logout script

4. Building the Registration System

Step 1: Database Connection (config.php)

<?php
$host = 'localhost';
$dbname = 'php_auth';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Step 2: Registration Form (register.php)

<!DOCTYPE html>
<html>
<head>
    <title>Register</title>
</head>
<body>
    <h2>Register</h2>
    <form action="includes/auth.php" method="POST">
        <input type="text" name="username" placeholder="Username" required><br>
        <input type="email" name="email" placeholder="Email" required><br>
        <input type="password" name="password" placeholder="Password" required><br>
        <button type="submit" name="register">Register</button>
    </form>
    <p>Already have an account? <a href="login.php">Login here</a></p>
</body>
</html>

Step 3: Handling Registration (auth.php)

<?php
session_start();
require 'config.php';

if (isset($_POST['register'])) {
    $username = $_POST['username'];
    $email = $_POST['email'];
    $password = password_hash($_POST['password'], PASSWORD_BCRYPT);

    try {
        $stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
        $stmt->execute([$username, $email, $password]);
        $_SESSION['success'] = "Registration successful! You can now login.";
        header("Location: login.php");
        exit();
    } catch (PDOException $e) {
        $_SESSION['error'] = "Registration failed: " . $e->getMessage();
        header("Location: register.php");
        exit();
    }
}
?>

Key Points:

• password_hash() → Securely hashes passwords.
• PDO Prepared Statements → Prevents SQL injection.
• $_SESSION → Stores success/error messages.

5. Implementing Login Functionality

Step 1: Login Form (login.php)

<!DOCTYPE html>
<html>
<head>
    <title>Login</title>
</head>
<body>
    <h2>Login</h2>
    <?php if (isset($_SESSION['error'])): ?>
        <p style="color: red;"><?= $_SESSION['error']; ?></p>
        <?php unset($_SESSION['error']); ?>
    <?php endif; ?>
    <form action="includes/auth.php" method="POST">
        <input type="text" name="username" placeholder="Username" required><br>
        <input type="password" name="password" placeholder="Password" required><br>
        <button type="submit" name="login">Login</button>
    </form>
    <p>Don't have an account? <a href="register.php">Register here</a></p>
</body>
</html>

Step 2: Handling Login (auth.php - Continued)

if (isset($_POST['login'])) {
    $username = $_POST['username'];
    $password = $_POST['password'];

    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
    $user = $stmt->fetch();

    if ($user && password_verify($password, $user['password'])) {
        $_SESSION['user_id'] = $user['id'];
        $_SESSION['username'] = $user['username'];
        header("Location: dashboard.php");
        exit();
    } else {
        $_SESSION['error'] = "Invalid username or password.";
        header("Location: login.php");
        exit();
    }
}

Key Points:

• password_verify() → Checks hashed passwords.
• $_SESSION['user_id'] → Stores logged-in user data.

6. Adding Logout Functionality (logout.php)

<?php
session_start();
session_unset();
session_destroy();
header("Location: login.php");
exit();
?>

7. Enhancing Security

✔️ Prevent SQL Injection

• Always use PDO prepared statements (as shown above).

✔️ Password Security

• Use password_hash() and password_verify().

✔️ CSRF Protection (Optional)

• Generate and validate tokens in forms.

✔️ HTTPS

• Ensure your site runs on HTTPS in production.

✔️ Rate Limiting

• Prevent brute-force attacks by limiting login attempts.

8. Conclusion

You've now built a secure PHP authentication system with MySQL! This includes:

• ✅ User registration & login
• ✅ Password hashing
• ✅ Session management
• ✅ Basic security best practices

Next Steps

• Add email verification.
• Implement password reset.
• Use PHP frameworks (Laravel, Symfony) for more robust solutions.

🚀 Happy Coding! 🚀

Let me know in the comments if you have any questions! 😊👇