SQL injection in a payroll reporting export
HR and payroll software
A payroll platform held salary, tax and bank details for hundreds of employers. We were asked whether a reporting export could be made to return data the caller was never entitled to.
Web & API penetration test, Payroll platform, severity CRITICAL, reported 2023
At a glance
- Challenge. A payroll reporting export built its query by pasting a user-supplied filter straight into SQL, so the filter could rewrite the query.
- Approach. We treated each report parameter as query input and confirmed a value that changed the result set in a way only a query change could explain.
- Result. Queries were moved to parameterised statements and the reporting database account was scoped down, then the surface was retested.
- Impact. A caller could read payroll tables outside the report, including pay runs for every employer, tax identifiers and the accounts wages are sent to.
- Classification. CWE-89 Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection'), OWASP Top 10 (2021): A03 Injection. Component: Payroll reporting export.
The challenge
A report filter is a question the user asks the database. When the answer is built by pasting the question straight into the query, the user is no longer asking a question, they are writing part of the query. The boundary between data and instruction has gone.
A single unparameterised filter is enough. It does not matter that every other query on the platform is safe, because the database does not grade on a curve. One reachable statement that concatenates input gives a caller the ability to read tables that have nothing to do with reporting, and on many engines to reach beyond the database entirely.
On a payroll system the tables that have nothing to do with reporting are the ones that matter most: pay runs for every employer, tax identifiers, and the account numbers wages are sent to.
Our approach
We treated each report parameter as a place where the query could be steered rather than answered, and confirmed the effect with a value that changed the result set in a way only a query change could explain, using two accounts so the before and after was unambiguous.
Around that single result we checked the surface that shared the assumption:
- Whether other export and search parameters were parameterised or concatenated
- Whether the reporting database account was scoped to reporting, or could read the whole schema
- Whether error responses returned query detail that made extraction faster
- Whether tenant ownership was enforced on the rows returned, not only on the request
Delivered with the exact parameter values and the resulting responses, so each finding could be reproduced against a test tenant without taking our word for it.
From finding to fix
- Treated each report filter as query input
- Steered a filter into a query change
- Read payroll tables outside the report
- Parameterised the queries, retested
The outcome
The reporting queries were moved to parameterised statements so input can no longer alter the query, and the reporting database account was scoped down so that even a missed query cannot read outside the data reporting needs. Error detail was removed from responses.
We retested the reporting surface and the neighbouring exports after the fix. Reported as CRITICAL in 2023. Retest included in the engagement.
Check your own system
- Replace every concatenated query with a parameterised statement, so input can never change the structure of the query.
- Scope the reporting database account to the tables reporting needs, so a missed query cannot read the whole schema.
- Strip query detail and stack traces from error responses, so a caller cannot use them to extract data faster.
- Confirm tenant ownership is enforced on the rows returned, not only on the request that asked for them.
Read more case studies, see Web Application Penetration Testing, or write to hello@yaamlabs.com.