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:
IP character string used as the Primary index. In our case we decided to use only three dotted decimal elements (essentially a Class C base) of the IP address giving a maximum field length of 11 characters (xxx.xxx.xxx). In our experience spammers tend to be able to move quickly within a Class C range. It could penalise some users but the chances were judged to be very low contrasted with the ability to quickly shut down the maximum amount of abuse.
COUNT an integer holding the count of messages sent by IP. Users are allowed to send up to ALLOW_MAIL (currently 3) all subsequent email is rejected but the count is reset after 24 hours (to trivialise the code this when the date changes rather than a real 24 hours). If users exceed MAX_MAIL (currently 10) then the user is permanently banned from using the feature, that is, the count is never reset.
DATE is an integer comprising the sum of 'yday' (day of the year) and 'year' obtained from the PHP function getdate(). This does not give unique values (the same number is generated the following year - 1 day) but makes date comparison a trivial operation and is more than adequate for our application. It is a good enough implementation and avoids unnecessary complexity. The worst downside is that someone could become banned unnecessarily if they send the maximum number of messages once per year one day earlier - three years in a row. We won't lose sleep over that possibility.
Spam Prevention - check_ip function
The code below implements the checks to implement our policy, it has reasonable comments throughout (maybe):
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).
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.
To suppress warnings (lots) from the SQLite3 object the check_ip function is called with the @ thus $result = @check_ip($ip, $date);.
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:
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).
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.
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.
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.
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.
