Gửi #879829: amirsanni Mini-Inventory-and-Sales-Management-System 0.1 SQL Injection

# SQL Injection in Transaction::getAll() via SQLite3 ORDER BY/LIMIT Clause (CWE-89)

## Summary

The Mini-Inventory-and-Sales-Management-System contains an authenticated SQL injection vulnerability in the Transaction::getAll() method when deployed with the SQLite3 database driver. User-controlled parameters $orderBy, $orderFormat, $limit, and $start are directly interpolated into the ORDER BY and LIMIT clauses of a raw SQL query. An authenticated attacker can exploit this via the Transactions::latr_() endpoint to perform boolean-based blind SQL injection through CASE WHEN expressions in the ORDER BY clause, enabling extraction of administrator password hashes and other sensitive database contents.

## Details

The vulnerability exists in Transaction::getAll() at application/models/Transaction.php lines 33-45. When the application uses SQLite3, the method constructs a raw SQL query with direct string interpolation of four user-controlled variables into the ORDER BY and LIMIT clauses: ORDER BY {$orderBy} {$orderFormat} LIMIT {$limit} OFFSET {$start}. These parameters originate from Transactions::latr_() at application/controllers/Transactions.php lines 54-65, where they are sourced from $this->input->get() with only XSS filtering (the TRUE parameter enables xss_clean, not SQL escaping). The Transaction controller constructor applies checkLogin() but does not restrict access beyond authentication. The MySQL path at lines 47-61 correctly uses CodeIgniter's query builder with $this->db->order_by() and $this->db->limit() which internally parameterize values. However, the SQLite3 path bypasses all protection by using raw string concatenation. An attacker can inject a CASE WHEN expression into $orderBy to create a boolean-based blind injection: (CASE WHEN (SELECT substr(password,1,1) FROM admin LIMIT 1)='$' THEN ref ELSE transDate END). By observing the order of returned records, the attacker infers whether the condition is true, extracting data one character at a time.

Core vulnerable code path:

```php
// application/models/Transaction.php:33-45
public function getAll($orderBy, $orderFormat, $start, $limit) {
if ($this->db->platform() == "sqlite3") {
$q = "SELECT transactions.ref, ...
ORDER BY {$orderBy} {$orderFormat}
LIMIT {$limit} OFFSET {$start}";

$run_q = $this->db->query($q);
}
```

This is the vulnerable sink. Four user-controlled variables ($orderBy, $orderFormat, $limit, $start) are directly interpolated into the SQL query at lines 42-43. In SQLite3, ORDER BY accepts arbitrary expressions including subqueries via CASE WHEN, enabling boolean-based blind data extraction. The MySQL path (lines 47-61) correctly uses query builder methods which internally parameterize values.

```php
// application/controllers/Transactions.php:51-55
public function latr_()
{
$orderBy = $this->input->get('orderBy', TRUE) ? $this->input->get('orderBy', TRUE) : "transDate";
$orderFormat = $this->input->get('orderFormat', TRUE) ? $this->input->get('orderFormat', TRUE) : "DESC";
...
```

Entry point for the vulnerability. $orderBy and $orderFormat are read from HTTP GET parameters. The second argument TRUE enables XSS filtering (xss_clean), which strips HTML/JavaScript but does NOT provide SQL escaping. The Transaction controller constructor requires authentication via checkLogin().

```php
// application/models/Transaction.php:47-61
else {
$this->db->select('...', FALSE);
$this->db->limit($limit, $start);
$this->db->order_by($orderBy, $orderFormat);
$run_q = $this->db->get('transactions');
}
```

The MySQL path demonstrates the correct secure approach: CI query builder methods like order_by() and limit() internally parameterize or validate inputs. The SQLite3 path (lines 34-45) inexplicably bypasses this protection, using raw string concatenation for the same parameters.

## POC

An authenticated attacker can exploit this via boolean-based blind SQL injection through the ORDER BY clause. Step 1: Authenticate via POST /home/login with valid admin credentials to obtain a session. Step 2: Send a GET request to /transactions/latr_ with a crafted orderBy parameter containing a CASE WHEN expression. For example: GET /transactions/latr_?orderBy=(CASE WHEN (SELECT substr(password,1,1) FROM admin LIMIT 1)='$' THEN ref ELSE transDate END)&orderFormat=ASC with X-Requested-With: XMLHttpRequest header. Step 3: Observe the order of returned transactions. If the first character of the admin password hash is '$' (which is always true for bcrypt hashes starting with $2y$), transactions will be ordered by ref. If the condition is false, they will be ordered by transDate. This differential response allows the attacker to extract data one character at a time by iterating through possible characters. The bcrypt hash prefix ($2y$) provides a known starting point to verify the technique works. The same approach can be used in the LIMIT clause with subqueries to extract additional data. Preconditions: Application configured with SQLite3 driver; attacker has valid admin session; database contains transaction records.

## Impact

An authenticated attacker can extract the entire database contents character by character through boolean-based blind SQL injection. This includes administrator bcrypt password hashes, email addresses, mobile numbers, and roles. The attacker can conduct offline cracking of weak bcrypt hashes, potentially escalating to full system compromise. Additionally, all transaction records with customer PII (names, phone numbers, email addresses, purchase amounts) and complete inventory data can be extracted.

## Remediation

Apply input validation for $orderBy: use a whitelist of allowed column names (e.g., ['transDate', 'ref', 'totalMoneySpent', 'cust_name']). Restrict $orderFormat to only 'ASC' or 'DESC'. Force $limit and $start to integers using intval(). As a defense-in-depth measure, convert the SQLite3 path to use the same parameterized query builder approach as the MySQL path, or use bound parameters for all values.

## Disclosure Notes

Discovered through source code audit of the Mini-Inventory-and-Sales-Management-System repository. Affected version: 0.1. Patched version: to be confirmed. The vulnerability requires authentication and is limited to the SQLite3 deployment mode. Responsible disclosure initiated per project SECURITY.md.

## Supplemental Information

### Affected products

- Ecosystem: self-hosted
- Package name: Mini-Inventory-and-Sales-Management-System
- Affected versions: 0.1
- Patched versions: to be confirmed

### Severity

- Scoring method: CVSS v3.1
- Score: 6.5
- Vector string: CVSS:3.1/AV:N/AC:L/PR:L/UI:N/S:U/C:H/I:N/A:N

### Weaknesses

- CWE: CWE-89 Improper Neutralization of Special Elements used in an SQL Command (SQL Injection)

• Nguồn: ⚠️ https://github.com/amirsanni/Mini-Inventory-and-Sales-Management-System/issues/101
• Người dùng: summmm (UID 69690)
• Đệ trình: 04/07/2026 05:05 (cách đây 2 các tháng)
• Kiểm duyệt: 19/08/2026 19:34 (2 months later)
• Trạng thái: được chấp nhận
• Mục VulDB: 393250 [amirsanni Mini-Inventory-and-Sales-Management-System 0.1 Transaction.php Transaction::getAll orderBy/orderFormat Tiêm SQL]
• điểm: 20