Employee Management System 1.0 - `txtfullname` and `txtphone` SQL Injection

Employee Management System 1.0 — txtfullname and txtphone SQL Injection (CVE-2024-24499)

This article examines an SQL injection vulnerability identified in Employee Management System 1.0 (CVE-2024-24499) affecting two admin profile fields: txtfullname and txtphone. It explains what the vulnerability is, why it matters, how to validate and remediate the issue, and provides secure coding examples and best practices for PHP applications.

Overview

SQL injection (SQLi) occurs when user-supplied input is incorporated into a database query without proper controls. In this application, the admin edit profile mechanism accepted raw text inputs for full name and phone number and used them in SQL statements in a way that allowed an attacker to manipulate the query logic. The consequence of SQLi ranges from data disclosure to database modification and, in some environments, remote code execution.

Affected components

• /employee_akpoly/Admin/edit_profile.php — update endpoint for admin profile
• User inputs: txtfullname and txtphone

Impact

• Exposure of sensitive data stored in the database (user records, credentials, configuration).
• Modification or deletion of records (integrity loss).
• Potential for privilege escalation or lateral movement if credentials or other secrets are exposed.
• Reputational and compliance consequences for organizations using the application.

High-level detection and testing (safe guidance)

• Perform a code review on the edit profile code path to look for unsanitized concatenation of request parameters into SQL queries.
• Use static analysis tools and security scanners (SAST/DAST) to identify SQL injection patterns automatically.
• On test systems only, verify using controlled inputs that prepared statements or parameterized APIs are used and that field validation restricts unexpected characters and lengths.

Why the vulnerability happens

Typical causes:

• Direct concatenation of request values into SQL strings (e.g., "... WHERE id = " . $_POST['id']).
• Lack of parameterized queries or ORM usage.
• Insufficient input validation or character filtering on form inputs such as fullname and phone.
• Overly permissive database user permissions.

Secure coding: Recommended fixes

The most important fixes are:

• Use prepared statements / parameterized queries (PDO or mysqli).
• Validate and normalize input (whitelist characters and enforce length limits).
• Implement least-privilege database accounts.
• Sanitize output when rendering data in HTML (to prevent XSS) even if SQLi is mitigated.

Example: Secure update using PDO (recommended)

<?php
// Example: secure profile update using PDO and strict input validation

// Database connection (use configuration management for credentials)
$dsn = 'mysql:host=localhost;dbname=employee_db;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, 'app_user', 'secure_password', $options);

// Input retrieval and trimming
$fullname = isset($_POST['txtfullname']) ? trim($_POST['txtfullname']) : '';
$phone    = isset($_POST['txtphone']) ? trim($_POST['txtphone']) : '';
$adminId  = isset($_SESSION['admin_id']) ? (int)$_SESSION['admin_id'] : 0;

// Whitelist validation
if ($adminId prepare($sql);
$stmt->bindValue(':fullname', $fullname, PDO::PARAM_STR);
$stmt->bindValue(':phone', $phone, PDO::PARAM_STR);
$stmt->bindValue(':id', $adminId, PDO::PARAM_INT);
$stmt->execute();

// Confirm success
echo "Profile updated successfully.";
?>

Explanation: This code demonstrates using PDO with prepared statements and parameter binding. It performs upfront validation: a Unicode-aware regex for names (letters, spaces, hyphens, apostrophes) and a conservative phone pattern allowing digits, spaces, hyphens and an optional leading plus. No user data is concatenated into the SQL string, and PDO is configured to throw exceptions and avoid emulated prepares.

Alternative: Secure update using mysqli (procedural style)

<?php
// mysqli example with prepared statements
$mysqli = new mysqli('localhost', 'app_user', 'secure_password', 'employee_db');
if ($mysqli->connect_errno) {
    error_log("DB connect error: " . $mysqli->connect_error);
    exit('Database unavailable');
}

$fullname = trim($_POST['txtfullname'] ?? '');
$phone    = trim($_POST['txtphone'] ?? '');
$adminId  = (int)($_SESSION['admin_id'] ?? 0);

if (!preg_match("/^[\p{L}\s'\-]{2,100}$/u", $fullname) || !preg_match("/^\+?[0-9\- ]{7,20}$/", $phone)) {
    exit('Invalid input.');
}

$stmt = $mysqli->prepare("UPDATE admins SET fullname = ?, phone = ? WHERE id = ?");
$stmt->bind_param('ssi', $fullname, $phone, $adminId);
$stmt->execute();
$stmt->close();
$mysqli->close();

echo "Updated.";
?>

Explanation: This snippet shows the mysqli procedural API with prepared statements and bind_param. Input is validated before being sent to the database, and values are bound as parameters so the DB engine treats them as data, not SQL syntax.

Input validation guidance for fullname and phone

• Full name: enforce a reasonable min/max length (e.g., 2–100 characters). Use Unicode-aware regex to allow international names; permit letters, spaces, hyphens, and apostrophes. Avoid overly permissive character classes that include SQL metacharacters.
• Phone number: normalize input to digits only or allow international format with a leading '+'. Enforce reasonable length and reject embedded SQL or control characters. Consider storing canonical numeric values and separate formatting for display.

Database and deployment hardening

• Use a database account with least privilege — the web app account should not be a DBA or superuser.
• Disable multiple statement execution where possible.
• Enable logging and monitor anomalous queries or error rates.
• Apply timely updates and patches to the web application framework, database server, and OS.

Testing and verification

After applying fixes:

• Run unit and integration tests that exercise the profile update path and verify expected behavior for valid and invalid inputs.
• Use static analysis tools to confirm no raw concatenation of request data into SQL across the codebase.
• Perform penetration testing on an isolated test environment under an authorized engagement to validate mitigations (do not run destructive tests on production).

Remediation checklist

Task	Action
Use parameterized queries	Migrate all SQL that uses external input to prepared statements or an ORM with safe parameter binding.
Input validation	Implement server-side whitelisting and normalization for fullname and phone fields; enforce length limits.
Least privilege	Create a DB user with only the required CRUD permissions for the application.
Logging & monitoring	Record failed validation attempts and unusual database errors for security monitoring.
Security testing	Perform code reviews, SAST, and authorized penetration tests on a staging environment.

References & further reading

• OWASP SQL Injection Prevention Cheat Sheet — patterns and examples for parameterization and validation.
• OWASP Input Validation Cheat Sheet — best practices for whitelisting and canonicalization.
• PHP documentation: PDO prepared statements and best practices.

Implementing prepared statements, strict input validation, and least privilege database access will mitigate this class of vulnerabilities and significantly improve the security posture of the Employee Management System.