Case Study 2: SecureFirst's Dynamic SQL Security Audit
Background
SecureFirst Insurance processes 8 million claims per year across auto, home, life, and commercial lines. Their mainframe application portfolio includes 420 COBOL programs, of which 38 use dynamic SQL in some form. Yuki Tanaka, the application security architect, had been requesting a comprehensive dynamic SQL audit for two years. Management approved it only after a competitor suffered a data breach traced to a SQL injection vulnerability in a mainframe application — a breach that cost $47 million in regulatory fines and remediation.
Carlos Mendez, senior DB2 programmer, was assigned to work with Yuki on the audit. Their mandate: review every dynamic SQL usage in the portfolio, classify the risk level, and remediate all critical and high-risk findings within 90 days.
The Audit Methodology
Yuki and Carlos developed a five-layer assessment framework aligned with the defense-in-depth strategy from this chapter:
Layer 1: Parameter Marker Usage
Check: Does every dynamic SQL statement use parameter markers for all user-supplied or externally-sourced values? Method: Grep the COBOL source for STRING statements that construct SQL, then trace whether the assembled SQL contains parameter markers or concatenated values.
Layer 2: Input Validation
Check: Is every value that influences the dynamic SQL (even parameter marker values) validated against expected formats, ranges, or whitelists? Method: Trace each host variable used in dynamic SQL back to its source. If it comes from user input (CICS screen, MQ message, file input), verify validation exists before the SQL is constructed.
Layer 3: DYNAMICRULES Setting
Check: Are dynamic SQL programs bound with DYNAMICRULES(BIND) to restrict authorization scope? Method: Query the DB2 catalog for the DYNAMICRULES setting of each plan/package containing dynamic SQL.
Layer 4: Audit Logging
Check: Is every dynamic SQL execution logged with the SQL text, parameters, user ID, and timestamp? Method: Review each program for audit logging before and after PREPARE/EXECUTE. Check that audit records are tamper-resistant (written to a DB2 table with restricted UPDATE/DELETE authority).
Layer 5: Prepared Statement Caching
Check: Are frequently-executed dynamic SQL statements prepared once and executed many times, rather than re-prepared with potentially modified SQL text? Method: Review the control flow around PREPARE statements. If PREPARE is inside a loop, verify that the SQL text is constant across iterations.
Findings
After three weeks of review, Yuki and Carlos classified the 38 programs into risk categories:
Critical Risk (Immediate Action Required): 3 Programs
Program RPTGEN02 — Ad Hoc Report Generator
The worst offender. RPTGEN02 accepted a complete WHERE clause from a CICS screen, stored it in a VSAM file, and later used it in batch processing:
* RPTGEN02 - The nightmare
01 WS-USER-WHERE PIC X(500).
01 WS-SQL-STMT PIC X(2000).
EXEC CICS RECEIVE MAP('RPTMAP') MAPSET('RPTSET')
INTO(WS-REPORT-REQUEST)
END-EXEC
MOVE WS-RPT-WHERE-CLAUSE TO WS-USER-WHERE
STRING 'SELECT CLAIM_ID, CLAIMANT_NAME, AMOUNT, '
'STATUS, ADJUSTER_ID '
'FROM CLAIMS WHERE '
WS-USER-WHERE
' ORDER BY CLAIM_ID'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
EXEC SQL PREPARE RPT-STMT FROM :WS-SQL-STMT END-EXEC
EXEC SQL DECLARE RPT-CUR CURSOR FOR RPT-STMT END-EXEC
EXEC SQL OPEN RPT-CUR END-EXEC
An authorized user could type 1=1 in the WHERE clause field and retrieve all 8 million claims. Worse, they could type 1=1 UNION SELECT AUTHID, GRANTEE, '', '', '' FROM SYSIBM.SYSTABAUTH -- and enumerate database security grants. The program was bound with DYNAMICRULES(RUN), meaning it executed with the user's full authority — including access to catalog tables.
Program CLMADJ07 — Claims Adjustment Utility
Used dynamic SQL to update claim amounts based on adjustment rules stored in a configuration table. The rule table contained SQL fragments that were concatenated into UPDATE statements:
* CLMADJ07 - Configuration-driven SQL fragments
EXEC SQL
SELECT ADJUSTMENT_SQL
INTO :WS-ADJ-SQL-FRAGMENT
FROM ADJUSTMENT_RULES
WHERE RULE_ID = :WS-CURRENT-RULE
END-EXEC
STRING 'UPDATE CLAIMS SET '
WS-ADJ-SQL-FRAGMENT
' WHERE CLAIM_ID = ?'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
The ADJUSTMENT_RULES table was maintained by business analysts through an ISPF panel with no input validation. A malicious or careless entry like AMOUNT = 999999, STATUS = 'PAID' -- could bypass the WHERE clause entirely if the double-dash was placed before the WHERE CLAIM_ID clause.
Program POLSRCH — Policy Search
Constructed dynamic SQL for a multi-criteria search screen. Most predicates used parameter markers, but the sort column was concatenated directly:
* POLSRCH - Almost right, but not quite
STRING WS-BASE-SQL DELIMITED SPACES
' ORDER BY ' WS-SORT-COLUMN
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
The WS-SORT-COLUMN came directly from the CICS screen with no validation. An attacker could enter 1; DELETE FROM POLICIES; -- in the sort field. While DB2 for z/OS doesn't support multiple statements in a single PREPARE (unlike some distributed databases), the unvalidated input could still cause unexpected errors or information leakage through crafted ORDER BY expressions.
High Risk: 8 Programs
These programs used parameter markers for values but had gaps in input validation, missing audit trails, or DYNAMICRULES(RUN) instead of DYNAMICRULES(BIND). Examples:
- CLMRPT01-04 (4 report programs): Used parameter markers correctly but had no audit logging and were bound with DYNAMICRULES(RUN).
- POLMAINT: Validated some inputs but not all — account type was validated against a whitelist, but the date range was not checked for reasonableness (a date of '0001-01-01' to '9999-12-31' would scan the entire table).
- FNDRPT01-03 (3 financial reports): Used parameter markers and had audit logging, but the audit log table had no access restrictions — any user could DELETE from the audit trail.
Medium Risk: 12 Programs
Used parameter markers, had basic input validation, but lacked complete audit trails or used DYNAMICRULES(RUN). No immediate exploitation path, but defense-in-depth was incomplete.
Low Risk: 15 Programs
Properly parameterized, validated, bound with DYNAMICRULES(BIND), and had audit logging. These were mostly newer programs written after SecureFirst adopted coding standards in 2018.
Remediation
Phase 1: Critical Fixes (Weeks 1-3)
RPTGEN02 was completely rewritten. Carlos replaced the free-form WHERE clause with a structured search interface:
* New RPTGEN02 - Structured search with parameter markers
01 WS-SEARCH-CRITERIA.
05 WS-CLAIM-STATUS PIC X(10).
05 WS-DATE-FROM PIC X(10).
05 WS-DATE-TO PIC X(10).
05 WS-MIN-AMOUNT PIC S9(13)V99 COMP-3.
05 WS-MAX-AMOUNT PIC S9(13)V99 COMP-3.
05 WS-ADJUSTER-ID PIC X(8).
05 WS-LINE-OF-BIZ PIC X(4).
05 WS-REGION-CODE PIC X(10).
01 WS-VALIDATED-FLAGS.
05 WS-HAS-STATUS PIC 9 VALUE 0.
05 WS-HAS-DATE-RANGE PIC 9 VALUE 0.
05 WS-HAS-AMOUNT-RANGE PIC 9 VALUE 0.
05 WS-HAS-ADJUSTER PIC 9 VALUE 0.
05 WS-HAS-LOB PIC 9 VALUE 0.
05 WS-HAS-REGION PIC 9 VALUE 0.
2000-BUILD-SAFE-SQL.
MOVE 'SELECT CLAIM_ID, CLAIMANT_NAME, AMOUNT, '
TO WS-SQL-STMT
STRING WS-SQL-STMT DELIMITED SPACES
'STATUS, ADJUSTER_ID '
'FROM CLAIMS WHERE 1=1'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
MOVE 0 TO WS-PARAM-COUNT
IF WS-HAS-STATUS = 1
* Validate against whitelist
EVALUATE WS-CLAIM-STATUS
WHEN 'OPEN'
WHEN 'CLOSED'
WHEN 'PENDING'
WHEN 'DENIED'
WHEN 'APPEALED'
STRING WS-SQL-STMT DELIMITED SPACES
' AND STATUS = ?'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
ADD 1 TO WS-PARAM-COUNT
WHEN OTHER
MOVE 'INVALID STATUS' TO WS-ERROR-MSG
SET WS-INPUT-ERROR TO TRUE
END-EVALUATE
END-IF
IF WS-HAS-DATE-RANGE = 1
* Validate date format and reasonableness
PERFORM 2100-VALIDATE-DATES
IF NOT WS-INPUT-ERROR
STRING WS-SQL-STMT DELIMITED SPACES
' AND CLAIM_DATE BETWEEN ? AND ?'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
ADD 2 TO WS-PARAM-COUNT
END-IF
END-IF
IF WS-HAS-AMOUNT-RANGE = 1
STRING WS-SQL-STMT DELIMITED SPACES
' AND AMOUNT BETWEEN ? AND ?'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING
ADD 2 TO WS-PARAM-COUNT
END-IF
* Sort column whitelist (NOT concatenated from user input)
EVALUATE WS-SORT-OPTION
WHEN 'C'
STRING WS-SQL-STMT DELIMITED SPACES
' ORDER BY CLAIM_ID'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN 'D'
STRING WS-SQL-STMT DELIMITED SPACES
' ORDER BY CLAIM_DATE DESC'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN 'A'
STRING WS-SQL-STMT DELIMITED SPACES
' ORDER BY AMOUNT DESC'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN OTHER
STRING WS-SQL-STMT DELIMITED SPACES
' ORDER BY CLAIM_ID'
DELIMITED SIZE INTO WS-SQL-STMT
END-EVALUATE
STRING WS-SQL-STMT DELIMITED SPACES
' FETCH FIRST 5000 ROWS ONLY'
DELIMITED SIZE INTO WS-SQL-STMT
END-STRING.
Key changes: - Free-form WHERE clause replaced with structured, validated fields - All values use parameter markers - Sort column uses a whitelist EVALUATE, not user input - Result set limited to 5,000 rows - Bound with DYNAMICRULES(BIND)
CLMADJ07 was redesigned to eliminate SQL fragments in the configuration table. Instead, adjustment rules were encoded as structured parameters (column name from whitelist, operation from whitelist, value as parameter marker):
01 WS-RULE-STRUCTURE.
05 WS-RULE-COLUMN PIC X(30).
05 WS-RULE-OPERATION PIC X(10).
05 WS-RULE-VALUE PIC X(50).
2500-BUILD-ADJUSTMENT-SQL.
* Column whitelist
EVALUATE WS-RULE-COLUMN
WHEN 'AMOUNT'
WHEN 'STATUS'
WHEN 'REVIEW_DATE'
WHEN 'ADJUSTER_NOTE'
CONTINUE
WHEN OTHER
MOVE 'INVALID RULE COLUMN' TO WS-ERROR
PERFORM 8000-ERROR-EXIT
END-EVALUATE
* Operation whitelist
EVALUATE WS-RULE-OPERATION
WHEN 'SET'
STRING 'UPDATE CLAIMS SET '
WS-RULE-COLUMN ' = ? '
'WHERE CLAIM_ID = ?'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN 'ADD'
STRING 'UPDATE CLAIMS SET '
WS-RULE-COLUMN ' = '
WS-RULE-COLUMN ' + ? '
'WHERE CLAIM_ID = ?'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN 'MULTIPLY'
STRING 'UPDATE CLAIMS SET '
WS-RULE-COLUMN ' = '
WS-RULE-COLUMN ' * ? '
'WHERE CLAIM_ID = ?'
DELIMITED SIZE INTO WS-SQL-STMT
WHEN OTHER
MOVE 'INVALID RULE OPERATION' TO WS-ERROR
PERFORM 8000-ERROR-EXIT
END-EVALUATE.
POLSRCH received the simplest fix — the sort column was changed from user input to a whitelist:
* Before: WS-SORT-COLUMN from screen, unvalidated
* After: Sort option from screen, whitelisted to column name
EVALUATE WS-SORT-OPTION
WHEN '1' MOVE 'POLICY_NUM' TO WS-SAFE-SORT
WHEN '2' MOVE 'CUSTOMER_NAME' TO WS-SAFE-SORT
WHEN '3' MOVE 'EFFECTIVE_DATE DESC' TO WS-SAFE-SORT
WHEN '4' MOVE 'PREMIUM DESC' TO WS-SAFE-SORT
WHEN OTHER MOVE 'POLICY_NUM' TO WS-SAFE-SORT
END-EVALUATE
Phase 2: High-Risk Remediation (Weeks 4-8)
All 8 high-risk programs were updated: - DYNAMICRULES changed from RUN to BIND on all plan rebinds - Audit logging added using a shared copybook (SQLAUDIT) that Carlos wrote:
* SQLAUDIT copybook - standardized audit logging
01 WS-AUDIT-RECORD.
05 WS-AUDIT-TIMESTAMP PIC X(26).
05 WS-AUDIT-PROGRAM PIC X(8).
05 WS-AUDIT-USER PIC X(8).
05 WS-AUDIT-SQL-TEXT PIC X(2000).
05 WS-AUDIT-PARAMS PIC X(500).
05 WS-AUDIT-SQLCODE PIC S9(9) COMP.
05 WS-AUDIT-ELAPSED PIC S9(9) COMP.
9100-LOG-SQL-AUDIT.
MOVE FUNCTION CURRENT-DATE TO WS-AUDIT-TIMESTAMP
EXEC SQL
INSERT INTO SQL_AUDIT_LOG
(AUDIT_TS, PROGRAM_ID, USER_ID,
SQL_TEXT, PARAM_VALUES, RESULT_SQLCODE,
ELAPSED_MICROSEC)
VALUES
(:WS-AUDIT-TIMESTAMP, :WS-AUDIT-PROGRAM,
:WS-AUDIT-USER, :WS-AUDIT-SQL-TEXT,
:WS-AUDIT-PARAMS, :WS-AUDIT-SQLCODE,
:WS-AUDIT-ELAPSED)
END-EXEC.
The SQL_AUDIT_LOG table was secured with restricted access:
CREATE TABLE SQL_AUDIT_LOG (
AUDIT_ID INTEGER GENERATED ALWAYS AS IDENTITY,
AUDIT_TS TIMESTAMP NOT NULL WITH DEFAULT,
PROGRAM_ID CHAR(8) NOT NULL,
USER_ID CHAR(8) NOT NULL,
SQL_TEXT VARCHAR(2000) NOT NULL,
PARAM_VALUES VARCHAR(500),
RESULT_SQLCODE INTEGER,
ELAPSED_MICROSEC INTEGER,
PRIMARY KEY (AUDIT_ID)
) IN AUDITDB.AUDIT_TS;
-- Only the audit service ID can INSERT
GRANT INSERT ON SQL_AUDIT_LOG TO AUDITSVC;
-- Only the security team can SELECT
GRANT SELECT ON SQL_AUDIT_LOG TO ROLE SECURITY_TEAM;
-- Nobody can UPDATE or DELETE
-- (no GRANT means no access)
Phase 3: Medium-Risk Hardening (Weeks 9-12)
The 12 medium-risk programs received audit logging (via the SQLAUDIT copybook) and DYNAMICRULES(BIND) rebinds. Input validation was enhanced where gaps existed.
Results
After 90 days:
| Metric | Before | After |
|---|---|---|
| Critical risk programs | 3 | 0 |
| High risk programs | 8 | 0 |
| Medium risk programs | 12 | 0 |
| Programs with audit logging | 15 | 38 |
| Programs with DYNAMICRULES(BIND) | 22 | 38 |
| Programs with input validation | 27 | 38 |
| SQL injection vectors | 7 confirmed | 0 |
The external auditors who reviewed the remediation work gave SecureFirst a clean finding — the first time in three audit cycles.
The Cost of Delay
Yuki calculated the risk exposure during the two years before the audit was approved. During that time:
- RPTGEN02 was used by 45 authorized users, any of whom could have extracted the entire claims database
- CLMADJ07's configuration table was modified 230 times by business analysts, any modification of which could have included malicious SQL
- POLSRCH processed approximately 2.4 million searches, each with an unvalidated sort field
No breach was detected during this period, but as Yuki noted in her final report: "The absence of evidence is not evidence of absence. We had no capability to detect exploitation because we had no audit logging on these programs. We were lucky, not secure."
Discussion Questions
-
RPTGEN02 was rewritten to use structured search fields instead of free-form SQL. Some power users complained about losing the ability to write custom queries. How would you balance security with user flexibility? Is there a safe way to support advanced queries?
-
The CLMADJ07 remediation replaced SQL fragments with structured rules. What happens when a business analyst needs an adjustment operation that doesn't fit the SET/ADD/MULTIPLY pattern? How do you extend the whitelist without recompiling?
-
Carlos created a shared SQLAUDIT copybook for standardized audit logging. What are the risks of this approach? What happens if the audit INSERT fails? Should audit failures prevent the business transaction from completing?
-
The SQL_AUDIT_LOG table restricts UPDATE and DELETE access. But the table will grow indefinitely. Design a purge strategy that maintains the security property (no one can delete recent audit records) while managing space.
-
Yuki's report noted that the absence of evidence wasn't evidence of absence. If you were building a detection capability for SQL injection attempts against dynamic SQL programs, what would you monitor? What patterns in the audit log would indicate an attack?