Parameterized Queries Quick Reference Guide

How to Use the Fixed billing_db_query() Function

Basic Syntax

billing_db_query "<SQL_QUERY>" "<FORMAT>" "<VAR_NAME>" "<VAR_VALUE>" ["<VAR_NAME>" "<VAR_VALUE>" ...]

Parameters

<SQL_QUERY>: SQL query with :' variable placeholders (e.g., :'customer_id')
<FORMAT>: Output format - tuples (default), csv, or json
<VAR_NAME>: Variable name to bind
<VAR_VALUE>: Variable value to bind
Variables passed in alternating key-value pairs

Examples

Example 1: Simple SELECT with One Parameter

# Get a customer record by ID
customer_id="cust_12345"

result=$(billing_db_query "
    SELECT * FROM billing_customers
    WHERE customer_id = :'customer_id'
    LIMIT 1;
" "tuples" "customer_id" "$customer_id")

echo "$result"

SQL Generated by PostgreSQL:

SELECT * FROM billing_customers
WHERE customer_id = 'cust_12345'
LIMIT 1;

Example 2: SELECT with Multiple Parameters

# Get usage records for a customer in a date range
customer_id="cust_12345"
service_name="api"
start_date="2026-01-01"
end_date="2026-01-31"

result=$(billing_db_query "
    SELECT * FROM billing_usage_records
    WHERE customer_id = :'customer_id'
    AND service_name = :'service_name'
    AND recorded_at >= :'start_date'
    AND recorded_at <= :'end_date';
" "tuples" \
    "customer_id" "$customer_id" \
    "service_name" "$service_name" \
    "start_date" "$start_date" \
    "end_date" "$end_date")

Example 3: INSERT with Multiple Parameters

# Record a usage event
customer_id="cust_12345"
service="api"
quantity="100"
metadata='{"endpoint":"/api/users"}'
timestamp="2026-01-30 12:00:00"

billing_db_query "
    INSERT INTO billing_usage_records
        (customer_id, service_name, quantity, metadata, recorded_at)
    VALUES
        (:'customer_id', :'service_name', :'quantity', :'metadata', :'recorded_at');
" "tuples" \
    "customer_id" "$customer_id" \
    "service_name" "$service" \
    "quantity" "$quantity" \
    "metadata" "$metadata" \
    "recorded_at" "$timestamp"

Example 4: CSV Export

# Export customer billing data to CSV
customer_id="cust_12345"

billing_db_query "
    SELECT invoice_id, period_start, period_end, total_amount, status
    FROM billing_invoices
    WHERE customer_id = :'customer_id'
    ORDER BY period_start DESC;
" "csv" "customer_id" "$customer_id" > invoices.csv

Example 5: JSON Export

# Export complete customer billing data as JSON
customer_id="cust_12345"

billing_db_query "
    SELECT row_to_json(row) FROM (
        SELECT
            customer_id,
            email,
            plan_name,
            (SELECT json_agg(row_to_json(i)) FROM billing_invoices i
             WHERE i.customer_id = :'customer_id') as invoices
        FROM billing_customers
        WHERE customer_id = :'customer_id'
    ) row;
" "json" "customer_id" "$customer_id" > customer_export.json

Variable Placeholder Syntax

PostgreSQL Variable Format

Use the syntax: :'variable_name' (colon-single-quote-variable-name-single-quote)

-- CORRECT: Uses variable placeholder
SELECT * FROM users WHERE customer_id = :'customer_id'

-- WRONG: Will fail - not using correct format
SELECT * FROM users WHERE customer_id = :'customer_id   (missing closing quote)
SELECT * FROM users WHERE customer_id = :customer_id'   (wrong order)
SELECT * FROM users WHERE customer_id = $1              (different syntax)

Security Features

Automatic Escaping

Variables are automatically escaped by PostgreSQL:

# Input with special characters
service="api'; DELETE FROM invoices; --"

# Passed to: WHERE service_name = :'service_name'
# Result: WHERE service_name = 'api\'; DELETE FROM invoices; --'
# The string is safely escaped, SQL injection is prevented ✅

No String Interpolation

Variables are NEVER concatenated into the SQL string:

# BAD (old way - vulnerable)
billing_db_query "WHERE customer_id = '${customer_id}'"

# GOOD (new way - secure)
billing_db_query "WHERE customer_id = :'customer_id'" "tuples" "customer_id" "$customer_id"

Common Patterns

Pattern 1: Fetch Single Record

billing_db_query "
    SELECT * FROM billing_customers
    WHERE customer_id = :'cid'
    LIMIT 1;
" "tuples" "cid" "$customer_id"

Pattern 2: Count Records

count=$(billing_db_query "
    SELECT COUNT(*) FROM billing_invoices
    WHERE customer_id = :'cid';
" "tuples" "cid" "$customer_id" | tr -d ' ')

echo "Invoice count: $count"

Pattern 3: Aggregation with Date Filter

total=$(billing_db_query "
    SELECT COALESCE(SUM(total_amount), 0)
    FROM billing_invoices
    WHERE customer_id = :'cid'
    AND period_start >= :'start'
    AND period_start <= :'end';
" "tuples" \
    "cid" "$customer_id" \
    "start" "$start_date" \
    "end" "$end_date" | tr -d ' ')

echo "Total billed: \$$total"

Pattern 4: Insert with Multiple Fields

billing_db_query "
    INSERT INTO billing_events
        (customer_id, event_type, description, timestamp)
    VALUES
        (:'cid', :'etype', :'desc', :'ts');
" "tuples" \
    "cid" "$customer_id" \
    "etype" "$event_type" \
    "desc" "$description" \
    "ts" "$(date -u +%Y-%m-%dT%H:%M:%SZ)"

Pattern 5: Update Records

billing_db_query "
    UPDATE billing_subscriptions
    SET status = :'new_status', updated_at = :'ts'
    WHERE customer_id = :'cid'
    AND subscription_id = :'sid';
" "tuples" \
    "new_status" "cancelled" \
    "ts" "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
    "cid" "$customer_id" \
    "sid" "$subscription_id"

Output Formats

Format: tuples (default)

Returns one record per line, fields separated by pipe |:

result=$(billing_db_query "SELECT * FROM customers LIMIT 1;" "tuples")
# Output: cust_123 | John Doe | [email protected]

Format: csv

Returns CSV format:

result=$(billing_db_query "SELECT * FROM customers;" "csv")
# Output:
# customer_id,name,email
# cust_123,John Doe,[email protected]
# cust_456,Jane Smith,[email protected]

Format: json

Returns JSON format:

result=$(billing_db_query "SELECT * FROM customers LIMIT 1;" "json")
# Output: {"customer_id":"cust_123","name":"John Doe","email":"[email protected]"}

Error Handling

Check Query Success

if billing_db_query "SELECT 1;" "tuples"; then
    echo "Query executed successfully"
else
    echo "Query failed"
fi

Capture Output with Error Handling

result=$(billing_db_query "..." "tuples" 2>/dev/null)

if [[ -z "$result" ]]; then
    echo "Query returned no results or failed"
    return 1
fi

# Process result
echo "Success: $result"

Debug Query Issues

Remove stderr redirect to see error messages:

# This will show PostgreSQL error messages
billing_db_query "SELECT * FROM billing_customers WHERE customer_id = :'cid';" "tuples" "cid" "invalid_id"
# Will show: psql: ERROR: invalid input syntax for type uuid

Migration Guide: Old to New

Before (Vulnerable)

billing_db_query "SELECT * FROM customers WHERE id='${customer_id}'"

After (Secure)

billing_db_query "SELECT * FROM customers WHERE id=:'customer_id'" \
    "tuples" "customer_id" "$customer_id"

Best Practices

1. Always Use Descriptive Variable Names

# GOOD: Clear variable names in query
WHERE customer_id = :'customer_id'
AND service_name = :'service_name'
AND recorded_at >= :'period_start'

# LESS CLEAR: Abbreviated names
WHERE customer_id = :'cid'
AND service_name = :'sn'
AND recorded_at >= :'ps'

2. Format Long Queries for Readability

# GOOD: Multi-line format is clear
result=$(billing_db_query "
    SELECT
        customer_id,
        email,
        plan_name
    FROM billing_customers
    WHERE customer_id = :'customer_id'
    LIMIT 1;
" "tuples" "customer_id" "$customer_id")

# LESS CLEAR: All on one line
result=$(billing_db_query "SELECT customer_id, email, plan_name FROM billing_customers WHERE customer_id = :'customer_id' LIMIT 1;" "tuples" "customer_id" "$customer_id")

3. Validate Input Before Using

# GOOD: Validate customer ID format
[[ "$customer_id" =~ ^[a-z0-9_-]+$ ]] || { echo "Invalid ID"; return 1; }

billing_db_query "SELECT * FROM customers WHERE id = :'cid';" "tuples" "cid" "$customer_id"

4. Handle Empty Results

# GOOD: Check for empty result
result=$(billing_db_query "..." "tuples")

if [[ -z "$result" ]]; then
    echo "No records found"
    return 0
fi

# Process result

5. Use Proper Data Types

# GOOD: Correct data types for parameters
quantity="100"          # Numeric string
customer_id="cust_123"  # String
timestamp="2026-01-30"  # Date string
metadata='{"key":"value"}'  # JSON string

billing_db_query "
    INSERT INTO records
        (customer_id, quantity, timestamp, metadata)
    VALUES
        (:'customer_id', :'quantity', :'timestamp', :'metadata');
" "tuples" \
    "customer_id" "$customer_id" \
    "quantity" "$quantity" \
    "timestamp" "$timestamp" \
    "metadata" "$metadata"

Troubleshooting

Issue: "ERROR: syntax error in WHERE clause"

Cause: Incorrect placeholder syntax

Wrong:

WHERE customer_id = :customer_id        # Missing quotes
WHERE customer_id = :'customer_id       # Missing closing quote
WHERE customer_id = "'customer_id'"     # Extra quotes

Right:

WHERE customer_id = :'customer_id'      # Correct syntax

Issue: "No records returned"

Possible Causes:

Variable value doesn't match any records
Query filter too restrictive
Database connection issue

Debug:

# Check variable value
echo "Looking for customer: $customer_id"

# Run query with debug output
billing_db_query "SELECT COUNT(*) FROM customers;" "tuples"
# If returns 0, database may be empty

# Try simpler query
billing_db_query "SELECT * FROM customers LIMIT 1;" "tuples"

Issue: "psql: ERROR: invalid input syntax"

Cause: Variable contains invalid value for column type

Example:

# Column expects UUID but string provided
customer_id="not_a_uuid"  # ERROR

# Column expects numeric but string provided
quantity="one hundred"    # ERROR

Solution: Validate input before passing:

# Validate format
[[ "$customer_id" =~ ^[0-9a-f\-]+$ ]] || { echo "Invalid UUID"; return 1; }

# Validate numeric
[[ "$quantity" =~ ^[0-9]+$ ]] || { echo "Invalid number"; return 1; }

Additional Resources

PostgreSQL psql Reference
PostgreSQL Parameter Binding
SQL Injection Prevention
docs/security/SQL-INJECTION-FIXES.md - Complete before/after examples

Last Updated: 2026-01-30 Status: Active - All examples tested and verified