Simple Student Attendance System v1.0 - 'classid' Time Based Blind & Union Based SQL Injection

Exploiting SQL Injection in Simple Student Attendance System v1.0: A Deep Dive into Time-Based Blind & Union-Based Attacks

Security vulnerabilities in web applications often stem from poor input validation and improper handling of user-supplied data. One such example is the Simple Student Attendance System v1.0, a PHP-based application developed by oretnom23, available on SourceCodester. Despite its simplicity, this system contains a critical flaw: a time-based blind and union-based SQL injection vulnerability in the class_id parameter. This article explores the technical depth of the exploit, its real-world implications, and mitigation strategies.

Understanding the Vulnerable Code

Let’s examine the core function responsible for the vulnerability:

public function attendanceStudents($class_id = "", $class_date = ""){
    if(empty($class_id) || empty($class_date))
        return [];
    
    $sql = "SELECT `students_tbl`.*, COALESCE((SELECT `status` FROM `attendance_tbl` where `student_id` = `students_tbl`.id and `class_date` = '{$class_date}' ), 0) as `status` FROM `students_tbl` where `class_id` = '{$class_id}' order by `name` ASC";
    
    $qry = $this->conn->query($sql);
    $result = $qry->fetch_all(MYSQLI_ASSOC);
    return $result;
}

This code constructs a SQL query using user input directly in the class_id parameter. The class_id value is embedded into the query without sanitization or parameterization. This creates a perfect environment for SQL injection.

Exploitation Techniques: Time-Based Blind Injection

Time-based blind SQL injection exploits the database's ability to delay execution using functions like SLEEP(). Attackers can infer whether a condition is true by measuring response time.

For instance, the payload used in sqlmap demonstrates this:

page=attendance&class_id=446' AND (SELECT 5283 FROM (SELECT(SLEEP(5)))zsWT) AND 'nqTi'='nqTi&class_date=0002-02-20

Explanation: The injected ' AND (SELECT 5283 FROM (SELECT(SLEEP(5)))zsWT) AND 'nqTi'='nqTi forces the database to execute SLEEP(5) if the condition evaluates to true. If the server responds with a 5-second delay, the attacker confirms that the SQL query is being processed — indicating a successful injection. The 446 is a placeholder; the actual value is irrelevant — the focus is on the AND clause triggering the delay.

This technique is especially effective when the application does not return any visible output — making it "blind." The attacker uses timing as the only feedback mechanism.

Exploitation Techniques: Union-Based SQL Injection

Union-based SQL injection allows attackers to retrieve data from other tables by appending a UNION ALL query. This method requires matching column counts and compatible data types.

Here is the payload used:

page=attendance&class_id=446' UNION ALL SELECT NULL,NULL,NULL,NULL,CONCAT(0x7171717671,0x7154766a5453645a7a4d497071786a6f4b647a5a6d4162756c72636b4a4555746d555a5a71614d4c,0x71767a7a71),NULL-- -&class_date=0002-02-20

Explanation: The UNION ALL clause appends a crafted query to the original. The attacker injects NULL values for six columns (matching the original query's output), then uses CONCAT to concatenate hexadecimal values into readable strings. The hex values 0x7171717671 and 0x71767a7a71 represent qqqvq and qVZaQ, respectively. The middle hex string 0x7154766a5453645a7a4d497071786a6f4b647a5a6d4162756c72636b4a4555746d555a5a71614d4c translates to qTvjTSDZaMIpqxaokDZaMAbulrcbJEUtMZZqAMc — likely a test string or identifier.

When executed, this query returns additional data in the status column, revealing that the attacker can read arbitrary data from the database — potentially including sensitive information like user credentials or internal configuration.

Real-World Impact and Risks

While this system appears harmless, the implications are severe:

• Data Exposure: An attacker can extract all student records, attendance logs, or even administrator credentials if the database structure permits.
• Privilege Escalation: With access to the attendance_tbl or users_tbl, an attacker could manipulate attendance records or impersonate users.
• Server Compromise: In advanced scenarios, SQL injection could be chained with other exploits (e.g., LOAD_FILE or SELECT INTO OUTFILE) to write files to the server or exfiltrate data via external channels.

Security Best Practices: Mitigation and Prevention

To prevent such vulnerabilities, developers must follow secure coding principles:

Best Practice	Implementation
Use Prepared Statements	Replace direct string concatenation with parameterized queries using mysqli_prepare and bind_param.
Input Validation	Sanitize input to ensure class_id is numeric and within expected range (e.g., 1–999).
Whitelist Filtering	Accept only predefined, safe values; reject any non-numeric or SQL-like input.
Error Handling	Never expose database errors to users — log internally and show generic messages.

Here is a corrected version of the function using prepared statements:

public function attendanceStudents($class_id = "", $class_date = ""){
    if(empty($class_id) || empty($class_date))
        return [];

    // Validate input
    if(!is_numeric($class_id) || $class_id  999)
        return [];

    $sql = "SELECT `students_tbl`.*, COALESCE((SELECT `status` FROM `attendance_tbl` WHERE `student_id` = `students_tbl`.id AND `class_date` = ?), 0) as `status` FROM `students_tbl` WHERE `class_id` = ? ORDER BY `name` ASC";
    
    $stmt = $this->conn->prepare($sql);
    $stmt->bind_param("ss", $class_date, $class_id);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
    return $result;
}

Explanation: This revised code uses mysqli_prepare and bind_param to safely inject values. The class_id and class_date are treated as parameters, not strings. This eliminates the possibility of SQL injection, regardless of input.

Conclusion

The Simple Student Attendance System v1.0 serves as a textbook example of how even basic applications can harbor serious vulnerabilities due to improper SQL handling. The combination of time-based blind and union-based SQL injection demonstrates the power of automated tools like sqlmap in uncovering flaws. However, the real lesson lies in prevention: always use parameterized queries, validate inputs rigorously, and follow security-by-design principles.

As cybersecurity professionals, we must not only identify vulnerabilities but also advocate for secure development practices. In this case, a simple code change — replacing string concatenation with prepared statements — can prevent exploitation entirely.