#!/usr/bin/env perl
# MySQL Query Stats
# Useful for tracking down problematic queries.
# Currently in beta, please only use this if you know its function.
#
# Maintainer: Cruz Cranford
# Support: ccranford@hostgator.com


# Add:
# Add link to the adminwiki https://adminwiki.hostgator.com/Mqs
# Create --help output.
# add Show user column options to view queries that hit the rules for $column;
# if no bad say no bad
# Change output to look more mysqlie


##  BUGS:
# cp: cannot stat `/var/lib/mysql/mysql/general_log.CSV': No such file or directory
# This is due to general_log being a non csv format.
# mysql> alter table mysql.general_log ENGINE = CSV;
#
# Slow Log ( disabled until we can find some use )
# DBD::mysql::db do failed: Table 'mysql.slow_log' doesn't exist at /root/bin/mqs line 234, <USERFILE> line 115.
# DBD::mysql::db do failed: Table 'mysql.slow_log' doesn't exist at /root/bin/mqs line 234, <USERFILE> line 115.
#
# Check for general_log table existance
# If not create it
# DBD::mysql::db do failed: Table 'mysql.general_log' doesn't exist at /root/bin/mqs line 233, <USERFILE> line 115.
# DBD::mysql::db do failed: Table 'mysql.general_log' doesn't exist at /root/bin/mqs line 233, <USERFILE
# CREATE TABLE `general_log` (
#  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
#  `user_host` mediumtext NOT NULL,
#  `thread_id` int(11) NOT NULL,
#  `server_id` int(10) unsigned NOT NULL,
#  `command_type` varchar(64) NOT NULL,
#  `argument` mediumtext NOT NULL
# ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
#
# Need to equate for disconnections after scan.
# disconnect after enabling shit and reconnect when disabling
#DBD::mysql::db do failed: MySQL server has gone away at /root/bin/mqs line 247, <STDIN> line 3.
#dBD::mysql::db do failed: MySQL server has gone away at /root/bin/mqs line 247, <STDIN> line 3.
#
# Log checking needs to check for no username being passed
#[?] Select Mode [ help, bad, sort, log, quit ]:  log
#[!] Output done: /home//.1307717242.log
#
# Check for false positives on rand*(
# root@lumina [~]# mqs -f /home/kplusk/kplusk.1308074736.log




#use strict;
#use warnings;
use DBI;
use Term::ANSIColor qw(:constants);
use Text::CSV;
use Getopt::Std;
use Scalar::Util qw(looks_like_number);
use MIME::Base64;

# Get options
my %options=();
getopts("o:d:f:r:u:s:l:e:j:", \%options );

if(defined($options{d})){ $duration = $options{d}; }else{ $duration = 10; }
if(defined($options{u})){ $username = $options{u}; }
if(defined($options{f})){ $file = "$options{f}"; }else{ $file = "/var/lib/mysql/mysql/general_log.CSV"; }
if(defined($options{r})){ $total_rows_for_bad = $options{r}; }else{ $total_rows_for_bad = 100000; }
if(defined($options{s})){ $sort_by = $options{s}; }else{ $sort_by = 'totalrows'; }
if(defined($options{l})){ $output_limit = $options{l}; $output_i = 1; }else{ $output_limit = 20; $output_i = 1; }
if(defined($options{o})){ $short_output = $options{o}; }
if(defined($options{j})){ $indygen = "on"; }
my $line = "[-] --------------------------------------------------------------------------------------------------------\n";

# Get Password and Connect
my $db = DBI->connect("DBI:mysql:database=mysql;host=localhost;mysql_read_default_file=/root/.my.cnf", undef, undef, {'RaiseError' => 1});

if($short_output !~ m/off/){
        version_check();
        header();
}

#$before_user_count = "select count(user) from user_statistics";
#$get_before_stats = "select user,select_commands,update_commands,cpu_time,rows_fetched,rows_updated,table_rows_read from USER_STATISTICS";
#$get_after_stats = "select user,select_commands,update_commands,cpu_time,rows_fetched,rows_updated,table_rows_read from USER_STATISTICS";
#$after_user_count = "select count(user) from user_statistics";

if(!defined($options{f})){
        check_and_see_if_someones_running_me();
        $SIG{INT} = \&gtfo;
        user_list_create();
        check_for_ischema_user_stats();
        clear_and_start_logging();
        if($short_output !~ m/off/){
                print "[.] Gathering queries...\n";
        }
	$db->disconnect();
        for(1..$duration){ sleep 1; }
	$db = DBI->connect("DBI:mysql:database=$init_hash{$k};host=localhost;mysql_read_default_file=/root/.my.cnf",undef, undef,{ 'PrintError' => 0 });
        stop_logging();
}

if(defined($file)){
        if($short_output !~ m/off/){
                print "[*] Reading File...\n";
        }
        my $csv = Text::CSV->new();

        open (CSV, "<", $file) or die $!;
        while (<CSV>) {
                if($csv->parse($_)){($date, $dbuser, $threadid, $serverid, $cmdtype, $command) = $csv->fields();}else{next;}
                chomp($command);
                if($cmdtype =~ m/Query/i){
                        if(!defined($connect_hash{$threadid})){
                                @test_user_find = split(/\[/,$dbuser);
                                chomp($test_user_find);
                                $connect_hash{$threadid} = $test_user_find[0];
                        }
                        if(exists($thread_hash{$threadid})){
                                push(@$threadid, $command);
                        }else{
                                $thread_hash{$threadid} = \@$threadid;
                        }
                        $total_queries++;
                }elsif($cmdtype =~ m/Connect/i){
                       $dbuser =~ s/\"//g;
                       if($options{'version'} >= 5.5){
                               $dbuser =~ s/\[//g;
                               $db_user = (split /\]/, $dbuser)[0];
                       }else{
                               $db_user = (split /\[/, $dbuser)[0];
                       }
                       if(!defined($db_user[0])){ $connect_hash{$threadid} = $dbuser; }else{ $connect_hash{$threadid} = $db_user[0];}
                }elsif($cmdtype =~ m/Init DB/i){
                        $command =~ s/\"//g;
                        chomp($command);
                        $init_hash{$threadid} = $command;
                }
        }
        foreach $k (sort keys %thread_hash) {
                $ttq = 0; $trq = 0; $totalrows = 0; $ttt = 0; $tfs = 0; $tc = 0; $tw = 0;
                # If the thread does not have a valid database it will skip processing
                if(!defined($init_hash{$k})){ next; }
                foreach (@{$thread_hash{$k}}) {
                        if($init_hash{$k} =~ m/Access denied/i){ next; }
                        if($_ =~ m/^select\ /i){
                                $_ =~ s/\n//ig;
                                $explain_query = 'explain ' . $_;
                                $explain_query =~ s/\\r//g;
                                $explain_query =~ s/\\n//g;
                                $explain_query =~ s/\\\"/\"/g;
                                my $db = DBI->connect("DBI:mysql:database=$init_hash{$k};host=localhost;mysql_read_default_file=/root/.my.cnf",undef, undef,{ 'PrintError' => 0 });
                                my @explain_output = $db->selectrow_array("$explain_query");
                                $row_total = $row_total + $explain_output[8];
                                $totalrows = $totalrows + $explain_output[8];
                                if($explain_output[9] =~ m/using temporary/i){ $tmp_total++; $ttt++; }
                                if($explain_output[9] =~ m/using filesort/i){ $tfs++; }
                                if($explain_output[8] > $total_rows_for_bad){
                                        $tbfq++;
                                        if($explain_output[9] =~ m/using temporary/i){ $query_output = $explain_output[10]; $tmp_total++; $ttt++; }
                                        $total_bad{$tbfq} = ();
                                        $total_bad->{$tbfq}->{'database_name'} = $init_hash{$k};
                                        $total_bad->{$tbfq}->{'rows'} = $explain_output[8];
                                        $total_bad->{$tbfq}->{'extra'} = $explain_output[9];
                                        $total_bad->{$tbfq}->{'key'} = $explain_output[5];
                                        $explain_query =~ s/^explain //;
                                        $explain_query =~ s/;$//;
                                        $total_bad->{$tbfq}->{'query'} = $explain_query;
                                        $extra_length = length $explain_output[9];
                                        $key_length = length $explain_output[5];
                                        $dbname_length = length $init_hash{$k};
                                        if($dbname_length > $dbnl){ $dbnl = $dbname_length; }
                                        if($key_length > $kl){ $kl = $key_length; }
                                        if($extra_length > $el){ $el = $extra_length;}
                                }
                        }
                        if($_ =~ m/rand(.*)/i){ $trq++; $rand_total++;  }
                        $ttq++;
                        $total_queries++;
                }

                @username = split(/_/,$connect_hash{$k});
                $username = $username[0];
                $total_stuff{$username} = ();
                $total_stuff->{$username}->{'queries'} += $ttq;
                $total_stuff->{$username}->{'connections'} += $tc;
                $total_stuff->{$username}->{'tw'} += $tw;
                $total_stuff->{$username}->{'random'} += $trq;
                $total_stuff->{$username}->{'tmptable'} += $ttt;
                $total_stuff->{$username}->{'filesort'} += $tfs;
                $total_stuff->{$username}->{'totalrows'} += $totalrows;
                $tid++;

        }
        foreach(%total_stuff){
                if(defined($_)){
                        push @output_sort, { hashname => "$_", sortby => $total_stuff->{$_}->{"$sort_by"}};
               }
        }
        @output_sorted = sort { $b->{'sortby'} <=> $a->{'sortby'} } @output_sort;
        if($short_output !~ m/off/){
                print_results(@output_sorted);
        }
        close LOG_IN;
        my $notexit = 0;
        if($short_output !~ m/off/){
                $notexit = 1;
        }else{
                my $bad_user;
                if(defined($options{u})){
                        $bad_user = $options{u};
                }
                output_bad_user_queries($bad_user);
        }
        while ($notexit) {
                print "[?] Select Mode [ help, bad, sort, log, quit ]:  ";
                my $select_mode = <STDIN>;

                if ($select_mode =~ /^sort/) {
                        ($mode, $sort_by) = split(/ /, $select_mode);
                        @output_sort = ();
                        @output_sorted = ();
                        chomp($sort_by);
                        my @shaunsoutput;
                        foreach my $key (keys %total_stuff){
                                push @shaunsoutput, { hashname => "$key", sortby => $total_stuff->{$key}->{"$sort_by"}};
                        }
                        @output_sorted = sort { $b->{'sortby'} <=> $a->{'sortby'} } @shaunsoutput;
                        print_results(@output_sorted);
                }

                if ($select_mode =~ /^quit/) {
                        $notexit = 0;
                        next;
                }

                if ($select_mode =~ /^bad/){
                        ($mode, $bad_user) = split(/ /, $select_mode);
                        output_bad_user_queries($bad_user);
                }

                if ($select_mode =~ /log/) {
                        ($mode, $user_to_log) = split(/ /, $select_mode);
                        chomp($user_to_log);
                        if(!defined($user_to_log)){
                                print "[!] WARNING: No user provided!  ( Syntax: log username )\n$line";
                                next;
                        }else{
                                write_user_queries_to_a_file($user_to_log, $file);
                        }
                        print $line;
                }

                if ($select_mode =~ /help/){
                        print $line;
                        help_shit();
                        print $line;
                }
        }
        gtfo();
}

sub header{
        print "$line\[!] MySQL Query Stats\n";
}

sub version_check{
        my @mysql = $db->selectrow_array("SHOW VARIABLES like 'version'");
        my @version = split(/\./, $mysql[1]);
        my $ver = $version[0] . "." . $version[1];
       $options{'version'} = $ver;
        if ($ver < 5.1){
                print "This script requires 5.1 or higher to run.";
                exit;
        }
}

sub check_for_ischema_user_stats{
        my @check_is_user_stats = $db->selectrow_array("show tables from information_schema where Tables_in_information_schema = 'USER_STATISTICS'");
        if($short_output !~ m/off/){
                if($check_is_user_stats[0] =~ 'USER_STATISTICS'){
                        print "[*] MySQL user_statistics available.\n";
                }else{
                        print "[!] No information_schema.user_statistics found, using explains\n";
                }
        }
}

sub clear_and_start_logging{
        if($short_output !~ m/off/){
                print "[*] Clearing current table and enabling logging. \n";
        }
        $db->do("truncate general_log");
        $db->do("truncate slow_log");
        $db->do("SET GLOBAL log_output = 'TABLE'");
        $db->do("SET GLOBAL general_log = 'ON'");
        $db->do("set GLOBAL slow_query_log = 'ON'");
        $db->do("set GLOBAL long_query_time = 2");
}

sub stop_logging{
        $db->do("SET GLOBAL general_log = 'OFF'");
        $db->do("set GLOBAL slow_query_log = 'OFF'");
}

sub clear_and_stop_logging{
        $db->do("SET GLOBAL general_log = 'OFF'");
        $db->do("SET GLOBAL log_output = 'TABLE'");
        $db->do("set GLOBAL slow_query_log = 'OFF'");
        $db->do("set GLOBAL long_query_time = 10");
        $db->do("truncate general_log");
        $db->do("truncate slow_log");
}

sub help_shit{
        print "$line";
        print "[?] mqs help\n";
        print "[?] bad: show all queries that are searching over $total_rows_for_bad rows.\n";
        print "[?] sort: change the sort options by column name.\n";
        print "[?] log: log query output to a file.\n";
        print "[?] quit: exit.\n";
        print "$line";
}

sub print_results{
        my @passed_array = @_;
        if(defined($options{l})){ $output_limit = $options{l}; $output_i = 1; }else{ $output_limit = 20; $output_i = 1; }
        print $line;
        printf("[-] %-12s | %10s | %12s | %8s | %8s | %12s | %8s | %10s |\n", "username", "queries", "connections", "random", "tmptable", "filesort", "totalrows");
        print $line;
        foreach(@passed_array){
                if($output_i <= $output_limit){
                        my $k = $_->{'hashname'};
                        printf("[-] %-12s | %10s | %12s | %8s | %8s | %12s | %8s | %10s |\n", "$k", $total_stuff->{$k}->{'queries'}, $total_stuff->{$k}->{'connections'}, $total_stuff->{$k}->{'random'}, $total_stuff->{$k}->{'tmptable'}, $total_stuff->{$k}->{'filesort'}, $total_stuff->{$k}->{'totalrows'});
                        $output_i++;
                }else{ last; }
        }
        print $line;
}

sub gtfo{
        if($short_output !~ m/off/){
                print "[!] Stopping logging and clearing data\n";
        }
        unlink("$mqs_lock");
        clear_and_stop_logging();
        if($short_output !~ m/off/){
                print "[!] Finished!\n\n";
        }
        exit;
}

sub write_user_queries_to_a_file{
        # Get user id and group id
        ($user, $mysql_file) = @_;
        chomp($user);
        $uid = getpwnam($user);
        $gid = getgrnam($user);

        my $legit_user = 0;
        foreach(@usernames){
                if($_ =~ m/$user/i){ $legit_user = 1; }
        }

        if( $legit_user == 1 ){
        my $output_file = "/home/$user/$user.".time().".log";

        open (F, $mysql_file) || die ("Could not open $mysql_file!");
        open(LOGOUT, ">>$output_file");

        while ($line = <F>){
                ($date,$dbuser,$threadid,$serverid,$cmdtype,$output) = split ',', $line;
                if($dbuser =~ m/^\"$user\_.*/i){
                        print LOGOUT $line
                }
        }
        close (LOGOUT);
        close (F);
        chown($uid,$gid, "$output_file");
        print "[!] Output done: $output_file \n";
        }else{
                print "[!] WARNING :: $user is not a valid user.\n";
        }
}

sub output_bad_user_queries{
        ($bad_user_check) = @_;
        chomp($bad_user_check);
        if($short_output !~ m/off/){
                print $line;
                printf("[-] \%$dbnl\s | %12s | \%$kl\s | \%-". $el ."s | %12s \n", "Database Name", "Rows", "Index Used", "Functions", "Query");
                print $line;
        }
        my @bad_sort;
        foreach(%total_bad){
                if(defined($_)){
                        push @bad_sort, { thread => "$_", rows => $total_bad->{$_}->{'rows'}};
                }
        }
        my @bad_sorted = sort { $b->{rows} <=> $a->{rows} } @bad_sort;
        foreach(@bad_sorted){
                my $k = $_->{thread};
                if(defined($bad_user_check)){
                        ($total_bad_user, $null) = split(/_/,$total_bad->{$k}->{'database_name'});
                        if($total_bad_user =~ $bad_user_check){
                                if($short_output !~ m/off/){
                                        printf("[-] \%$dbnl\s | %12s | \%$kl\s | \%-$el\s | %12s \n", "$total_bad->{$k}->{'database_name'}", "$total_bad->{$k}->{'rows'}", "$total_bad->{$k}->{'key'}" , "$total_bad->{$k}->{'extra'}", "$total_bad->{$k}->{'query'}");
                                }else{
                                        print "$total_bad->{$k}->{'database_name'} ::: $total_bad->{$k}->{'query'}\n";
                                        if($indygen =~ m/on/){
                                                $base64_query = encode_base64($total_bad->{$k}->{'query'}, ':::::');
                                                system("indygen --database=$total_bad->{$k}->{'database_name'}  --mqs --query=$base64_query");
                                                #print "indygen --database=$total_bad->{$k}->{'database_name'} --mqs --query=$base64_query \n"
                                        }else{
                                                print "$total_bad->{$k}->{'database_name'} ::: $total_bad->{$k}->{'query'}\n";
                                        }
                                }
                        }
                }else{
                        if($short_output !~ m/off/){
                                printf("[-] \%$dbnl\s | %12s | \%$kl\s | \%-$el\s | %12s \n", "$total_bad->{$k}->{'database_name'}", "$total_bad->{$k}->{'rows'}", "$total_bad->{$k}->{'key'}" , "$total_bad->{$k}->{'extra'}", "$total_bad->{$k}->{'query'}");
                        }else{
                                if($indygen =~ m/on/){
                                        $base64_query = encode_base64($total_bad->{$k}->{'query'}, ':::::');
                                        #print "indygen --database=$total_bad->{$k}->{'database_name'} --mqs  --query=$base64_query \n";
                                        system("indygen --database=$total_bad->{$k}->{'database_name'} --mqs --query=$base64_query");
                                }else{
                                        print "$total_bad->{$k}->{'database_name'} ::: $total_bad->{$k}->{'query'}\n";
                                }
                        }
                }
        }
        if($short_output !~ m/off/){
                print "$line\n";
        }
}

sub check_and_see_if_someones_running_me{
        $mqs_lock = "/tmp/mqs.lock";
        if(-e $mqs_lock){ print "\n\n[!] mqs may already running, $mqs_lock exists. \n\n"; exit;}
        open(mqs_lock, ">$mqs_lock");close(mqs_lock);
}

sub user_list_create{
        if($short_output !~ m/off/){
                print "[*] Creating userlist. \n";
        }
        $user_file = '/etc/trueuserdomains';
        open (USERFILE, "<", $user_file) or die $!;
        while (<USERFILE>){
                push(@usernames, $_);
        }
        $total = scalar(@usernames);
}
