Database compromised (SQL-injection): how to handle the situaton, after the fact?

Question

Situation

We found out that one of our (MySQL) databases has been compromised through SQL-injection. The injection vector was a query that was vulnerable to SQL-injection due to string-concatenation in its WHERE clause.

The MySQL-user for the web application in question ran with fairly limited privileges (SELECT, DELETE, UPDATE, INSERT) on a single database along with the information_schema database. The database-server is only accessible from whitelisted machines (no unrestricted internet access).

As far as we can see, the attacker performed all interaction with our database through HTTP GET requests to the vulnerable web application. We have secured the webserver's access and error logs which clearly show all attempted queries. The unfortunate thing is that the attacker had access to our database for approximately 3 days. As such, I think we must assume that our whole database has been mapped and most data of interest has been downloaded by the attacker.

At the time of discovery the attack was still ongoing; it has been stopped and the vulnerable WHERE clause has been replaced by a prepared statement placeholder (as it should have been all along). Given that the attack was still ongoing, I think I can assume they did not yet manage to get all data. After the vulnerability got fixed, we rotated the 'passwords'¹ for all users that had access to this database server.

The data

The database in question is the datastore for a webshop. There is no payment information in the database (all payment transactions are handled by a (thirdpary) payment provider) and user passwords have been hashed with BCrypt with a quite decent workfactor.
The database does contain customer orders (along with address information), products, stock, etc.

Questions

1. The hole has been plugged, what technical² steps should be taken now (other than rotating the MySQL user credentials, which we have already done)?

2. Since we have the log files with all query attempts, it should be possible to figure out exactly what tables have been dumped.
   What is a viable tool for pulling this information from a combined 1 GB of webserver access and error log files?

3. What are the common malicious uses of stolen data such as this? Phishing attacks aimed at the webshop's customers?

---

_{¹: 32 character long, printable ASCII, random strings.
²: There is definitely a judicial, communications and PR part to this issue, but this question focusses solely on the technical part of the incident.}

Answer 1

1. The hole has been plugged, what technical steps should be taken now (other than rotating the MySQL user credentials, which we have already done)?

Audit the website. Preferably by someone outside the company if you have the budget. The search term you're looking for is "pentest". Furthermore, if your application displays any data stored in the database directly onto the website, check if the attacker opportunistically put some stored XSS in there. (Javascript to steal cleartext credentials for instance)

2. Since we have the log files with all query attempts, it should be possible to figure out exactly what tables have been dumped. What is a viable tool for pulling this information from a combined 1 GB of webserver access and error log files?

I'm not aware of such tool, sorry. You could always try and do it manually. Try and use any kind of scripting language (bash and command prompt will do for this) to recreate the executed queries from the log files. From there you can easily figure out what tables were accessed, it would be a lot of work to get more details in this matter though.

If you do this, look for insert and update statements, to see if he modified or added data as well.

3. What are the common malicious uses of stolen data such as this? Phishing attacks aimed at the webshop's customers?

It can be used for a few things. I'm assuming the list also contains email adresses? (webshops often do)

The attacker could try and use your data to compromise other accounts on other services. he could:

• try and crack the passwords, and use these on other websites. Were the passwords salted? If not, he can try to use rainbow tables.
• Use the information of your website to try and recover accounts on other websites without cracking the password. By using recovery questions.
• Use the email addresses(if you stored them) for spam purposes. He can also just sell the email list.
• phishing like you mentioned yourself

Answer 2

The only thing I can say regarding the matter is how to avoid this again. Changing the SQL database user information would be one small step, but just escaping the data before entering it into the database or even just the query is essential. This can be done in about 5 lines via a function that you could reuse for every input.

For example, in PHP, it would look something like this:

$con = In this example, I'm saying this is the connection.

function escape($string) {
  global $con;
  return mysqli_real_escape_string($con, $string);
}

Then, before even entering it into the database, for example grabbing a variable from a URL via a get method, you would do something like this:

$data = escape($_GET['data']);

Then if you were to insert "$data" into the database, you would be sure it's safe from SQL injection with the escape method.

I understand you may not be using PHP, but I just wanted to get the point across how easy it is to protect yourself from injections in just a few lines.

It's really that simple to stay safe from SQL injections.

Cheers.