Setting Up a Secure MySQL Database with JWT Authentication in PHP

In today's digital landscape, securing user data is paramount. When developing web applications, utilizing a secure database setup alongside robust authentication mechanisms is critical. In this article, we’ll explore how to set up a secure MySQL database and implement JSON Web Token (JWT) authentication in PHP. By the end, you will have a solid understanding of how to protect your application and its users.

Understanding MySQL and JWT

MySQL is an open-source relational database management system. It’s widely used for web applications due to its reliability, performance, and ease of use. On the other hand, JWT is a compact token format used for securely transmitting information between parties. JWTs are particularly useful in stateless authentication systems, allowing for secure user sessions without server-side session storage.

Why Use JWT?

• Stateless Authentication: No need to store session data on the server.
• Cross-Domain Support: Suitable for single-page applications (SPAs) and mobile apps.
• Secure Information Transfer: JWTs can be signed to verify authenticity.

Prerequisites

Before diving into the implementation, ensure you have the following:

• PHP installed (preferably PHP 7.0 or higher).
• Composer for managing dependencies.
• A MySQL server running locally or remotely.
• Basic knowledge of PHP and SQL.

Step 1: Setting Up the MySQL Database

1. Create a Database: Start by creating a database for your application. You can do this using the MySQL command line or a GUI tool like phpMyAdmin.

sql CREATE DATABASE jwt_auth;

1. Create a Users Table: This table will store user credentials.

```sql USE jwt_auth;

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL ); ```

1. Insert Sample User: Add a user to test our authentication.

sql INSERT INTO users (username, password) VALUES ('testuser', '$2y$10$e4v5u8g6Q4g5D6d.NfG3Me6C2eX8oK9v1sC5v7Zy2c1ZslWcPZ.O6O');

Note: The password above is a hashed version of "password123" using bcrypt. Use password_hash() in PHP to generate it.

Step 2: Setting Up PHP Environment

1. Install Required Libraries: Use Composer to install the necessary libraries for handling JWT.

bash composer require firebase/php-jwt

1. Create a PHP File for Your Application: Let's call it index.php.

Step 3: Implementing JWT Authentication

Setting Up the Database Connection

Add the following code to connect to your MySQL database:

<?php
$host = 'localhost';
$db = 'jwt_auth';
$user = 'root';
$pass = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Could not connect to the database: " . $e->getMessage());
}
?>

User Registration

Create a function to register new users:

function registerUser($username, $password) {
    global $pdo;
    $hashedPassword = password_hash($password, PASSWORD_BCRYPT);
    $stmt = $pdo->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
    return $stmt->execute([$username, $hashedPassword]);
}

User Login and JWT Generation

Next, implement user login and JWT generation.

use Firebase\JWT\JWT;

function loginUser($username, $password) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
    $user = $stmt->fetch();

    if ($user && password_verify($password, $user['password'])) {
        $secretKey = 'your_secret_key';
        $payload = [
            'iat' => time(),
            'exp' => time() + (60 * 60), // Token valid for 1 hour
            'id' => $user['id'],
            'username' => $user['username']
        ];
        return JWT::encode($payload, $secretKey);
    }
    return false;
}

Putting It All Together

Finally, let’s create a simple API endpoint for login:

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $data = json_decode(file_get_contents('php://input'), true);
    $username = $data['username'];
    $password = $data['password'];

    $token = loginUser($username, $password);
    if ($token) {
        echo json_encode(['token' => $token]);
    } else {
        http_response_code(401);
        echo json_encode(['message' => 'Invalid credentials']);
    }
}

Step 4: Securing Routes with JWT

To secure routes, you can create a middleware function to verify the token.

function validateJWT($jwt) {
    $secretKey = 'your_secret_key';
    try {
        $decoded = JWT::decode($jwt, $secretKey, ['HS256']);
        return $decoded;
    } catch (Exception $e) {
        return false;
    }
}

Example of Secured Route

To use this middleware, you can check the token before accessing protected resources:

if (isset($_SERVER['HTTP_AUTHORIZATION'])) {
    list($jwt) = sscanf($_SERVER['HTTP_AUTHORIZATION'], 'Bearer %s');
    $userData = validateJWT($jwt);
    if (!$userData) {
        http_response_code(401);
        echo json_encode(['message' => 'Unauthorized']);
        exit;
    }
    echo json_encode(['message' => 'Welcome, ' . $userData->username]);
} else {
    http_response_code(401);
    echo json_encode(['message' => 'Token not provided']);
}

Conclusion

Setting up a secure MySQL database with JWT authentication in PHP is an essential skill for modern web developers. By following the steps outlined in this article, you can create a robust authentication system that ensures user data is protected while providing a seamless user experience. Remember, the key to effective security is continuous learning and implementation of best practices. Now it’s your turn to implement and optimize your own secure applications!