Prompt

A payment transaction log was compromised in a data breach. Help us determine what information was stolen.

payments.log786.6KB

Walk-Through

This challenge involves parsing a log file from a SOAP web server. SOAP is used to send messages using the Extensible Markup Language (XML). Solving this challenge requires a mechanism to parse the XML and query the underlying data. This can be done by extracting the relevant XML from the log file, converting the XML to CSV format, and then using a spreadsheet editor to query the data.

Extracting the XML

There are many debug lines within this log file; however, the only relevant lines for this scenario are the ones that begin with PPAPIService: Request: and PPAPIService: Response:. These lines appear to contain the plaintext payment data that are being sent to/from the server. A regular expression can quickly match these lines and extract the raw XML to be saved as a separate file.

sed -nr 's/PPAPIService: Request: (.*)/\1/p' payments.log > requests.xml
sed -nr 's/PPAPIService: Response: <\?.*\?>(.*)/\1/p' payments.log > responses.xml

Parses the entries for the requests and responses and saves them into separate files. The regex for the responses also removes the starting XML tag (which is not present in the requests) from each response to avoid confusing the XML to CSV parser.

Get-Content payments.log | ForEach-Object {if ($_ -match 'PPAPIService: Request: (.*)') {$matches[1] | Out-File -Append requests.xml}
}

Get-Content payments.log | ForEach-Object {if ($_ -match 'PPAPIService: Response: <\?.*\?>(.*)') {$matches[1] | Out-File -Append responses.xml}
}

Here are Powershell commands for the same extraction.

⚠️

You will need to add <xml> to the beginning and </xml> to the end of your XML file so it can be recognized properly by your CSV converter.

Converting the XML to CSV

Once you have extracted the payment requests into a separate file, you can now convert the XML into a CSV (or .xlsx file). There are various tools that can do this conversion, such as convertcsv.

Using a Spreadsheet Editor

Any common spreadsheet editor should provide tools that can be used to answer the questions from the prompt.

To determine how many transactions occurred, count the number of rows of data from either csv file.

To determine the largest purchase made in the log and its associated transaction ID, use the “requests” spreadsheet. Sort the column for the order total (column ‘F’) with the largest transaction at the top. Then unsort and find the row number for the highest value.

Use the row number to find the corresponding response in the “responses” spreadsheet. Look for the transaction ID (column ‘Z’).

To determine which state had the greatest number of purchases, use the “requests” spreadsheet. Right click on the column header for the state of the ship-to address and selecting “Column Stats”.

Column stats will display the most common unique values and their frequency

Useful tools:

convertcsv.com

Questions

1. How many transactions are contained in the log?

Count the number of lines that start with PPAPIService: Request:

2. What is the transaction ID of the largest purchase made in the log?

Sort the requests by the order total column to find the largest purchase, then get the transaction ID from the corresponding response

3. Which state made the greatest number of purchases?

Get a count of the unique values for the state of the ship-to address

Payments