0

Assuming that I know that one of my applications was attacked by an SQL injection, how can I identify edited or selected data sets?

On obvious source are webserver logs. This should allow us to identify the vulnerability and the injected code. Usually, webserver log only GET requests but no POST requests. This limits the use of webserver logs.

I know that MySQL and other DBMS have different kind of log files, e.g. for slow queries. Are there other places that might help in this case? Are there any settings or adjustments that I should apply beforehand and that would simplify the analysis?

3
  • depends on your logging settings for mysql
    – schroeder
    Commented May 20, 2016 at 20:00
  • 3
    If you can prove that SQL Injection was done successfully, you should consider all data (which the application can access) compromised.
    – lepe
    Commented May 21, 2016 at 5:26
  • "how can I identify edited or selected data sets?" Honestly ? How are we even mean to answer that ? Only you know what "good data" looks like in your context ! Restore from a historical backup, and put in place audit trails for the future. Commented May 22, 2016 at 10:42

1 Answer 1

1

If you already experienced an sql injection attack:

MySQL by default doesn't log all queries[1], as it would impact performance significantly. It's also not enough to just consider the tables your webapplication directly accessed.

  • If you don't have logging or auditing enabled, determine what tables would be considered "high value" in the main database. Think: users, user_creditcards, oauth_keys. Anything that might yield a gain (financial gain, further access, passwords) for your attacker. Those are probably compromised.
  • Also consider any other databases the user used by the webapplication has access to.
  • Use replication? [2] Find the attack period of time in the access logs and inspect the MySQL Binlogs available for that time. This will only give you modifying queries (UPDATE/INSERT/DELETE/CREATE/ALTER/DROP).
  • Is your network monitored? Is the database on a different host and the connection unencrypted? Ask the network security guys if they have any captures of the network traffic. Investigate captures with Wireshark [3].
  • Determine if LOAD DATA INFILE / INTO OUTFILE [4] was available for the database user. If so, you should also further investigate the server hosting the database for further signs of compromise.

If you're trying to design an incident response procedure/defense tactics or config guidelines:

  • Consider using an audit plugin [5]. Determine your high value tables. Audit access to those tables by the relevant users. The resulting log may contain highly sensitive queries, protect accordingly. Implement retention policy.
  • Consider deploying a WAF to atleast catch indications of attacks. See if you can flag an IP as suspicious and logging all its traffic for a period. Again, this generates sensitive data. Protect accordingly.
  • Limit the database user on the webapplication to the absolute minimum it should be able to access. This also means looking at if you can limit the access patterns for the database user: does it have to be able to SELECT * FROM secrets or would it make sense to limit this application user to a stored procedure ala CALL findSecretByIdentifier('MySecretIdentifier');?

Resources:

[1] http://dev.mysql.com/doc/refman/5.7/en/query-log.html

[2] http://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html

[3] https://www.wireshark.org/docs/dfref/m/mysql.html

[4] http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html

[5] https://github.com/mcafee/mysql-audit

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .