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)

Prerequisites
Setting Up the Database
Creating the Project Structure
Building the Registration System
Implementing Login Functionality
Adding Logout Functionality
Enhancing Security
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:

Building a Secure PHP Authentication System with MySQL

Building a Secure PHP Authentication System with MySQL

Table of Contents

1. Prerequisites

2. Setting Up the Database

SQL Query to Create Database & Table

Explanation:

3. Creating the Project Structure

4. Building the Registration System

Step 1: Database Connection (`config.php`)

Step 2: Registration Form (`register.php`)

Step 3: Handling Registration (`auth.php`)

Key Points:

5. Implementing Login Functionality

Step 1: Login Form (`login.php`)

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

Key Points:

6. Adding Logout Functionality (`logout.php`)

7. Enhancing Security

✔️ Prevent SQL Injection

✔️ Password Security

✔️ CSRF Protection (Optional)

✔️ HTTPS

✔️ Rate Limiting

8. Conclusion

Next Steps

Tags

Comments

Related Posts

Building a Secure PHP Authentication System with MySQL

Building a Secure PHP Authentication System with MySQL

Table of Contents

1. Prerequisites

2. Setting Up the Database

SQL Query to Create Database & Table

Explanation:

3. Creating the Project Structure

4. Building the Registration System

Step 1: Database Connection (config.php)

Step 2: Registration Form (register.php)

Step 3: Handling Registration (auth.php)

Key Points:

5. Implementing Login Functionality

Step 1: Login Form (login.php)

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

Key Points:

6. Adding Logout Functionality (logout.php)

7. Enhancing Security

✔️ Prevent SQL Injection

✔️ Password Security

✔️ CSRF Protection (Optional)

✔️ HTTPS

✔️ Rate Limiting

8. Conclusion

Next Steps

Tags

Comments

Related Posts

Step 1: Database Connection (`config.php`)

Step 2: Registration Form (`register.php`)

Step 3: Handling Registration (`auth.php`)

Step 1: Login Form (`login.php`)

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

6. Adding Logout Functionality (`logout.php`)