Actual Budget addTransaction.sh SQL Injection via Shell Variable

Affected Versions

Introduction

A critical SQL injection vulnerability reached production in Actual Budget's transaction automation infrastructure, where shell variables flowed directly into SQL INSERT statements through heredoc blocks. The addTransaction.sh script—used to automate budget entry—constructed database queries by interpolating variables like $PAYEE_UUID, $ACCOUNT_ID, $CATEGORY_ID, and $AMOUNT into raw SQL strings. While the developers recognized the risk of single-quote injection and implemented basic escaping, they missed a fundamental threat: numeric fields require no quotes to inject.

This vulnerability illustrates a common pitfall in shell-to-database bridges. Developers often assume that escaping single quotes solves SQL injection, but when variables interpolate into unquoted numeric contexts—or when attackers find encoding bypasses—the protection collapses.

The Vulnerability Explained

The vulnerable code constructed SQL by embedding shell variables directly into heredoc-delimited SQL blocks:

# Vulnerable pattern: variables interpolated into SQL
sql="INSERT INTO transactions (account, amount, payee, notes) 
     VALUES ($ACCOUNT_ID, $AMOUNT, '$PAYEE_NAME', '$NOTES');"

The original defense was minimal—single quotes were doubled through ad-hoc escaping. But this left critical gaps:

The numeric injection vector: Variables like $AMOUNT and $DATE were interpolated without surrounding quotes. An attacker could set AMOUNT="1; DELETE FROM transactions; --" and execute arbitrary SQL without needing a single quote to break out of string context.

The incomplete string protection: While $PAYEE_NAME and $NOTES had single quotes escaped, the escaping occurred inconsistently and failed to address other SQL metacharacters or encoding issues.

Consider this exploitation path through the transaction automation:

1. A user-controlled input sets AMOUNT="0,0,0,0,0,0,0,0)--"
2. The shell variable interpolates into: VALUES (3, 0,0,0,0,0,0,0,0)--, 5, 'Payee', 'Note')
3. SQLite parses this as valid SQL with a commented suffix, injecting unintended values

The real-world impact extends beyond data corruption. An attacker with control over automation inputs could:
- Exfiltrate budget data through boolean-based blind SQL injection
- Modify transaction amounts to hide fraudulent transfers
- Delete or corrupt entire budget databases

The Fix

The remediation introduced a defense-in-depth strategy with two specific technical controls.

Control 1: Consistent Single-Quote Escaping

A dedicated escape_sql() function centralizes string sanitization:

# Function to escape single quotes for SQL by doubling them
escape_sql() {
    echo "$1" | sed "s/'/''/g"
}

# Escape all string values that are interpolated into SQL statements
ACCOUNT_ID=$(escape_sql "$ACCOUNT_ID")
CATEGORY_ID=$(escape_sql "$CATEGORY_ID")
PAYEE_ID=$(escape_sql "$PAYEE_ID")
TRANSFER_ACCT=$(escape_sql "$TRANSFER_ACCT")

This ensures all string-typed variables receive consistent escaping before interpolation.

Control 2: Numeric Validation

For fields that must be numeric and interpolate without quotes, strict regex validation rejects non-numeric input:

# Reject non-numeric AMOUNT/DATE since they are interpolated unquoted into SQL
if ! [[ "$AMOUNT" =~ ^-?[0-9]+$ ]]; then
    echo "ERROR: Invalid amount"
    exit 1
fi
if [ -n "$DATE" ] && ! [[ "$DATE" =~ ^-?[0-9]+$ ]]; then
    echo "ERROR: Invalid date"
    exit 1
fi

The ^-?[0-9]+$ pattern enforces:
- ^ start of string
- -? optional negative sign
- [0-9]+ one or more digits
- $ end of string

This prevents injection through numeric fields by rejecting any input containing non-digit characters, including SQL metacharacters like semicolons, dashes, and comments.

Key Takeaways

• Heredoc SQL construction in shell scripts is inherently risky — the <<EOF pattern with variable expansion creates injection surfaces that are difficult to audit and easy to miss in code review.

• Single-quote escaping is insufficient for SQL injection defense — numeric contexts, identifier contexts, and certain Unicode encodings can bypass quote-based escaping entirely.

• Type validation must precede interpolation — the fix validates $AMOUNT and $DATE as integers before they reach SQL, demonstrating that input validation belongs at the trust boundary, not the database layer.

• Shell scripts bridging to SQL need parameterized query alternatives — where SQLite's CLI doesn't support true parameterization, rigorous whitelisting and escaping must substitute.

• Automation scripts often inherit trust boundaries poorly — scripts designed for "internal" use or automation frequently receive insufficient security scrutiny, yet they process the same untrusted inputs as production APIs.

How Orbis AppSec Detected This

Source: Shell environment variables passed to the transaction automation script, including PAYEE_UUID, NEW_PAYEE_NAME, ACCOUNT_ID, CATEGORY_ID, PAYEE_ID, NOTES, TRANSFER_UUID, AMOUNT, and DATE.

Sink: SQL INSERT statement construction via heredoc blocks with direct variable interpolation, where untrusted values reached SQLite without parameterization.

Missing control: No systematic escaping of string values and no validation that numeric fields contained only digits before unquoted interpolation.

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

Fix: Introduced escape_sql() function for consistent single-quote doubling and regex validation (^-?[0-9]+$) to reject non-numeric values before unquoted SQL interpolation.

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

This vulnerability in Actual Budget's transaction automation demonstrates how shell scripts—often overlooked in security assessments—can become critical SQL injection vectors when bridging user input to databases. The fix's dual approach of consistent string escaping and strict numeric validation provides a practical defense where true parameterized queries aren't available. For developers maintaining similar automation, the lesson is clear: never interpolate shell variables into SQL without both type validation and proper escaping, and treat heredoc SQL construction as a code smell requiring exceptional justification.