Employee Management System v1 - 'email' SQL Injection

SQL Injection in Employee Management System v1: A Critical Security Flaw Exploited via the 'email' Field

Security vulnerabilities in web applications often stem from poor input validation and improper handling of user data. One such critical flaw was discovered in the Employee Management System v1, a PHP-based application hosted on SourceCodester. This system, designed for managing employee records, includes a login mechanism that is vulnerable to SQL Injection due to direct concatenation of user input into SQL queries.

Understanding the Vulnerability

SQL Injection occurs when an attacker manipulates input fields to alter the intended SQL query. In this case, the txtemail field in the login form is directly inserted into a database query without sanitization or parameterization. This creates a perfect attack vector.

$sql = "SELECT * FROM users WHERE email='" .$email. "' and password = '".$password."' and status = '".$status."'"; 

This code snippet is the root of the problem. It constructs a SQL query by concatenating user-supplied data (email, password, status) directly into the string. An attacker can exploit this by entering malicious input that alters the logic of the query.

Proof of Concept: Exploiting the 'email' Field

Consider the following input in the email field:

• email: ' OR '1'='1' --
• password: anything

When this input is processed, the resulting SQL query becomes:

SELECT * FROM users WHERE email='' OR '1'='1' --' AND password = 'anything' AND status = 'Active'

The OR '1'='1' condition is always true, effectively bypassing the email check. The -- comment symbol terminates the rest of the query, preventing further validation. As a result, the query returns any user with an active status, regardless of the actual email or password.

Even if the password field is correctly filled, the attacker can still gain access because the query logic is broken by the injected condition. This means the system fails to enforce authentication rules, allowing unauthorized login.

Real-World Implications

Such a vulnerability is not just theoretical—it has real-world consequences. In a corporate environment, an attacker could:

• Gain unauthorized access to employee records.
• Modify or delete sensitive data.
• Extract confidential information such as passwords, contact details, or salary information.
• Escalate attacks to other systems via compromised credentials.

Additionally, since the application uses mysqli_query() with raw string concatenation, it opens the door to more advanced attacks like union-based SQL injection, blind SQL injection, or even database enumeration, especially if the application is exposed to the internet.

Why This Vulnerability Exists

Despite being a relatively simple application, the flaw stems from a lack of fundamental security practices:

• Direct user input insertion: The developer used string concatenation instead of prepared statements.
• No input validation: No checks were performed to ensure email format or prevent special characters.
• Use of deprecated functions: The code relies on mysqli_query() without proper escaping.

These are common mistakes in beginner-level PHP applications, especially when sourced from open platforms like SourceCodester. While the code may function correctly under normal conditions, it fails under adversarial input.

Corrected Code: Using Prepared Statements

Modern security standards mandate the use of prepared statements to prevent SQL injection. Here’s how the login query should be rewritten:

$stmt = $conn->prepare("SELECT * FROM users WHERE email = ? AND password = ? AND status = ?");
$stmt->bind_param("sss", $email, $password, $status);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    $row = $result->fetch_assoc();
    $_SESSION["email"] = $row['email'];
    $_SESSION["password"] = $row['password'];
    $_SESSION["phone"] = $row['phone'];
    $_SESSION["firstname"] = $row['firstname'];
    $fa = $row['2FA'];
}

Explanation: This revised code uses prepare() to create a parameterized query. The bind_param() function safely binds user inputs as variables, ensuring that SQL syntax cannot be altered by malicious input. The database engine treats these values as data, not executable code, eliminating the risk of injection.

Additional Security Best Practices

To further harden the system, developers should implement the following measures:

• Input sanitization: Validate email format using filter_var() or regex.
• Password hashing: Store passwords using password_hash() and verify with password_verify().
• Rate limiting: Implement login attempt throttling to prevent brute-force attacks.
• Logging: Record failed login attempts for monitoring and forensic analysis.
• Use of HTTPS: Ensure all login traffic is encrypted to prevent credential interception.

Conclusion

The Employee Management System v1 vulnerability serves as a stark reminder that even simple applications can harbor critical security flaws. The email field SQL injection is not just a technical issue—it’s a breach of trust and data integrity. Developers must prioritize security from the outset, especially when building systems that manage sensitive employee data.

By adopting prepared statements, input validation, and secure authentication practices, developers can prevent such attacks and build resilient, trustworthy systems. This case highlights the importance of continuous security audits, even for seemingly low-risk applications.