The Known SQL Injection and the Six-Week API
Meridian Mutual is a regional insurer with 1,400 staff and a claims portal written in 2011 that holds 340,000 policyholder records. An external penetration test confirmed a blind SQL injection in the claim-search function that returns full database contents from a single crafted parameter, using only a valid low-privilege agent login. The portal vendor quotes USD 180,000 and seven months to rebuild the query layer. A web application firewall with tuned virtual patches costs USD 25,000 and takes three weeks; the vendor claims it blocks roughly 90 percent of known exploitation patterns. The risk team's actuarial estimate is a 12 percent annual probability of a breach event with a USD 4.5 million loss, and the state insurance regulator requires breach notification within 72 hours. Cyber insurance carries a USD 2 million limit, a USD 250,000 retention, and a clause excluding losses from failure to maintain commercially reasonable security controls. A business unit has already committed to launching a broker-facing API in six weeks.
1. Recommend a risk treatment for the SQL injection finding and justify it both quantitatively and qualitatively. Explain why acceptance and transfer are not sufficient on their own.
Read the worked answer
Start with the numbers because they settle the argument. ALE = 0.12 x USD 4.5M = USD 540,000 per year. The WAF costs USD 25,000 and, taking the vendor's 90 percent claim at a heavy discount (say 70 percent), still removes roughly USD 378,000 of expected annual loss. That ratio is why doing nothing fails.
The treatment is mitigate, in two layers. The USD 25,000 WAF with virtual patching is the time-boxed interim control; the USD 180,000 rebuild is the permanent fix and should start the same week, not after the WAF is tuned. Do not let the WAF become the answer.
Acceptance alone is indefensible. A USD 540,000 ALE against a regulator with a 72-hour clock is a loss the insurer has implicitly said it will not carry, and acceptance without a remediation plan looks like neglect in any later litigation.
Transfer does not work either. The policy excludes failure to maintain commercially reasonable controls, and a known, externally confirmed, un-remediated SQL injection is precisely the fact pattern that clause targets. Insurance covers the residual, it does not substitute for the fix. Confirm this reading with the broker in writing.
Then write the exception: named business owner (not the CISO), expiry aligned to a rebuild milestone, monthly review, and the monitoring evidence that justifies calling the interim control effective.
2. The business unit refuses to move the broker API launch date. What conditions, if any, would make launching defensible?
Read the worked answer
The instinct is to treat the API as a separate project. It is not. If the API calls the same database with the same credentials, a six-week launch turns an authenticated portal into a broader attack surface and moves the finding into a probability band nobody has priced.
Three conditions make a launch defensible:
- No shared code path. The API gets its own service and its own read-mostly view, with a least-privilege database login that cannot reach the policyholder table the injection targets. Verify this by attempting the pen-test payloads against the API's own credentials.
- The WAF is in blocking mode, not monitoring, with injection rules regression-tested against the exact payloads from the test before external traffic is allowed. A WAF tuned for a form post does not automatically cover JSON bodies or path parameters.
- An independent test of the API itself before general availability.
If any condition cannot be met, the honest recommendation is a limited pilot with named broker partners rather than a general launch. The decision belongs to the business owner who owns the revenue commitment, documented in the risk register, with the security team's dissent recorded if it is overruled.
Also check the contract: a broker API may impose availability or data-handling obligations that interact with the 72-hour notification duty.
3. Describe what the residual risk record for this finding must contain and who is entitled to accept it.
Read the worked answer
Residual risk is what remains after the controls actually work, and the record has to survive an examiner, an auditor, and a lawyer two years later. It should contain:
- The risk in business language (unauthorized disclosure of 340,000 policyholder records), not just CVE text.
- The current exposure with the numbers: 12 percent annual likelihood, USD 4.5M impact, USD 540,000 ALE, and the recalculated residual after WAF effectiveness is discounted.
- The controls in place, their tested effectiveness, and how effectiveness is being measured (blocked-attempt counts, rule hit telemetry, a follow-up test date).
- The acceptance criteria and the explicit statement that residual risk exceeds, meets, or falls below appetite.
- The accepting authority. This is the crux: acceptance must come from someone with authority over the loss, normally the business owner of the claims portal acting with the board risk committee's delegated limit. The CISO advises, quantifies, and recommends. A security team accepting risk it does not own is a documentation failure, not a control.
- An expiry date tied to the rebuild milestone, review triggers (a second finding, a change to the portal, a new regulator guidance), and the escalation path if the milestone slips.
- Linkage to the register entry and to the insurance notification, so the underwriter's conditions are not breached silently.