Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
How to write Python CGI program to interact with MySQL?
CGI (Common Gateway Interface) allows Python scripts to handle web requests and interact with databases like MySQL. This tutorial shows how to create a login system using Python CGI that validates user credentials against a MySQL database.
HTML Login Form
First, create an HTML form that collects user credentials and submits them to the Python CGI script ?
login.html
<html>
<body>
<form action="login.py" method="post">
Email: <input type="text" name="email" required>
Password: <input type="password" name="password" required>
<input type="submit" value="Login">
</form>
</body>
</html>
Python CGI Script for MySQL Authentication
The Python script handles the form submission, queries the MySQL database, and returns appropriate responses ?
login.py
#!/usr/bin/env python3
import pymysql
import cgi
import cgitb
import hashlib
# Enable CGI error reporting
cgitb.enable()
# Print content type header
print("Content-Type: text/html\n")
try:
# Parse form data
form = cgi.FieldStorage()
email = form.getvalue('email')
password = form.getvalue('password')
if not email or not password:
raise ValueError("Email and password are required")
# Hash password for secure comparison
password_hash = hashlib.sha256(password.encode()).hexdigest()
# Connect to MySQL database
connection = pymysql.connect(
host='localhost',
user='root',
password='your_password',
database='student',
charset='utf8mb4'
)
cursor = connection.cursor()
# Prepared statement to prevent SQL injection
query = "SELECT id, email FROM users WHERE email = %s AND password = %s"
cursor.execute(query, (email, password_hash))
result = cursor.fetchone()
if result:
# Successful login
user_id, user_email = result
print(f"""
<html>
<head>
<title>Login Successful</title>
</head>
<body>
<h2>Welcome, {user_email}!</h2>
<p>Login successful. User ID: {user_id}</p>
</body>
</html>
""")
else:
# Failed login
print("""
<html>
<head>
<title>Login Failed</title>
</head>
<body>
<h2>Login Failed</h2>
<p>Invalid email or password.</p>
<a href="login.html">Try Again</a>
</body>
</html>
""")
except Exception as e:
# Error handling
print(f"""
<html>
<body>
<h2>Error</h2>
<p>An error occurred: {str(e)}</p>
</body>
</html>
""")
finally:
# Close database connection
if 'connection' in locals():
connection.close()
Database Setup
Create a MySQL database and user table with the following structure ?
CREATE DATABASE student;
USE student;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(64) NOT NULL
);
-- Insert sample user (password is hashed SHA256 of "password123")
INSERT INTO users (email, password) VALUES
('user@example.com', 'ef92b778bafe771e89245b89ecbc08a44a4e166c06659911881f383d4473e94f');
Security Considerations
Key security improvements in this implementation:
- Uses prepared statements to prevent SQL injection attacks
- Implements password hashing using SHA-256 for secure storage
- Uses POST method instead of GET for sensitive data
- Includes error handling to prevent information disclosure
- Uses modern PyMySQL library instead of deprecated MySQLdb
Conclusion
Python CGI scripts can effectively interact with MySQL databases for user authentication. Always use prepared statements and password hashing for security. Modern web frameworks like Flask or Django are recommended over CGI for production applications.
1K+ Views
