How to log mysql_query into mysql db

[Hobbyist_PHPer]

MySQL Version: 5.0.91-community

 

Hi, I have never tried to do this before, and never really thought of the implications of it... but I need to log the queries made in my application, but as you can imagine, when you try to insert a query into the database, it throws it for a loop... From the output of the query, I imagine that it resembles what I have always heard about, injection attacks... Now I know... LOL...

 

So how do I overcome this? I'm clueless, I searched and found references to slow log or something, but I don't know if that is really what I need or not...

 

Here's an example bit from one of the queries:

$query = "DELETE FROM table_name WHERE FileID = '$_SESSION[FileID]' AND theVariableID = '$_GET[theVariableID ]'";
mysql_query($query);
mysql_query("INSERT INTO AccessLogs VALUES ('','$_SESSION[userID]','$_SESSION[FileID]','$CurrentDateTimeStamp','$query','$_SERVER[REMOTE_ADDR]'");

 

Thanks in advance for any help you can provide...

[requinix]

Why do you need to store the actual SQL itself? What good could that possibly do that storing a log of actions cannot?

[Hobbyist_PHPer]

I just figured it would be easier to store the query than trying to create a table that has to try to store every bit of information from every type of query done on every other type of table within the database...

[requinix]

But do you really have to log every single query? Including SELECTs?

 

If not then you're only really logging UPDATEs, INSERTs, and DELETEs. Actions that modify data. There's still a good way to do this.

Have a table with fields for

- The identifier of whatever's being updated or inserted

- The type of thing (which could just be a string like "user" or "file")

- The action (like "update" or "insert")

- Normal logging stuff (active user, date/time of action, etc)

- Serialized data

 

As in

mysql_query(sprintf("DELETE FROM table_name WHERE FileID = %d AND theVariableID = %d",
$_SESSION["FileID"],
$_GET["theVariableID"]
));

mysql_query(sprintf("INSERT INTO AccessLogs (user, userAddr, date, itemID, itemType, itemAction, data) VALUES (%d, '%s', NOW(), %d, 'file', 'delete', '%s')",
$_SESSION["UserID"],
$_SERVER["REMOTE_ADDR"],
$_SESSION["FileID"],
mysql_real_escape_string(serialize(array("theVariableID" => $_GET["theVariableID"])))
));

With the data normalized you can easily search it.

 

Even if you wanted to log SELECTs too you can still do this: itemID=null, itemAction="select", and data=serialize(whatever conditions you're searching on).

[mikosiko]

even when requinix is giving you some manual option to log activity on your DB, should be a lot easier just to enable the "query log" in your Mysql instance instead of write code yourself.

 

how?... this is an option:

 

- Stop your Mysql Instance

- Edit your my.ini (or whatever name your mysql init file has) and add this line:

    log=<path & name of the login file>  p.e: log=Sql-LoginFile  or log="C:/logs/Sql-LoginFile" (if under Windows)

 

- Re-start your Mysql Instance.

 

this assuming that you have access to your mysql ini file

[Hobbyist_PHPer]

even when requinix is giving you some manual option to log activity on your DB, should be a lot easier just to enable the "query log" in your Mysql instance instead of write code yourself.

 

how?... this is an option:

 

- Stop your Mysql Instance

- Edit your my.ini (or whatever name your mysql init file has) and add this line:

    log=<path & name of the login file>  p.e: log=Sql-LoginFile  or log="C:/logs/Sql-LoginFile" (if under Windows)

 

- Re-start your Mysql Instance.

 

this assuming that you have access to your mysql ini file

 

Are there any security issues related to using this method, e.g., more vulnerable to sql injection attacks?

[Hobbyist_PHPer]

But do you really have to log every single query? Including SELECTs?

 

If not then you're only really logging UPDATEs, INSERTs, and DELETEs. Actions that modify data. There's still a good way to do this.

Have a table with fields for

- The identifier of whatever's being updated or inserted

- The type of thing (which could just be a string like "user" or "file")

- The action (like "update" or "insert")

- Normal logging stuff (active user, date/time of action, etc)

- Serialized data

 

As in

mysql_query(sprintf("DELETE FROM table_name WHERE FileID = %d AND theVariableID = %d",
$_SESSION["FileID"],
$_GET["theVariableID"]
));

mysql_query(sprintf("INSERT INTO AccessLogs (user, userAddr, date, itemID, itemType, itemAction, data) VALUES (%d, '%s', NOW(), %d, 'file', 'delete', '%s')",
$_SESSION["UserID"],
$_SERVER["REMOTE_ADDR"],
$_SESSION["FileID"],
mysql_real_escape_string(serialize(array("theVariableID" => $_GET["theVariableID"])))
));

With the data normalized you can easily search it.

 

Even if you wanted to log SELECTs too you can still do this: itemID=null, itemAction="select", and data=serialize(whatever conditions you're searching on).

 

Thanks requinix, I'll use this solution if I can't modify my mysql.ini file or if there are greater security concerns with that method...

[mikosiko]

even when requinix is giving you some manual option to log activity on your DB, should be a lot easier just to enable the "query log" in your Mysql instance instead of write code yourself.

 

how?... this is an option:

 

- Stop your Mysql Instance

- Edit your my.ini (or whatever name your mysql init file has) and add this line:

    log=<path & name of the login file>  p.e: log=Sql-LoginFile  or log="C:/logs/Sql-LoginFile" (if under Windows)

 

- Re-start your Mysql Instance.

 

this assuming that you have access to your mysql ini file

 

Are there any security issues related to using this method, e.g., more vulnerable to sql injection attacks?

 

This is just a logging method; security concerns must be addressed before the query reach the DB