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.

⚠️

You may need to add <xml> to the beginning and </xml> to the end of your XML file if it is not being 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. 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.

The first question can be solved by counting the number of rows of data from either csv file.

The second question can be solved using the “requests” spreadsheet, sorting the column for the order total, and then obtaining the transaction ID from the corresponding response for that request.

The third question can be solved using the “requests” spreadsheet by right clicking 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

Questions

How many transactions are contained in the log?

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

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

Which state made the greatest number of purchases?

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

Payments