mail us  |  mail this page

contact us
training  | 
tech stuff  | 

Tech Stuff - PHP SQLite3 Web Editor and Spam Preventer

For simple persistent data storage with no maintenance and non-specialized backup procedures it takes a lot to beat SQLite. A superb and well thought out implementation. We always forget SQL so remembering its syntax can be a real pain - but is a trivial overhead.

We needed to keep track of how many times in any given period (24 hours in our case) users had sent emails. When certain thresholds were reached we reject further attempts but if the number is low we reset their allowance after 24 hours, if the user persists (typical from automated zombies) then we place the user into a 'banned' category and refuse all susbsequent requests. Simply put, we needed persistence of data that a simple database provides with minimal learning overheads - SQLite was the obvious implementation of choice. Since the orginal application had been written in PHP and this addition would represent a relatively small enhancement it made sense to implement using PHP SQLite. We also needed some simple web tools to allow maintenance (view, add, update and delete) of the database. Again PHP was the choice.

Health-Warning: This is made available at your risk, we make no warranty that the code has no bugs in it or that it will even do what we think or say or even say what we think it will. Like all downloaded software we urge caution if you use it.

SQLite Database Specification

In order to accomplish our goals we only need three fields in the database:

Spam Prevention - check_ip function

The code below implements the checks to implement our policy, it has reasonable comments throughout (maybe):

  1. From PHP 5.3 SQLite3 interface is the only one supported (though we could have used the PHP Data Object (PDO) which does support SQLite - but it looked too complicated).

  2. Documentation indicates the database will be created if it does not exist. Technically this is correct but it does not indicate that if an empty file, with the correct write permissions for the web server, does not exist the instatiation of SQLite3 will throw an exception.

  3. To suppress warnings (lots) from the SQLite3 object the check_ip function is called with the @ thus $result = @check_ip($ip, $date);.

  4. There is no trivial way to check if any SELECT query returns 0 or > 0 results. We used a crude counting technique to keep things simple. We could have use a specific SELECT query to return a count, but that would involve another transaction and we would have to have written more code. Lazy is good.

define("ALLOWED_MAIL",3);
define("MAX_MAIL",10);
define("BANNED_MAIL",999);
function check_ip($ip, $date){
// $ip is three decimal dotted ip address which counts
// the class C address space (256 addresses) as one
// $date is the sum of the year and the 'yday' from a getdate() function
// user can be restored if less than MAX_MAIL in 1 day period
// user is allowed ALLOW_MAIL in 24 hour period
// if user has ever sent more than MAX_MAIL in 1 day period they are permanently banned
// if $s (return) is non-empty mail is rejected
  $s = "";
  if(file_exists('/path/to/db/ips.sq3')){
    // file must exist with acceptable write permissions for web server
    // else instantiation of SQLite3 throws an exception
    $db = new SQLite3('/usr/local/www/zytrax/db/ips.sq3');
    if(!$db){
      $s = $db->lastErrorMsg();
    }else{
      // checks to see if the table exists by reading the count of entries
      // from the sqlite_master table for the table IPS if != 0 table exists
      $sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='IPS';";
      $ret = $db->querySingle($sql);
      if($ret == 0){
        // table does not exist create and initialize with known bad guys
        // IF NOT EXISTS is superfluous but does no harm
        $sql =<<<EOF
        CREATE TABLE IF NOT EXISTS IPS
        (IP CHAR(11) PRIMARY KEY     NOT NULL,
        COUNT            INT     NOT NULL,
        DATE        INT        NOT NULL);
EOF;
        $ret = $db->exec($sql);
        // load permanently blocked IPs (from previous injection problems)
        $sql =<<<EOF
        INSERT INTO IPS (IP,COUNT, DATE)
        VALUES ('xxx.xx.xxx',999,999);
  			
        INSERT INTO IPS (IP,COUNT, DATE)
        VALUES ('112.198.64',999,999);
EOF;
        $ret = $db->exec($sql);
        if(!$ret){
          $s = $db->lastErrorMsg();
        }
      }
      // DB set up in all case - now check for this ip
      $sql = "SELECT * from IPS where IP='".$ip."';";
      $ret = $db->query($sql);
      if(!$ret){
        $s = $db->lastErrorMsg();
      }else{
        $c = 0;  // this is crude programming but there is no simple way to determine 
                 // if any records were returned until after the fact
        while($row = $ret->fetchArray()){
          $c++;
          $count = $row['COUNT'] + 1;  // compensate for current transaction
          $s = "Rejected too many";
          if($row['DATE'] != BANNED_MAIL){
            if($row['DATE'] != $date){  // must be earlier date
              if($count < MAX_MAIL){
                // reset count and date - allow mail
                $s = "";
                $sql = "UPDATE IPS SET COUNT = 1, DATE = ".$date." WHERE IP ='".$ip."';";
                $res = $db->exec($sql);
                if(!$res){
                  $s = $db->lastErrorMsg();
                }
              }
            }else{  // current date - update count - all cases
              $sql = "UPDATE IPS SET COUNT = ".$count." WHERE IP ='".$ip."';";
                $res = $db->exec($sql);
              if(!$res){
                $s = $db->lastErrorMsg();
              }else{
                if($count <= ALLOWED_MAIL){
                  // - allow mail
                  $s = "";
                }else if($count == MAX_MAIL) {
                  // trigger email - disallow mail
                  $s = "IP=".$ip." (too many attempts in 24 hours)";
                }
              }
            }
          }
        } // while
        if($c == 0){  // no records returned because while loop not activated
          // add new ip range, set count to 1 with current date and allow mail
          $sql = "INSERT INTO IPS (IP, COUNT, DATE) VALUES('".$ip."',1,".$date.");";
          $ret = $db->exec($sql);
          if(!$ret){
            $s = $db->lastErrorMsg();
          }
        }
      }
    }
    $db->close;
  }else{ // prevents exception and triggers admin notification
    $s = "Problem: IPS /path/to/db/ips.sq3 does not exist";
  }
  return $s;
}

Web Based SQLite3 Maintenance

We use a simple form based interface shown below. We have disabled the send button in this one but it has a commented-out line that you would change to reflect wherever you run your php scripts from use 'view source' from your browser to inspect form.

SQLite3 Web Processor

Manipulates (View/Update/Add/Delete) data from the ips.sq3 (SQLite3) database containing a list of all IPs (class C) that have used the 'mail this page' service and the number of messages sent. This database is used by mailthispage.php to control the allowed number of mail items (max 5 per day) that can be sent from any class C IP address range. IPs can be permanently banned by setting DATE field to BANNED_MAIL (currently 999 which can never exist as a valid date). If an IP attempts to send more than MAX_MAIL (currently 10 per day) then it can never use 'mail this page' again and an email is sent to local admin to indicate it has been banned (and should be added to the list of banned IPs loaded by default). The database consists of three fields: IP: Primary index of 11 chars (3 dotted decimal strings defining a class C base), COUNT: Integer count of number of mail attempts in 24 hour period or the total attempts over all time if MAX_MAIL is exceeded, DATE: integer consisting of the sum of the day number and the year (today's day number is shown in Current DATE field for info).

If IP: is blank then clicking SQLite Exec will display all the current records in the database (values of View/Update/Add/Delete and any values in COUNT: and DATE: are ignored). If IP: contains a 3 dotted decimal value (class c base) then:

  1. If View is selected (default) and SQLite Exec is clicked only this entry will be displayed (any values in COUNT: and DATE: will be ignored).

  2. If Update is selected and SQLite Exec is clicked this entry will be be updated using the values in either or both of COUNT: and DATE:. Blank entries in either field will be left unchanged but if both are blank the Update operation will result in an error message.

  3. If Add is selected and SQLite Exec is clicked this entry will be be added using the values in COUNT: and DATE:. Blank entries in either field will result in an error message.

  4. If Delete is selected and SQLite Exec is clicked only this entry will be deleted (any values in COUNT: and DATE: will be ignored). No confirmation is requested for the delete operation. Use with care.

IP:
COUNT:
DATE:
  View Update Add Delete

PHP Module

The PHP called from the above form is shown below. Pretty gruesome, but simple and does the job. Few if any comments - so what's new.

<?php
$e = 0;
$t = 1;	// set default view all
// variables $iip, $icount, $idate and $it are all passed in from the form
// validate and confirm operation requested
$it = $_POST['it'];
$iip = $_POST['iip'];
$icount = $_POST['icount'];
$idate = $_POST['idate'];
if($iip == ""){
  echo "<p>Read all records in ips.sq3 SQLite3 database.</p>";
}else{
  switch($it){
  case "v":
    // logically this test is superfluous
    if($iip != ""){
      echo "<p>Read ".$iip." record only in ips.sq3 SQLite3 database.</p>";
      $t = 2;
    }
    break;
  case "d":
    if($iip == ""){
      $e = 1;
      echo "<p><b>Error:</b> Requested record deletion but IP defined not defined.</p>";
    }else{
      echo "<p>Delete ".$iip." record from ips.sq3 SQLite3 database.</p>";
      $t = 3;
    }
    break;
  case "a":
    if($iip == "" or $icount == "" or $idate == ""){
      $e = 1;
      echo "<p><b>Error:</b> Requested record add but one or more of IP, COUNT, DATE have not been defined.</p>";
    }else{
      $t = 4;
      echo "<p>Add IP=".$iip." COUNT=".$icount." DATE=".$idate." record to ips.sq3 SQLite3 database.</p>";
    }
    break;
  case "u":
    if($iip == ""){
      $e = 1;
      echo "<p><b>Error:</b> Requested record update but IP not defined.</p>";
    }else if($icount == "" and $idate == ""){
      $e = 1;
      echo "<p><b>Error:</b> Requested record update but neither COUNT nor DATE have been defined (at least one required).</p>";
    }else{
      $t = 5;
      $ch = $icount;
      if($icount == ""){
        $ch = "unchanged";
      }
      $dt = $idate;
      if($idate == ""){
        $dt = "unchanged";
      }
      echo "<p>Update IP=".$iip." COUNT=".$ch." DATE=".$dt." record in ips.sq3 SQLite3 database.</p>";
    }
    break;
  }
}
// if no errors execute the operation
if($e == 0){
  $db = new SQLite3('/path/to/db/ips.sq3');
  switch($t){  // $t is set up from validation above
  case 1:  // view all
    $sql = "SELECT * FROM IPS;";
    $ret = $db->query($sql);
    if(!$ret){
      echo $db->lastErrorMsg();
    }else{
      $c = 0;
      while($row = $ret->fetchArray()){
        echo "<p>IP=".$row['IP']." COUNT=".$row['COUNT']." DATE=".$row['DATE']."</p>";
        $c++;
      }
      if($c == 0){
        echo "<p>No Records found</p>";
      }else{
        echo "<p>".$c." record(s) displayed</p>";
      }
    }
    break;
  case 2: // view 1 record
    $sql = "SELECT * FROM IPS where IP='".$iip."';";
    $ret = $db->query($sql);
    if(!$ret){
      echo $db->lastErrorMsg();
    }else{
      $c = 0;
      while($row = $ret->fetchArray()){
        echo "<p>IP=".$row['IP']." COUNT=".$row['COUNT']." DATE=".$row['DATE']."</p>";
        $c++;
      }
      if($c == 0){
        echo "No Record found";
      }
    }
    break;
  case 3:	// delete 1 record
    $sql = "DELETE FROM IPS where IP='".$iip."';";
    $ret = $db->exec($sql);
    if($ret === true){
      if($db->changes() == 1){
        echo "Record deleted.";
      }else{
        echo "No record found.";
      }
    }else{
      echo "Delete failed.";
    }
    break;
  case 4:	// add 1 record
    $sql = "INSERT INTO IPS (IP, COUNT, DATE) VALUES ('".$iip."',".$icount.",".$idate.");";
    $ret = $db->exec($sql);
    if($ret === true){
      if($db->changes() == 1){
        echo "Record Added.";
      }else{
        echo "No record added.";
      }  
    }else{
      echo "Add Failed.";
    }
    break;
  case 5: // update 1 record
    $sql = "UPDATE IPS SET ";
    if($icount != ""){
      $sql .= "COUNT=".$icount;
      if($idate != ""){
        $sql .= ",DATE=".idate;
      }
    }else{
      $sql .= "DATE=".$idate;
    }
    $sql.= " where IP='".$iip."';";
    $ret = $db->exec($sql);
    if(!$ret){
      echo $db->lastErrorMsg();
    }else{
      echo "Record updated.";
    }
    break;
  }
  $db->close();
}else{  //validation failures all go here
  echo "<p><b>No action performed</b></p>";
}
?>


Problems, comments, suggestions, corrections (including broken links) or something to add? Please take the time from a busy life to 'mail us' (at top of screen), the webmaster (below) or info-support at zytrax. You will have a warm inner glow for the rest of the day.

Tech Stuff

RSS Feed Icon

If you are happy it's OK - but your browser is giving a less than optimal experience on our site. You could, at no charge, upgrade to a W3C standards compliant browser such as Firefox

Search

web zytrax.com

Share

Icons made by Icomoon from www.flaticon.com is licensed by CC 3.0 BY
share page via facebook tweet this page

Page

email us Send to a friend feature print this page Display full width page Decrease font size Increase font size

Resources

HTML Stuff

W3C HTML 4.01
HTML5 (WHATWG)
HTML4 vs HTML5
HTML5 Reference
W3C Page Validator
W3C DOCTYPE

CSS Stuff

W3C CSS2.1
W3C CSS2.2
Default Styles
CSS3 Selectors
CSS 3 Media Queries
CSS 3 Colors

DOM Stuff

W3C DOM
W3C DOM 3 Core
W3C 3 Events

Accessibility

usability.gov
W3C - WAI
Web Style Guide
WebAim.org

Useful Stuff

Peter-Paul Koch
A List Apart
Eric Meyer on CSS
glish.com

Our Stuff

Our DOM Pages
DOM Navigation
Liquid Layout
CSS Short Cuts
CSS overview
CSS One Page

Javascript Stuff

ECMA-262

Site

CSS Technology SPF Record Conformant Domain
Copyright © 1994 - 2017 ZyTrax, Inc.
All rights reserved. Legal and Privacy
site by zytrax
Hosted by super.net.sg
web-master at zytrax
Page modified: October 21 2015.