CRSC2014 Guestbook Backend

readme.md

The backend of CRSC2014 guestbook for Yu-Ren Pan! Using PHP(>5.3), SQLite3. With a little tweak, you can use PDO to rewrite it so that you can run it in PHP 5.1.

By Andy Pan H1296. Have fun!

crsc2014.gbapi.php

<?php
  //Comment for easier reading
   
  // Override default SQLite constructor to
  // open the file at first.
  class MyDB extends SQLite3{
    function __construct(){
      $this->open('dbstore/gb.sqlite');
    }
  }
  
  //Email regex for validation
  $regex_email="/^[a-z0-9!#$%&'*+\\/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+\\/=?^_`{|}~-]+)*"
    ."@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?$/i";
  
  //Auth!
  //You can arbitally set this value
  // but do not expose it
  $PASS_SALT="madebyandy1296";
  //The most important part! The password
  // shown is 'banana' 
  $PASS_SHA1="198df1ceb81e0f70943b0da0fcf68f2b845b060a";

  $isAdmin=isset($_POST["pass"])&&sha1($_POST["pass"].$PASS_SALT)===$PASS_SHA1;

  function normalizeNL($str){
    return preg_replace("/\R/u", "\n", $str);
  }

  function makeFlaggedMessage($data){
    return array(
      "no"=>$data["no"],
      "flagged"=>true,
      "comment"=>"管理員已經刪除此訊息！",
      "timestamp"=>$data["timestamp"]
    );
  }

  //Send header so that the content will be recognized as JSON.
  header( 'Content-type: application/json' );
  $db = new MyDB();
  $action=$_GET["action"];    

  function writeData(){
    global $db,$data,$regex_email,$info,$isAdmin;

    //Truncate the incoming data
    $_POST["name"]=substr($_POST["name"],0,24);
    $_POST["comment"]=substr($_POST["comment"],0,1024);
    //normalize newline characters
    $_POST["comment"]=normalizeNL($_POST["comment"]);

    $stmt=$db->prepare("insert into book (`category`,`name`,`email`,`comment`,`related`,`by_admin`)"
      ." values (?,?,?,?,?,?)");
    
    if(!$_POST["name"]||!$_POST["comment"])
      return "Some parameters are missing!";

    if($_POST["email"]){
      $vali=preg_match($regex_email,$_POST["email"]);
      if($vali===0){
        return "Email is not valid!";
      }else if(!$vali){
        return "Error occured while validating email.";
      }
    }

    if($_POST["related"]){
      $stmt_count=$db->prepare("select category from book where no=?");
      $stmt_count->bindParam(1,$_POST["related"],SQLITE3_TEXT);
      $relcat=$stmt_count->execute()->fetchArray(SQLITE3_NUM);
      if(!$relcat)
        return "Related post does not exist.";
      else if($relcat[0]=="reply")
        return "Nested replies are banned.";
    }

    $cat=$_POST["related"]?"reply":"comment";
    $byAdmin=$isAdmin?1:NULL;
    if($byAdmin)
      $info["by_admin"]=true;

    //bind param as follows
    $stmt->bindParam(1,$cat,SQLITE3_TEXT);
    $stmt->bindParam(2,$_POST["name"],SQLITE3_TEXT);
    $stmt->bindParam(3,$_POST["email"],SQLITE3_TEXT);
    $stmt->bindParam(4,$_POST["comment"],SQLITE3_TEXT);
    $stmt->bindParam(5,$_POST["related"],SQLITE3_TEXT);
    $stmt->bindParam(6,$byAdmin,SQLITE3_INTEGER);
    
    //id and timestamp will automatically be added,
    // so we don't need to worry about them.
    $stmt->execute();
    return false;
  }
   
  $et=microtime(true);

  //array works like dict in python
  $rtn=array();
  $info=array();
  $data=array();
 
  if($action=="write"){
    $result=writeData();
    if(!$result){
      $rtn["status"]="ok";
      $rtn["msg"]="Data is successfully saved.";
    }else{
      $rtn["status"]="error";
      $rtn["msg"]=$result;
    }
    
  }else if($action=="read"){
    $info["status"]="ok";
    $result=$db->query("select `no`,`name`, `flagged`, `email`, `comment`, `by_admin`, datetime(`timestamp`,'+8 hours') as `timestamp` from book order by `no` desc");
    while($arr=$result->fetchArray(SQLITE3_ASSOC)){
      $arr["comment"]=normalizeNL($arr["comment"]);
      $data[]=$arr["flagged"]?makeFlaggedMessage($arr):$arr;
      //must ensure that the related data does exist!!!!
    }
  }else if($action=="hide"){
    if(!$isAdmin){
      $rtn["status"]="error";
      $rtn["msg"]="Auth failed. This can only be done by administrators.";
    }else{
      $stmt=$db->prepare("update book set `flagged`= (`flagged` is null or(not `flagged`)) where no=?");
      if(isset($_POST["comment"])){
        $stmt->bindParam(1,$_POST["comment"],SQLITE3_TEXT);
        $stmt->execute();
        if($affect_rows=$db->changes()){
          $rtn["status"]="ok";
          $info["affect_rows"]=$affect_rows;
        }else{
          $rtn["status"]="error";
          $rtn["msg"]="No record found.";
        }
      }else{
        $rtn["status"]="error";
        $rtn["msg"]="Invalid parameters.";
      }
    }
  }else{
    //not supported action
    $rtn["status"]="error";
    $rtn["msg"]="Specified action is not supported!";
  }
  $info["elapsed-time"]=round((microtime(true)-$et)*1000,3);
 
  $rtn["info"]=$info;
  $rtn["data"]=$data;
 
  print json_encode($rtn);
  //Do not leave anything beyond the end of the tag!
?>