Daily Expense Manager 1.0 - 'term' SQLi

Daily Expense Manager 1.0 — 'term' SQL Injection (CWE-89)

This article explains a SQL injection vulnerability discovered in Daily Expense Manager 1.0 that affects the GET parameter term on the readxp.php endpoint. It covers why the issue exists, how an attacker can abuse it (proof-of-concept), the real risk to applications and data, and robust remediation and mitigation strategies with clear, secure PHP examples.

Vulnerability summary

• Vulnerable application: Daily Expense Manager (version 1.0)
• Vulnerable parameter: term (GET)
• Affected endpoint: /Daily-Expense-Manager/readxp.php?term=...
• Technical root cause: Unsanitized string interpolation into SQL (dynamic query construction) allowing classic SQL injection
• Cwe: CWE-89 (SQL Injection) and related input handling weaknesses (CWE-74 for injection of special elements)

Affected file and code (excerpt)

<?php
//get search term
$searchTerm = $_GET['term']; # unsanitized and under control of the attacker.
//get matched data from skills table
$query = $conn->query("SELECT * FROM expense WHERE pname like '%$searchTerm%' AND uid='$sid' and isdel='0' group by pname");
while ($row = $query->fetch_assoc()) {
 $data[] = $row['pname'];
}
//return json data
echo json_encode($data);
?>

Explanation: The application reads the user-supplied term and injects it directly into a SQL string. Because the value is placed inside single quotes and used with a LIKE pattern, an attacker can escape the quote and append arbitrary SQL fragments (for example, UNION SELECT) to manipulate the query or extract data.

Proof-of-Concept (demonstration)

http://example.com/Daily-Expense-Manager/readxp.php?term=asd%27%20UNION%20ALL%20SELECT%201,@@version,3,4,5,6--%20-

Explanation: This example closes the original quoted string (the %27 is a URL-encoded apostrophe), appends a UNION SELECT to merge attacker-controlled columns into the result set, and uses @@version to reveal the database version. The returned JSON would contain the injected value where the application expects the product name. The payload demonstrates a common UNION-based extraction technique.

Why the query is exploitable

• The application trusts user input and places it directly into the SQL command without parameter binding.
• Using LIKE with single quotes still allows an attacker to terminate the quote and append arbitrary SQL.
• Group and projection logic (GROUP BY pname) does not prevent an injected UNION query from returning results; instead it gives an attacker rows to exfiltrate data into the JSON response.

Potential impact

• Data disclosure — attacker can extract sensitive database content (usernames, emails, hashed passwords, financial records).
• Data manipulation — depending on DB permissions, attacker could update or delete records.
• Authentication bypass — sensitive fields can be used to escalate privileges or pivot to other parts of the system.
• Reputation and compliance risk — breaches exposing financial or PII data can lead to legal and regulatory consequences.

Secure fixes — parameterized queries

Best practice: use prepared statements (parameterized queries) and avoid embedding user data directly into SQL. For LIKE patterns, bind the value and add wildcards on the application side.

Fix using MySQLi (object-oriented)

// Assume $conn is a mysqli object and $sid is the current session user id (validated)
$search = '%' . $conn->real_escape_string($_GET['term']) . '%';
$stmt = $conn->prepare("SELECT pname FROM expense WHERE pname LIKE ? AND uid = ? AND isdel = '0' GROUP BY pname");
$stmt->bind_param('ss', $search, $sid);
$stmt->execute();
$result = $stmt->get_result();
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row['pname'];
}
echo json_encode($data);

Explanation: This example prepares the query and binds two parameters: the search pattern and the user id. Even though we used real_escape_string to be defensive, the key protection is prepared statements and binding. Using prepared statements prevents the injected payload from being interpreted as SQL structure. Wildcards are added in PHP so the placeholder only carries data, not SQL tokens.

Preferred fix using PDO (recommended for flexibility)

// Assume $pdo is a PDO instance with ATTR_EMULATE_PREPARES = false and proper error mode
$term = isset($_GET['term']) ? $_GET['term'] : '';
$search = '%' . $term . '%';
$sql = "SELECT pname FROM expense WHERE pname LIKE :search AND uid = :uid AND isdel = '0' GROUP BY pname";
$stmt = $pdo->prepare($sql);
$stmt->execute([':search' => $search, ':uid' => $sid]);
$data = $stmt->fetchAll(PDO::FETCH_COLUMN);
echo json_encode($data);

Explanation: PDO's named parameters make the code readable. Setting PDO::ATTR_EMULATE_PREPARES = false ensures true, native prepared statements when supported. Binding user input as values prevents it from altering SQL structure.

Additional hardening and best practices

• Input validation: apply a whitelist or length limits for search terms (e.g., max 100 characters). Validation does not replace prepared statements but reduces attack surface.
• Least privilege: the DB user used by the web app should have the minimum privileges required (SELECT, INSERT/UPDATE if needed). Avoid using a DBA or all-powerful account.
• Use parameterized queries for all SQL operations — never dynamically concatenate user input into SQL strings.
• Sanitize outputs: encode JSON and HTML outputs when displayed in a browser to prevent secondary issues like XSS.
• Disable verbose DB error messages in production to avoid leaking schema or SQL text in responses; log errors securely instead.
• Deploy WAF and input anomaly detection as additional layers to help block obvious attacks.

Testing and detection

• Perform code reviews focusing on all places user input touches a SQL string.
• Use automated scanners (SAST for source code, DAST for running apps) and manual verification of endpoints that accept user data.
• Look for suspicious query patterns or error messages in logs, unusual SELECT patterns (use of UNION), or repeated requests with special characters.
• Monitor for data exfiltration attempts and anomalous outbound traffic from the database server.

Responsible disclosure and remediation workflow

• Notify the vendor/maintainer with reproduction steps, affected versions and suggested fixes. Provide a timeline for remediation.
• Assign tracking (CVE if appropriate) and coordinate public advisories only after a patch or mitigation is available.
• Provide users of the software with clear update instructions or temporary mitigations (e.g., disable features, restrict access) if immediate patching is not possible.

Quick checklist for developers and administrators

Action	Why it matters
Use prepared statements	Prevents SQL injection by separating code and data
Least-privilege DB accounts	Limits damage if credentials are compromised
Input validation & length limits	Reduces attack surface and avoids large payloads
Disable verbose errors	Avoids leaking database details to attackers
Regular code scans and pen tests	Detects regressions and new issues early

Resources

• Vendor page (original project): https://code-projects.org/daily-expense-manager-in-php-with-source-code/
• OWASP SQL Injection Prevention Cheat Sheet — practical guidelines and patterns
• PDO and MySQLi official docs — using prepared statements and parameter binding