Wednesday March 29, 2017

MySQL Logging to MySQL

Author: Wayne Eggert
Date: 03/14/05
Difficulty: Novice

Introduction
This tutorial explains a straightfoward way of logging MySQL queries to a MySQL table. Why would you want to do this? Well, one possiblity is for tracking queries that were run in your application and which users ran them. This is especially useful if you have a backend with multiple users and want to keep track of who caused a system meltdown =)

Create the Database Table

CREATE TABLE `systemlog` ( `UID` int(11) NOT NULL auto_increment, `username` varchar(255) NOT NULL default '', `sqlcommand` text NOT NULL, `page` varchar(255) NOT NULL default '', `ipaddress` varchar(255) NOT NULL default '', `dateTime` varchar(14) NOT NULL default '', PRIMARY KEY (`UID`) ) TYPE=MyISAM;

Function
Now add the function below to a global include file or create a new file called functions.php to include in every file that you want to perform SQL query logging.

<?
function mysql_execute_query($sql){
    global 
$PHP_SELF;
    global 
$HTTP_SESSION_VARS;
    global 
$HTTP_SERVER_VARS;

    
$pagename basename($PHP_SELF);
    
$username $HTTP_SESSION_VARS['username'];
    
$ip $HTTP_SERVER_VARS['REMOTE_ADDR'];

    
$sqlcheck substr(strtoupper($sql),0,6);

    
$cleansql addslashes($sql);
    
// log ip address, username, query
    
if($sqlcheck != 'SELECT'){
        
$log_sql "INSERT INTO systemlog (username,sqlcommand,page,ipaddress,dateTime)
VALUES ('"
.$username."','".$cleansql."','".$pagename."','".$ip."','".date("Ymdhis",time())."')";
        
mysql_query($log_sql);
    }

    return 
mysql_query($sql);
}
?>

Explanation:

  • The first few lines allow the function to access some global system variables.
  • The file name, user name & ip address are stored in variables.
  • $sqlcheck grabs the first 6 characters of the SQL query being ran (to determine if it is a SELECT, UPDATE, DELETE, etc)
  • Finally, as long as the query is not a SELECT statement, the query is stored in the database. You may choose to store SELECT statements, but in my case I was only interested in SQL statements that caused data to change.

This function can then be used instead of the "mysql_query()" function as such:

mysql_execute_query($your_sql_query);

That's all there is to it. Happy SQL logging!




Comments:
nice
Posted 12/11/06 6:51AM by Anonymous Techdoser
very nice and very easy solution,
personally i get pagename from HTTP_SERVER_VARS['PATH_INFO'] or HTTP_SERVER_VARS['REQUEST_URI']
meltdown
Posted 03/25/05 4:39AM by Anonymous Techdoser
I remember this one time I had a loop in a script i wrote that caused an infinite query loop resulting in the MySQL server's storage being completely filled. Wow was the system admin guy pissed =)