Make Sense of Legacy Code with AI Explainers

# Legacy Perl CGI Expense Report Script Analysis

## 1. High-Level Purpose

This is a classic early-2000s web application: a single Perl CGI (Common Gateway Interface) script that renders an HTML form for employees to submit expense reports, validates input, and persists records to an Oracle database via DBI (Perl's DataBase Interface module). It likely handles the full round-trip—display form, accept POST, insert rows, show confirmation—in one monolithic file.

## 2. Key Components & Responsibilities

- **CGI request handling**: Probably uses `CGI.pm` or raw `%ENV`/`STDIN` parsing to read form parameters.
- **DBI/Oracle connection block**: Opens a connection to Oracle (likely via `DBD::Oracle`), using hardcoded or environment-based credentials.
- **Inline HTML generation**: Uses `print` statements or heredocs (`<<HTML`) to emit markup directly, mixing presentation with logic.
- **Input validation**: Ad-hoc regex checks on amounts, dates, employee IDs.
- **SQL INSERT logic**: Builds statements to write into expense-related tables.
- **`mangle_approver` subroutine**: Almost certainly transforms an approver identifier before lookup or insert. Common possibilities:
  - Strips domain from an email (`jdoe@corp.com` → `jdoe`)
  - Converts a manager's name to an LDAP/NT username
  - Applies a legacy org-chart rule (e.g., routes certain dollar amounts to a VP)
  - Normalizes case or pads with leading zeros for an Oracle key
- **Response output**: Confirmation page or error redisplay.

## 3. Control Flow / Data Flow Walkthrough

1. Web server (likely Apache with `mod_cgi`) invokes the script per request.
2. Script prints `Content-Type` header, then reads CGI parameters.
3. If no form data present → render blank HTML form and exit.
4. If form submitted → validate fields (amount, date, category, approver).
5. Calls `mangle_approver($raw_input)` to normalize the approver value.
6. Opens DBI handle to Oracle: `DBI->connect("dbi:Oracle:SID", $user, $pass)`.
7. Prepares and executes INSERT (possibly with string interpolation rather than bind variables).
8. On success, prints confirmation HTML with the new expense ID.
9. Disconnects and exits.

## 4. Suspicious or Outdated Patterns

- **SQL injection risk**: 2003-era scripts often interpolate variables into SQL instead of using `?` placeholders with `bind_values`.
- **Hardcoded DB credentials**: Likely in plaintext at the top of the script.
- **No `use strict; use warnings;`**: Common omission that masks typos and scoping bugs.
- **CGI model itself is deprecated**: `CGI.pm` was removed from Perl core in 5.22 (2015); modern Perl uses PSGI/Plack.
- **Inline HTML**: No templating layer (Template Toolkit, Mason) means XSS risk if user input is echoed unescaped.
- **Opaque `mangle_approver`**: Un-commented transformation logic is a business-rule landmine—changes to HR systems may have silently broken it.
- **No transaction handling**: Likely auto-commit, so partial failures could leave orphan rows.
- **No logging**: Errors probably go to Apache's `error_log` via `die`, if at all.

## 5. Questions to Ask the Original Author (or Successor Team)

1. What exactly does `mangle_approver` produce—is it an LDAP UID, an Oracle FK, or something else?
2. Are there approver-routing rules based on dollar thresholds or department?
3. Where do the DB credentials come from, and who rotates them?
4. Is this still the system of record, or has data migrated elsewhere?
5. Are there known edge cases (contractors, terminated managers) that `mangle_approver` handles specially?
6. What downstream systems consume these rows—payroll, GL, audit?

# Legacy Perl Expense Report System – Reverse Engineering Analysis

## High-Level Purpose
This is a web-based expense report submission system built as a Perl CGI script that accepts user-submitted expense data, validates it, and stores it in an Oracle database for approval workflows. It serves as the bridge between frontend forms and the company's financial database.

## Key Components & Responsibilities

- **CGI Input Handler**: Parses form submissions from the web browser (employee ID, expense amounts, categories, etc.)
- **DBI Oracle Interface**: Executes SQL queries to read/write expense records and employee data
- **HTML Generator**: Dynamically builds web pages returned to the browser (no templating engine used)
- **`mangle_approver()` Subroutine**: Unknown transformation logic applied to approval chains—likely encrypts, obfuscates, or formats approver identifiers before database storage or email routing
- **Form Validation Logic**: Checks required fields and data types before database commit

## Control Flow / Data Flow Walkthrough

1. User submits expense form via HTTP POST
2. CGI script receives parameters (name, amount, date, manager ID, etc.)
3. Basic validation occurs in-memory (field presence, numeric checks)
4. `mangle_approver()` is called on the manager/approver identifier—output is stored or used in routing
5. SQL INSERT/UPDATE executes against Oracle, storing the expense record
6. HTML response is generated and returned to browser confirming submission
7. Downstream process (cron job or application) likely reads these records and routes approvals

## Suspicious or Outdated Patterns

- **CGI.pm usage**: CGI scripts are vulnerable to header injection and path traversal; modern frameworks (Catalyst, Dancer, Mojolicious) are standard
- **Inline HTML**: String concatenation to build HTML invites XSS vulnerabilities and is unmaintainable
- **No visible input sanitization**: SQL injection risk if `mangle_approver()` or other routines don't parameterize queries (check for `$dbh->prepare()` with placeholders vs. string concatenation)
- **No error handling**: Missing try/catch or die/warn statements likely mask database connection failures
- **Hardcoded credentials**: Oracle connection string may contain passwords in plaintext
- **No CSRF protection**: 2003-era script predates CSRF token patterns
- **No logging/audit trail**: Expense modifications are not tracked for compliance/fraud detection

## Questions to Ask the Original Author (or Reconstruct From Context)

1. **What does `mangle_approver()` actually do?** Does it hash, encrypt, format hierarchical chains, or look up manager escalations?
2. **Are all SQL queries parameterized?** Search for `$dbh->do()` or string interpolation in queries—these are critical vulnerabilities.
3. **How are approvers determined?** Is there a manager chain lookup, or is the approver hardcoded/user-supplied?
4. **What happens after insertion?** Does a separate approval workflow system consume these records?
5. **Are there any downstream dependencies** on the database schema or the specific format of data written by this script?

---

**Recommendation**: Before modifying this code, audit all SQL statements for injection vulnerabilities and document `mangle_approver()` by tracing its output into the database.

## 1) High-Level Purpose

This script is a classic early-2000s internal web app endpoint: it receives an expense report form submission through CGI (Common Gateway Interface, an older way to run web code), validates or reshapes the input, writes data into Oracle through DBI (Perl’s database interface), and returns an HTML response directly to the browser. It was likely built quickly as a self-contained intranet tool, combining request handling, business rules, database access, and presentation in one file because that was common for small internal systems at the time.

## 2) Key Components & Responsibilities

- **CGI request handling**
  - Reads submitted form fields from the web request.
  - Likely distinguishes between “new report,” “update,” or “submit for approval” based on parameters.

- **Input parsing / normalization**
  - Cleans up dates, amounts, employee IDs, cost centers, or descriptions.
  - May convert browser form values into the format Oracle expects.

- **`mangle_approver` subroutine**
  - Probably transforms an approver identifier into the final value stored or used.
  - Common possibilities: mapping username to employee ID, rewriting aliases, applying approval-routing rules, or sanitizing malformed input.

- **DBI + Oracle access**
  - Opens a database connection.
  - Runs `INSERT`, `UPDATE`, or `SELECT` statements for expense header/detail rows and approval metadata.

- **Inline HTML generation**
  - Produces success/error pages directly in Perl with embedded strings.
  - Likely echoes submitted values and displays the resulting report status.

- **Error handling**
  - Probably uses simple `die`, printed error messages, or conditional checks.
  - May expose raw database errors to the user.

## 3) Control Flow / Data Flow Walkthrough

1. **Web server invokes the CGI script** when the user submits the expense report form.
2. **Script reads CGI parameters** such as employee, date, amount, purpose, approver, and line items.
3. **Basic validation occurs**: required fields, numeric totals, date formats, maybe duplicate-checking.
4. **`mangle_approver` runs** on the approver field.
   - Most likely to normalize who the approver should be before saving or routing.
5. **Script connects to Oracle via DBI** using a DSN (data source name), username, and password.
6. **Business data is written/read**:
   - Insert/update report header.
   - Insert expense items.
   - Possibly lookup employee/manager info or approval chain.
7. **Outcome is determined**:
   - Success: report ID generated, status set, confirmation page rendered.
   - Failure: DB or validation error displayed in HTML.
8. **Script prints HTML response** directly and exits.

## 4) Suspicious or Outdated Patterns

- **Perl CGI architecture**: outdated, hard to maintain, and slower than persistent app frameworks.
- **Inline HTML mixed with logic**: makes changes risky and obscures business rules.
- **No comments/docs**: raises risk of hidden assumptions, especially around approvals and finance logic.
- **Possible SQL injection risk** if SQL is built with string concatenation instead of placeholders.
- **Possible weak input validation/escaping**: could allow malformed data or internal XSS (browser script injection).
- **Hard-coded DB credentials or SQL**: common in legacy CGI and a security/ops risk.
- **`mangle_approver` ambiguity**: “mangle” suggests ad hoc data rewriting rather than clear business logic.
- **Likely weak transaction handling**: partial writes could leave header/details out of sync.

## 5) Questions to Ask the Original Author

- What exactly is `mangle_approver` supposed to do, and what business rule does it encode?
- Are approvers users, managers, cost-center owners, or Oracle IDs?
- What are the required validation rules for amounts, receipts, dates, and duplicate submissions?
- Should the database writes be atomic (all succeed or all fail in one transaction)?
- What Oracle tables and stored procedures does this script depend on?
- Are there edge cases for delegates, temporary approvers, or out-of-office routing?
- What errors should users see versus what should only go to logs?
- Is this still the source of truth, or does another system also modify these expense records?

Of course. Here is a reverse-engineering analysis of the legacy Perl CGI script.

### 1. High-Level Purpose
This script is a classic, self-contained web application that serves two functions. First, it generates a web form for employees to enter expense report data. Second, it processes the data submitted through that form, applies some business logic to determine the correct approver, and then saves the final record into an Oracle database.

### 2. Key Components & Responsibilities
*   **CGI (Common Gateway Interface) Script:** The core of the application. This is a program the web server executes to dynamically generate a web page. It's responsible for handling both the initial display of the form and the subsequent processing of submitted data.
*   **Inline HTML Generation:** The script contains raw HTML code within the Perl logic itself, likely using `print` statements. This component is responsible for creating the user interface (the form and confirmation/error pages) that is sent to the user's browser.
*   **DBI (DataBase Interface) Module:** This is the data access layer. It's a standard Perl library used to connect to the Oracle database, prepare, and

### (1) High-Level Purpose
This Perl CGI script is a web form handler for submitting internal expense reports in 2003-era tech. It parses user-submitted form data (e.g., expenses, amounts, approvers), validates and stores it in an Oracle database using DBI (Database Interface module), and generates dynamic HTML responses like success pages or error forms. The mysterious `mangle_approver` subroutine likely custom-transforms approver data for workflow routing.

### (2) Key Components & Responsibilities
- **CGI parsing**: Uses CGI.pm to read POST/GET form fields (e.g., employee ID, expense details).
- **DBI/Oracle layer**: Connects to Oracle DB, executes INSERT/UPDATE/SELECT queries for reports and approvals.
- **HTML generation**: Inline `print` statements mix Perl logic with raw HTML for pages (forms, confirmations).
- **`mangle_approver` subroutine**: Processes approver fields—possibly maps IDs to emails, hierarchies, or obfuscates for security/privacy.
- **Validation/error handling**: Basic checks on inputs before DB ops, with redirects or HTML errors.

### (3) Control Flow / Data Flow Walkthrough
1. Script starts; `use CGI;` initializes, parses params into a hash (e.g., `$cgi->param('expense_amount')`).
2. DB connect: `$dbh = DBI->connect(dsn, user, pass)`; prepares statements.
3. Validate inputs (if/else on amounts, dates); if invalid, print HTML error form and exit.
4. Insert expense data via `$sth->execute(@bind_values)` (placeholders prevent SQL injection?).
5. Call `mangle_approver($approver_id)`—transforms output (e.g., lookup chain to final approver).
6. Update approval table with mangled data.
7. Print HTML success page (table of submitted report) or redirect; `$dbh->disconnect()`.

### (4) Suspicious or Outdated Patterns
- **Inline HTML**: Spaghetti code; prone to XSS (cross-site scripting) without escaping (`CGI::escapeHTML` likely missing).
- **Monolithic 300 lines**: No modularity; hard to test/maintain (anti-pattern: god script).
- **CGI in 2003**: Deprecated for modern frameworks (e.g., no CSRF tokens, session mgmt via cookies only).
- **DBI without placeholders?**: Risky SQL injection if concatenated queries.
- **`mangle_approver`**: Opaque name hints at business hack (e.g., hardcoded org chart mangling); potential data leak.
- **Hardcoded DB creds**: In script = security hole.

### (5) Questions to Ask the Original Author
- What exactly does `mangle_approver` compute (e.g., approver lookup logic, edge cases)?
- Any implicit business rules for expense validation or approval chains?
- Were DB credentials ever rotated; any encryption used?
- Known bugs or unhandled workflows (e.g., multi-level approvals)?

(Word count: 378)