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
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 =) |