Ajax-Powered Error Logs


Update: Check out the new and improved Ajax Error Log Version 2.0!

[ Ajax Error Log - File Structure ]
As an obsessive website administrator, I like to keep a keen eye on my error logs. Once a week, I download my PHP, 404, and other error logs, and analyze them as closely as time allows. Monitoring site errors and other traffic patterns leads to many of the security-related articles I post here atPerishable Press, including resources such as the 5G Blacklist, Ultimate HTAccess Blacklist, and the Blacklist Candidate Series. Easily, one of the best ways to protect your site is to understand the different types of errors that are happening behind the scenes. This is why monitoring your error logs is so important. In this article, you’ll learn how to make it a little easier and more dynamic with MySQL, PHP, and the magic of Ajax.

Overview

I tend to over-explain things, so here is an overview of this tutorial:
  • What we’re doing
  • How is this useful?
  • Demonstration
  • Implementation
    • Step 1: Create the database table
    • Step 2: Create the custom error page
    • Step 3: Setup the 404 redirect
    • Step 4: Verify functionality
    • Step 5: Create the dynamic log script
    • Step 6: Create the dynamic log page
  • Download the files
  • Thrilling conclusion

Dynamic Error Monitoring with Ajax

For the past couple of years, I have wanted to develop a way to easily monitor my sites’ errors in “real time.” My idea is to use Ajax and jQuery to display different types of errors dynamically on the web page. If you think of a typical Ajax chat script, where the page is refreshed at short intervals to display new entries, you’ve got a good idea of what I’m doing here. The process goes something like this:
  1. 404 errors are redirected to a custom PHP script
  2. The script then logs the different errors into a database
  3. The contents of the database are displayed on a web page
  4. jQuery/Ajax is used to refresh the page at short intervals
Is this for everyone? Probably not, but if you’re into logging visitor information, recording errors in a database, and keeping an eye on your site in general, you’ll probably benefit from the technique. Keep in mind that the tutorial focuses on setting up and putting things together, so I don’t spend a lot of time getting into the technical aspects of what the actual code is doing. There’s actually a lot of neat stuff happening behind the scenes, such as looking up geographical IP data, encoding image content for the database, and displaying results dynamically with Ajax.

How is this useful?


[ Ajax: Asynchronous Javascript and XML ]
By examining static error logs, we’re catching problemsafter they happen. In my mind, it would be better to have the ability to keep an eye on things in real time and resolve issues as they happen. For example, Perishable Press isfrequently scanned for vulnerabilities by teh evil scriptz, but the Ajax Error Log enables me to take immediate action and protect my site. Plus, catching 404 errors helps you spotlegitimate errors for things that should exist, enabling a more responsive maintenance strategy.
I honestly don’t think any of this is really necessary, but more like an enhancement over boring static log files.
Sure, you can’t sit there and just watch your error log all day, but you can keep a tab open and keep an eye on things while you’re working online. For many of us, that’s nearly all the time, but even if you’re only watching your errors for a few minutes a day, you’re still going to benefit from observing errors as they happen. Even that much will help you better understand what’s happening (or not happening) on your server, which is the key to good security.
Now that I’ve got this script working for Perishable Press, I like to open a browser window, resize it so it fits in the corner of my screen, and then just sort of keep an eye on things as I work. It’s extremely satisfying stopping the bad guys dead in their tracks. It’s good times ;)

Demonstration

This may all sound rather abstract, so here is a Live Demo of my Ajax Error Log technique. The Demo displays the 404 errors just moments after they occur at my test site, bluefeed.net. The site gets very little traffic, so if no new errors are appearing, try the following to watch the Dynamic Error Log do its thing:
  1. Open the Demo in new window (not tab), and position it next to the current window (so you can see both at the same time).
  2. Then trigger a 404 error by clicking on this link: http://bluefeed.net/404 *
  3. Watch the 404 error automagically appear on the Demo page!
* Note: Please only click the error link once or twice. Obviously, I’m heavy into monitoring things, so if you don’t want to get banned, please click only a few times to get the idea. Then if you like the technique and want to continue experimenting, follow the tutorial and set it up at your own site. Thanks in advance for being cool :)
While we’re on the subject, it’s also important to note that you SHOULD NOT display your error log to the general public. This technique is designed for use by a single user from a secure andprivate web page. Making it public reveals potentially sensitive information and increases the load on your server.

Implementation

Here are the three files you will need to implement your own dynamic error log:
  • 404.php – contains the PHP script that logs the errors
  • AjaxErrorLog.php – connects to the database and displays its contents
  • AjaxErrorLog.html – contains the HTML & jQuery used to display the results
We’ll create and configure each of these files in this tutorial, but you can also just download the files and follow along that way.
In addition to these 3 files, you’ll need to create a table in your MySQL database. This can either be an existing database or a new one.

Step 1: Create the database table

Once you’ve got the database in place, execute the following SQL query (via phpMyAdmin or similar):
CREATE TABLE `error_log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `logtime` varchar(200) NOT NULL default '',
  `request` varchar(200) NOT NULL default '',
  `referer` varchar(200) NOT NULL default '',
  `qstring` varchar(200) NOT NULL default '',
  `address` varchar(200) NOT NULL default '',
  `agent` varchar(200) NOT NULL default '',
  `error` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`),
  cur_timestamp TIMESTAMP(8)
) AUTO_INCREMENT = 1
That should create a table named “error_log” in your database. This is where we will be storing the data collected from our 404.php file, which serves as our site’s actual “404-Error” page that also logs each 404 error into the database.

Step 2: Create the custom error page

Create a blank file named 404.php, and fill it with the following PHP code:
<?php
/*
Dynamic Error Logging by Jeff Starr @ Perishable Press
Project URL: http://perishablepress.com/ajax-error-log/
License: GNU General Public License
Version: 1.1
*/

// database credentials
$hostname = "hostname";
$database = "database";
$username = "username";
$password = "password";

// site URL (no trailing slash!)
$website = "http://example.com";

// send proper headers for 404
header("HTTP/1.1 404 Not Found");
header("Status: 404 Not Found");

// set some server variables
$logtime = date("F jS Y, h:ia", time() - 10800);
$request = $site . $_SERVER['REQUEST_URI'];
$referer = $_SERVER['HTTP_REFERER'];
$qstring = $_SERVER['QUERY_STRING'];
$address = $_SERVER['REMOTE_ADDR'];
$agent   = $_SERVER['HTTP_USER_AGENT'];
$error   = "404"; // for other errors

// connect to database
$dbcon = mysql_connect($hostname, $username, $password)
 or die("Couldn't connect to SQL Server on $dbhost");
$selected = mysql_select_db($database, $dbcon)
 or die("Couldn't open database $database");

// sanitize server variables
$logtime_esc = mysql_real_escape_string($logtime);
$request_esc = mysql_real_escape_string($request);
$referer_esc = mysql_real_escape_string($referer);
$qstring_esc = mysql_real_escape_string($qstring);
$address_esc = mysql_real_escape_string($address);
$agent_esc   = mysql_real_escape_string($agent);

// insert record and close database
$query  = "INSERT INTO `error_log` (logtime,request,referer,qstring,address,agent,error) ";
$query .= "VALUES ('$logtime_esc','$request_esc','$referer_esc','$qstring_esc','$address_esc','$agent_esc','$error')";
mysql_query($query);
mysql_close($dbcon);

// and finally output the html
?><html>
 <head><title>404 - Not Found</title></head>
 <body id="error-404">
  <div class="left">
   <div class="post">
    <h1>Error 404 &mdash; Not Found</h1>
    <p>The requested resource was not found on this server.</p>
    <div class="content">
     <p><?php echo "Requested URL: " . $website . $request_esc; ?></p>
    </div>
   </div>
  </div>
 </body>
</html>
This script basically declares some server variables and then sanitizes them before recording them in the database. It also send the correct headers and performs a Geographical IP Lookup to capture key attributes of the request. Thanks to hostip.info for their awesome lookup service, and also to this forum thread for the help with forwarded URLs.
For the 404.php file to work, you’ll need to edit the first four variables – $hostname, $database,$username, and $password – to enable the script to connect to your database. Once the file is configured and in place, the server needs to know to use it when responding to 404 errors. We set this up in the next step..

Step 3: Setup the 404 redirect

The next step is to instruct the server to use the custom 404.php file instead of the default error page. With Apache, this is possible with a single line added to your site’s root .htaccess file:
ErrorDocument 404 /ajax-error-log/404.php
..and then edit the path to match the file’s actual location on your server. Once that’s in place on your server, it’s time to check that things are working.

Step 4: Verify functionality

Once you get the 404.php file uploaded and the redirect set up, trigger a few 404 errors on your site and check the results. For each error, you should see the custom 404 page displayed in your browser and a recorded entry in your error_log table. Give it a few spins and ensure that everything is working. You will probably want to customize the appearance of your new error page to fit your site’s design (or not). It’s pretty basic by default, but there’s a ton of cool things you can do. To help with customizing, check out my post on pimping out your 404 error page.

Step 5: Create the dynamic log script

At this point, you have something very useful: a custom 404 page that records all 404 errors in the database. Now let’s get dynamic with it and display the information in “real-time” on a web page. Create a file called AjaxErrorLog.php and insert the following PHP code:
<?php
/*
Dynamic Error Logging by Jeff Starr @ Perishable Press
Project URL: http://perishablepress.com/ajax-error-log/
License: GNU General Public License
Version: 1.1
*/

// database credentials
$hostname = "hostname";
$database = "database";
$username = "username";
$password = "password";

// site URL (no trailing slash!)
$website = "http://example.com";

// connect to server
$dbhandle = mysql_connect($hostname, $username, $password)
 or die('Could not connect to SQL Server on $hostname');

// select the database
$selected = mysql_select_db($database, $dbhandle)
 or die('Could not open database $database');

// create the sql query
$query = 'SELECT id,logtime,request,referer,qstring,address,agent,error
 FROM error_log ORDER BY id DESC LIMIT 50'; // defaults to 50 entries

// execute query and return records
$result = mysql_query($query);
$number = mysql_num_rows($result);

// display the results
while($row = mysql_fetch_array($result)) {
 echo '<div class="log-entry"><pre>';
 echo 'Error: '.htmlspecialchars($row['error']);
 echo ' - '.htmlspecialchars($row['logtime'])."\n";
 echo 'Request: '.$website.htmlspecialchars($row['request'])."\n";
 echo 'User Agent: '.htmlspecialchars($row['agent'])."\n";
 echo 'IP Address: '.htmlspecialchars($row['address']);
 echo '</pre></div>';
}

//close db connection
mysql_close($dbhandle);
?>
Let’s see what’s happening with this script. First we specify our database credentials, which should be the same as those specified in the 404.php file. Using this information, the script then connects to the server (or dies trying) and selects the specified database. From there, it creates and executes an SQL query and returns the newest ten results (configurable). It then uses that information to output chunks of HTML markup to the web page. Finally, the database connection is closed.
After configuring the AjaxErrorLog.php file, it’s time to bring it all together by including the output in an actual web page and displaying the log results dynamically via jQuery-flavored Ajax.

Step 6: Create the dynamic log page

Finally, create a file named “AjaxErrorLog.html” and insert the following code:
<!DOCTYPE HTML>
<html>
 <head>
  <title>Ajax Error Log</title>
  <!-- Ajax Error Log @ http://perishablepress.com/ajax-error-log/ -->
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <style>
   pre {
    font: 10px/1.5 Courier, "Courier New", mono;
    background-color: #efefef; border: 1px solid #ccc;
    width: 700px; margin: 7px; padding: 10px;
    white-space: pre-wrap;
    }
  </style>
  <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>
  <script>
   $(document).ready(function() {
    $("#results").load("AjaxErrorLog.php");
    var refreshId = setInterval(function() {
     $("#results").load("AjaxErrorLog.php").fadeIn("slow");
    }, 2000); // refresh time (default = 2000 ms = 2 seconds)
   });
  </script>
 </head>
 <body>
  <noscript><div id="response"><h1>JavaScript is required for this demo.</h1></div></noscript>
  <div id="results"></div>
 </body>
</html>
This is the page that will display the results dynamically in a browser. It’s a basic web page that uses a little jQuery/Ajax magic to load the output of the AjaxErrorLog.php file and refresh the results every two seconds (as specified in the code as “2000”). Feel free to use any refresh interval you wish, just keep in mind that these are server requests, so don’t go too crazy. ;)
We’re also using some CSS to add a little style to the markup. Currently, the styling is pretty minimal, so you’ll probably want to customize things to suit your own design. Also note that, for the sake of simplicity, we are using Google’s ajax.googleapis.com for the jQuery script.
Once you get all the files configured and in place, open the AjaxErrorLog.html file in your browser and watch the results scroll across your screen. If the database doesn’t contain any errors yet, go ahead and make up some crazy URLs and request them in another tab. Things should be working quite beautifully.

Download the files

Ajax Error Log – Version 2.0 (4 kB zip)
Changelog:
  • v1.1: Replaced mysql_escape_string with mysql_real_escape_string
  • v1.1: Added htmlspecialchars to escape output SQL
  • v1.1: Added $website variable for better accuracy
  • v1.1: Changed the default number of displayed results to 50

With a glass eye on you..

So that’s how to build a Dynamic Error Log using PHP, HTML, CSS, and jQuery. The Ajax Error Log that we build in this tutorial is pretty basic, but it’s also fairly easy to customize once you understand how everything works. To see an example of the possibilities, check out my highly customized version.
I would eventually like to expand the functionality of this Dynamic Error Log and translate it into a WordPress plugin. Until then, I want to share the technique and get as much feedback as possible. If you have any ideas for improving the security, functionality, or usefulness of the technique, please let me know, either in the comments or via email. As always, Thank you for reading Perishable Press =)