Challenge #3: Implement Basic Authentication with MySQL
In this challenge, your task is to build a simple authentication system using Python (or your preferred language) and MySQL. This system should allow users to register with a username and password and then log in with those credentials. Here’s how it should work:
Requirements
Database Setup:
Create a MySQL database with a table named
users.The
userstable should contain the following fields:id(Primary Key, Auto Increment)username(Unique)password_hash(Hashed password)
Registration Endpoint:
Create a function
register(username, password)that allows new users to sign up.The function should:
Check if the username is already taken.
Hash the password before storing it (use a secure hashing algorithm like bcrypt or Argon2).
Save the username and password hash in the
userstable.
Login Endpoint:
Create a function
login(username, password)that allows users to log in.The function should:
Retrieve the stored password hash for the given username.
Verify the provided password against the stored hash.
Return a success message if authentication succeeds or an error if it fails.
Password Hashing:
Use a secure hashing library (e.g., bcrypt in Python) to hash and verify passwords.
Avoid storing plain text passwords.
import mysql.connector
import bcrypt
# Database configuration for Docker MySQL container
config = {
'user': 'root',
'password': 'my-secret-pw', # Root password set in Docker
'host': 'localhost', # Docker container exposed on localhost
'port': 3306,
'database': 'user_database' # Database created in Docker container
}
def create_connection():
"""Establish a connection to the database and create the users table if it doesn't exist."""
try:
connection = mysql.connector.connect(**config)
cursor = connection.cursor()
# Create the users table if it doesn't exist
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL
)
"""
cursor.execute(create_table_query)
connection.commit()
return connection
except mysql.connector.Error as err:
print(f"Database error: {err}")
return None
def register(username, password):
"""Register a new user with a hashed password."""
connection = create_connection()
if not connection:
return "Error: Could not connect to the database."
try:
cursor = connection.cursor()
# Check if username is already taken
cursor.execute("SELECT username FROM users WHERE username = %s", (username,))
if cursor.fetchone():
return "Error: Username already taken."
# Hash the password
password_hash = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt())
# Insert the new user into the database
# print("password_hash",password_hash)
cursor.execute("INSERT INTO users (username, password_hash) VALUES (%s, %s)", (username, password_hash))
connection.commit()
return "User registered successfully."
except mysql.connector.Error as err:
print(f"Database error: {err}")
return "Error: Could not register user."
finally:
cursor.close()
connection.close()
def login(username, password):
"""Authenticate a user by verifying the provided password."""
connection = create_connection()
if not connection:
return "Error: Could not connect to the database."
try:
cursor = connection.cursor()
# Retrieve the stored password hash for the given username
cursor.execute("SELECT password_hash FROM users WHERE username = %s", (username,))
result = cursor.fetchone()
if result is None:
return "Error: Username not found."
stored_password_hash = result[0]
# Verify the provided password against the stored hash
if bcrypt.checkpw(password.encode('utf-8'), stored_password_hash.encode('utf-8')):
return "Login successful!"
else:
return "Error: Invalid password."
except mysql.connector.Error as err:
print(f"Database error: {err}")
return "Error: Could not log in."
finally:
cursor.close()
connection.close()
#print(register("new", "mypassword123"))
#print(login("new","mypassword123"))
print(register("new1","pass")) is this correct sir?