In this comprehensive tutorial, we'll walk through building a complete authentication system with Flask and MySQL. You'll learn how to implement user registration, login functionality, protected dashboard access, and logout features - all while securely storing user data in a MySQL database.
Why Build Authentication with Flask and MySQL?
Flask is a lightweight Python web framework that's perfect for building web applications, while MySQL remains one of the most popular relational database systems. Combining these technologies gives you:
- A secure way to manage user accounts
- Scalable storage for user credentials
- Python's simplicity with MySQL's reliability
- Full control over your authentication flow
Prerequisites
Before we begin, make sure you have:
- Python 3.6+ installed
- MySQL server installed and running
- Basic knowledge of Python and Flask
- pip for installing Python packages
Setting Up the Project
First, let's create our project structure and install the necessary packages.
1. Create a project folder
mkdir flask-auth-tutorial
cd flask-auth-tutorial
2. Set up a virtual environment
python -m venv venv
source venv/bin/activate # On Windows use `venv\Scripts\activate`
3. Install required packages
pip install flask flask-mysqldb flask-login passlib
Project Structure
Here's how we'll organize our files:
flask-auth-tutorial/
│
├── app.py # Main application file
├── templates/ # HTML templates
│ ├── layout.html # Base template
│ ├── login.html # Login page
│ ├── register.html # Registration page
│ └── dashboard.html # Dashboard page
└── requirements.txt # Dependencies
Database Setup
First, let's create our MySQL database and user table.
1. Connect to MySQL
mysql -u root -p
2. Create database and table
CREATE DATABASE flask_auth;
USE flask_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
);
Building the Flask Application
Now, let's create our Flask application with authentication functionality.
app.py
from flask import Flask, render_template, request, redirect, url_for, flash, session
from flask_mysqldb import MySQL
from passlib.hash import sha256_crypt
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
app = Flask(__name__)
# MySQL Configuration
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '' # Enter your MySQL password here
app.config['MYSQL_DB'] = 'flask_auth'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
# Secret Key
app.secret_key = 'your_secret_key_here'
# Initialize MySQL
mysql = MySQL(app)
# Flask-Login Configuration
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'
class User(UserMixin):
def __init__(self, id, username, email):
self.id = id
self.username = username
self.email = email
@login_manager.user_loader
def load_user(user_id):
cur = mysql.connection.cursor()
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cur.fetchone()
cur.close()
if user:
return User(id=user['id'], username=user['username'], email=user['email'])
return None
# Routes
@app.route('/')
def home():
return render_template('home.html')
@app.route('/register', methods=['GET', 'POST'])
def register():
if request.method == 'POST':
username = request.form['username']
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
cur = mysql.connection.cursor()
# Check if username or email already exists
cur.execute("SELECT * FROM users WHERE username = %s OR email = %s", (username, email))
existing_user = cur.fetchone()
if existing_user:
flash('Username or email already exists', 'danger')
return render_template('register.html')
# Insert new user
cur.execute("INSERT INTO users (username, email, password) VALUES (%s, %s, %s)",
(username, email, password))
mysql.connection.commit()
cur.close()
flash('Registration successful! Please login.', 'success')
return redirect(url_for('login'))
return render_template('register.html')
@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
username = request.form['username']
password_candidate = request.form['password']
cur = mysql.connection.cursor()
# Get user by username
result = cur.execute("SELECT * FROM users WHERE username = %s", [username])
if result > 0:
data = cur.fetchone()
password = data['password']
# Compare passwords
if sha256_crypt.verify(password_candidate, password):
user = User(id=data['id'], username=data['username'], email=data['email'])
login_user(user)
flash('You are now logged in', 'success')
return redirect(url_for('dashboard'))
else:
flash('Invalid password', 'danger')
return render_template('login.html')
else:
flash('Username not found', 'danger')
return render_template('login.html')
cur.close()
return render_template('login.html')
@app.route('/dashboard')
@login_required
def dashboard():
return render_template('dashboard.html', username=current_user.username)
@app.route('/logout')
@login_required
def logout():
logout_user()
flash('You are now logged out', 'success')
return redirect(url_for('login'))
if __name__ == '__main__':
app.run(debug=True)
Creating the HTML Templates
Now let's create our templates for the different pages.
templates/layout.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Flask Auth System</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<div class="container">
<a class="navbar-brand" href="/">Flask Auth</a>
<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ms-auto">
{% if current_user.is_authenticated %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('dashboard') }}">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{ url_for('logout') }}">Logout</a>
</li>
{% else %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('login') }}">Login</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{ url_for('register') }}">Register</a>
</li>
{% endif %}
</ul>
</div>
</div>
</nav>
<div class="container mt-4">
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
<div class="alert alert-{{ category }} alert-dismissible fade show" role="alert">
{{ message }}
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
{% block content %}{% endblock %}
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
templates/login.html
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-6">
<div class="card">
<div class="card-header">
<h4>Login</h4>
</div>
<div class="card-body">
<form action="{{ url_for('login') }}" method="POST">
<div class="form-group mb-3">
<label for="username">Username</label>
<input type="text" name="username" id="username" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="password">Password</label>
<input type="password" name="password" id="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary">Login</button>
</form>
<p class="mt-3">Don't have an account? <a href="{{ url_for('register') }}">Register here</a></p>
</div>
</div>
</div>
</div>
{% endblock %}
templates/register.html
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-6">
<div class="card">
<div class="card-header">
<h4>Register</h4>
</div>
<div class="card-body">
<form action="{{ url_for('register') }}" method="POST">
<div class="form-group mb-3">
<label for="username">Username</label>
<input type="text" name="username" id="username" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="email">Email</label>
<input type="email" name="email" id="email" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="password">Password</label>
<input type="password" name="password" id="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary">Register</button>
</form>
<p class="mt-3">Already have an account? <a href="{{ url_for('login') }}">Login here</a></p>
</div>
</div>
</div>
</div>
{% endblock %}
templates/dashboard.html
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-8">
<div class="card">
<div class="card-header">
<h4>Welcome to Your Dashboard, {{ username }}!</h4>
</div>
<div class="card-body">
<p>This is your protected dashboard page. Only authenticated users can access this page.</p>
<p>You can implement any content here that should be visible to logged-in users only.</p>
</div>
</div>
</div>
</div>
{% endblock %}
Running the Application
To start your Flask application, run:
python app.py
Then open your browser and navigate to http://localhost:5000.
Key Features Explained
1. User Registration
- Collects username, email, and password
- Hashes password using SHA-256 before storage
- Checks for existing username/email to prevent duplicates
- Stores user securely in MySQL database
2. User Login
- Verifies username exists in database
- Compares hashed password with user input
- Uses Flask-Login to manage user sessions
- Redirects to protected dashboard on success
3. Protected Dashboard
- Requires authentication to access
- Displays personalized welcome message
- Demonstrates route protection with
@login_required
4. User Logout
- Ends the user session
- Clears authentication cookies
- Redirects to login page with success message
Security Considerations
Password Hashing: We use
passlibwith SHA-256 to securely hash passwords before storing them.Session Management: Flask-Login handles session management securely.
CSRF Protection: While not shown in this basic example, consider implementing CSRF protection for production.
SQL Injection Prevention: Using parameterized queries helps prevent SQL injection.
HTTPS: Always use HTTPS in production to encrypt data in transit.
Extending the Application
Here are some ways you can extend this basic authentication system:
Email Verification: Add email confirmation before allowing login.
Password Reset: Implement a password reset functionality.
User Roles: Add roles (admin, user, etc.) with different permissions.
Profile Management: Allow users to edit their profiles.
Remember Me: Implement "remember me" functionality for persistent sessions.
Troubleshooting Common Issues
MySQL Connection Errors: Ensure MySQL is running and credentials in
app.pyare correct.Table Not Found: Verify you created the
userstable in theflask_authdatabase.Module Not Found: Make sure you installed all required packages with
pip.Login Not Persisting: Check your secret key is set and consistent.
Password Verification Failing: Ensure you're using the same hashing method consistently.
Conclusion
In this tutorial, we've built a complete Flask authentication system with MySQL that includes:
- User registration with secure password storage
- Login functionality with session management
- Protected dashboard page
- Logout capability
This foundation can be extended to build more complex web applications with user authentication. The combination of Flask and MySQL provides a robust yet flexible system that can scale as your needs grow.
Remember to always prioritize security when dealing with user authentication, especially in production environments. Consider adding additional layers of security like rate limiting, brute force protection, and more sophisticated password policies for real-world applications.
