Building a Secure Supervisor Registration System with PHP and MySQL

Introduction: Supervisor registration is a crucial aspect of many web applications, facilitating the management and delegation of tasks within teams. However, developing a robust and secure registration system requires careful consideration of various factors, including data validation, password hashing, file uploads, and email verification. In this comprehensive guide, we'll delve into the process of building a supervisor registration system using PHP and MySQL, covering every step from database setup to email confirmation.

1. Setting Up the Database:

• Start by creating a MySQL database and defining the schema for the supervisor table. This table will store supervisor details such as first name, last name, email, mobile number, username, password, and other metadata.
• Use the following SQL schema to create the supervisor table:
  
  

CREATE TABLE `supervisor` (
  `suprv_id` int(11) NOT NULL AUTO_INCREMENT,
  `suprv_vch_fname` varchar(50) NOT NULL,
  `suprv_vch_lname` varchar(50) NOT NULL,
  `suprv_vch_emailid` varchar(120) NOT NULL,
  `suprv_vch_mobileno` varchar(20) NOT NULL,
  `suprv_vch_username` varchar(20) NOT NULL,
  `suprv_vch_password` varchar(255) NOT NULL,
  `suprv_vch_profileimg` varchar(255) NOT NULL DEFAULT 'avatar.png',
  `suprv_int_ownerid` int(11) NOT NULL,
  `suprv_bit_blacklisted` tinyint(1) NOT NULL DEFAULT '0',
  `suprv_bit_isactive` tinyint(1) NOT NULL DEFAULT '1',
  `suprv_bit_delete` tinyint(1) NOT NULL DEFAULT '0',
  `suprv_last_seen` datetime DEFAULT NULL,
  `suprv_lastseen_ip` varchar(200) DEFAULT NULL,
  `suprv_welcomemail_sent` tinyint(1) NOT NULL DEFAULT '0',
  `suprv_vch_forgotpasswordtoken` varchar(255) DEFAULT NULL,
  `suprv_dtm_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `suprv_dtm_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `suprv_int_onlinestatus` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`suprv_id`),
  UNIQUE KEY `suprv_vch_emailid` (`suprv_vch_emailid`,`suprv_vch_mobileno`,`suprv_vch_username`),
  KEY `suprv_int_ownerid` (`suprv_int_ownerid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Creating the Registration Form:

• Develop an HTML form to capture supervisor details such as first name, last name, email, mobile number, username, password, and profile image.
• Implement client-side validation using JavaScript to ensure that all required fields are filled out correctly before submission.
  
  

3. Processing the Registration Form:

• In the register.php script, retrieve form data using $_POST variables and perform server-side validation to ensure data integrity and security.
• Hash the supervisor's password using PHP's password_hash() function to securely store it in the database.

4. File Upload for Profile Image:

• Allow supervisors to upload a profile image during registration.
• Validate uploaded files and move them to the appropriate directory.
• Store the file path in the database for future reference.
  

5. Sending Confirmation Email:

• Generate a unique token for email verification and include it in the confirmation email.
• Use PHP's mail() function or a third-party library to send the confirmation email.
• Log email communication in the database for auditing purposes.

6. Implementing Server-Side Validation:

• Server-side validation is crucial to ensure that the data submitted by users is safe and meets the required criteria.
• Use PHP's built-in functions and regular expressions to validate fields such as email, mobile number, and password strength.

// Server-side validation for email
if (!filter_var($strEmail, FILTER_VALIDATE_EMAIL)) {
    echo "Invalid email format";
}

// Server-side validation for mobile number
if (!preg_match("/^[0-9]{10}$/", $strMobileNo)) {
    echo "Invalid mobile number";
}

// Server-side validation for password strength
if (strlen($strPassword) < 8) {
    echo "Password must be at least 8 characters long";
}

7. Enhancing User Experience with Client-Side Validation:

• Although server-side validation is essential for security, implementing client-side validation using JavaScript can improve the user experience by providing instant feedback.
• Use JavaScript to validate form fields as the user fills them out, highlighting errors and preventing submission until all fields are valid.

// Client-side validation for email
const emailInput = document.getElementById('email');
emailInput.addEventListener('blur', function() {
    if (!isValidEmail(emailInput.value)) {
        // Display error message
        emailInput.classList.add('error');
    } else {
        emailInput.classList.remove('error');
    }
});

function isValidEmail(email) {
    return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
}

8. Handling File Uploads Securely:

• File uploads pose security risks, so it's essential to implement proper validation and handling.
• Validate file types and sizes on both the client and server sides to prevent malicious uploads.
• Use unique filenames and store uploaded files outside the web root directory to prevent direct access.

// Server-side validation for file upload
$allowedExtensions = array('jpg', 'jpeg', 'png', 'gif');
$uploadPath = 'uploads/';

if (in_array(strtolower(pathinfo($_FILES['avatarImage']['name'], PATHINFO_EXTENSION)), $allowedExtensions)) {
    if ($_FILES['avatarImage']['size'] <= 5000000) { // 5MB limit
        // Move uploaded file to desired directory
        move_uploaded_file($_FILES['avatarImage']['tmp_name'], $uploadPath . uniqid() . '_' . $_FILES['avatarImage']['name']);
    } else {
        echo "File size exceeds the limit";
    }
} else {
    echo "Invalid file type";
}

9. Secure Password Storage with Hashing:

• Storing passwords securely is essential to protect user accounts from unauthorized access.
• Use PHP's password_hash() function to hash passwords before storing them in the database.
• When verifying passwords during login, use PHP's password_verify() function to compare the stored hash with the user's input.

11. Email Verification and Welcome Messages:

• Email verification adds an extra layer of security by confirming the user's email address before granting access to the system.
• Generate a unique token for each user during registration and include it in the confirmation email.
• Create a separate table to store email verification tokens and expiration timestamps.
• When users click the verification link, validate the token and activate their accounts if valid.

// Generate unique token for email verification
$emailVerificationToken = bin2hex(random_bytes(32));

// Store token and expiration timestamp in the database
$stmt = $mysqliCon->prepare("INSERT INTO email_verification (user_id, token, expires_at) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $userId, $emailVerificationToken, $expirationTimestamp);
$stmt->execute();

// Send email with verification link
$verificationLink = "https://example.com/verify_email.php?token=$emailVerificationToken";
// Construct email body with verification link
// Send email using mail() function or a third-party library

12. Handling Account Activation:

• Create a separate page or endpoint to handle email verification requests.
• When users click the verification link, validate the token and activate their accounts if valid.
• Update the database to mark the user's account as verified.

// Verify email verification token
$stmt = $mysqliCon->prepare("SELECT * FROM email_verification WHERE token = ? AND expires_at > NOW()");
$stmt->bind_param("s", $token);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows == 1) {
    // Token is valid
    $row = $result->fetch_assoc();
    $userId = $row['user_id'];

    // Update user's account to mark it as verified
    $stmt = $mysqliCon->prepare("UPDATE users SET verified = 1 WHERE id = ?");
    $stmt->bind_param("i", $userId);
    $stmt->execute();

    // Delete verification token from database
    $stmt = $mysqliCon->prepare("DELETE FROM email_verification WHERE token = ?");
    $stmt->bind_param("s", $token);
    $stmt->execute();

    // Redirect user to login page with a success message
} else {
    // Token is invalid or expired
    // Redirect user to an error page or display an error message
}

Conclusion:

• Building a secure supervisor registration system requires attention to detail and adherence to best practices in web application security.
• By implementing measures such as CSRF protection, SQL injection prevention, logging and auditing, and continuous monitoring and maintenance, you can create a robust and secure registration process that protects user data and preserves the integrity of your application.
• Remember that security is a never-ending journey, and it's essential to stay vigilant and proactive in safeguarding your system against evolving threats.